Using multiple cores for single MySQL queries on Debian

I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four.

I am doing expensive joins on large tables, which can take several minutes, but I see on the guest OS, that only one core is used at a time. This happens regardless of the storage engine used for the tables involved (tested with MyISAM and InnoDB). Additionally, the whole database seems to be blocked when doing these large queries, I can't do any additional queries in parallel. Strangely htop shows, that the core used for the query changes during the runtime of the query!

Why does this happen?

This is the relevant entry from SHOW FULL PROCESSLIST; (there are no other queries):

| 153 | root       | localhost | pulse_stocks  | Query   |   50 | Copying to tmp table | 
SELECT DISTINCT * FROM 
`pulse_stocks`.`stocks` sto,
`pulse_new`.`security` sec
WHERE
(sto.excntry = sec.excntry AND sto.stock_id = sec.ibtic) OR
( sto.isin = sec.isin AND sto.isin <> "" AND sec.isin <> "" )
ORDER BY
sto.id
LIMIT 0, 30

There are no other queries pending. Another interesting observations is, that MySQL will answer this query in a second, if I leave out the ORDER BY part.

This what SHOW ENGINE INNODB STATUS; shows:

```
=====================================
120316 9:55:56 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 47258, signal count 47258
Mutex spin waits 0, rounds 10260, OS waits 39
RW-shared spins 94442, OS waits 47210; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 5381
Purge done for trx's n:o "" AND sec.isin <> "" )
ORDER BY
sto.id
LIMIT 0, 30

Trx read view will not see trx with id >= 0 5381, sees < 0 5381
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting f

You may find this surprising, but you should set the innodb_thread_concurrency to 0 (which is infinite concurrency). This will allow the InnoDB Storage Engine to decide how many concurrency tickets to issue.

I wrote a post about InnoDB's multicore engagement (MySQL 5.5, also MySQL 5.1.38 InnoDB Plugin) back on May 26, 2011.

According to the MySQL Documentation, the thread_concurrency variable only works for Solaris.

I have one more concern: Are your JOINs involving MyISAM and InnoDB together? MyISAM's full-table locking behavior nullifies InnoDB's row-level locking and MVCC.

If you are not using MySQL 5.5, please upgrade ASAP in order to setup InnoDB's multicore engagement options.
UPDATE 2012-03-19 08:30 EDT

Starting with MySQL 5.1.38, you can install the InnoDB Plugin to use new settings for multicore engagement. However, you have to tune the settings properly.

In fact, left unconfigured

• InnoDB for MySQL 4.1 runs better in a single-threaded environment than all other versions of MySQL before or after it

• InnoDB 5.1 Plugin runs better in a single CPU environment than all other versions of MySQL before or after it

• I wrote a post about this back in November 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

• I also wrote a post back in June 2011 on how you can run your own Benchmark against MySQL, Percona Server, and MariaDB : How do I properly perform a MySQL bake-off?

• All my assertions are based on Percona who actually ran a Performance Benchmark against all the versions of MySQL : http://www.mysqlperformanceblog.com/2011/10/10/mysql-versions-shootout/

StackExchange Database Administrators Q#15146, answer score: 10