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:

port        = 3306
socket      = /tmp/mysql.sock

port        = 3306
socket      = /tmp/mysql.sock
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



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

max_allowed_packet = 16M


key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M


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).

  • how does your IO look? Commented Apr 23, 2013 at 14:18
  • It jumps from 2 to 11%, but no more than 11%
    – tread
    Commented Apr 23, 2013 at 14:20
  • What version of MySQL ? (Run SELECT VERSION();) Commented Apr 23, 2013 at 14:41
  • Ver 14.14 Distrib 5.5.24 Win64 (x86)
    – tread
    Commented Apr 23, 2013 at 14:43
  • We don't know what the procedure does, so we can't tell if the nature of the procedure is an issue. How long does the procedure take to execute? If it is fast, you might not be getting accurate CPU usage readings.
    – G-Nugget
    Commented Apr 23, 2013 at 19:24

2 Answers 2


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.


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:

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:

  • Hi I tried it but it is still limited. Looking at the log it says: The InnoDB memory heap is disabled InnoDB: Not using CPU crc32 instructions InnoDB: Initializing buffer pool, size = 12.0G
    – tread
    Commented Apr 25, 2013 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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