bohra

Registered
May 22, 2014
3
0
1
cPanel Access Level
Website Owner
My current mysql config is

Code:
[mysqld]
bind-address=127.0.0.1
query_cache_type=1
thread_cache_size=6
innodb_file_per_table=1
query_cache_size=24M
tmp_table_size=256M
open_files_limit=3186
default-storage-engine=MyISAM
max_heap_table_size="256M)"
query_cache_limit=20M
query_cache_size=20M
key_buffer=500M
my server specifications are below.

Operating system - CentOs
Hard Drive - 480 GB
mEMORY - 32GB DDR3
Processor - 3.5ghz Quad Core E3 Bridge Xeon
64Bit


Running a Wordpress site with with W3 Total cache, the site is high trafficked. i am experiencing a load increase of about 0.1 in a daily basis.. wanted to tweak my mysql to keep my server load to the lowest..

Please provide optimum mysql config

- - - Updated - - -

Sorry forgot to mention that I am experiencing an increase in load of the Mysql CPU % of about 0.1 on a daily basis.

Any help is appreciated, thanks
 

bohra

Registered
May 22, 2014
3
0
1
cPanel Access Level
Website Owner
i have run both the reports the mysqlmonlites mysql tuner and the full mysqlmymonlite script


below is the output of the tuner

Code:
 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.6.16
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 398M (Tables: 102)
[--] Data in InnoDB tables: 1M (Tables: 72)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 17

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 57m 58s (1M q [49.860 qps], 107K conn, TX: 22B, RX: 147M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 920.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.1G (3% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 11% (18/151)
[OK] Key buffer size / total MyISAM indexes: 500.0M/69.6M
[OK] Key buffer hit rate: 99.9% (1M cached / 1K reads)
[OK] Query cache efficiency: 72.1% (642K cached / 891K selects)
[!!] Query cache prunes per day: 159462
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 42K sorts)
[!!] Temporary tables created on disk: 46% (6K on disk / 14K total)
[OK] Thread cache hit rate: 99% (878 created / 107K connections)
[OK] Table cache hit rate: 97% (283 open / 290 opened)
[OK] Open file limit used: 6% (280/4K)
[OK] Table locks acquired immediately: 99% (106K immediate / 106K locks)
[OK] InnoDB buffer pool / data size: 128.0M/1.1M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 20M)
also attached is the complete report
 

Attachments

Last edited by a moderator:

JaredR.

Well-Known Member
Feb 25, 2010
1,834
24
143
Houston, TX
cPanel Access Level
Root Administrator
Please use CODE tags, instead of QUOTE tags, when pasting output from the shell into a forum message. The reason this is helpful is it uses a fixed-width font, just like the terminal, so it is a more accurate representation of what you see in your SSH session.

A shortcut button for the CODE tags is available if you click "Go Advanced" while you compose your reply. The button looks like a pound sign, #.
 

bohra

Registered
May 22, 2014
3
0
1
cPanel Access Level
Website Owner
Please use CODE tags, instead of QUOTE tags, when pasting output from the shell into a forum message. The reason this is helpful is it uses a fixed-width font, just like the terminal, so it is a more accurate representation of what you see in your SSH session.

A shortcut button for the CODE tags is available if you click "Go Advanced" while you compose your reply. The button looks like a pound sign, #.
yes i by mistakenly clicked the wrong button.. please help me in optimization