Страницы

Поиск по вопросам

среда, 5 декабря 2018 г.

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

Стоял настроенный "более-менее" сервер чуть более полу года. 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 показал, что диск угроблен. Обратился к провайдеру и проблему решили перемещением на другой физический сервер.

Комментариев нет:

Отправить комментарий