Using multiple cores for single MySQL queries on Debian

Question

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 < 0 1810 undo n:o < 0 0
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 7503, OS thread id 140316748777216
MySQL thread id 154, query id 654 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 5380, ACTIVE 105 sec, process no 7503, OS thread id 140316748977920
 fetching rows, thread declared inside InnoDB 429
mysql tables in use 2, locked 0
MySQL thread id 153, query id 623 localhost root 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

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 for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
116089 OS file reads, 7 OS file writes, 7 OS fsyncs
1063.16 reads/s, 117085 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, node heap has 1 buffer(s)
0.00 hash searches/s, 14.73 non-hash searches/s
---
LOG
---
Log sequence number 0 38201270
Log flushed up to   0 38201270
Last checkpoint at  0 38201270
0 pending log writes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20638760; in additional pool allocated 994816
Dictionary memory allocated 162680
Buffer pool size   512
Free buffers       0
Database pages     511
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 816631, created 0, written 1
7597.72 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 964 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 7503, id 140316711446272, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 160338394
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1495933.31 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Answer 1

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/

Answer 2

MySQL, any version, does not have any code to use multiple cores in a single connection.

Percona does a better job of using multiple cores across multiple connections in its Xtradb. InnoDB runs out of steam at about 8 cores; Xtradb flattens out at 32 or so.

A "large query" could be locking the table (or rows of the table) that some other connection needs. Let's look at the query and the SHOW CREATE TABLE.