Tunning MySQL (mysqltuner.pl)

[Dimass]

Hi,

I want to optimize a MySQL server with 2CPUs and 4Gb of RAM, shared with a Apache service.

The MySQL have abut 1500 tables (10% InnoDB).

The my.cnf settings:

innodb_buffer_pool_size = 64M
join_buffer_size        = 512K
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
#max_connections        = 100
table_cache            = 200
#thread_concurrency     = 10
query_cache_limit       = 1M
query_cache_size        = 32M
query_cache_type        = 1

The mysqltuner.pl script output this:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-1ubuntu8.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 200M (Tables: 1294)
[--] Data in InnoDB tables: 35M (Tables: 194)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 207

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 27s (30K q [114.536 qps], 304 conn, TX: 232M, RX: 5M)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 130.0M global + 3.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 592.4M (14% of installed RAM)
[OK] Slow queries: 0% (169/30K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/62.4M
[OK] Key buffer hit rate: 98.7% (765K cached / 9K reads)
[OK] Query cache efficiency: 93.6% (26K cached / 28K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 214 sorts)
[!!] Joins performed without indexes: 2
[OK] Temporary tables created on disk: 23% (67 on disk / 283 total)
[OK] Thread cache hit rate: 96% (12 created / 304 connections)
[!!] Table cache hit rate: 2% (200 open / 9K opened)
[OK] Open file limit used: 37% (385/1K)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
[OK] InnoDB data size / buffer pool: 35.5M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)
    table_cache (> 200)

I've incremented join_buffer_size and table_cache but keep throwing these advices. I don't like the "Table cache hit rate: 2% (200 open / 9K opened)".

Any advice to optimize the MySQl config?

Thx

[falko]

Did you restart MySQL after your changes?

[Dimass]

Every time...