Ebridge

Member
May 3, 2012
16
1
53
cPanel Access Level
Root Administrator
Requesting help optimizing my.conf

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)
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:

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=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 ;)
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
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.
 

Ebridge

Member
May 3, 2012
16
1
53
cPanel Access Level
Root Administrator
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...

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: 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)
 

Ebridge

Member
May 3, 2012
16
1
53
cPanel Access Level
Root Administrator
adjust

query-cache-type = 1
query-cache-size = 50M
Thanks :)

Any chance of reducing the slow queries some more? Other recommendations?

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: 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)