Mysql optimization help

Question

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:

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-innodb
query_cache_limit=64M
query_cache_size=64M
query_cache_type=1
max_user_connections=100
max_connections=150
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=256M
join_buffer=4M
max_allowed_packet=128M
table_cache=163840
table_definition_cache=163840
#record_buffer=1M
sort_buffer_size=2M
read_buffer_size=8M
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=64M
server-id=1
innodb_buffer_pool_size=6G 
innodb_file_per_table=1
tmp_table_size=3G
max_heap_table_size=3G
low_priority_updates=1
concurrent_insert=ALWAYS
log-slow-queries=/var/lib/mysql/slow.log
log-queries-not-using-indexes = /var/log/mysql-indexes.log

#[mysql.server]
#user=mysql
#basedir=/var/lib 

open_files_limit=50000
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192 

#[mysqldump]
#quick
#max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M 

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

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.

Thanks.

Answer 1

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

Answer 2

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

Answer 3

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:

http://www.cloudlinux.com/blog/clnews/200.php

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

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