Maria trundles the tokutek challenge

So I saw the tokutek challenge, and wondered to myself how Maria would get along with it.

I watched it closely, for about a day, then got bored and forgot about it. I remembered today that I should take a look!

So I saw the tokutek challenge, and wondered to myself how Maria would get along with it. I duly downloaded a 6.0 tree, and the iiBench code, tinkered with it to make it actually build, and fired things up.

I watched it closely, for about a day, then got bored and forgot about it. I remembered today that I should take a look!

CPU Usage (Quad Core)
CPU Usage (Quad Core)
Average rows per second inserted
Average rows per second inserted
Load Averages
Load Averages

You can see, in just over a day the IO load became too heavy to process efficiently.

MySQL was built like this:

./configure –prefix=/usr/local/mysql –localstatedir=/data0/mysqldata \
–without-query-cache –with-extra-charsets=complex –with-pic –with-fast-mutexes \
–enable-thread-safe-client –with-readline –with-big-tables –with-zlib-dir=bundled \
–with-plugin-maria –with-plugin-partition –with-plugin-perfschema \
–without-innodb –without-falcon –without-ndbcluster –without-archive-storage-engine \
–without-example-storage-engine –without-federated-storage-engine –without-backhole-storage-engine

I tinkered with Maria right from the start though, I wanted to see what a longer checkpoint interval would give, so increased it to every 5 minutes – obviously this doesn’t seem great. 🙂 I also wanted to use the same page size as InnoDB out of morbid curiosity. Here’s the my.cnf:

[mysqld]
basedir = /usr/local/mysql
datadir = /data0/mysqldata
log-error = /usr/local/mysql/logs/error.log

maria-block-size = 16384
maria_log_dir_path = /usr/local/mysql/logs
maria_page_checksum = 0
maria_pagecache_buffer_size = 12G
maria-checkpoint-interval = 300

The tables were defined as ENGINE = Maria – i.e TRANSACTIONAL = 1 (with crash recovery) by default.

I added a new custom graph for MEM, to track how the Maria Page Cache gets utilized:

Maria Page Cache Usage
Maria Page Cache Usage

I’ll be making a couple more for Maria as well – including the easy read and write physcial/logical requests from SHOW GLOBAL STATUS (to be released with MEM once Maria is ready, let me know if you want the custom graphs before hand).

The server is RHEL5, Quad Xeon, with 16G RAM, and a 4 disk 10krpm RAID 10 array for the /data0 mountpoint (although using ext3, along with the noop scheduler). Taking a look at iostat when I came back to it, it’s clear that this was my barrier (well, the io wait in the CPU graph is a pretty good indicator as well eh!):

avg-cpu: %user %nice %system %iowait %steal %idle
1.25 0.00 0.00 23.75 0.00 75.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d1 0.00 7.00 9.00 813.00 288.00 6592.00 8.37 142.09 177.35 1.22 100.10

avg-cpu: %user %nice %system %iowait %steal %idle
4.00 0.00 0.25 20.75 0.00 75.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 125.00 0.00 8.00 0.00 1064.00 133.00 0.05 6.25 4.38 3.50
cciss/c0d1 0.00 4.00 7.00 869.00 224.00 7008.00 8.26 142.00 163.25 1.14 100.10

avg-cpu: %user %nice %system %iowait %steal %idle
0.25 0.00 0.25 24.50 0.00 75.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d1 0.00 0.00 8.00 880.00 256.00 7040.00 8.22 143.06 162.75 1.13 100.10

Maria does not make use of bulk_insert_buffer_size, unfortunately, when TRANSACTIONAL = 1. It does when TRANSACTIONAL = 0 however. It also doesn’t use something like InnoDB’s Insert Buffer, so it’s clear that there is probably some way to go when it comes to bulk inserts within Maria for the TRANSACTIONAL mode.

Maria does support concurrent inserts with TRANSACTIONAL = 1, however this is disabled when the table has an AUTO_INCREMENT, (or FULLTEXT/GIS indexes) – so that makes this benchmark difficult from that respect too.

I’d like to restart this benchmark in a few ways:

TRANSACTIONAL = 0, single threaded, default checkpoint interval and block size, default auto_inc

The IO overhead for the log files (on cciss/c0d0 above) was not huge, so it will be interesting to see how this affects things (I’ll report back). This should show how just the new page cache works out as well.

TRANSACTIONAL = 1, 4 threads, default checkpoint interval and block size, default auto_inc

There’s only 4 CPUs on the system, so this will certainly max those out – but I’d like to see the performance differences with filling the cache as well, with concurrent INSERT still disabled.

TRANSACTIONAL = 1, 4 threads, default checkpoint interval and block size, synthetic auto_inc

This would really show how quickly concurrent inserts could start filling the cache concurrently. Sadly I think once the cache is full we will continue to see issues at least for now though.

Maybe I’ll get to those in my copious free time, but thought I’d at least post some initial findings on a “benchmark gone wrong” for amusement factor. 🙂

1 thought on “Maria trundles the tokutek challenge”

Got something to say?