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

[Melanxolik]

http://ruhighload.com/post/Работа+с+индексами+в+MySQL

[KaYot]

Да, запрос нужно переделать, на входе давать start_time и end_time сразу в unixtime, и проверки делать стандартным >start AND <end. Тогда заработает индекс и по этому полю.

А при использовании FROM_UNIXTIME ключ работать не может, идет полный просмотр таблицы.

 

Попробуйте explain еще раз сделать, с созданными ключами. Только в условии что-то вразумительное задайте, не select *

[Golthana]

Да, запрос нужно переделать, на входе давать start_time и end_time сразу в unixtime, и проверки делать стандартным >start AND

 

Я даже представление не имею каким должен быть запрос. Честно говоря, я еле этот склеил) Так что был бы признательный за пример запроса в моем случае

[Golthana]

Попробуйте explain еще раз сделать, с созданными ключами. Только в условии что-то вразумительное задайте, не select *

 

Индекс еще создается... Так что позже сделаю

 

Переменные stardate и enddate имеют значение ГГГГ-мм-дд (2016-06-06)

Edited June 22, 2016 by Golthana

[Ruslik]

Все ерунда.

Поставьте ссд диск и будет нормально.

Диск даст +10-20тыс% производительности.

Индексы-кеши дадут максимум +20-30%.

[Golthana]

 

 

Поставьте ссд диск и будет нормально.

 

К сожалению, нет такой возможности. Даже, если результатом всех вышеупомянутых манипуляций даст хоть какой-то результат - уже будет прорыв в моём случае. А за совет спасибо. В будущем буду обращать внимание.

[KaYot]

Все ерунда.

Поставьте ссд диск и будет нормально.

Диск даст +10-20тыс% производительности.

Индексы-кеши дадут максимум +20-30%.

Нормальная база должна целиком быть в RAM-кеше, и обращаться к диску изредка для выгрузки блока.

Предложение поставить SSD ерунда.

Да и индексы дадут не 20-30%, а 20к-30к%

[Golthana]

Что-то я не могу сообразить каким должен быть запрос, чтобы unixtime сделать выборку по диапазону дат

[bos]

я не знаю, почему вы не захотели использовать вторую часть моего совета, может религия, может я чего не знаю. но на всякий случай: https://habrahabr.ru/post/66151/- почитайте зачем и как используется)

[ValRevan]

 

Все ерунда.

Поставьте ссд диск и будет нормально.

Диск даст +10-20тыс% производительности.

Индексы-кеши дадут максимум +20-30%.

Нормальная база должна целиком быть в RAM-кеше, и обращаться к диску изредка для выгрузки блока.

Предложение поставить SSD ерунда.

Да и индексы дадут не 20-30%, а 20к-30к%

 

В нормальных СУБД ОЗУ важнее дисковой подсистемы

Лично я бы сначало проверил настройки СУБД и глянул хватает ли ОЗУ 

А затем еще сам запрос бы покрутил

[Golthana]

 required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again Ошибка по время создание индекса

[Golthana]

я не знаю, почему вы не захотели использовать вторую часть моего совета, может религия, может я чего не знаю. но на всякий случай: https://habrahabr.ru/post/66151/-почитайте зачем и как используется)

 

Предлагаете разбить ее по неделям, например?

 

Дело в том, что на данный момент в таблице данные только со второго числа сего месяца

Edited June 23, 2016 by Golthana

[dead]

1) Выбрать правильный тип таблицы. Тут звучал неправильный совет сконвертировать в иннодб. Иннодб - это транзакционный движок, это очень хорошо, но не в данном случае, поскольку этот функционал только ухудшит производительность. Для таких таблиц в своем биллинге я как раз и использую майисам, для всех остальных инодб.

2) Под миллиард записей - это многовато для мускула, разумно делить инфу на таблицы и группировать инфу скажем по дням (я так делаю)

3) Подобрать правильный тип полей, по максимуму сделать их маленькими и желательно цифровыми. Например ip хранить в виде insigned int и при select делать inet_ntoa(ip). По возможности выкинуть поля, которые использоваться не будут

4) Сделать индексы и главное правильные индексы. Индексы занимают объем и чтобы и если их много, они тупо не поместятся в ОЗУ и будет тупняк. Для такой таблицы индексы могут занимать громадный объем,  несколько десятков процентов от ее объема. Возможно надо делать составные индексы - это нужно смотреть на конкретные sql

5) Хороший объем ОЗУ на сервере. Подтюнить конфиг mysql под использование большего количества ОЗУ

6) Тюнить сами  SQL Explain-ами чтобы использовали индексы

7) Более быстрый диск, конечно добавит производительности

8) Не забывать, что параллельно select  к тебя будут идти постоянные insert (тут мне влом описывать к чему это приводит и как поступить)

Короче, самый главный пункт - это 2

[dead]

