Cannot Utilize Maximum CPU and Memory Usage for MySQL

Question

Good day.

I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem.

I have a single stored procedure working with max 3 tables. when I run the procedure, only 30% of my CPU is used and about 25% of RAM.

I am sitting with a CPU with 4 cores and 16GB RAM.

my.ini looks as follows:

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 32M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 32M
thread_concurrency = 0

log-bin=mysql-bin

binlog_format=mixed

server-id   = 1

innodb_buffer_pool_size = 12G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_read_io_threads = 64
innodb_write_io_threads = 64

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Is it the nature of the procedure called which is causing mysql to under utiize the hardware or is it my configuration?

I was running XAMPP but then realised it was 32-bit so I switched to the 64-bit version of WAMP. I use a 32-bit MySQLWorkbench to run queries.

I am using the InnoDB engine.

Using MySQL Ver 14.14 Distrib 5.5.24 Win64 (x86).

Answer 1

The authors of High Performance MySQL describe part of your problem fairly succinctly (p. 234):

MySQL can’t execute a single query in parallel on many CPUs. This is a feature offered by some other database servers, but not MySQL.

Running a single stored procedure will use no more than 1 of your CPU cores for executing queries, with a little extra on other cores being used by various background and worker threads, such as those performing I/O. MySQL will only really use the full power of a multi-core or multi-processor system when there are multiple queries running at once.

If you're seeing 30% of 1 core being used, that indicates a potential problem, but 1.2 cores out of 4 for a single query is quite normal.

Answer 2

Even with all the changes you have implemented in my.ini, I would like to suggest just one more:

I noticed you did not set innodb_buffer_pool_instances.

According to the MySQL Documentation, this is what innodb_buffer_pool_instances is for:

The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

The default is 1. Given that you have 4 cores, I would set it 4. Please add that variable to my.ini:

[mysqld]
innodb_buffer_pool_instances = 4

Since this is not a dynamic variable, you must run restart mysql as follows

C:\> net stop mysql
C:\> net start mysql

Give it a Try !!!

UPDATE 2013-05-24 12:16 EDT

Looking again at your my.ini, I have some more recommendations in terms of memory usage:

• Set query_cache_size to 0. Yes, I am saying disable the query cache the query cache because it battles head-to-head with InnoDB all the time.
• Lower your innodb_log_buffer_size to 32M. Granted, having the InnoDB Log Buffer large can save on disk I/O. Having the InnoDB Log Buffer too large can result in longer commits and checkpoints take. This could manifest itself as a bottleneck for each thread, possibly underutilizing cores.
• You did not set innodb_log_file_size. The default is 5M. This might be too small. With such a large Buffer Pool, you may want to increase it to 1G. See my post How to safely change MySQL innodb variable 'innodb_log_file_size'? on how increase ib_logfile0 and ib_logfile1.
• SUPPLEMENTAL INFO : You did not set innodb_fast_shutdown (Default is 1). You should add innodb_fast_shutdown=0 to my.ini. That will cause all uncommitted transactions to be fully purged from ibdata1, ib_logfile0, and ib_logfile1 whenever you shutdown mysqld. This makes for a shorter InnoDB Crash Recovery Cycle on startup.
• SUPPLEMENTAL INFO : You did not set innodb_file_per_table. That means everything InnoDB and its grandmother is sitting in ibdata1. Besides data and index pages, there are other moving parts to ibdata1. You can spread out disk I/O for InnoDB by separating InnoDB tables from ibdata1. See my StackOverflow post Howto: Clean a mysql InnoDB storage engine? on how to do this and to fully defragment ibdata1.