MySQL Optimization

david.cocos

May 19, 2014 06:05

Hello, I have a problem with my VPM server. It runs on a 4 core processor and 32GB of RAM. The server has a single domain hosted on it with aprox 500 real time users on it. The website freezes for 3-5 minutes, and this happens 5-10 times per day. MySQL Tunner log:


 >>  MySQLTuner 1.3.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 13G (Tables: 39)
[--] Data in InnoDB tables: 2G (Tables: 233)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 41

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 19h 31m 4s (39M q [161.618 qps], 3M conn, TX: 107B, RX: 31B)
[--] Reads / Writes: 56% / 44%
[--] Total buffers: 8.6G global + 14.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 10.8G (33% of installed RAM)
[OK] Slow queries: 0% (20K/39M)
[OK] Highest usage of available connections: 63% (96/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.9G
[OK] Key buffer hit rate: 99.9% (658M cached / 346K reads)
[OK] Query cache efficiency: 41.2% (5M cached / 12M selects)
[!!] Query cache prunes per day: 321735
[OK] Sorts requiring temporary tables: 0% (8K temp sorts / 2M sorts)
[!!] Temporary tables created on disk: 47% (14M on disk / 30M total)
[OK] Thread cache hit rate: 99% (96 created / 3M connections)
[!!] Table cache hit rate: 0% (536 open / 253K opened)
[OK] Open file limit used: 0% (178/50K)
[OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
[OK] InnoDB buffer pool / data size: 8.0G/2.3G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 256M) [see warning above]
    table_cache (> 4096)

my.cnf:


[mysqld]
innodb_file_per_table=1
open_files_limit=50000
thread_cache_size = 16K
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_size = 256M
query_cache_limit = 8M
table_cache = 4K
table_definition_cache = 8K
key_buffer_size = 128M
join_buffer_size = 12M
innodb_buffer_pool_size = 8G
# log_queries_not_using_indexes
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow-query.log
long_query_time=1
log_error = /var/log/mysql/error.log


[mysqld_safe]
log-error=/var/log/mysqld.log

Please help with a solution, Thanks

Comments

8 comments

cPanelMichael

May 19, 2014 13:54
[quote="david.cocos, post: 1647412">The server has a single domain hosted on it with aprox 500 real time users on it. The website freezes for 3-5 minutes, and this happens 5-10 times per day.
To clarify, does the entire website fail to load, or is it only pages that utilize MySQL? Do you notice any error messages in the MySQL error log when this happens? Thank you.
0
david.cocos

May 19, 2014 15:42
[quote="cPanelMichael, post: 1647492">To clarify, does the entire website fail to load, or is it only pages that utilize MySQL? Do you notice any error messages in the MySQL error log when this happens? Thank you.
The entire VPS freezes. I didn't find anything specific in the error log. Which error log would be most useful?
0
cPanelMichael

May 19, 2014 16:27
If it's an OpenVZ/Virtuozzo VPS, check the /proc/user_beancounters file to see if the VPS is reaching any resource limits imposed from the node. Thank you.
0
david.cocos

May 20, 2014 07:49
It is a Virtuozzo VPS. This is what the /proc/user_beancounters file is showing: Version: 2.5 uid resource held maxheld barrier limit failcnt 99837: kmemsize 237096228 244813824 773094113 858993459 0 lockedpages 0 0 2059 2059 0 privvmpages 3134756 3193840 7549747 8388608 0 shmpages 1950 1950 524288 524288 0 dummy 0 0 9223372036854775807 9223372036854775807 0 numproc 526 597 1600 1600 0 physpages 7416304 7469372 7549747 8388608 0 vmguarpages 0 0 4194304 9223372036854775807 0 oomguarpages 1525539 1527215 4194304 9223372036854775807 0 numtcpsock 329 370 1550 1550 0 numflock 288 301 1000 1100 0 numpty 0 0 102 102 0 numsiginfo 0 48 1024 1024 0 tcpsndbuf 3412184 7202544 14880000 22320000 677 tcprcvbuf 1857760 2025976 14880000 22320000 0 othersockbuf 339752 650432 14400000 21600000 0 dgramrcvbuf 0 13872 13440000 13440000 0 numothersock 351 404 1700 1700 0 dcachesize 128847168 128849018 115964116 128849018 0 numfile 5745 6825 38496 38496 0 dummy 0 0 9223372036854775807 9223372036854775807 0 dummy 0 0 9223372036854775807 9223372036854775807 0 dummy 0 0 9223372036854775807 9223372036854775807 0 numiptent 70 70 9223372036854775807 9223372036854775807 0
Thank you [COLOR="silver">- - - Updated - - - This is the original file.
0
cPanelMichael

May 20, 2014 13:01
Notice the fail count for the "tcpsndbuf" value. Please report this to your VPS hosting provider and have them check to see if they can increase that limit from the VPS hardware node. Thank you.
0
david.cocos

May 23, 2014 12:18
My VPS Hosting Provider said: "the "tcpsndbuf" value is already the highest value we can offer". So it wasn't very helpful. The specs of the server are: Intel(R) Xeon(R) CPU E5-2620 v2 8 cores, 2,1 GHz, 32 GB RAM, 500 GB SSD. I want to specify that the server uses very few memory. Also max_connections is set to 151. Could this also be a problem? Thank you for your help
0
cPanelMichael

May 23, 2014 12:41
You are welcome to gather user-feedback for MySQL optimization on this thread. I'm not sure what else would cause your VPS to stop responding. You may want to have you provider address the issue with the VPS failing to respond if it continues to be an issue, or try reviewing the output of additional logs if it happens again (/var/log/messages or /var/log/dmesg). Thank you.
0
david.cocos

May 23, 2014 13:16
I have also found this with the "sar -P ALL" command: Average: CPU %user %nice %system %iowait %steal %idle Average: all 66.34 0.22 14.09 0.01 0.42 18.92 Average: 0 0.00 0.00 0.00 0.00 0.00 100.00 Average: 1 74.88 0.26 16.07 0.02 0.52 8.25 Average: 2 0.00 0.00 0.00 0.00 0.00 100.00 Average: 3 74.89 0.24 15.98 0.01 0.46 8.42 Average: 4 0.00 0.00 0.00 0.00 0.00 100.00 Average: 5 74.90 0.23 15.82 0.02 0.47 8.56 Average: 6 0.00 0.00 0.00 0.00 0.00 100.00 Average: 7 75.04 0.25 15.78 0.01 0.46 8.47 Average: 8 0.00 0.00 0.00 0.00 0.00 0.00
I notice that for 4 of my cores %idle is 100%. Is this ok and if not how can I solve this?
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?