MySql doesn’t use extra CPU

cpuMySQL

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

Best Answer

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.

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4

Best Answer

Related Solutions

InnoDB INSERT Performance Capabilities in MySQL

MySQL Performance – Why Simple SELECTs on InnoDB Are 100x Slower Than on MyISAM