Mysql tuning help

[AlexCl]

Hello everyone,

I know this has been discussed over and over again, but having reached a problem, here I am posting

I've tried to tune my.cnf as good as I could, but for over 2 weeks I've been having a lot of problems due to high %wa, which creates spikes in load. It's happening 2-3 times / day and takes around 20 minutes (the load goes up to 5.00) and then it goes back to normal (0.60-0.90). I'm assuming that mysql is the culprit, as I've checked for anything else (disk failure, DDOS attack, websites having too many visitors etc. ).

So to start with server specs:
AMD Opteron 8 physical cores
16 GB RAM
2x2.000 GB HDD's, 7.200 RPM, Software Raid 1
Cloudlinux + Cpanel
Around 60 websites hosted, maybe half of them on Joomla; Average bandwidth usage per month for the server: 200 GB (both incoming and outgoing)

Current my.cnf:

[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=1800M 
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

Current mysqltuner results:

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

I've also noticed in WHM -> Server status that 82 % of swap is used, while over 8 GB of RAM memory was free (only 47 % used).


If anyone can help me out I will be forever in your debt

Thank you.

 

[AlexCl]

I've identified the problem that was using the swap. It was fcgi and the tutorial on cloudlinux helped me fix it.

Nevertheless I'd like a second opinion on my current my.cnf if anyone cares to have a look.

Thanks.