0

A server running only a MySql 5.7.30 instance consumes on average around 25% of its CPU, having over 100 threads connected, and of which generally 15-25 are running threads. The CPU in the server has 8 cores / 16 threads, and the load on CPU seems to be distributed over all cores/threads well.

Now a second identical CPU has been added to the server, but we notice that the second CPU does not get any load on any of its cores/threads. Are there any settings I need to adjust, to tell MySql to make use both CPU's?

Here are some settings from the my.ini on the machine:

default-storage-engine=INNODB
log-output=FILE
table_open_cache=2000
thread_cache_size=10
myisam_max_sort_file_size=100G
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
open_files_limit=4161
table_definition_cache=1400
binlog_row_event_max_size=8K
max_connections=301
tmp_table_size=6G
myisam_sort_buffer_size=12G
key_buffer_size=11M
innodb_buffer_pool_size=20G
innodb_log_file_size=2G
innodb_thread_concurrency=33
innodb_open_files=2000
join_buffer_size=4M
max_allowed_packet=32M
sort_buffer_size=512K
event-scheduler=ON
max_heap_table_size=32M
query_cache_type=1
2
  • Additional information request. RAM size, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report for Windows version - github.com/pmachapman/mysqltuner F) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. Jul 17 '20 at 14:21
  • I have the same issue. Even when I stress test the server with many simultaneous connections/queries, and the first CPU is buried under 100% load, the second CPU is not getting addressed. (Windows 10. Mysql 5.6.10)
    – nl-x
    Mar 11 at 12:32
0

In MySQL one connection uses one CPU core. Period. End of discussion.

Well...

  • InnoDB has some background threads, for example for handling I/O, purging, pruning, etc.
  • MySQL 8.0.xx has a very limited number of queries for which it will spawn multiple threads.

average around 25% of its CPU,

Yeah, MySQL is very fast at performing well written, well indexed queries. So, thousands of qps might use only a fraction of a CPU. CPU is rarely the limiting factor in MySQL systems. When it is, usually adding a composite index "fixes" it.

distributed over all cores/threads well

That's up to the OS. Just note that each connection lives in a "thread" (or "process") and it up to the OS to allocate CPU cores to threads.

If you are averaging 25% of one CPU, the OS rarely has a good reason to wake up the other CPU. Now we are getting into gory details of hardware -- NUMA, affinity, etc, etc. It may be power consumption, non-uniform memory access, etc.

Even if you get it to use both CPUs, each at about 12.5%, users may not see any improvement in latency.

Multiple threads:

  • May be blocked waiting on I/O -- which probably has only one way to get to disk
  • May be blocked on various Mutexes -- At somewhere around 64 threads, this becomes a problem. Not much of an issue at 25.
  • May be blocked for transactional integrity -- effectively serializing certain types of actions; hence additional CPUs are useless.

What's the real goal??

  • More throughput? Either you have not hit the limit yet, or you are I/O bound.
  • Lower latency? The extra CPU, even if used, won't help much.

Either of those may be helped by improving the queries. Turn on the slowlog; use pt-query-digest; then let's discuss the worst couple of queries.

2
  • If you are averaging 25% of one CPU, the OS rarely has a good reason to wake up the other CPU. Now we are getting into gory details of hardware -- NUMA, affinity, etc, etc. It may be power consumption, non-uniform memory access, etc. These gory details are what I want to know. Is there anything on Mysql configuration side I can do to get this going. And what at OS side can I do? (Windows Server 2012 R2)
    – nl-x
    Jul 6 '20 at 19:52
  • 1
    @nl-x - You get only one chance to squeeze more out of hardware. And often it is only a single-digit percentage improvement. The real improvements happen with design changes -- in the software architecture, avoiding CPU or I/O (versus getting a faster disk). Most high-end systems don't see Windows as being worth considering.
    – Rick James
    Jul 6 '20 at 21:29

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.