Помогите с оптимизацией MySQL

Question

[Дмитрий Морозов]

Есть свой 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

mysql> SHOW STATUS;
+-----------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------+-------------+
| Aborted_clients | 4 |
| Aborted_connects | 437654 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 148 |
| Bytes_sent | 336 |
| Com_select | 1 |
| Com_show_status | 2 |
| Compression | OFF |
| Connections | 6605115 |
| Created_tmp_files | 981624 |
| Created_tmp_tables | 1 |
| Flush_commands | 1 |
| Handler_write | 132 |
| Key_blocks_used | 28995 |
| Key_read_requests | 63806241894 |
| Key_reads | 76824130 |
| Key_write_requests | 127477205 |
| Key_writes | 47262433 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 66 |
| Open_files | 4915 |
| Open_tables | 2948 |
| Qcache_free_blocks | 10967 |
| Qcache_free_memory | 37494008 |
| Qcache_hits | 48773973 |
| Qcache_inserts | 31843780 |
| Qcache_lowmem_prunes | 5234025 |
| Qcache_not_cached | 7450097 |
| Qcache_queries_in_cache | 42978 |
| Qcache_total_blocks | 99926 |
| Queries | 131748067 |
| Questions | 3 |
| Rpl_status | NULL |
| Select_scan | 1 |
| Slave_running | OFF |
| Table_locks_immediate | 64940767 |
| Table_locks_waited | 1065540 |
| Threads_connected | 5 |
| Threads_created | 598470 |
| Threads_running | 3 |
| Uptime | 2162098 |
+-----------------------------------+-------------+
248 rows in set (0.00 sec)

Запустил 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)

Answer 1

[Vampiro]

Даже не знаю как вам намекнуть, чтобы вы не обиделись. В вашем вопросе нет ничего такого, что не стало бы очевидным, прочти вы хотя бы минимальный объем мануалов по администрированию MySQL. Более того, в тексте, который вы привели содержатся указания на необходимые изменения. Причем, так указаны как переменные, так и рекомендуемые значения. Любой совет тут будет «капитанским», например «посмотрите какие запросы у вас идут мимо индексов (log-slow-queries = /var/log/mysqld-slow-query.log) и проставьте индексы по нужным полям (Adjust your join queries to always utilize indexes).

Answer 2

[truekenny]

Тюнер просит:

1. Сменить ОС на 64 битную
2. Оптимизировать 105 таблиц
3. В настройках поставить:
   
   • query_cache_size = 256M
   • sort_buffer_size = 2M
   • read_rnd_buffer_size = 8M
   • join_buffer_size = 8M

Еще просит найти запросы, где используете JOIN, а в конструкции ON нет индексов на соотвт. полях.

Comments

[Дмитрий Морозов]

тогда мускул просит 5гигов оперативки…

[truekenny]

Поставьте max_connections = 50

[Дмитрий Морозов]

О, спасибо. кажется помогло. понаблюдаю