Форум пользователей MySQL

kingkobra97 · 08.12.2016 21:24:11

Стоял настроенный "более-менее" сервер чуть более полу года. 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 до 1000, запросы выполняются очень быстро. А если запрашивать пример id = 80000 или 150000 ищет очень долго, доходит до 1 секунды и более. Такое чувство будто используется только половина индексов. Я уже не знаю как еще изменять переменные. Перепробовал кучу советов и формул для настройки, но ничего путного не получил. Кто лучше понимает, прошу помощи.
Сервер 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 больше чем нужно , производительность слегка увеличилась и то только для запросов id которых от 0 до 1000, но это все-равно не то, что я хотел.)

Отредактированно kingkobra97 (08.12.2016 22:15:57)

vasya · 08.12.2016 22:25:42

покажите:
show create table `имя таблицы`;

explain select ...;

и профайлинг запроса http://webew.ru/articles/2732.webew

kingkobra97 · 08.12.2016 23:27:51

vasya написал:
покажите:
show create table `имя таблицы`;

explain select ...;

и профайлинг запроса http://webew.ru/articles/2732.webew

  SHOW CREATE TABLE `table`;

  CREATE TABLE `table` (

 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

 `info_hash` varbinary(40) DEFAULT NULL,

 `name` varchar(255) NOT NULL DEFAULT '',

 `moder` smallint(5) NOT NULL,

 `descr` longtext NOT NULL,

 `category` tinyint(2) NOT NULL,

 `size` bigint(15) DEFAULT NULL,

 `added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

 `numfiles` smallint(5) NOT NULL DEFAULT '0',

 `comments` smallint(5) unsigned NOT NULL DEFAULT '0',

 `times_completed` int(7) unsigned NOT NULL DEFAULT '0',

 `leechers` int(7) unsigned NOT NULL DEFAULT '0',

 `seeders` int(7) unsigned NOT NULL DEFAULT '0',

 `tscode` varbinary(40) DEFAULT NULL,

 `visible` enum('yes','no') NOT NULL DEFAULT 'yes',

 `owner` smallint(5) unsigned NOT NULL DEFAULT '0',

 `owner_name` varchar(25) NOT NULL,

 `owner_class` tinyint(3) NOT NULL,

 `sticky` enum('yes','no') NOT NULL DEFAULT 'no',

 `magnet` text,

 `anonymous` enum('yes','no') DEFAULT 'no',

 `tube` tinytext NOT NULL,

 `tags` tinytext NOT NULL,

 `modded` enum('yes','no') NOT NULL DEFAULT 'no',

 `scrape_time` bigint(15) NOT NULL,

 `scrape` enum('yes','no') DEFAULT 'no',

 `updscrape` tinyint(1) DEFAULT '0',

 `lang` tinyint(2) DEFAULT NULL,

 `url` varchar(250) DEFAULT NULL,

 `poster` varchar(255) NOT NULL,

 `allowcom` enum('yes','no') DEFAULT 'no',

 `closed` enum('yes','no') NOT NULL DEFAULT 'no',

 `vote` float NOT NULL DEFAULT '0',

 `voters` smallint(5) NOT NULL DEFAULT '0',

 `cast` text,

 `director` text,

 PRIMARY KEY (`id`),

 UNIQUE KEY `info_hash` (`info_hash`),

 KEY `owner` (`owner`),

 KEY `scraping` (`updscrape`)

) ENGINE=MyISAM AUTO_INCREMENT=177840 DEFAULT CHARSET=utf8

EXPLAIN SELECT id, info_hash, name, moder, descr, category AS catid, size, added, numfiles, comments, times_completed, leechers, seeders, owner, owner_name AS username, owner_class AS class, magnet, anonymous, tube, tags, modded, scrape_time, scrape, updscrape, lang, url, allowcom, closed, vote, voters FROM table WHERE id = 147834 LIMIT 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table const PRIMARY PRIMARY 4 const 1

Профайлинг

+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000047 |
| Waiting for query cache lock | 0.000004 |
| checking query cache for query | 0.000086 |
| checking permissions | 0.000007 |
| Opening tables | 0.000027 |
| System lock | 0.000010 |
| Waiting for query cache lock | 0.000035 |
| init | 0.000052 |
| optimizing | 0.000015 |
| statistics | 0.071843 |
| preparing | 0.000028 |
| executing | 0.000004 |
| Sending data | 0.000087 |
| end | 0.000010 |
| query end | 0.000003 |
| closing tables | 0.000011 |
| freeing items | 0.000010 |
| Waiting for query cache lock | 0.000004 |
| freeing items | 0.000033 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000001 |
| storing result in query cache | 0.000003 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+--------------------------------+----------+

Отредактированно kingkobra97 (08.12.2016 23:31:48)

vasya · 08.12.2016 23:52:22

это профайлинг запроса (select..) или плана запроса (explain select..)?

kingkobra97 · 08.12.2016 23:53:10

vasya написал:
это профайлинг запроса (select..) или плана запроса (explain select..)?

это (select..), а нужен и (explain select..)?

vasya · 08.12.2016 23:57:58

нужен профайлинг для select, но в показанном я не вижу выполнение более 0.5 сек, поэтому и уточнил

vasya · 09.12.2016 00:00:43

можно попробовать:
ANALYZE TABLE `имя таблицы`;
пересоздать таблицу
проверить диск на ошибки

kingkobra97 · 09.12.2016 00:09:25

Тот запрос выполнялся 0.07, что уже очень много. Должен как минимум 0.007.
Вот более тяжелый запрос. Оптимизировать тут нечего. Раньше он просто летал.

EXPALIN SELECT id, name, url, times_completed as download, leechers as peers, seeders as seeds, size FROM table WHERE id IN (177771,3015,10915,11214,11871,12618,13480,13529,13557,13578) ORDER BY id = 177771 DESC,id = 3015 DESC,id = 10915 DESC,id = 11214 DESC,id = 11871 DESC,id = 12618 DESC,id = 13480 DESC,id = 13529 DESC,id = 13557 DESC,id = 13578 DESC LIMIT 10

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table range PRIMARY PRIMARY 4 NULL 10 Using where; Using filesort

+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000335 |
| Waiting for query cache lock | 0.000014 |
| checking query cache for query | 0.000111 |
| checking permissions | 0.000014 |
| Opening tables | 0.000029 |
| System lock | 0.000014 |
| Waiting for query cache lock | 0.000032 |
| init | 0.000050 |
| optimizing | 0.000019 |
| statistics | 0.000089 |
| preparing | 0.000030 |
| executing | 0.000010 |
| Sorting result | 1.575109 |
| Sending data | 0.000209 |
| end | 0.000015 |
| query end | 0.000007 |
| closing tables | 0.000014 |
| freeing items | 0.000016 |
| Waiting for query cache lock | 0.000007 |
| freeing items | 0.000617 |
| Waiting for query cache lock | 0.000011 |
| freeing items | 0.000006 |
| storing result in query cache | 0.000008 |
| logging slow query | 0.000006 |
| cleaning up | 0.000007 |
+--------------------------------+----------+

Отредактированно kingkobra97 (09.12.2016 00:14:27)

kingkobra97 · 09.12.2016 00:23:40

Как проверить диск на ошибки? Я больше склоняюсь к диску потому, что запросы выполняются хаотично. То быстро, то медленно. Кэша естественно нет поэтому это не из-за него. Сколько бы я не крутил my.cnf не могу найти золотую середину. В день когда началось все это, по логам я увидел подобное.

161201 14:23:11 [ERROR] /usr/sbin/mysqld: Table './db/all_visits' is marked as crashed and should be repaired

Через некоторое время

161201 14:23:12 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './db/users.MYI'; try to repair it

И ошибка под ним же

161201 14:31:11 [ERROR] Got an error from thread_id=4837, /build/mysql-5.5-8EvVme/mysql-5.5-5.5.52/storage/myisam/mi_update.c:226

После я восстановил таблицу all_visits, но что-то снова пошло не так

161202 21:39:51 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './db/all_visits.MYI'; try to repair it

Через час сломались мои самые большие таблицы

161202 22:55:57 [ERROR] /usr/sbin/mysqld: Table './db/all_visits' is marked as crashed and should be repaired
161202 22:55:57 [ERROR] /usr/sbin/mysqld: Table './db/table' is marked as crashed and should be repaired

Очень странное поведение.

Отредактированно kingkobra97 (09.12.2016 00:25:24)

vasya · 09.12.2016 00:58:07

в первом 0.07 это сервер выбирал какой индекс использовать
во втором 1.57 - сортировка 10 строк
весьма странное поведение

по диску: утилита, которая читает смарт; что показывает iostat; вопрос хостеру.

kingkobra97 · 09.12.2016 17:28:58

vasya написал:
в первом 0.07 это сервер выбирал какой индекс использовать
во втором 1.57 - сортировка 10 строк
весьма странное поведение

по диску: утилита, которая читает смарт; что показывает iostat; вопрос хостеру.

smart не знаю если верен, провайдер многое потер.

Также лог iostat сделал в течении 10 секунд.

Прикрепленные файлы:

log.txt, Размер: 6,657 байт, Скачано: 1,218

vasya · 10.12.2016 10:15:27

см колонку WHEN_FAILED
диск умер

kingkobra97 · 11.12.2016 13:16:46

vasya написал:
см колонку WHEN_FAILED
диск умер

Благодарю за помощь. Провайдер перенес на другой физический сервер. Все стало работать как и раньше.

Форум пользователей MySQL

#1 08.12.2016 21:24:11

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

#2 08.12.2016 22:25:42

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

#3 08.12.2016 23:27:51

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

vasya написал:

#4 08.12.2016 23:52:22

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

#5 08.12.2016 23:53:10

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

vasya написал:

#6 08.12.2016 23:57:58

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

#7 09.12.2016 00:00:43

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

#8 09.12.2016 00:09:25

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

#9 09.12.2016 00:23:40

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

#10 09.12.2016 00:58:07

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

#11 09.12.2016 17:28:58

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

vasya написал:

#12 10.12.2016 10:15:27

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

#13 11.12.2016 13:16:46

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

vasya написал:

Board footer