Оптимизация индексов и MySQL в целом

#mysql #linux

Стоял настроенный "более-менее" сервер чуть более полу года. SELECT запросы летали
в прямом смысле. Средняя скорость самых частых запросов типа:

SELECT id, name, ... FROM table WHERE id = 1 LIMT 1

SELECT id, name, ... FROM table WHERE id IN (4,2,1,3) ORDER BY id = 4 DESC, id =
2 DESC, id = 1 DESC, id = 3 LIMIT 4


колеблась в среднем до 0.0050 сек. Таблица размером под 1Гб, а индекс около 25 Мб.
~180,000к записей. Тип MyiSAM.

После аварийного выключения сервера провайдером сразу крашнулись самые большие таблицы
имеющие больше 30,000к записей.
Восстановить удалось только с помощью консоли и функции myisamchk -r -f table.MYI.

Сразу же после успешного восстановления, по непонятным причинам упала производительность
MySQL в целом. Конфиг my.cnf не изменялся. Нагрузка на сервер была такой же как и раньше
30-50%. Мои запросы(которые сверху да и запросы к маленьким таблицам тоже) начали выполняться
не за доли тысячной секунды, а от 0.50 сек. и выше. Сразу же почувствовалось проседание
всего сайта в скорости.

Решил покрутить значения в my.cnf и заодно делать запросы замеряя функцией microtime().
Кэш на время выключил ибо меня больше интересует мгновенная выдача неизвестного результата
для MySQL.

Во время настройки заметил парадокс, что чем старее я выбираю id записи, т.е самые
первые где-то от 0 до 10,000, запросы выполняются очень быстро. А если запрашивать
пример id = 80000 или 170000 ищет очень долго, доходит до 1 секунды и то это для одиночных
запросов, а для WHERE id IN() вообще уходит в долгое раздумье. Такое чувство будто
используется только половина индексов или даже меньше.

Я уже не знаю как еще изменять переменные. Перепробовал кучу советов и формул для
настройки, но ничего путного не получил. Будто поменял сервер, и то когда я менял сервер,
добиться хорошей скорости мог в разы быстрее и my.cnf не был идеален судя по количеству
советов во вкладке "Состояние" который в панели PhpMyAdmin. Сейчас кручу, верчу особо
скорость не меняется. Кто лучше понимает в оптимизации этого конфига, прошу помощи.

Сервер 1Gb RAM
1 CPU
Debian 8
MySQL 5.5.53


Конфиг my.cnf:

key_buffer_size = 204M

query_cache_size = 1M
query_cache_min_res_unit = 3800
query_cache_limit = 64M
query_cache_type = OFF

max_heap_table_size = 30M
tmp_table_size = 30M

max_connections = 200
thread_cache_size = 100

read_buffer_size = 256K
read_rnd_buffer_size = 3M
join_buffer_size = 16M
sort_buffer_size = 800K

myisam_sort_buffer_size = 128M
table_open_cache = 270


Результат MySQLTuner:

[!!] Maximum reached memory usage: 2.1G (218.11% of installed RAM)
[!!] Maximum possible memory usage: 4.2G (428.94% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] Slow queries: 7% (52K/667K)
[OK] Highest usage of available connections: 48% (96/200)
[OK] Aborted connections: 0.00%  (0/86540)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 46K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 9% (56 on disk / 606 total)
[OK] Table cache hit rate: 51% (270 open / 522 opened)
[OK] Open file limit used: 44% (452/1K)
[OK] Table locks acquired immediately: 99% (209K immediate / 211K locks)

-------- ThreadPool Metrics --------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema --------------------------------------------------------------
[--] Performance schema is disabled.

-------- MyISAM Metrics ------------------------------------------------------------------
[!!] Key buffer used: 20.4% (43M used / 213M cache)
[OK] Key buffer size / total MyISAM indexes: 204.0M/22.3M
[OK] Read Key buffer hit rate: 99.8% (1M cached / 3K reads)
[!!] Write Key buffer hit rate: 16.2% (44K cached / 37K writes)


(Накрутил RAM больше чем нужно :D, производительность слегка увеличилась и то только
для запросов id которых от 0 до 1000, но это все-равно не то, что я хотел.)
    

Ответы

Ответ 1

S.M.A.R.T показал, что диск угроблен. Обратился к провайдеру и проблему решили перемещением
на другой физический сервер.