my.cnf Optimisation - 24GB RAM

[tone]

Hello,

I've completed the mysqltuner script and here are the results:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 307M (Tables: 993)
[--] Data in InnoDB tables: 885M (Tables: 1685)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 993K (Tables: 24)
[!!] Total fragmented tables: 137

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 5h 12m 20s (185M q [665.911 qps], 995K conn, TX: 437B, RX: 24B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 2.0G global + 2.8M per thread (5000 max threads)
[OK] Maximum possible memory usage: 15.5G (66% of installed RAM)
[OK] Slow queries: 0% (2K/185M)
[OK] Highest usage of available connections: 2% (106/5000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/77.1M
[OK] Key buffer hit rate: 99.9% (1B cached / 2M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (57 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 46473
[!!] Temporary tables created on disk: 43% (206K on disk / 474K total)
[OK] Thread cache hit rate: 99% (106 created / 995K connections)
[!!] Table cache hit rate: 0% (400 open / 96K opened)
[OK] Open file limit used: 0% (92/25K)
[OK] Table locks acquired immediately: 99% (185M immediate / 185M locks)
[OK] InnoDB data size / buffer pool: 885.2M/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 400)

And here is the content of my.cnf

local-infile=0
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=12424
max_user_connections=3000
max_connections=5000
thread_cache_size=1000
innodb_buffer_pool_size=2048M

I've attempted to read up on these values and have read a lot of posts on here with a similar question, but I'm still uncertain of the best settings to use. I would appreciate someone with more knowledge giving their input.

The server is a E3-1240 with 24GB of RAM. The largest database is 900MB.

Regards,

Piers

 

[tone]

I've updated the my.cnf with the below values as advised by my hosting provider.

query_cache_size=256M
table-cache=512M
tmp_table_size=64M
join_buffer_size=1M

 

[24x7server]

Hello,

Also you can add following values in your my.cnf file

wait_timeout = 30
connect_timeout = 10
interactive_timeout = 10

 

[cPanelMichael]

Keep in mind that when you make changes, it's a good idea to let MySQL run at least 24 hours before running the tuner script again to ensure more reliable results.

Thank you.

 

[thinkbot]

I've updated the my.cnf with the below values as advised by my hosting provider.

query_cache_size=256M
table-cache=512M
tmp_table_size=64M
join_buffer_size=1M

table-cache=512M is ridiculous

table_cache is buffer for open tables, it can be set max 524288,
but it's one of the variables which can decrease performance, when set too big, since mysql scallability problem

so in your case set it as
table_cache = 2000


full suggested config

[mysqld]
skip-name-resolve

wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=12424
default-storage-engine=MyISAM

max_connections=2000

key_buffer_size = 5G
max_allowed_packet=32M

query_cache_size=50M
query_cache_limit=1M
tmp_table_size=100M
max_heap_table_size=100M
thread_cache_size=100

table_open_cache = 2000
table_definition_cache = 1000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

#### Per connection configuration ####
sort_buffer_size=256K
join_buffer_size=4M
read_rnd_buffer_size=8M

##### INNODB Specific Options ######
innodb_buffer_pool_size=15G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_open_files=2000

thats all,

before restarting the server with new config, remove old log files, since the size is changed
do it like this:
rm -rf /var/lib/mysql/ib_logfile*

then remove mysql-slow.log file
rm -rf /var/lib/mysql/*-slow.log

and restart mysql

let it run few hours and generate new mysqltuner and pt-query-digest like that:
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

in slow.txt you will have slow queries review, which you can later put here

 

[tone]

Keep in mind that when you make changes, it's a good idea to let MySQL run at least 24 hours before running the tuner script again to ensure more reliable results.

Thank you.

Thank you for the replies. I've run mysqltuner.pl again and here are the results. Is the above still suggested?

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 316M (Tables: 1081)
[--] Data in InnoDB tables: 763M (Tables: 1687)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 745K (Tables: 24)
[!!] Total fragmented tables: 150

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 19h 35m 33s (24M q [73.971 qps], 565K conn, TX: 447B, RX: 5B)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 1.3G global + 3.6M per thread (5000 max threads)
[OK] Maximum possible memory usage: 19.0G (81% of installed RAM)
[OK] Slow queries: 0% (129K/24M)
[OK] Highest usage of available connections: 1% (63/5000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/80.7M
[OK] Key buffer hit rate: 99.8% (39M cached / 93K reads)
[OK] Query cache efficiency: 92.0% (20M cached / 21M selects)
[!!] Query cache prunes per day: 124002
[OK] Sorts requiring temporary tables: 0% (360 temp sorts / 118K sorts)
[!!] Joins performed without indexes: 51911
[!!] Temporary tables created on disk: 42% (94K on disk / 220K total)
[OK] Thread cache hit rate: 99% (63 created / 565K connections)
[OK] Table cache hit rate: 80% (5K open / 7K opened)
[OK] Open file limit used: 25% (3K/12K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 763.2M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 256M) [see warning above]
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 192M)
    max_heap_table_size (> 24M)