Skip to main content

MySQL optimization for a 16GB ram server.

johnburk
Server is a 8-core Xeon with 16GB of ram running multiple wordpress blogs. Highest usage of available connections based on the last 6 weeks has been 382. Any suggestions on how I can improve and optimize my.cnf? Mysqltuner output Fragmentation of 60 to 100 happens within 2 to 4 hours of optimizing the database. -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.35-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 4G (Tables: 2018) [--] Data in InnoDB tables: 262M (Tables: 720) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 3M (Tables: 34) [!!] Total fragmented tables: 91 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 3d 17h 26m 49s (35M q [109.964 qps], 474K conn, TX: 889B, RX: 8B) [--] Reads / Writes: 76% / 24% [--] Total buffers: 2.6G global + 14.2M per thread (400 max threads) [OK] Maximum possible memory usage: 8.1G (51% of installed RAM) [OK] Slow queries: 0% (2K/35M) [OK] Highest usage of available connections: 10% (43/400) [OK] Key buffer size / total MyISAM indexes: 1.2G/1.2G [OK] Key buffer hit rate: 99.9% (540M cached / 344K reads) [OK] Query cache efficiency: 60.5% (17M cached / 29M selects) [!!] Query cache prunes per day: 240109 [OK] Sorts requiring temporary tables: 0% (73 temp sorts / 1M sorts) [!!] Joins performed without indexes: 2285 [OK] Temporary tables created on disk: 23% (369K on disk / 1M total) [OK] Thread cache hit rate: 99% (43 created / 474K connections) [!!] Table cache hit rate: 0% (5K open / 3M opened) [OK] Open file limit used: 4% (6K/128K) [OK] Table locks acquired immediately: 99% (14M immediate / 14M locks) [OK] InnoDB data size / buffer pool: 262.7M/512.0M -------- 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 (> 512M) [see warning above] join_buffer_size (> 2.0M, or always use indexes with joins) table_cache (> 64000)
My.cnf [mysqld] connect_timeout=360 default-storage-engine=MyISAM innodb_additional_mem_pool_size=40M innodb_buffer_pool_size=512M innodb_commit_concurrency=16 innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_log_buffer_size=12M innodb_max_dirty_pages_pct=90 innodb_thread_concurrency=16 interactive_timeout=2400 join_buffer_size=2M key_buffer_size=1280M local-infile=0 log-slow-queries long_query_time=1 max_allowed_packet=32M max_connections=400 max_heap_table_size=256M open_files_limit=100000 query_cache_limit=32M query_cache_min_res_unit=512 query_cache_size=512M read_buffer_size=4M read_rnd_buffer_size=2M sort_buffer_size=6M table_cache=64K table_definition_cache=8K table_open_cache=64000 thread_cache_size=4M tmp_table_size=256M wait_timeout=2400

Comments

5 comments

Date Votes
  • cPanelMichael
    Hello :) I just wanted to note that the mysqlmymonlite script might be better suited as a tuner for MySQL version 5.5: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
    0
  • thinkbot
    lower table_cache to something like 6000 since it scales bad on high numbers table_definition_cache to 2000 Many of the values you put there are too big, it seems like it was copied from somewhere without thought The best thing would be to add caching to wordpress
    0
  • johnburk
    [quote="cPanelMichael, post: 1581151">Hello :) I just wanted to note that the mysqlmymonlite script might be better suited as a tuner for MySQL version 5.5: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
    I will give it a try, but I am not comfortable with adding my mysql root password in a plain text environment. [COLOR="silver">- - - Updated - - - [quote="thinkbot, post: 1581772">lower table_cache to something like 6000 since it scales bad on high numbers table_definition_cache to 2000 Many of the values you put there are too big, it seems like it was copied from somewhere without thought The best thing would be to add caching to wordpress
    All sites already work with caching. The values are based on advice of tools such as mysqltuner.pl and tuning-primer.sh which both seem to love giving the advice to increase values.
    0
  • thinkbot
    Exactly, tools always suggest to increase and increase :) put there this, it's cleaned up version of your my.cnf with enabled slow query log, after it gatherers some slow queries for few days, you can run pt-query-digest and review them Also install Munin module from WHM, you will have there server resource monitoring nicely displayed on graphs [mysqld] local-infile=0 connect_timeout=360 wait_timeout=2400 interactive_timeout=2400 default-storage-engine=MyISAM max_connections = 400 max_user_connections = 100 key_buffer_size=1400M join_buffer_size=2M sort_buffer_size=256K slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 query_cache_type = 1 query_cache_size = 150M query_cache_limit = 1M max_allowed_packet=32M tmp_table_size=256M max_heap_table_size=256M open_files_limit=100000 table_definition_cache=2000 table_open_cache=6000 thread_cache_size=64 innodb_buffer_pool_size=512M innodb_file_per_table=1
    0
  • johnburk
    [quote="thinkbot, post: 1582691">Exactly, tools always suggest to increase and increase :) put there this, it's cleaned up version of your my.cnf with enabled slow query log, after it gatherers some slow queries for few days, you can run pt-query-digest and review them Also install Munin module from WHM, you will have there server resource monitoring nicely displayed on graphs [mysqld] local-infile=0 connect_timeout=360 wait_timeout=2400 interactive_timeout=2400 default-storage-engine=MyISAM max_connections = 400 max_user_connections = 100 key_buffer_size=1400M join_buffer_size=2M sort_buffer_size=256K slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 query_cache_type = 1 query_cache_size = 150M query_cache_limit = 1M max_allowed_packet=32M tmp_table_size=256M max_heap_table_size=256M open_files_limit=100000 table_definition_cache=2000 table_open_cache=6000 thread_cache_size=64 innodb_buffer_pool_size=512M innodb_file_per_table=1

    Thank you. Unfortunately cPanel has not updated Munin for a few years. They still use 1.4.7 which has some bugs in term of memory usage.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post