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.

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 <major@mhtx.net>
>> 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.

share|improve this question
    
Have you looked at MySQL's SHOW FULL PROCESSLIST while the spikes are occurring? –  Michael - sqlbot Feb 20 '13 at 16:54
    
Hi Michael. To be very honest I don't know how to do that. But I will if you tell me how.. Thanks –  AlexCl Feb 20 '13 at 17:36
    
dev.mysql.com/doc/refman/5.5/en/show-processlist.html ... however you normally talk to the server (the mysql command line client, MySQL workbench, phpmyadmin, etc.) just send SHOW FULL PROCESSLIST; while logged in a a user with the SUPER privilege. It shows all of the current connections and what they're doing, and how long they've been doing it, right at the moment you issue the statement. You're pretty much operating blind with out it. –  Michael - sqlbot Feb 20 '13 at 20:32
    
You can refer these existing links which can be helpful for you: stackoverflow.com/questions/362223/… stackoverflow.com/questions/12026488/mysql-query-monitor-tool –  Mahesh Patil Feb 26 '13 at 11:26

3 Answers 3

up vote 0 down vote accepted

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.

share|improve this answer

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

share|improve this answer
    
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 Feb 20 '13 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. –  Adam C. Scott Feb 20 '13 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 Feb 21 '13 at 13:11
    
thread_cache_size = {at least 60 and near the number of connections established in about 2 seconds} –  Adam C. Scott Feb 21 '13 at 16:18
    
innodb_log_file_size={the value should be innodb_buffer_pool_size/2 } –  Adam C. Scott Feb 21 '13 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

share|improve this answer
    
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 Feb 20 '13 at 17:17

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.