Оптимизация MySQL

Есть свой dedicated сервер, на нем две VPSки. На одной из них нагрузка около 2000 хостов в сутки.

конфиг такой:

[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

max_connections = 150

wait_timeout = 20000

interactive_timeout = 20000

skip-external-locking

key_buffer_size = 32M

query_cache_size = 128M

join_buffer_size = 8M

tmp_table_size = 64M

max_heap_table_size = 64M

thread_cache_size = 4

max_allowed_packet = 1M

table_cache = 30720

#table_open_cache = 64000

sort_buffer_size = 256K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 128K

#default-character-set = cp1251

init_connect = "SET NAMES 'cp1251'"

long_query_time = 10

log-slow-queries = /var/log/mysqld-slow-query.log

log-queries-not-using-indexes

skip-bdb

skip-innodb

Память на сервере:

Mem: 2098968k total, 576912k used, 1522056k free, 0k buffers

Запустил mysqltuner.pl вот что выдал

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.95-log

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 4G (Tables: 1957)

[--] Data in MEMORY tables: 762K (Tables: 6)

[!!] Total fragmented tables: 105

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 25d 0h 17m 48s (131M q [60.941 qps], 6M conn, TX: 1B, RX: 1B)

[--] Reads / Writes: 75% / 25%

[--] Total buffers: 234.0M global + 8.9M per thread (150 max threads)

[OK] Maximum possible memory usage: 1.5G (76% of installed RAM)

[OK] Slow queries: 2% (3M/131M)

[OK] Highest usage of available connections: 44% (66/150)

[OK] Key buffer size / total MyISAM indexes: 32.0M/837.3M

[OK] Key buffer hit rate: 99.9% (63B cached / 76M reads)

[OK] Query cache efficiency: 55.6% (48M cached / 87M selects)

[!!] Query cache prunes per day: 209257

[!!] Sorts requiring temporary tables: 30% (1M temp sorts / 4M sorts)

[!!] Joins performed without indexes: 137119

[OK] Temporary tables created on disk: 16% (898K on disk / 5M total)

[OK] Thread cache hit rate: 90% (598K created / 6M connections)

[OK] Table cache hit rate: 86% (2K open / 3K opened)

[OK] Open file limit used: 7% (4K/61K)

[OK] Table locks acquired immediately: 98% (64M immediate / 65M locks)

[!!] Connections aborted: 6%

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Adjust your join queries to always utilize indexes

Your applications are not closing MySQL connections properly

Variables to adjust:

query_cache_size (> 128M)

sort_buffer_size (> 256K)

read_rnd_buffer_size (> 256K)

join_buffer_size (> 8.0M, or always use indexes with joins)