help requested optimizing MySQL
Requesting help optimizing my.conf
Note: MySQL didn't run for 24 hours, but it did run a full working day in this timezone (weekend ahead with 0 activity at all) my.conf:
Server: VPS with 4GB ram, 2 cores, CentOS running some small Drupal projects (traffic is expected to increase tenfold in the comings months however) I didn't follow the mysqltuner recommendations this time as I had much better results asking in this forum for help ;)
>> MySQLTuner 1.2.0 - Major Hayden
>> 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.6.16-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 14M (Tables: 76)
[--] Data in InnoDB tables: 33M (Tables: 424)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 25
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 14h 9m 42s (310K q [6.089 qps], 3K conn, TX: 1B, RX: 165M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 680.0M global + 1.1M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.2G (32% of installed RAM)
[!!] Slow queries: 8% (25K/310K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 32.0M/2.5M
[OK] Key buffer hit rate: 98.8% (27K cached / 321 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23K sorts)
[OK] Temporary tables created on disk: 14% (3K on disk / 27K total)
[OK] Thread cache hit rate: 99% (8 created / 3K connections)
[!!] Table cache hit rate: 0% (756 open / 99K opened)
[OK] Open file limit used: 2% (217/8K)
[OK] Table locks acquired immediately: 100% (378K immediate / 378K locks)
[OK] InnoDB data size / buffer pool: 34.0M/600.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
table_cache (> 4096)Note: MySQL didn't run for 24 hours, but it did run a full working day in this timezone (weekend ahead with 0 activity at all) my.conf:
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 600M
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
open_files_limit=7472
Server: VPS with 4GB ram, 2 cores, CentOS running some small Drupal projects (traffic is expected to increase tenfold in the comings months however) I didn't follow the mysqltuner recommendations this time as I had much better results asking in this forum for help ;)
-
Hello :) It still might be a good idea to let MySQL run for 24 hours before running the tuner. Feel free to post the tuner results again at the 24-hour mark if you have not received any user feedback by then. Thank you. 0 -
[quote="cPanelMichael, post: 1619392">Hello :) It still might be a good idea to let MySQL run for 24 hours before running the tuner. Feel free to post the tuner results again at the 24-hour mark if you have not received any user feedback by then. Thank you.
Thanks for your reply Michael! Here you are...>> MySQLTuner 1.2.0 - Major Hayden >> 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.6.16-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 14M (Tables: 76) [--] Data in InnoDB tables: 39M (Tables: 424) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [!!] Total fragmented tables: 27 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 11h 34m 23s (407K q [3.180 qps], 6K conn, TX: 2B, RX: 198M) [--] Reads / Writes: 93% / 7% [--] Total buffers: 680.0M global + 1.1M per thread (500 max threads) [OK] Maximum possible memory usage: 1.2G (32% of installed RAM) [!!] Slow queries: 8% (34K/407K) [OK] Highest usage of available connections: 1% (8/500) [OK] Key buffer size / total MyISAM indexes: 32.0M/2.5M [OK] Key buffer hit rate: 99.4% (58K cached / 376 reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30K sorts) [OK] Temporary tables created on disk: 14% (9K on disk / 66K total) [OK] Thread cache hit rate: 99% (8 created / 6K connections) [!!] Table cache hit rate: 0% (767 open / 249K opened) [OK] Open file limit used: 2% (218/8K) [OK] Table locks acquired immediately: 99% (489K immediate / 489K locks) [OK] InnoDB data size / buffer pool: 39.6M/600.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (>= 8M) table_cache (> 4096)0 -
adjust query-cache-type = 1 query-cache-size = 50M 0 -
[quote="thinkbot, post: 1623451">adjust query-cache-type = 1 query-cache-size = 50M
Thanks :) Any chance of reducing the slow queries some more? Other recommendations?>> MySQLTuner 1.2.0 - Major Hayden >> 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.6.16-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 13M (Tables: 76) [--] Data in InnoDB tables: 62M (Tables: 579) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [!!] Total fragmented tables: 35 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 20h 18m 14s (192K q [1.208 qps], 6K conn, TX: 968M, RX: 317M) [--] Reads / Writes: 62% / 38% [--] Total buffers: 730.0M global + 1.1M per thread (500 max threads) [OK] Maximum possible memory usage: 1.3G (33% of installed RAM) [OK] Slow queries: 2% (4K/192K) [OK] Highest usage of available connections: 1% (9/500) [OK] Key buffer size / total MyISAM indexes: 32.0M/2.1M [OK] Key buffer hit rate: 99.1% (66K cached / 586 reads) [OK] Query cache efficiency: 63.5% (90K cached / 141K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts) [OK] Temporary tables created on disk: 15% (12K on disk / 81K total) [OK] Thread cache hit rate: 99% (9 created / 6K connections) [!!] Table cache hit rate: 0% (1K open / 330K opened) [OK] Open file limit used: 2% (219/8K) [OK] Table locks acquired immediately: 99% (82K immediate / 82K locks) [OK] InnoDB data size / buffer pool: 62.9M/600.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increase table_cache gradually to avoid file descriptor limits Variables to adjust: table_cache (> 4096)0 -
check your slow log and review the queries 0
Please sign in to leave a comment.
Comments
5 comments