Skip to main content

Need Help with mysql tweaking

bohra
My current mysql config is [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 [COLOR="silver">- - - 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

Comments

4 comments

Date Votes
  • cPanelMichael
    Hello :) Please run the MySQL tuner so we can better gauge the current performance of MySQL on your system. This thread will help you get started: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
    0
  • bohra
    i have run both the reports the mysqlmonlites mysql tuner and the full mysqlmymonlite script below is the output of the tuner >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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
    0
  • JaredR.
    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, #.
    0
  • bohra
    [quote="cPanelJared, post: 1650811">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
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post