Help optimizing MySQL for a 32GB system

johnburk

Well-Known Member
Jun 23, 2006
241
0
166
I am going to upgrade from 16GB ram to 32GB and I looks I am not using the full potential of it. Can you please look at my config and see how you would optimize it?


MySQL Settings
Code:
[mysqld]
innodb_buffer_pool_size=1342177280
max_allowed_packet=268435456

key_buffer_size = 512M
local-infile=0
max_allowed_packet = 32M
max_connections = 300
max_heap_table_size = 512MB
open_files_limit=32000
query_cache_limit = 64M
query_cache_size = 256M
query_cache_type = 1
table_definition_cache = 8000
table_open_cache = 16000
thread_cache_size = 128
thread_cache_size=100
tmp_table_size = 512MB
connect_timeout=40
interactive_timeout=120
myisam_sort_buffer_size=64M

##### SLOW QUERY ######
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.9
log-queries-not-using-indexes

##### INNODB ######
innodb_file_per_table=1
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_open_files=2400

#### Per connection ####
join_buffer_size=1M
read_buffer_size=2M
read_rnd_buffer_size=4M
sort_buffer_size=256K

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M
Mysqltuner.pl output
Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.22-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 450M (Tables: 2655)
[--] Data in InnoDB tables: 652M (Tables: 1398)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 0B (Tables: 68)
[!!] Total fragmented tables: 18

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 0h 21m 14s (74M q [428.119 qps], 939K conn, TX: 702B, RX: 9B)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 2.5G global + 7.5M per thread (300 max threads)
[OK] Maximum possible memory usage: 4.7G (30% of installed RAM)
[OK] Slow queries: 0% (717K/74M)
[OK] Highest usage of available connections: 60% (182/300)
[OK] Key buffer size / total MyISAM indexes: 512.0M/240.1M
[OK] Key buffer hit rate: 99.9% (221M cached / 315K reads)
[OK] Query cache efficiency: 88.1% (59M cached / 67M selects)
[!!] Query cache prunes per day: 984286
[OK] Sorts requiring temporary tables: 0% (207 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 646
[OK] Temporary tables created on disk: 20% (469K on disk / 2M total)
[OK] Thread cache hit rate: 99% (369 created / 939K connections)
[!!] Table cache hit rate: 0% (2K open / 2M opened)
[OK] Open file limit used: 8% (2K/32K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
[OK] InnoDB data size / buffer pool: 652.9M/1.2G

-------- 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
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 256M) [see warning above]
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 16000)
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,236
463
Hello :)

You could modify the variables based on the "Variables to adjust" results under "Recommendations", however note that you may want to consult with a qualified system administrator for assistance with tuning your MySQL configuration if you do not receive sufficient user-feedback on this thread.

Thank you.