MySQL selects/updates only using 1 core

Question

I have a server with 4 physical Xeon processors, each with 8 cores and each core is capable of 2 threads.

When I run MySQL queries I'm only seeing 1 CPU go to 100% in top and the total CPU usage at only 1.5%. I'm using MyISM as the MySQL engine.

Is it possible for MySQL to use all 64 threads instead of just the single one it is using right now?

If so, what outside of the default config do I need to change?

So far I tried everything in this article: Possible to make MySQL use more than one core?

• put innodb_thread_concurrency at 0
• innodb_read_io_threads and innodb_write_io_threads to 64.

The only other idea I had was to run the query 64 different times and piece the results together which actually does engage all the threads but that seems a rather crude way to accomplish the multi-threading.

Answer 1

The innodb threads have their place; but they do not help you out in your SELECT query. With MySQL, unfortunately, your SELECT can only use one single core.

This immediately means that if you want to use more cores, you must parallelize your queries. Very often this is possible. Do you have this huge script you're running? Are there independent parts to it? Parallelize them.

But if you cannot parallelize for some reason, you're more limited. Take a look at Shard Query. Either use it or get some ideas from it.

• RolandoMySQLDBA: I agree. The InnoDB read and write I/O threads are for accommodating the InnoDB Engine Infrastructure, not to internalize the multiprocessing of any one query. +1 !!!