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

Bug #31551	MyISAM poor performance with multiple queries running concurrently
Submitted:	12 Oct 2007 0:30	Modified:	10 Jun 2008 12:35
Reporter:	Vadim Tkachenko
Status:	Verified
Category:	Server: MyISAM	Severity:	S2 (Serious)
Version:	5.0.45, 5.0 BK, 5.1 BK	OS:	Linux (CentOS 4.5)
Assigned to:		Target Version:
Tags:	qc
Triage:	Triaged: D3 (Medium) / R3 (Medium) / E5 (Major)

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.