I've tried optimizing mysql as good as I could, but apparently I'm not to good at it :-)

So I come to you guys for help.

Server specs are: AMD Opteron CPU 8 cores 16 GB RAM 2x2.000 GB HDD 7.200 RPM Software raid 1 Cloudlinux + Cpanel Around 60 websites hosted, half of them Joomla driven Total traffic for last month : 218 GB (both incoming and outgoing)

My current my.cnf looks like this:

log-queries-not-using-indexes = /var/log/mysql-indexes.log








The output from mysqltuner is this:

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.30-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3186)
[--] Data in InnoDB tables: 250M (Tables: 8508)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 266)
[!!] Total fragmented tables: 40

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 5h 15m 11s (6M q [34.844 qps], 157K conn, TX: 43B, RX: 1B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 5.1G global + 14.5M per thread (150 max threads)
[OK] Maximum possible memory usage: 7.2G (46% of installed RAM)
[OK] Slow queries: 0% (169/6M)
[OK] Highest usage of available connections: 26% (39/150)
[OK] Key buffer size / total MyISAM indexes: 256.0M/187.6M
[OK] Key buffer hit rate: 99.9% (357M cached / 236K reads)
[OK] Query cache efficiency: 82.0% (4M cached / 4M selects)
[!!] Query cache prunes per day: 95307
[OK] Sorts requiring temporary tables: 1% (2K temp sorts / 233K sorts)
[!!] Joins performed without indexes: 3349
[!!] Temporary tables created on disk: 38% (186K on disk / 479K total)
[OK] Thread cache hit rate: 99% (39 created / 157K connections)
[OK] Table cache hit rate: 32% (24K open / 74K opened)
[OK] Open file limit used: 2% (9K/327K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 250.3M/1.8G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 64M)
join_buffer_size (> 4.0M, or always use indexes with joins)

Something that I've notced is that 90 % of Swap is being used, while the server has over 8GB of free RAM.

 total       used       free     shared    buffers     cached
Mem:         15851      15733        118          0        569       8094
-/+ buffers/cache:       7069       8782
Swap:         4047       3664        383

Normally the server load stays around 0.5-0.8 but when the load spike occur it goes u to 5.00-6.00 and stays like that until I restart mysql, after which it goes back to normal (until the next spike).

As for PHP I'm using fcgi as the handler and Nginx ( via nginxcp ). I've tried removing nginxcp, thinking that it may be the cause, but the spikes still occurred.

All the software on the server is up to date, including the Joomla websites.

Fcgi is configures with the following settings, made according to Cloudlinux recommendations:

FcgidMinProcessesPerClass 0
FcgidMaxProcessesPerClass 16
FcgidMaxProcesses 300
FcgidIdleTimeout 60
FcgidProcessLifeTime 120
FcgidIdleScanInterval 30

I've also checked for network issues or DDOS attacks, but that is not the case. Hardware is brand new and in perfect state. So what could it be ?

Hope anyone can help out, I'll be forever grateful.



3 Answers 3


With multi-cores on MySQL the single mutex will create contention on the query cache. Set query_cache_type=0 and query_cache_size=0 in my.cnf.

Next, make the innodb_buffer_pool_size close to the size of the database; looks like you still have memory left. This helps reduce overall IO.

Outside of my.cnf, the queries hitting your server should use indexes. If not, they can create more IO. And finally, to handle high IO, you want to spread IO across disk drives. The first candidate for that is your binary logs. If /var is mounted on /dev/sda1 then add log_bin = {directory mounted on /dev/sda2}.

  • Hi Adam, I've set the query values in my.cnf and upped innodb_buffer_pool_size to 10 GB. I've also read that thread_concurrency should be 2xnumber of cores. Considering I have 8 physical cores on the server, shout I set it to 16 instead of 8 which is set now?
    – AlexCl
    Commented Feb 20, 2013 at 18:54
  • That's only for Solaris systems, so no need to fiddle with that. (see dev.mysql.com/doc/refman/5.1/en/…) The next step after these settings in my.cnf is tracking down the cause of the spikes. When you connect to mysql (see section Connecting to the MySQL Server For the First Time: dev.mysql.com/tech-resources/articles/…) if you issue the command show full processlist; (as dba.stackexchange.com/users/11651/michael-sqlbot mentioned) you can gather evidence of the root cause of high IO. Commented Feb 20, 2013 at 19:50
  • Hi Adam, I'm keeping an eye out for the IO abuser. Until I find the issue, are there any other reccomendations that you would give me on my current my.cnf settings ? Thanks for the help.
    – AlexCl
    Commented Feb 21, 2013 at 13:11
  • thread_cache_size = {at least 60 and near the number of connections established in about 2 seconds} Commented Feb 21, 2013 at 16:18
  • innodb_log_file_size={the value should be innodb_buffer_pool_size/2 } Commented Feb 21, 2013 at 16:18

Bump up innodb_buffer_pool_size to use more of the 16GB on your machine. Too high and you run out of memory. Look at free memory available on your box and use that as a guide to the total amount you can add. This is the biggest thing I can see you can do.

But you are concerned with 5.00-6.00 load. That's okay with 8 cores. (See http://blog.scoutapp.com/articles/2009/07/31/understanding-load-averages)

For your comfort, just trying using a website when CPU hits that load average. If you response time is suitable everything is okay.

Hope this helps, Adam C. Scott

  • Hi Adam, thanks for your reply. During the spikes websites that normally load in under 3 seconds load in over 10 seconds, so there definetly is an impact. The problem isn't CPU usage, but IO usage. Basically that's the one that slowing down everything. In regards to the innodb_buffer_pool_size, I've upped it to 10 GB, but still got a load spike and the free ram still showed 8 GB free.
    – AlexCl
    Commented Feb 20, 2013 at 17:17

I have the solution ( thanks to Igor and the incredible people at Cloudlinux ). Apparently it's a common problem with fcgi and can be avoided by just adding a cron job. To read a bit more about it and hw to fix it check this link:


Hope this is useful for anyone else out there having the same issue.

All the best to you guys and thanks for the help.

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.