SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 08.12.2016 21:24:11

kingkobra97
Участник
Зарегистрирован: 08.12.2016
Сообщений: 12

Оптимизация индексов и 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 до 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 больше чем нужно big_smile, производительность слегка увеличилась и то только для запросов id которых от 0 до 1000, но это все-равно не то, что я хотел.)

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

Неактивен

 

#2 08.12.2016 22:25:42

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

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

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

explain select ...;

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

Неактивен

 

#3 08.12.2016 23:27:51

kingkobra97
Участник
Зарегистрирован: 08.12.2016
Сообщений: 12

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

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)

Неактивен

 

#4 08.12.2016 23:52:22

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

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

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

Неактивен

 

#5 08.12.2016 23:53:10

kingkobra97
Участник
Зарегистрирован: 08.12.2016
Сообщений: 12

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

vasya написал:

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

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

Неактивен

 

#6 08.12.2016 23:57:58

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

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

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

Неактивен

 

#7 09.12.2016 00:00:43

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

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

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

Неактивен

 

#8 09.12.2016 00:09:25

kingkobra97
Участник
Зарегистрирован: 08.12.2016
Сообщений: 12

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

Тот запрос выполнялся 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)

Неактивен

 

#9 09.12.2016 00:23:40

kingkobra97
Участник
Зарегистрирован: 08.12.2016
Сообщений: 12

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

Как проверить диск на ошибки? Я больше склоняюсь к диску потому, что запросы выполняются хаотично. То быстро, то медленно. Кэша естественно нет поэтому это не из-за него. Сколько бы я не крутил 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)

Неактивен

 

#10 09.12.2016 00:58:07

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

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

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

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

Неактивен

 

#11 09.12.2016 17:28:58

kingkobra97
Участник
Зарегистрирован: 08.12.2016
Сообщений: 12

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

vasya написал:

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

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

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

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


Прикрепленные файлы:
Attachment Icon log.txt, Размер: 6,657 байт, Скачано: 1,111

Неактивен

 

#12 10.12.2016 10:15:27

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

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

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

Неактивен

 

#13 11.12.2016 13:16:46

kingkobra97
Участник
Зарегистрирован: 08.12.2016
Сообщений: 12

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

vasya написал:

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson