Mysql DB Optimization
Hello All,
I am using AWS M3.2xlarge instance for a Moodle 2.4 site, but for the last few days we are observing very high cpu usage on server when traffic is high. So I have used mysqltuner.pl script to examine me server & database, below are the results for same -
Below are few parameters from mysql configurations -
We are working to examine the fragmented tables in our database and fixing them. But, it would be great if any one can suggest other areas/parameters, where do we need to improvements. Let me know if any other information is required. Great Thanks in advance !! :)
>> MySQLTuner 1.3.0 - Major Hayden
>> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major
>> Run with '--help' for additional options and output filtering
[[0;34m--[0m] Performing tests on ameengagelive.cxa7hf0nm4ut.us-east-1.rds.amazonaws.com:3306
[[0;34m--[0m] Assuming 30000 MB of physical memory
[[0;31m!![0m] Assuming 0 MB of swap space (use --forceswap to specify)
[[0;32mOK[0m] Currently running supported MySQL version 5.5.31-log
-------- Storage Engine Statistics -------------------------------------------
[[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;31m-FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m
[[0;34m--[0m] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[[0;34m--[0m] Data in InnoDB tables: 11G (Tables: 663)
[[0;34m--[0m] Data in MyISAM tables: 30M (Tables: 17)
[[0;31m!![0m] Total fragmented tables: 60
-------- Security Recommendations -------------------------------------------
[[0;32mOK[0m] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[[0;34m--[0m] Up for: 27d 0h 18m 35s (183M q [78.458 qps], 1M conn, TX: 7680B, RX: 112B)
[[0;34m--[0m] Reads / Writes: 71% / 29%
[[0;34m--[0m] Total buffers: 15.3G global + 3.9M per thread (2836 max threads)
[[0;31m!![0m] Maximum possible memory usage: 26.0G (88% of installed RAM)
[[0;32mOK[0m] Slow queries: 0% (11K/183M)
[[0;32mOK[0m] Highest usage of available connections: 8% (254/2836)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 128.0M/29.1M
[[0;32mOK[0m] Key buffer hit rate: 99.8% (4M cached / 7K reads)
[[0;32mOK[0m] Query cache efficiency: 75.3% (121M cached / 160M selects)
[[0;31m!![0m] Query cache prunes per day: 180978
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (2K temp sorts / 11M sorts)
[[0;31m!![0m] Joins performed without indexes: 74090
[[0;32mOK[0m] Temporary tables created on disk: 11% (804K on disk / 6M total)
[[0;32mOK[0m] Thread cache hit rate: 99% (254 created / 1M connections)
[[0;31m!![0m] Table cache hit rate: 0% (400 open / 2M opened)
[[0;32mOK[0m] Open file limit used: 0% (53/65K)
[[0;32mOK[0m] Table locks acquired immediately: 99% (61M immediate / 61M locks)
[[0;32mOK[0m] InnoDB buffer pool / data size: 15.0G/11.0G
[[0;32mOK[0m] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_open_cache (> 400)Below are few parameters from mysql configurations -
log_slow_queries ON
slow_query_log ON
slow_query_log_file /rdsdbdata/log/slowquery/mysql-slowquery.log
long_query_time 10.000000
max_connections 2836
max_user_connections 0
query_cache_type ON
query_cache_limit 16777216
query_cache_size 134217728
join_buffer_size 131072
read_rnd_buffer_size 524288
max_allowed_packet 134217728
tmp_table_size 16777216
max_heap_table_size 16777216
table_open_cache 400
thread_cache_size 300
open_files_limit 65535
key_buffer_size 134217728
myisam_sort_buffer_size 8388608
innodb_file_per_table ON
innodb_buffer_pool_size 16106127360
innodb_log_file_size 134217728We are working to examine the fragmented tables in our database and fixing them. But, it would be great if any one can suggest other areas/parameters, where do we need to improvements. Let me know if any other information is required. Great Thanks in advance !! :)
-
Hello :) To note, the following thread is helpful if you want to troubleshoot the server load overall if you are not convinced it's from MySQL: Troubleshooting High Loads On Linux Systems Thank you. 0
Please sign in to leave a comment.
Comments
1 comment