7

Our MySQL server has been using a lot of CPU lately (it's reached 100% several times and stays there for a while) and I noticed that it the CPU load is all on one core of one cpu. I was hoping to spread that out to all 4 on my server.

I have been tweaking the MySQL settings to use more ram and less cpu, but it still occasionally reaches very high CPU usage.

It seems like everything about the topic refers to thread_concurrency (which I've read is a solaris only setting). What can I do in Linux?

Thanks.

2

You are right, thread_concurrency is only for Solaris and it is deprecated in MySQL 5.6 onward. I don't think you can use multi cores/cpus with MyISAM. To fully utilize the hardware, use InnoDB.

This might help you to understand things better.

1
  • I have InnoDB, and still the queries all end up on 1 CPU reaching 100%, while the other says idle. (Windows 10. Mysql 5.6.10)
    – nl-x
    Mar 11 at 12:34
1

The correct value for this variable is dependent on environment and workload. You will need to try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.

The range of this variable is 0 to 1000. A value of 20 or higher is interpreted as infinite concurrency before MySQL 5.0.19. From 5.0.19 on, you can disable thread concurrency checking by setting the value to 0. Disabling thread concurrency checking enables InnoDB to create as many threads as it needs.

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_thread_concurrency

2
  • That setting is already set to 8. However, my tables are myisam and it still seems to be using only one cpu.
    – mhost
    Dec 26 '10 at 19:20
  • 2
    Since you are using Myisam table type, InnoDB's concurrency has no effect to your mysql server. I am not aware of any configuration or setting which makes myisam scale for multicore systems.
    – Istvan
    Dec 27 '10 at 16:11
0

To keep it simple - each query executed in MySQL uses only 1 CPU core regardless of how multi-core the InnoDB engine is.

All multi-core optimizations inside MySQL help when you have many different queries running at the same and demading for CPU and data, but if you have some big query which takes minutes / hours to complete, it will always use only 1 CPU core. (And if you must have such queries, you should buy servers with higher CPU speed and less number of cores.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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