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):

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 BrownMar 16 '12 at 13:37

Thank you, I updated the question with the requested information.
–
ThomasMar 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 SchwartzMar 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.
–
ThomasMar 20 '12 at 11:15

2 Answers
2

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.

Thank you very much. Does your answer imply, that using multiple cores is not implemented in version 5.1.X?
–
ThomasMar 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.
–
RolandoMySQLDBAMar 19 '12 at 12:11

Thank you very much for the detailed answer!
–
ThomasMar 19 '12 at 14:35