You are here

MyISAM Scalability and Innodb, Falcon Benchmarks

MySQL Performance Blog - Fri, 12/10/2007 - 12:01pm

We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that's why we did not escalate MyISAM scalability question. This time working on the customer system we figured out that box with 1 CPU Core is able to handle more queries per second than identical box, but with 4 CPU Cores.

The main query which showed this problem was similar to this:

PLAIN TEXT SQL:
  1. SELECT name FROM t1, t2 WHERE t2.t1_id = t1.id AND t1.stat=1  AND  t2.val = 5 LIMIT 1206,18;
  2. mysql> EXPLAIN  SELECT name FROM t1, t2 WHERE t2.t1_id = t1.id AND t1.stat=1  AND  t2.val = 5 LIMIT 1206,18\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         TABLE: t2
  7.          type: ref
  8. possible_keys: val
  9.           KEY: val
  10.       key_len: 4
  11.           ref: const
  12.          rows: 4092
  13.         Extra:
  14. *************************** 2. row ***************************
  15.            id: 1
  16.   select_type: SIMPLE
  17.         TABLE: t1
  18.          type: eq_ref
  19. possible_keys: PRIMARY,id
  20.           KEY: PRIMARY
  21.       key_len: 4
  22.           ref: scale.t2.t1_id
  23.          rows: 1
  24.         Extra: USING WHERE
  25. 2 rows IN SET (0.00 sec)
  26.  
  27. WHERE
  28. CREATE TABLE `t1` (
  29.   `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  30.   `stat` int(11) UNSIGNED NOT NULL,
  31.   PRIMARY KEY  (`id`),
  32.   KEY `id` (`id`,`stat`)
  33. ) ENGINE=MyISAM;
  34.  
  35. CREATE TABLE `t2` (
  36.   `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  37.   `val` int(11) UNSIGNED NOT NULL,
  38.   `name` varchar(100) NOT NULL,
  39.   `t1_id` int(10) UNSIGNED NOT NULL,
  40.   PRIMARY KEY  (`id`),
  41.   KEY `val` (`val`)
  42. ) ENGINE=MyISAM AUTO_INCREMENT=4097

Table t1 contains about 260,000 records, all with stat=1, and t2 contains 4000 records, all with val=5 and different t1_id. It is surely not smart index structure for such data distribution but good enough for performance gotcha illustration purposes.

The benchmark shows following results for MyISAM using MySQL 5.0.45 run on 4 Core System:

Threads queries/sec 1 161 2 107 4 110 8 121 16 138

As you see running 2-4 threads concurrently we get result by 30% worse than with 1 thread, although it's only simple select query which should be executed without exclusive table locking. Even on 16 threads we're getting performance worse than with single query.

The problem in this case is key buffer contention which unlike popular belief not fully fixed by changes done in MySQL 4.1

As Monty explained us in MySQL 4.1 the change to key cache locking was done so disk IO is not done while lock is held, while lock is still held when key block is copied to processing thread local storage on Key Read Request. This lock is per key cache so if you have contention while multiple indexes are used you can create multiple key caches and map those to them. In this case however single index had most of the load.

This is partially proved by oprofile data (case with 4 threads):

samples  %        app name                 symbol name
2312008  31.3752  libpthread-2.3.4.so      pthread_mutex_lock
2235465  30.3364  no-vmlinux               (no symbols)
723200    9.8142  libpthread-2.3.4.so      pthread_mutex_unlock
237062    3.2171  mysqld                   key_cache_read
215254    2.9211  mysqld                   find_key_block

As you see 40% of effective CPU time is spent in pthread_mutex_lock / pthread_mutex_unlock.

We could not get oprofile call tree to work on this box so we can only guess where these mutex lock requests come from.

A second confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0).

Results for MyISAM with key_buffer_size=0

Threads queries/sec 1 128 2 113 4 193 8 196 16 195

The result for 1 thread is decreased and it is expected, but, funny, we have more queries per second for 4, 8, 16 with disabled key_cache.

The results for 2 threads is however quite unexpected. Though we did not have a time to profile it in more details.
Note however even in this case scalability is far from perfect giving only 1.5 times gain with.

The solution we proposed in this case was converting table t1 into InnoDB, and results:

Threads Queries/sec 1 296 2 341 4 544 8 493 16 498

InnoDB both performs much better in this case (not surprisingly as there is a lot of primary key lookups) but its scalability is not perfect giving less than 2x in peak which happens to be at 4 concurrent threads. So there is still work to do in addition to fixes done in later MySQL 5.0 versions.

We also decided to take a time and see may be brand new Falcon (significantly updated in 6.0.2 release) handles this query:

Threads Queries/sec 1 51 2 79 4 116 8 142 16 164

As you can see Falcon perform extremely poorly when single query executed being 1/3rd of MyISAM and 1/6th of Innodb. On other hand it scales quite nicely as number of threads increase.

The interesting thing is it shows best performance at 16 threads, showing 50% gain from 4 threads - which is quite unexpected for CPU bound load on system with 4 Cores.

Here is comparison of MyISAM Innodb and Falcon results in the graphical form:

2007-10-12_145302.png

I've created bug, for MyISAM key cache contention issue and lets see if there are any plans to have it fixed.

Entry posted by Vadim | No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks