MySQL Bugs: #31551: MyISAM poor performance with multiple queries running concurrently

Bug #31551	MyISAM poor performance with multiple queries running concurrently
Submitted:	11 Oct 2007 22:30	Modified:	10 Jun 2008 10:35
Reporter:	Vadim TKACHENKO	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.45, 5.0 BK, 5.1 BK	OS:	Linux (CentOS 4.5)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
I observe poor performance of queries running concurrently on 4 CPU box.

This is real situation from production sysem when box with 1 CPU is able to handle more queries / sec than box with 4 CPUs

Query 
SELECT name FROM t1, t2 WHERE t2.id_t1 = t1.id AND t1.stat=1  AND  t2.val = 5 LIMIT 1206,18;

Threads, result (queries per sec, more is better)
1, 161 q/s
2, 107 q/s
4, 110 q/s
8, 121 q/s
16, 138 q/s
 

How to repeat:
 CREATE TABLE `t1` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `stat` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`,`stat`)
) ENGINE=MyISAM;

insert into t1 (stat) values (1);
insert into t1 (stat) select stat from t1;
insert into t1 (stat) select stat from t1;
insert into t1 (stat) select stat from t1;
.. repeat N times to fill 260,000 records

CREATE TABLE `t2` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `val` int(11) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `t1_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `val` (`val`)
) ENGINE=MyISAM AUTO_INCREMENT=4097

insert into t2 (val) values (5);
insert into t2 (val) select val from t2;
insert into t2 (val) select val from t2;
.. repeat N times to fill 4,000 records...

update t2 set t1_id=id;

I used sysbench 0.5 with scripting to script the query above and ran with 1,2,4,8, 16 threads

mysql parameters:
mysqld --key_buffer_size=4G --table-cache=128

Suggested fix:
For me it looks like mutex contetion problem, presumably, key_cache mutex.

The oprofile output in 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

Monty has stated that the key cache mutex contention problem is easy to fix. When will it be fixed?

Thank you for the report.

I can not repeat described behavior in exact way as described. My results similar to:

1 - 45.84
2 - 66.85
4 - 77.55
8 - 75.43
16 - 77.11
32 - 77.99

on 8 CPU machine.

Please provide your configuration file and indicate accurate version of binary you use (or provide configure line if you buile server yourself).

Sveta,
Your numbers don't look that different to me. There is little speedup from 2 to 4 and none after 4.

Mark, thank you for the feedback.

In my environment not only results of queries/per second are different. I also don't get any improvement if I change key_buffere_size to 0 or use InnoDB storage engine.

So technically it is possible to set this bug to "Verified" (as problem exists anyway), but is better to have additional information.

Lua script used for testing:

function thread_init(thread_id)

end

function event(thread_id)
query = "SELECT name FROM t1, t2 WHERE t2.t1_id = t1.id AND t1.stat=1  AND  t2.val = 5 LIMIT 1206,18;"
--db_query(query)
stmt=db_prepare(query)
rs = db_execute(stmt)
db_store_results(rs)
 db_free_results(rs)
end

sysbench is from https://sysbench.svn.sourceforge.net/svnroot/sysbench

I tested on 8 CPU machine.

Is better to see difference if use my-huge.ini:

1 - 53.17
2 - 74.93
4 - 71.75
8 - 29.06
16 - 33.21 
32 - 33.46

The test that I use is:
* create 2 copies of the sysbench table 'sbtest' with 1M rows in each
* run concurrent queries: select count(*) from a, b where a.id = b.id
 (a and b are the tables, id is the PK column)

Thank you for the feedback.

With the test provided I got results similar to initial description:

mysql-5.0 - myisam - key_buffer_size=4G
----
1 - 176.34
2 - 91.17
4 - 91.90
8 - 74.30
16 - 75.89
32 - 79.78
64 - 83.18

mysql-5.0 - myisam - key_buffer_size=0
----
1 - 115.65
2 - 169.22
4 - 200.95
8 - 218.07
16 - 217.99
32 - 217.21
64 - 216.39