Да, count(*) from table берется из метаданных таблицы, поэтому происходит мгновенно на любом количестве записей

[alsdfg]

Даю совет на миллион.

Если ТС еле запрос склепал что толку ему за индексы рассказывать?

Найми тут кого-то на форуме, дай пару по десять долларов - тебе и запрос оптимизируют и растолкуют как это было сделано. А дальше уже сам будешь мануалы курить в нужном направлении или сохранишь в избранное номер специалиста.

Хотя если скорость решения проблемы не важна, то можно и потолковать за индексы.

Edited June 23, 2016 by alsdfg

[NiTr0]

 

 

Например?

unix_secs between '".$starttime."' AND '".$endtime."

 

никаких функций от значений столбцов в WHERE, поиск только по значениям.

 

 

 

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

бред, таблицы на десятки, а то и сотни гигабайт с индексами вполне успешно работают с временем запроса в десятки миллисекунд. если руки у писавшего запросы ровные...

 

 

 

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

вы вообще в курсе, что такое "временная таблица" и когда она создается? намекаю - она создается при join-ах, которыми в запросе и не пахнет.

 

 

 

Под миллиард записей - это многовато для мускула

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

[Melanxolik]

Индексы не дадут прироста Оо, поставить ssd oO
Мне кажется кто-то увлекается...

 

Правильные индексы всегда дают прирост, БД в 100 записей можно уволить в хлам без индексов несколькими запросами.

 

INNODB под статические записи? ужас.

Подумайте зачем в обще используют InnoDB?  Один из ответов это Lock на уровне строки... А если у вас тупые Insert и select, накой вам Lock? MyISAM пока самый шустрый вариант для тупых данных.

 

 

Zabbix делает безумное количество Insert и Delete по этой причине там нужна InnoDB в  остальных случаях в сад.

Надеюсь не забываете для InnoDB perf file выставлять... а то мало ли...

 

TMP_TABLE в раму или на SSD, остальное только за счет индексов и правильного типа полей, плюс несколько ключей для тюнинга параметров, все остальное зависит исключительно от профиля хранимых данных.

Иногда лучше сделать выборку во временную таблицу, потом её обработать чем гонять массив данных туда сюда.

[NiTr0]

 

 

MyISAM пока самый шустрый вариант для тупых данных.

как показывает практика - нет, переход в заббиксе с myisam на innodb дал прирост на пару порядков.

 

myisam - разве что для сайтов-визиток и прочих "огромных объемов данных", ввиду своей примитивности.

 

 

 

Zabbix делает безумное количество Insert и Delete по этой причине там нужна InnoDB

у ТСа в таблице вообще хранятся netflow'ы. да-да, под ярд записей за 3 недели. это не "безумное кол-во инсертов"? и да, delete тутразве не будет использоваться?

 

а главное - расскажите, как MyIASM живет при 600 инсертах в секунду, и при размере БД в несколько десятков ярдов записей...  и как хорошо он переносит внезапные отключения питания

[dead]

INSERT-ы надо агрегировать в INSERT INTO .. (val, val,val),  (val, val,val),  (val, val,val) - тогда никаких проблем не будет

[Prime]

еще можно поставить PostgreSQL и с коробки будет все работать без тюнинга

[Golthana]

 

 

еще можно поставить PostgreSQL и с коробки будет все работать без тюнинга

 

Можно затестить)

[Melanxolik]

 

 

как показывает практика - нет, переход в заббиксе с myisam на innodb дал прирост на пару порядков.

 

да не сравнивайте вы xxx с пальцем, разные задачи, разные наборы данных.

 

myisam в случае краха гораздо проще восстановить, в отличии от innodb, там огромные отличия, опять же, надо подбирать правильный инструмент под задачи.

 

Вам ранее уже объяснили чем в первую очередь отличается innodb от других типов, транзакционная модель хранения данных. В одних случая она хороша, в других убийство.

[NiTr0]

 

 

еще можно поставить PostgreSQL и с коробки будет все работать без тюнинга

постгря медленнее мускула...

 

 

 

да не сравнивайте вы xxx с пальцем, разные задачи, разные наборы данных.

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

 

 

 

myisam в случае краха гораздо проще восстановить, в отличии от innodb, там огромные отличия,

вот только проблема в том, что myisam крашится от любого чиха т.к. не умеет ни транзакции, ни проверку целостности записанных данных, а innodb - просто работает...

 

 

 

Вам ранее уже объяснили чем в первую очередь отличается innodb от других типов, транзакционная модель хранения данных. В одних случая она хороша, в других убийство.

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

[Golthana]

 

 

unix_secs between '".$starttime."' AND '".$endtime."

 

Таким образом не работает.  Запрос формируется с датой в виде ГГГГ-мм-дд, а значения в столбце unix_secs имеют другой вид. 

[Prime]

постгря медленнее мускула...

 

важно то, что грамотно и точно настроенный mysql будет быстрее