MySQL my.cnf File Optimization help

I need help for Optimizing my MySQL Server for my Dedicated server with configuration of Intel Core 2 Duo CPU E7500 @ 2.93GHz 4GB of RAM installed. OS installed is CentOS 6.5. Mostly on my Dedicated Server I run Wordpress based websites over MyISAM Tables. The Server Response Time is greater than 1.5ms which I would like to reduce. If I open a page which is only basic php it takes 800ms but if I open page with (Wordpress Website)php and mysql it takes 1.5ms to respond. Some Information from phpmyadmin (All Status Variables):-

Aborted clients 92.8k
Aborted connects 2.3k

My Current my.cnf configuration is:-

[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[mysqld]
server-id=14
table_open_cache=512
default-storage-engine=MyISAM
sort_buffer_size=2M
thread_concurrency=4
open_files_limit=6192
key_buffer=384M
read_rnd_buffer_size=8M
thread_cache_size=8
query_cache_size=32M
skip-external-locking
innodb_file_per_table=1
connect_timeout=10
max_user_connections=60
wait_timeout=40
max_allowed_packet=1M
myisam_sort_buffer_size=64M
read_buffer_size=2M
max_connections=300
[mysqlhotcopy]
interactive-timeout
[myisamchk]
key_buffer=256M
write_buffer=2M
read_buffer=2M
sort_buffer_size=256M

and the MySQLTuner.pl output is:-

MySQLTuner 1.3.0 - Major Hayden Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering [ [0;32mOK [0m] Currently running supported MySQL version 5.6.16 [ [0;32mOK [0m] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[ [0;34m-- [0m] Status: [0;32m+ARCHIVE [0m [0;32m+BLACKHOLE [0m [0;32m+CSV [0m [0;31m-FEDERATED [0m [0;32m+InnoDB [0m [0;32m+MRG_MYISAM [0m
[ [0;34m-- [0m] Data in MyISAM tables: 622M (Tables: 962)
[ [0;34m-- [0m] Data in InnoDB tables: 6M (Tables: 155)
[ [0;34m-- [0m] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[ [0;34m-- [0m] Data in MEMORY tables: 0B (Tables: 3)
[ [0;31m!! [0m] Total fragmented tables: 247

-------- Security Recommendations  -------------------------------------------
[ [0;32mOK [0m] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[ [0;34m-- [0m] Up for: 59d 0h 20m 19s (144M q [28.319 qps], 2M conn, TX: 432B, RX: 22B)
[ [0;34m-- [0m] Reads / Writes: 94% / 6%
[ [0;34m-- [0m] Total buffers: 576.0M global + 12.5M per thread (300 max threads)
[ [0;31m!! [0m] Maximum possible memory usage: 4.2G (112% of installed RAM)
[ [0;32mOK [0m] Slow queries: 0% (83/144M)
[ [0;32mOK [0m] Highest usage of available connections: 27% (82/300)
[ [0;32mOK [0m] Key buffer size / total MyISAM indexes: 384.0M/234.3M
[ [0;32mOK [0m] Key buffer hit rate: 99.7% (3B cached / 11M reads)
[ [0;31m!! [0m] Query cache is disabled
[ [0;32mOK [0m] Sorts requiring temporary tables: 0% (4 temp sorts / 42M sorts)
[ [0;31m!! [0m] Joins performed without indexes: 96992
[ [0;31m!! [0m] Temporary tables created on disk: 33% (5M on disk / 15M total)
[ [0;32mOK [0m] Thread cache hit rate: 99% (4K created / 2M connections)
[ [0;31m!! [0m] Table cache hit rate: 0% (512 open / 620K opened)
[ [0;32mOK [0m] Open file limit used: 16% (1K/6K)
[ [0;32mOK [0m] Table locks acquired immediately: 99% (158M immediate / 158M locks)
[ [0;32mOK [0m] InnoDB buffer pool / data size: 128.0M/6.3M
[ [0;32mOK [0m] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    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_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=1)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 512)

I would be grateful to you for your optimized valuable suggestion and times.

postcd

June 09, 2014 07:51

im noob but i would raise tmp_table_size (> 16M) max_heap_table_size (> 16M) ..to 32M or 64M, then restart mysqld service but this wont improve main issue you have - 800ms to load basic .php file

cPanelMichael

June 09, 2014 15:51

Hello :) Feel free to try implementing some of the suggestions from the tuner output, let MySQL run for an additional 24 hours, and then run the tuner again to see the updated results. Thank you.

ashishpatel1992

June 09, 2014 17:44

Do I really need to restart the MySQL server so that old results are not again calculated by MySQLTuner or I can do changes without restarting MySQL Server Regards, Ashish [quote="cPanelMichael, post: 1660682">Hello :) Feel free to try implementing some of the suggestions from the tuner output, let MySQL run for an additional 24 hours, and then run the tuner again to see the updated results. Thank you.

June 09, 2014 18:21

You will need to restart MySQL to ensure the changes you make to /etc/my.cnf are utilized by the MySQL server. Thank you.

johntang

June 10, 2014 08:08

I think you need lowering key_buffer and sort_buffer_size will boost also. and other variables are nice. then service mysqld restart.

MySQL my.cnf File Optimization help

Comments

Didn't find what you were looking for?