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
Code:
>> 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.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)my.conf:
Code:
[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=7472I didn't follow the mysqltuner recommendations this time as I had much better results asking in this forum for help
