Mysql – Problems with the.cnf and php.ini – Using thesql tuner

MySQLPHPphp.ini

My server was running fine until a recent update in Plesk and now it has been slow. My server tech had to rebuild the Apache configuration file and ever since the sites have been slow.

The server is the XL8 dedicated box from 1and1. it has 16gb of ram and an 8 core cpu. The server contains 2 fairly busy vbulletin forums and some smaller sites.

Here is the current my.cnf as I set it-

 [mysqld]
#bind-address=127.0.0.1
#skip-bdb
local-infile=0
max_connections=90
open_files_limit=2050
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_limit = 1M
query_cache_size = 15M
join_buffer_size = 512K
read_buffer_size = 1M
tmp_table_size = 3024M
thread_cache_size = 8
table_cache = 450
innodb_buffer_pool_size = 540M
key_buffer_size = 32M
table_definition_cache = 4024
max_allowed_packet = 35M
max_heap_table_size = 3024M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

And here is the latest results from mysql tuner

Last login: Tue Sep 24 19:28:02 2013 from ip70-181-17-116.ri.ri.cox.net
[root@u16557714 ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  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.1.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3316)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 372K (Tables: 329)
[!!] Total fragmented tables: 612

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 15s (10K q [42.020 qps], 506 conn, TX: 286M, RX: 2M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 4.5G global + 3.1M per thread (90 max threads)
[OK] Maximum possible memory usage: 4.8G (30% of installed RAM)
[OK] Slow queries: 0% (1/10K)
[OK] Highest usage of available connections: 13% (12/90)
[OK] Key buffer size / total MyISAM indexes: 1.0G/504.2M
[OK] Key buffer hit rate: 98.0% (131K cached / 2K reads)
[OK] Query cache efficiency: 71.4% (5K cached / 8K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 429 sorts)
[!!] Joins performed without indexes: 6
[!!] Temporary tables created on disk: 34% (109 on disk / 320 total)
[OK] Thread cache hit rate: 95% (25 created / 506 connections)
[OK] Table cache hit rate: 97% (330 open / 337 opened)
[OK] Open file limit used: 4% (509/12K)
[OK] Table locks acquired immediately: 99% (4K immediate / 4K locks)
[OK] InnoDB data size / buffer pool: 111.0M/540.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)

[root@u16557714 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@u16557714 ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  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.1.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3316)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 124K (Tables: 329)
[!!] Total fragmented tables: 613

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 52s (683 q [13.135 qps], 39 conn, TX: 27M, RX: 229K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 4.5G global + 3.1M per thread (90 max threads)
[OK] Maximum possible memory usage: 4.8G (30% of installed RAM)
[OK] Slow queries: 0% (0/683)
[OK] Highest usage of available connections: 2% (2/90)
[OK] Key buffer size / total MyISAM indexes: 1.0G/504.3M
[!!] Key buffer hit rate: 92.2% (11K cached / 928 reads)
[OK] Query cache efficiency: 38.5% (194 cached / 504 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 54 sorts)
[!!] Joins performed without indexes: 1
[OK] Temporary tables created on disk: 19% (8 on disk / 41 total)
[OK] Thread cache hit rate: 94% (2 created / 39 connections)
[OK] Table cache hit rate: 94% (113 open / 120 opened)
[OK] Open file limit used: 1% (212/12K)
[OK] Table locks acquired immediately: 100% (656 immediate / 656 locks)
[OK] InnoDB data size / buffer pool: 111.0M/540.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)

[root@u16557714 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@u16557714 ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  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.1.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3316)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 124K (Tables: 329)
[!!] Total fragmented tables: 612

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 25s (411 q [16.440 qps], 25 conn, TX: 14M, RX: 152K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 4.0G global + 3.1M per thread (90 max threads)
[OK] Maximum possible memory usage: 4.3G (27% of installed RAM)
[OK] Slow queries: 0% (0/411)
[OK] Highest usage of available connections: 3% (3/90)
[!!] Key buffer size / total MyISAM indexes: 500.0M/504.2M
[!!] Key buffer hit rate: 92.5% (8K cached / 610 reads)
[OK] Query cache efficiency: 29.7% (91 cached / 306 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 39 sorts)
[!!] Joins performed without indexes: 1
[OK] Temporary tables created on disk: 15% (3 on disk / 19 total)
[OK] Thread cache hit rate: 88% (3 created / 25 connections)
[OK] Table cache hit rate: 92% (81 open / 88 opened)
[OK] Open file limit used: 7% (148/2K)
[OK] Table locks acquired immediately: 100% (381 immediate / 381 locks)
[OK] InnoDB data size / buffer pool: 111.0M/540.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    key_buffer_size (> 504.2M)
    join_buffer_size (> 512.0K, or always use indexes with joins)

[root@u16557714 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@u16557714 ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  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.1.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3316)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 1M (Tables: 329)
[!!] Total fragmented tables: 613

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13s (262 q [20.154 qps], 24 conn, TX: 15M, RX: 64K)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 3.5G global + 4.0M per thread (90 max threads)
[OK] Maximum possible memory usage: 3.9G (24% of installed RAM)
[OK] Slow queries: 0% (0/262)
[OK] Highest usage of available connections: 2% (2/90)
[!!] Key buffer size / total MyISAM indexes: 32.0M/504.2M
[!!] Key buffer hit rate: 89.5% (2K cached / 240 reads)
[OK] Query cache efficiency: 46.4% (78 cached / 168 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 5 total)
[OK] Thread cache hit rate: 91% (2 created / 24 connections)
[OK] Table cache hit rate: 88% (54 open / 61 opened)
[OK] Open file limit used: 5% (103/2K)
[OK] Table locks acquired immediately: 100% (197 immediate / 197 locks)
[OK] InnoDB data size / buffer pool: 111.0M/540.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    key_buffer_size (> 504.2M)

Any advice or assistance would be so much appreciated.

Thanks so much!!

Here is my latest 

    [mysqld]
#bind-address=127.0.0.1
#skip-bdb
local-infile=0
max_connections=90
open_files_limit=2050
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_limit = 1M
query_cache_size = 15M
join_buffer_size = 512K
read_buffer_size = 1M
tmp_table_size = 100M
thread_cache_size = 8
table_cache = 450
innodb_buffer_pool_size = 540M
key_buffer_size = 32M
table_definition_cache = 4024
max_allowed_packet = 35M
max_heap_table_size = 100M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Best Answer

i would run a "show full processlist" when its running slow, as well as enabling the slow log.

log-slow-queries = slow.log
long_query_time = 20
log-queries-not-using-indexes

this is to get an idea of exactly what queries are killing the box, as it could be a join without index situation.

also, some other optimizations may be reducing your innodb buffer pool, as its mostly unused

[OK] InnoDB data size / buffer pool: 111.0M/5.0G

upping your key buffer size:

[OK] Key buffer size / total MyISAM indexes: 525.0M/504.2M

upping your tmp_table_size so that tmp tables are not written to disk

tmp_table_size (> 100M)
max_heap_table_size (> 100M)

reduce buffer allocation, as this is way to high and may hang your box under load

[!!] Maximum possible memory usage: 1960.1G (12495% of installed RAM)

in the mysql community download, at least the source tarball, there are examples of small/medium/large/huge my.cnf examples, and i would probably replace some of your existing buffer values with the "large" my.cnf (or "huge" if its dedicated mysql). remember to make changes one at a time, and then load test.

Related Topic