Skip navigation links

MySQL Forums :: Performance :: Help tunning MySQL 5.5 (query_cache_size, table_cache, ...)


Advanced Search

Help tunning MySQL 5.5 (query_cache_size, table_cache, ...)
Posted by: Dimas Streich ()
Date: February 18, 2013 06:46AM

Good morning,

I've a MySQL server (Ubuntu 64bits, 2 cores, 4gb ram) with about 90 databases, 1700 MyISAM tables and 1100 InnoDB tables. I haven't deep acknowledgement of MySQL and their configuration parameters and I'd like to ask for some help about tuning these parameters and performance tips.

I tried executing MySQL Tunner (v 1.2) tool but I don't know how to interpret the values. It recommends me to change this parameters::
- query_cache_size (> 32M)
- join_buffer_size (> 512.0K, or always use indexes with joins)
- table_cache (> 200)
- innodb_buffer_pool_size (>= 791M)

I tried updating their values to a higher number but the problem persist, and I don't know which values are the recommendable.

The full output is this:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28-0ubuntu0.12.04.3-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 911M (Tables: 1656)
[--] Data in InnoDB tables: 791M (Tables: 1105)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 497K (Tables: 7)
[!!] Total fragmented tables: 143

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 24d 13h 37m 52s (282M q [132.975 qps], 6M conn, TX: 1509B, RX: 54B)
[--] Reads / Writes: 7% / 93%
[--] Total buffers: 144.0M global + 3.1M per thread (160 max threads)
[OK] Maximum possible memory usage: 634.0M (16% of installed RAM)
[OK] Slow queries: 0% (2M/282M)
[OK] Highest usage of available connections: 75% (121/160)
[OK] Key buffer size / total MyISAM indexes: 16.0M/495.3M
[OK] Key buffer hit rate: 99.3% (15B cached / 103M reads)
[OK] Query cache efficiency: 90.0% (198M cached / 220M selects)
[!!] Query cache prunes per day: 163815
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 32714
[OK] Temporary tables created on disk: 8% (807K on disk / 8M total)
[OK] Thread cache hit rate: 98% (71K created / 6M connections)
[!!] Table cache hit rate: 0% (200 open / 525K opened)
[OK] Open file limit used: 26% (268/1K)
[OK] Table locks acquired immediately: 99% (300M immediate / 300M locks)
[!!] InnoDB data size / buffer pool: 791.9M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 512.0K, or always use indexes with joins)
table_cache (> 200)
innodb_buffer_pool_size (>= 791M)

Options: ReplyQuote


Subject Views Written By Posted
Help tunning MySQL 5.5 (query_cache_size, table_cache, ...) 3354 Dimas Streich 02/18/2013 06:46AM
Re: Help tunning MySQL 5.5 (query_cache_size, table_cache, ...) 1561 Rick James 02/20/2013 10:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.