Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
how does your IO look? –  Derek Downey Apr 23 '13 at 14:18
    
It jumps from 2 to 11%, but no more than 11% –  Stephen H Apr 23 '13 at 14:20
    
What version of MySQL ? (Run SELECT VERSION();) –  RolandoMySQLDBA Apr 23 '13 at 14:41
    
Ver 14.14 Distrib 5.5.24 Win64 (x86) –  Stephen H Apr 23 '13 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 Apr 23 '13 at 19:24

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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:

share|improve this answer
    
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 –  Stephen H Apr 25 '13 at 9:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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