Стоял настроенный "более-менее" сервер чуть более полу года. 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, но это все-равно не то, что я хотел.)
Ответ
S.M.A.R.T показал, что диск угроблен. Обратился к провайдеру и проблему решили перемещением на другой физический сервер.
Комментариев нет:
Отправить комментарий