MYSQL 5.1.X Optimization

Hi, Centos 6.4 Memory: 1.25GB Long query time = 10 MYSQLTUNER


 >>  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
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 135M (Tables: 499)
[--] Data in InnoDB tables: 857M (Tables: 1264)
[!!] Total fragmented tables: 304

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21d 19h 27m 34s (12M q [6.801 qps], 521K conn, TX: 70B, RX: 5B)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 421.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 696.0M (54% of installed RAM)
[OK] Slow queries: 0% (174/12M)
[OK] Highest usage of available connections: 33% (33/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/28.4M
[OK] Key buffer hit rate: 99.8% (28M cached / 56K reads)
[OK] Query cache efficiency: 79.9% (6M cached / 8M selects)
[!!] Query cache prunes per day: 2159
[OK] Sorts requiring temporary tables: 0% (319 temp sorts / 163K sorts)
[!!] Joins performed without indexes: 17612
[!!] Temporary tables created on disk: 32% (92K on disk / 280K total)
[OK] Thread cache hit rate: 99% (5K created / 521K connections)
[!!] Table cache hit rate: 0% (400 open / 92K opened)
[OK] Open file limit used: 1% (81/4K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[!!] InnoDB  buffer pool / data size: 128.0M/857.5M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 96M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 857M)

much appreciated.

cPanelMichael

May 01, 2014 18:09

Hello :) Thank you posting the output from the MySQL tuner. In addition to that, are you able to provide details about any particular issues you are having with MySQL? Or, are you just seeking to improve the overall speed/performance? Note that most of the advice on the "Optimization" forum will come from other users. Thank you.

server10

May 01, 2014 18:48

I'd suggest you : 1- Increase the value of tmp_table_size to a greater value. 2- Increase the value of table_open_cache and open_files_limit variables, have in mind that you might need to change max open file size for mysql user. /http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit

Makento

May 02, 2014 08:50

Hi, we running a drupal & civicrm website. Experiencing poor page loads hence looking to speed up. Thanks

thinkbot

May 03, 2014 06:08

2 basics in your case that will make huge difference: table_cache = 2048 innodb_buffer_pool_size = 900M and modify query_cache_size = 50M then restart

MYSQL 5.1.X Optimization

Comments

Didn't find what you were looking for?