Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
============================
share|improve this question
    
Please post the output of SHOW FULL PROCESSLIST (possibly sanitized) and SHOW ENGINE INNODB STATUS to help us diagnose why "the whole database" is locked. –  Aaron Brown Mar 16 '12 at 13:37
    
Thank you, I updated the question with the requested information. –  Thomas Mar 16 '12 at 14:07
1  
If there are no other queries running, then there's no evidence that the whole database is locked. Can you reproduce this condition and post what happens when the long-running query is apparently locking others out? –  Baron Schwartz Mar 19 '12 at 18:06
    
Ok, I checked this out. It is possible to do other queries in the mysql console, even when the large query is running. However, phpmyadmin is not responding at all, even to simple login attempts, while the query is being executed. The execution time itself is often less than 10 seconds, but the query stays in the "sending data" state for several minutes. –  Thomas Mar 20 '12 at 11:15
add comment

2 Answers

up vote 6 down vote accepted

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

share|improve this answer
    
Thank you very much. Does your answer imply, that using multiple cores is not implemented in version 5.1.X? –  Thomas Mar 19 '12 at 10:44
    
YES and NO. I say YES becasue InnoDB 5.1.x does not have multicore settings. I say NO becasue you can install the InnoDB Plugin for these new settings, but it is only available for MySQL 5.1.38 and above. –  RolandoMySQLDBA Mar 19 '12 at 12:11
    
Thank you very much for the detailed answer! –  Thomas Mar 19 '12 at 14:35
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.