Why MySQL only Use ONE THREAD for Query

MySQL & MariaDB Percona Server for MySQL 5.6
1

Hi Gurus,

I have a question that puzzled me long time. I was using percona 5.6 as my MySQL database version. From the documents, I know that there are two variables: innodb_read_io_threads and innodb_write_io_threads can be used to make more pocessors to help on IO performance.

I have a server with 2 CPUs and each has 4 cores. I configured these two variables with value 4, and I used iotop to monitor the IO status. From my understanding, when I run a query, there should be 4 threads in iotop screen. When I run a DML statement, there will be 4 threads in iotop as well.

It is true when I run a DML statement. But it’s ONLY ONE mysqld thread when I ran a query. It looked like this query only use one core, the other 3 cores was idle.

Can anyone please let me know how I can make the query use 4 processors, not only 1?

Many thanks,

Tony

2

Hey Tony,

InnoDB and XtraDB do not support parallelization of cores with single queries.

The following article may prove useful with a more in depth explanation.

[url]https://dzone.com/articles/increasing-slow-query[/url]

~Blair Page