Есть веб-сайт, есть страница где клиент может посмотреть в одном месте всю основную статистику по своей работе. Есть тормозные запросы на выборку (несколько секунд). Есть база MySql. Есть большая таблица из которой идет выборка. Нет подходящего индекса чтобы выборка его использовала. В таблице уже есть 5 индексов. В таблице ~40 столбцов и 700К строк. Строки добавляются пачками примерно несколько раз в час. В сумме прогнозируемый прирост строк в таблице ~20K строк в месяц. Прогнозируемый прирост столбцов — пара-тройка в год. Селекты и изменения происходят примерно в равных пропорциях. Возможно селектов чуть больше. Конкретно этот тормозной запрос используется нечасто (допустим у нас сотни клиентов и клиент может зайти в статистику в среднем раз в несколько дней), но когда используются — тормоза заметны на глаз.
Какие у нас варианты:
1) Добавить индекс.
2) Реализовать кеширование (и обновлять например раз в день).
3) Сделать сервис который на внутреннем (главном т.е.) сервере будет подсчитывать статистику и отправлять ее по ночам на сайт по веб-сервису.
4) Сделать вертикальное партицирование на 2 таблицы. В одной минимальные индексы и туда будут добавляться новые данные. Во второй данные со всеми нужными индексами и раз в день например в нее переносятся данные из первой таблицы. Добавить view которая будет объединять эти 2 таблицы. Ну и работать через эту view. Т.е. бОльшая часть выборок будет затрагивать вторую таблицу с индексаим и все будет быстро.
5) Изменить требования, например тормозную информацию предоставлять только по отдельному запросу (щелчку) от пользователя.
После экспериментов с MySQL я получил приближенные результаты, что добавление очередного индекса, приводит к замедлению инсертов примерно на 2%. Не сказать что много, но если индексов будут десятки — разница будет заметна.
Можно сделать кеш, но это заморочнее (чем просто добавить индекс) и к тому же не всегда это даст крутой эффект. Если клиент будет раз в день заходить в статистику, то каждый раз она будет пересчитываться и будет так же все тормозно.
По поводу вариантов 3 и 4 — сделать реально, но есть сомнения в том что эти заморочки будут оправданны.
Изменить требования тоже можно, но для клиента это будет менее удобным (т.е. он не будет все сразу видеть, придется дополнительно щелкать)
Я склоняюсь к тому чтобы не париться и добавить индекс. Но меня все-таки беспокоит, что это может привести к тому, что сегодня я добавлю индекс, через пару месяцев еще один, и через пару лет у меня будут десятки индексов и просажены изменения в 1.5 раза.
Здравствуйте, MozgC, Вы писали:
MC>Я склоняюсь к тому чтобы не париться и добавить индекс. Но меня все-таки беспокоит, что это может привести к тому, что сегодня я добавлю индекс, через пару месяцев еще один, и через пару лет у меня будут десятки индексов и просажены изменения в 1.5 раза.
"Проблемы надо решать по мере поступления". Индекс надо добавлять
Шурыгин Сергей
"Не следует преумножать сущности сверх необходимости" (с) Оккам
Здравствуйте, MozgC, Вы писали:
MC>Поделитесь вашим опытом. Заранее спасибо.
Я за 3 вариант. По моему опыту, статистика вещь такая, которая со временем становится все сложнее и требуется все чаще.
Я бы считал ее и писал в другую таблицу. А в скрипте предусмотрел бы fallback — если статистика не подсчитана, делаем старый (долгий) запрос.
Если принять во внимание это : > Есть веб-сайт, есть страница где клиент может посмотреть в одном месте > всю основную статистику по своей работе. Есть тормозные запросы на > выборку (несколько секунд). Есть база MySql. Есть большая таблица из
> Я склоняюсь к тому чтобы не париться и добавить индекс. Но меня все-таки > беспокоит, что это может привести к тому, что сегодня я добавлю индекс, > через пару месяцев еще один, и через пару лет у меня будут десятки
то тебе надо не париться и плюнуть на ускорение этого конкретного запроса.
Несколько секунд -- это не много.
Здравствуйте, MasterZiv, Вы писали:
MZ>то тебе надо не париться и плюнуть на ускорение этого конкретного запроса. MZ>Несколько секунд -- это не много. MZ>Много — минуты, часы.
Дело в том что даже на моем компе запрос выполняется до 3-5 секунд, а на shared hosting'е — там уже в часы-пик он может выполняться десятки секунд. Так что забить — не вариант.
Здравствуйте, JazzzMaster, Вы писали:
JM>20 К строк в месяц — это вообще крохотный прирост. Делай индекс и не парься.
Да, но дело не только в добавляемых строках, они еще и меняются, думаю что на каждую из этих 20К строк еще будет раз 5 запрос на изменение слаться (запросы на изменения так же отправляются несколько раз в час, за раз меняется обычно несколько сот или несколько тысяч строк).
MozgC wrote:
> Дело в том что даже на моем компе запрос выполняется до 3-5 секунд, а на
Пофигу. Надо будет — подождут.
Короче, либо ВООБЩЕ не думай более об этом запросе, либо добавляй
индекс. Твои мысли -- это как у Умной Эльзы. Они конечно не сказать,
что неправильные, но ... В общем, не заметишь ты ничего.
А другого средства оптимизации выборок, кроме индексов, в СУБД
не существует.
Здравствуйте, MozgC, Вы писали:
MC>Есть веб-сайт, есть страница где клиент может посмотреть в одном месте всю основную статистику по своей работе. Есть тормозные запросы на выборку (несколько секунд). Есть база MySql. Есть большая таблица из которой идет выборка. Нет подходящего индекса чтобы выборка его использовала. В таблице уже есть 5 индексов. В таблице ~40 столбцов и 700К строк. Строки добавляются пачками примерно несколько раз в час. В сумме прогнозируемый прирост строк в таблице ~20K строк в месяц. Прогнозируемый прирост столбцов — пара-тройка в год. Селекты и изменения происходят примерно в равных пропорциях. Возможно селектов чуть больше. Конкретно этот тормозной запрос используется нечасто (допустим у нас сотни клиентов и клиент может зайти в статистику в среднем раз в несколько дней), но когда используются — тормоза заметны на глаз.
MC>Поделитесь вашим опытом. Заранее спасибо.
Однозначно индекс надо добавлять. Замедления в производительности ты не заметишь. Вот если у тебя вставлялось бы несколько тысяч записей в секунду — то да. А тут даже нечего размышлять.
И посмотри остальные индексы. Может быть достаточно будет поменять последовательность полей в существующем индексе и настанет счастье (мускул ведь умеет частично использовать индексы? никогда не пользовался им.
Здравствуйте, Romanzek, Вы писали:
R>Однозначно индекс надо добавлять. Замедления в производительности ты не заметишь. Вот если у тебя вставлялось бы несколько тысяч записей в секунду — то да. А тут даже нечего размышлять. R>И посмотри остальные индексы. Может быть достаточно будет поменять последовательность полей в существующем индексе и настанет счастье (мускул ведь умеет частично использовать индексы? никогда не пользовался им.
Как это по-нашему, по-русски. Я бы сказал по-RSDN-овски. "Никогда не пользовался", но советы все однозначные, "даже нечего размышлять"
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, Romanzek, Вы писали:
R>>Однозначно индекс надо добавлять. Замедления в производительности ты не заметишь. Вот если у тебя вставлялось бы несколько тысяч записей в секунду — то да. А тут даже нечего размышлять. R>>И посмотри остальные индексы. Может быть достаточно будет поменять последовательность полей в существующем индексе и настанет счастье (мускул ведь умеет частично использовать индексы? никогда не пользовался им.
W>Как это по-нашему, по-русски. Я бы сказал по-RSDN-овски. "Никогда не пользовался", но советы все однозначные, "даже нечего размышлять"
А в чем проблема? Совет добавить индекс однозначен. И мысль о том, что может быть не придется добавлять кучу индексов, так как индекс может включать несколько полей и может быть достаточко будет одного индекса сразу для нескольких видов запросов? Или вы прочитали только первую и последнюю фразу?
Шурыгин Сергей
"Не следует преумножать сущности сверх необходимости" (с) Оккам
innodb?
Этот запрос сколько в среднем строк обрабатывает (возвращает или аггрегирует)?
Сколько колонок учавствует в формировании результата? Все?
Если много, то как они кластеризованы относительно первичного ключа? случайно или есть корреляция?
Какая средняя длина одной строки?
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, Romanzek, Вы писали:
R>>Однозначно индекс надо добавлять. Замедления в производительности ты не заметишь. Вот если у тебя вставлялось бы несколько тысяч записей в секунду — то да. А тут даже нечего размышлять. R>>И посмотри остальные индексы. Может быть достаточно будет поменять последовательность полей в существующем индексе и настанет счастье (мускул ведь умеет частично использовать индексы? никогда не пользовался им.
W>Как это по-нашему, по-русски. Я бы сказал по-RSDN-овски. "Никогда не пользовался", но советы все однозначные, "даже нечего размышлять"
Так мускул — это ж РСУБД. Не думаю, что она разительно отличается от оракула или других РСУБД. Ничего нового там не придумано — в этом я уверен на 100%. А в данном вопросе все достаточно прозрачно. Если индекс значительно ускоряет выборку — значит надо его создать. Запрос конечно тоже может быть кривым и его возможно надо оптимизировать — но автор не просит
Про другие индексы надо тоже не забыть — я про это написал.
Так что критику не понял, простите. А постю я редко на РСДН, просто потому, что работы обычно много. Некогда.
MZ>>Пофигу. Надо будет — подождут.
W>Интересный подход.
MZ>>А другого средства оптимизации выборок, кроме индексов, в СУБД MZ>>не существует.
W>Еще интереснее.
Похоже ***нокодеры переползают с хабра в РСДН. Печально.
Здравствуйте, LelicDsp, Вы писали:
LD>innodb?
да.
LD>Этот запрос сколько в среднем строк обрабатывает (возвращает или аггрегирует)?
В среднем — сотни. В худшем случае — тысячи.
LD>Сколько колонок учавствует в формировании результата? Все?
Допустим до 5 колонок.
LD>Если много, то как они кластеризованы относительно первичного ключа? случайно или есть корреляция?
В MySql нет кластерных индексов.
LD>Какая средняя длина одной строки?
136
LD>>Этот запрос сколько в среднем строк обрабатывает (возвращает или аггрегирует)? MC>В среднем — сотни. В худшем случае — тысячи.
тогда индекс вполне можно добавить. у Вас получается 120 строк на одну страницу innodb, а селективность около 1/1000. Те даже в худшем случае равномерного случайного распределения будет в 10 раз быстрее фулскана.
LD>>Сколько колонок учавствует в формировании результата? Все? MC>Допустим до 5 колонок.
LD>>Если много, то как они кластеризованы относительно первичного ключа? случайно или есть корреляция? MC>В MySql нет кластерных индексов.
Это в postgres, если я правильно понимаю нет. В innodb первичный ключ — кластерный.
LD>>Какая средняя длина одной строки? MC>136
Здравствуйте, LelicDsp, Вы писали:
LD>тогда индекс вполне можно добавить. у Вас получается 120 строк на одну страницу innodb, а селективность около 1/1000. Те даже в худшем случае равномерного случайного распределения будет в 10 раз быстрее фулскана.
Вообще вопрос был не в этом. Сразу понятно было, что индекс раз в 10 минимум ускорит данную выборку. У меня чисто было опасение, что через несколько лет может накопиться куча индексов и это приведет к заметному понижению скорости изменения данных в таблице. Но решил все-таки не заморачиваться, добавить индекс, а через 5 лет если вдруг будут проблемы с производительностью — тогда их и решать — всегда можно будет дропнуть индексы и сделать один из вариантов, которые я привел в исходном посте. Так что пока добавил один один индекс + в один имеющийся индекс добавил 1 колонку + переписал пару DAL-методов, ну и теперь все ок — вся статистика в самом худшем случае собирается в пределах 1-2 секунд, а после кеширования, либо в среднем случае — до 200 мс.
wildwind wrote: > MZ>Пофигу. Надо будет — подождут. > > Интересный подход.
Ты все на свете запросы не сможешь оптимизировать, ты в курсе ?
А речь идёт именно о них.
> MZ>А другого средства оптимизации выборок, кроме индексов, в СУБД > MZ>не существует. > > Еще интереснее.
Romanzek wrote:
> Так мускул — это ж РСУБД. Не думаю, что она разительно отличается от > оракула или других РСУБД. Ничего нового там не придумано — в этом я > уверен на 100%.
MozgC wrote:
> LD>Если много, то как они кластеризованы относительно первичного ключа? > случайно или есть корреляция? > В MySql нет кластерных индексов.
Знай же, что в MySQL, а именно в InnoDb все индексы наоборот кластерные.
MozgC wrote:
> Вообще вопрос был не в этом. Сразу понятно было, что индекс раз в 10 > минимум ускорит данную выборку. У меня чисто было опасение, что через > несколько лет может накопиться куча индексов и это приведет к заметному > понижению скорости изменения данных в таблице. Но решил все-таки не > заморачиваться, добавить индекс, а через 5 лет если вдруг будут проблемы > с производительностью — тогда их и решать — всегда можно будет дропнуть > индексы и сделать один из вариантов, которые я привел в исходном посте.
В этом и заключается печальная правда об оптимизации производительности
реляционных СУБД -- вечный балланс между производительностью выборок
и модификаций. Но думаю конкретно тебе (да и на самом деле большинству
приложений) до точки, где начинает играть производительность модификаций
ещё очень далеко. Да и выборок как правило больше в несколько раз,
так что их производительность важнее в целом для приложения.
> Так что пока добавил один один индекс + в один имеющийся индекс добавил > 1 колонку + переписал пару DAL-методов, ну и теперь все ок — вся > статистика в самом худшем случае собирается в пределах 1-2 секунд, а > после кеширования, либо в среднем случае — до 200 мс.
Вот как раз для сбора статистики производительность нигде не важна.
О чём я и говорил -- можно подождать.
Здравствуйте, Romanzek, Вы писали:
R>Так мускул — это ж РСУБД. Не думаю, что она разительно отличается от оракула или других РСУБД. Ничего нового там не придумано — в этом я уверен на 100%.
К сожалению, там местами и «старого ничего не придумано».
LD>>тогда индекс вполне можно добавить. у Вас получается 120 строк на одну страницу innodb, а селективность около 1/1000. Те даже в худшем случае равномерного случайного распределения будет в 10 раз быстрее фулскана.
MC>Вообще вопрос был не в этом. Сразу понятно было, что индекс раз в 10 минимум ускорит данную выборку. У меня чисто было опасение, что через несколько лет может накопиться куча индексов и это приведет к заметному понижению скорости изменения данных в таблице. Но решил все-таки не заморачиваться, добавить индекс, а через 5 лет если вдруг будут проблемы с производительностью — тогда их и решать — всегда можно будет дропнуть индексы и сделать один из вариантов, которые я привел в исходном посте. Так что пока добавил один один индекс + в один имеющийся индекс добавил 1 колонку + переписал пару DAL-методов, ну и теперь все ок — вся статистика в самом худшем случае собирается в пределах 1-2 секунд, а после кеширования, либо в среднем случае — до 200 мс.
Ну всегда полезно проверить все варианты
Почему может падать скорость ставки со временем?
1) увеличивается глубина btree. У Вас БД небольшая относительно, считать точно считать влом, но а) вряд ли увеличение глубины дерева на 1 уровень произойдет больше одного раза, б) все равно индекс достаточно компактный
2) увеличение вероятности необходимости перебалансировки при вставке / апдейте. тут много зависит от того как расределены значения индексируемого столбца, но по-идее этот показатель не должен сильно меняться.
3) что-то я забыл еще?
Можно еще сделать по-другому, я собственно так в боевой системе и делаю сейчас, особенно в связи с тем что там есть определенные косяки в дизайне. Настраивается мастер-слэйв репликация, на мастере индексов по-минимуму, только необходимые для OLTP. Соответственно весь OLTP-style workload идет через мастер, а вся аналитика, отчеты — со слэйва, и там уже индексы сделать нужные для отчетов, и вообще можно много индексов, в т.ч. covering (fat) индексы.
Здравствуйте, LelicDsp, Вы писали:
LD>Можно еще сделать по-другому, я собственно так в боевой системе и делаю сейчас, особенно в связи с тем что там есть определенные косяки в дизайне. Настраивается мастер-слэйв репликация, на мастере индексов по-минимуму, только необходимые для OLTP. Соответственно весь OLTP-style workload идет через мастер, а вся аналитика, отчеты — со слэйва, и там уже индексы сделать нужные для отчетов, и вообще можно много индексов, в т.ч. covering (fat) индексы.
Согласен, если надо запускать на базе тяжелые отчеты — то лучше делать это на копии. У MSSQL это замечательно делается лог шиппингом, когда дочерняя база стоит в read only
Шурыгин Сергей
"Не следует преумножать сущности сверх необходимости" (с) Оккам
Здравствуйте, MasterZiv, Вы писали:
MZ>Знай же, что в MySQL, а именно в InnoDb все индексы наоборот кластерные.
Это неверное заявление. Ну, право, будьте все-таки внимательнее, сдержаннее и точнее в своих утверждениях. Во-первых, не все, т.к. кластерный индекс у таблицы может быть только один. Во-вторых, если я добавлю в таблицу только один, неуникальный, индекс, или один уникальный индекс с колонкой(ами) которая может принимать NULL-значения, то такой индекс не будет кластерным.
Здравствуйте, LelicDsp, Вы писали:
LD>Можно еще сделать по-другому, я собственно так в боевой системе и делаю сейчас, особенно в связи с тем что там есть определенные косяки в дизайне. Настраивается мастер-слэйв репликация, на мастере индексов по-минимуму, только необходимые для OLTP. Соответственно весь OLTP-style workload идет через мастер, а вся аналитика, отчеты — со слэйва, и там уже индексы сделать нужные для отчетов, и вообще можно много индексов, в т.ч. covering (fat) индексы.
Можно и так было бы сделать, но:
1) У меня веб-сайт на shared hosting'е.
2) Мне кажется что так заморачиваться нужно только при реально больших нагрузках, когда уже начинаются проблемы. Например репликация требует дополнительного надсмотра и администрирования (см. пункт 3 ниже).
3) Если вы про MySql, то на моем опыте там не очень хорошо дела с репликацией. STATEMENT based и MIXED режимы не подходят в реальной системе (регулярно время от времени всплывают ошибки репликации, в следствие чего происходит либо рассинхронизация, либо остановка вообще). Насчет ROW based репликации точно не помню, по-моему и там были проблемы. Вчера как раз опять на главном сервере настроил ROW based репликацию, посмотрим как будет работать.
MC>3) Если вы про MySql, то на моем опыте там не очень хорошо дела с репликацией. STATEMENT based и MIXED режимы не подходят в реальной системе (регулярно время от времени всплывают ошибки репликации, в следствие чего происходит либо рассинхронизация, либо остановка вообще). Насчет ROW based репликации точно не помню, по-моему и там были проблемы. Вчера как раз опять на главном сервере настроил ROW based репликацию, посмотрим как будет работать.
Пока Бог миловал тьфу-тьфу-тьфу, не считая ошибок при недостаточно большом максимальном размере пакета. Но мы, кстати, используем только statement репликацию, потому что у нас на слэйве еще и триггеры висят, которые еще дополнительные таблицы для аудита и статистики ведут.
Здравствуйте, LelicDsp, Вы писали:
LD>Пока Бог миловал тьфу-тьфу-тьфу, не считая ошибок при недостаточно большом максимальном размере пакета. Но мы, кстати, используем только statement репликацию, потому что у нас на слэйве еще и триггеры висят, которые еще дополнительные таблицы для аудита и статистики ведут.
А ХП используете? Насколько я помню большая часть проблем была от них.
Здравствуйте, _d_m_, Вы писали:
___>Здравствуйте, MasterZiv, Вы писали:
MZ>>Мне глубоко пофигу как на плюсы, так и на минусы.
___>Мне в принципе тоже, просто некоторых задевает
Тех же минусов могут понаставить кто угодно — особенно это любят тупенькие ламерки. Плюсы там оценки людям ставлю — может им приятно. Минусы ставлю очень очень редко.
.