mysql-5.0 - innodb - key_buffer_size=4
----
1 - 352.44
2 - 552.22
4 - 753.00 
8 - 877.65
16 - 816.64
32 - 717.24
64 - 630.30

With mysql-5.1 pattern is same although absolute values are much higher:

mysql-5.1 - myisam - key_buffer_size=4
----
1 - 3906.55
2 - 6461.36
4 - 8988.34
8 - 10264.56
16 - 9116.77
32 - 8752.32
64 - 2270.16

mysql-5.1 - myisam - key_buffer_size=0
----
1 - 4177.00
2 - 6713.03
4 - 9820.00
8 - 11051.21
16 - 10091.04
32 - 9993.33
64 - 9573.34

mysql-5.1 - innodb - key_buffer_size=4
----
1 - 4080.30
2 - 6606.27
4 - 9340.55
8 - 10566.74
16 -9370.69 
32 - 9173.52
64 - 2272.78

Description of last test:

1. Create sbtest table with sysbenc-0.4.8 (--test=oltp prepare)
2. Modify sbtest table so it contains only 1000 rows.
3. Copy sbtest table to table a and b
4. Modify query in bug31551.lua to query which Mark provided
5. Run sysbench-0.5 --test=bug31551.lua

I see the same behavior on the Windows Server 2003 Enterprise x64 SP2 platform.

Same on MacOsX 10.5.4 with mysql-5.0.51b-osx10.5-x86_64.

Found a workaround using temp tables.

Is anyone taking care of this?

Bug #45536 was marked as duplicate of this one.

This problem seems to be aggrevated intensely by use of FULLTEXT.

Take the following example:

Table:
CREATE TABLE `t1` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(112) NOT NULL,
  `address` varchar(300) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name_key` (`name`),
  FULLTEXT KEY `address` (`address`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

SHOW TABLE STATUS output:

| t1 | MyISAM |      10 | Dynamic    | 3362366 |             68 |   231372144 | 281474976710655 |    320683008 |         0 |           NULL | 2009-08-15 11:21:57 | 2009-09-03 11:25:12 | 2009-09-03 00:30:11 | latin1_swedish_ci |     NULL |    

Server key_buffer_size is set to 400M and this is the only MyISAM table. Rest of server is entirely idle..

After running..

LOAD INDEX INTO CACHE t1 INDEX (address);

Take the query:

SELECT SQL_NO_CACHE field1 FROM t1 
WHERE MATCH(t1.address) AGAINST('+south +ridge +12345' IN BOOLEAN MODE)

The following test results show the query run by itself, followed by in 3 threads simultaenously.

shell> perl ./test.pl
Parent took 0.35 seconds

MAX: 3
Thread 1 took 4.58 seconds
Thread 2 took 4.67 seconds
Thread 3 took 4.67 seconds
shell> perl ./test.pl
Parent took 0.36 seconds

MAX: 3
Thread 3 took 5.90 seconds
Thread 1 took 5.99 seconds
Thread 2 took 6.01 seconds
shell> perl ./test.pl
Parent took 0.36 seconds

MAX: 3
Thread 3 took 5.24 seconds
Thread 1 took 5.30 seconds
Thread 2 took 5.31 seconds
shell>

A work around for this was to serialize such searches by wrapping the SELECT in GET_LOCK and RELEASE_LOCK functions.

eg.

SELECT GET_LOCK('FTsearch',5);
SELECT <fulltext search>;
SELECT RELEASE_LOCK('FTsearch');

Results for this method were more like 0.37 seconds for single query and 0.37, 0.75, 1.1 seconds for the 3 simultaneously started (forced sequential with locks) queries.

This bug effectively means that one should ensure that any FULLTEXT index has it's own key buffer and that searches utilising that index should be serialised.

It would be interesting to test again with --concurrent_insert=0 and see if that mutex contention is any lower

would also be interesting to benchmark on a recent server after changing/adjusting these defines (described in mf_keycache.cc):

#define SERIALIZED_READ_FROM_CACHE
#define MAX_THREADS   100

http://www.mysqlperformanceblog.com/2008/08/12/beware-of-myisam-key-cache-mutex-contention...