Оптимизация MYSQL

[Golthana]

Извините, если в в той теме. Вопрос по оптимизации работы mysql. Есть таблица, в которой хранится netflow. Любой запрос выполняется 10 мин. Как диагностировать проблему?

[KaYot]

http://mysqltuner.com/

Качаете скрипт, запускаете, он вам все подробно расскажет.

[Golthana]

Да. Запускал, выполнил рекомендации. Все равно выполнение запроса = 10 минутам. При том, что select COUNT(*) from 'table_name'; Делается быстро.

[alsdfg]

Да. Запускал, выполнил рекомендации. Все равно выполнение запроса = 10 минутам. При том, что select COUNT(*) from 'table_name'; Делается быстро.

Смотрите http://dev.mysql.com/doc/refman/5.7/en/explain.html

Также смотрите чтобы были индексы на поля по которым происходит выборка в запросе.

[Golthana]

mysql> EXPLAIN SELECT * FROM raw;
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows      | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------+
|  1 | SIMPLE      | raw   | ALL  | NULL          | NULL | NULL    | NULL | 820510737 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------+
1 row in set (0.01 sec)
 

[bodidron]

теорию БД курите( НФ не даром придумали

[Golthana]

Вот структура:

mysql> show create table raw;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| raw   | CREATE TABLE `raw` (
  `unix_secs` int(11) unsigned NOT NULL DEFAULT '0',
  `unix_nsecs` int(11) unsigned NOT NULL DEFAULT '0',
  `sysuptime` int(11) unsigned NOT NULL DEFAULT '0',
  `exaddr` varchar(15) NOT NULL DEFAULT '0',
  `dpkts` int(11) unsigned NOT NULL DEFAULT '0',
  `doctets` int(11) unsigned NOT NULL DEFAULT '0',
  `srcaddr` varchar(15) NOT NULL DEFAULT '0',
  `dstaddr` varchar(15) NOT NULL DEFAULT '0',
  `srcport` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dstport` smallint(5) unsigned NOT NULL DEFAULT '0',
  `prot` tinyint(3) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 

[bos]

я конечно не силен, в БД, но мне кажется, что если вы проведете партиционирование таблицы, и перепишите запросы чтобы они использовали все прелести партиционирования, то будет значительно легче. использование innodb так же предпочтительнее, разработчики говорят что myisam безнадежно устарел и в след 7м релизе  будет выпилен. (по заявлениям некоторых людей innodb показывает более лучшие результаты и в olap oltp )

[KaYot]

Первым делом нужно поменять движок таблицы на InnoDB, и задать для mysql здоровенный innodb_pool.

Сейчас у вас запросы вероятно вообще не кешируются и каждый раз выполняются с вычиткой всей таблицы с диска.

[Golthana]

Спасибо. Сейчас попробую

[NiTr0]

 

 

Вот структура

1) как уже сказали - InnoDB вместо окаменелости MyISAM

2) индексы где? без индексов - мускул тупо сравнивает каждую из 820 млн строк с критериями where...

[Golthana]

 

 

индексы где?

Их на каждый столбец прописывать?

[Golthana]

SELECT FROM_UNIXTIME(unix_secs), srcaddr, (SUM(doctets)/1024)/1024 as 'doctets' FROM raw WHERE srcaddr = '".$ip."' AND DATE_FORMAT(FROM_UNIXTIME(unix_secs), '%Y-%m-%d') between '".$startdate."' AND '".$enddate."' GROUP BY DATE_FORMAT(FROM_UNIXTIME(unix_secs), '%Y-%m-%d')

 

 

Вот весь запрос

[KaYot]

Индексами должны быть поля используемые в where. У вас это unix_secs и srcaddr.

Добавьте, это сразу даст многократный буст к скорости обработки.

[Golthana]

CREATE INDEX ip ON raw(srcaddr);

 

и

 

CREATE INDEX date ON raw(unix_secs)

ТАК?

 

А запрос поменяется от этого?

Движок таблицы уже уже сменил

Edited June 22, 2016 by Golthana

[KaYot]

Запрос не поменяется, индексы лишь упрощают движку поиск в таблице.

Теоретически 1 индекс на базе 2ух полей в вашем случае(с миллионами записей) будет еще эффективнее, но тут жена-архитектор БД сказала "хз, нужно тестить"

 

Добавьте индексы, дайте базе большой кэш innodb и будет все космически.

[NiTr0]

 

 

DATE_FORMAT(FROM_UNIXTIME(unix_secs), '%Y-%m-%d') between '".$startdate."' AND '".$enddate."

эта херня принципиально неиндексируемая. уж не в абиллсе ли такой бред подсмотрели?

 

делайте нормальный запрос (да-да, startdate и enddate в unixtime, а не вырывание гланд через анус) и делайте индекс по полям, используемым в выборке.

[Golthana]

 

 

делайте нормальный запрос

Например?

 

 

 

уж не в абиллсе ли такой бред подсмотрели?

 

Нет. В какой-то статье прочитал. Поскольку в mysql несилен, то не могу оценить правильность введенного запроса

[foreverok]

Индексы, движки - это конечно все возможно и даст небольшой прирост.

 

Судя по самому запросу и размеру БД, проблема в том, что создается временная таблица на диске (диски наверное не ssd?), причем не маленькая.

 

Посмотрите размер временной таблицы которая создается при выполнении запроса, возможно удастся засунуть её в tmpfs. 

[Golthana]

 

 

диски наверное не ssd?)

Нет

 

 

Посмотрите размер временной таблицы которая создается при выполнении запроса, возможно удастся засунуть её в tmpfs.

А ссылку на документацию по этому поводу можно? Или в двух словах?

[KaYot]

А с какой стати автоматически создаваться временной таблице?

Edited June 22, 2016 by KaYot

[Golthana]

Создаю два индекса. Процесс задумался, видимо время надо) Потом сравню результат.

 

SELECT FROM_UNIXTIME(unix_secs), srcaddr, (SUM(doctets)/1024)/1024 as 'doctets' FROM raw WHERE srcaddr = '".$ip."' AND FROM_UNIXTIME(unix_secs) between '".$startdate."' AND '".$enddate."' GROUP BY FROM_UNIXTIME(unix_secs);

Такой запрос имелся ввиду?

Edited June 22, 2016 by Golthana

[Melanxolik]

Srcaddr какой тип поля?

[Golthana]

| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| raw   | CREATE TABLE `raw` (
  `unix_secs` int(11) unsigned NOT NULL DEFAULT '0',
  `unix_nsecs` int(11) unsigned NOT NULL DEFAULT '0',
  `sysuptime` int(11) unsigned NOT NULL DEFAULT '0',
  `exaddr` varchar(15) NOT NULL DEFAULT '0',
  `dpkts` int(11) unsigned NOT NULL DEFAULT '0',
  `doctets` int(11) unsigned NOT NULL DEFAULT '0',
  `srcaddr` varchar(15) NOT NULL DEFAULT '0',
  `dstaddr` varchar(15) NOT NULL DEFAULT '0',
  `srcport` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dstport` smallint(5) unsigned NOT NULL DEFAULT '0',
  `prot` tinyint(3) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

[Melanxolik]

Я бы ip загонял в ntoa.

Unixsec генерирует кучу неповторных значений что заставляет сканировать таблицу до поиска соответствий.

Ради интереса. Если сперва найти диапазон времени и потом ip?