Как повысить производительность сканирования таблиц с помощью innodb
Вкратце: есть ли способ улучшить производительность сканирования таблиц в таблицах InnoDB?
Пожалуйста, не предлагайте добавлять индексы при сканировании таблицы избегать. (см. ниже)
innodb_buffer_pool_size занимает 75% памяти сервера (48 ГБ / 64 ГБ) Я использую последнюю версию Percona (5.7.19), если это что-то меняет
Дольше: у нас есть 600 ГБ данных последних временных рядов (мы агрегируем и удаляем старые данные), распределенных по 50-60 таблицам. Так что по большей части это «активные» данные, которые регулярно запрашиваются. Эти таблицы несколько большие (400+ числовых столбцов), и многие запросы выполняются по нескольким из этих столбцов (тревожно), поэтому нецелесообразно добавлять индексы (поскольку нам пришлось бы добавить несколько десятков). Самые большие таблицы разбиваются на разделы за день.
Я полностью осознаю, что это проблема дизайна приложения / таблицы, а не проблема "настройки сервера". В настоящее время мы работаем над тем, чтобы значительно изменить способ проектирования и запроса этих таблиц, но должны поддерживать существующую систему, пока это не произойдет, поэтому я ищу способ немного улучшить ситуацию, чтобы выиграть нам немного времени.
Недавно мы разделили эту систему и переместили ее часть на новый сервер. Ранее он использовал MyISAM, и мы попытались перейти на TokuDB, что казалось подходящим, но столкнулось с некоторыми странными проблемами. Мы перешли на InnoDB, но производительность очень плохая. У меня сложилось впечатление, что MyISAM лучше справляется со сканированием таблиц, поэтому, если не считать лучшего варианта, мы вернемся к нему, пока не будет установлена новая система.
Обновлять
Все таблицы имеют примерно одинаковую структуру: -timestamp -первичный ключ (поле varchar (20)) - около 15 полей различных типов, представляющих другие вторичные атрибуты, по которым можно фильтровать (вместе с соответствующим образом проиндексированными критериями в первую очередь) -А потом около нескольких сотен тактов (поплавков), между 200-400.
Я уже обрезал длину ряда настолько, насколько мог, не меняя саму структуру. Раньше первичный ключ был varchar (100), все меры были двойными, у многих вторичных атрибутов были изменены типы данных.
Обновление оборудования на самом деле не вариант.
Создание небольших таблиц только с нужным мне набором столбцов помогло бы некоторым процессам работать быстрее. Но ценой создания этой таблицы со сканированием таблицы вначале и дублированием данных. Может, если бы я создал это как таблицу памяти. По моим оценкам, это займет пару ГБ от буферного пула. Также существуют процессы агрегирования, которые регулярно читают примерно столько же данных из основных таблиц, и им нужны все столбцы.
К сожалению, есть много дублирования усилий в тех запросах, которые я планирую рассмотреть в следующей версии. Процессы сигнализации и агрегирования в основном повторно обрабатывают данные за весь день каждый раз, когда вставляются некоторые строки (каждые полчаса), а не просто имеют дело с новыми / измененными данными.
Как я уже сказал, большие таблицы разбиты на разделы, поэтому обычно выполняется сканирование ежедневного раздела, а не всей таблицы, что является небольшим утешением.
Внедрение системы для хранения этого в памяти вне БД может сработать, но это повлечет за собой множество изменений в унаследованной системе и разработке. С таким же успехом можно потратить это время на лучший дизайн.
Тот факт, что таблица InnoDB намного больше для тех же данных, что и MyISAM (в 2-3 раза больше в моем случае), действительно снижает производительность.
Какая часть этих данных является частью процесса запроса? Не могли бы вы подробнее рассказать о структуре? У вас может быть таблица A, в которой Только - столбцы, используемые при запросе, и таблица B, в которой есть все данные. Запросы к таблице A будут быстрее, и тогда вы сможете получить значения идентификаторов для выборки из таблицы B, то, что JOIN может сделать за вас.
Вы также можете загрузить эти столбцы в память в виде постоянного процесса, который время от времени обновляется и запрашивает их. Сканирование нескольких миллионов элементов в массиве в памяти происходит очень быстро, почти нулевое время, если вы выполняете простую фильтрацию. Выполнение этого в базе данных происходит намного медленнее, обязательно из-за реляционных накладных расходов, проблем MVCC и т. д.
Спасибо за ваши предложения, даже если я не уверен, что смогу сразу использовать его, это всегда помогает обмениваться идеями. Я отредактировал свой вопрос, добавив дополнительную информацию
Ответы 2
MyISAM немного лучше выполняет сканирование таблиц, потому что он хранит данные более компактно, чем InnoDB. Если ваши запросы связаны с вводом-выводом, сканирование через меньший объем данных на диске выполняется быстрее. Но это довольно слабое решение.
Вы можете попробовать использовать сжатие InnoDB, чтобы уменьшить размер данных. Это может приблизить вас к размеру MyISAM, но вы все еще привязаны к вводу-выводу, так что это будет отстой.
В конечном счете, похоже, что вам нужна база данных, предназначенная для рабочей нагрузки OLAP, например, хранилище данных. И InnoDB, и TokuDB предназначены для рабочей нагрузки OLTP.
Пахнет хранилищем данных с «отчетами». Путем разумного выбора того, что агрегировать (выбранный из ваших плавающих объектов) за какой период времени (обычно час или день), вы можете создавать и поддерживать сводные таблицы, которые намного эффективнее работают с отчетами. Это позволяет сканировать данные только один раз (для построения сводок), а не повторно. Сводные таблицы намного меньше, поэтому отчеты выполняются намного быстрее - типично 10x.
Также возможно расширение сводных таблиц по мере вставки необработанных данных. (См. INSERT .. ON DUPLICATE KEY UPDATE ..)
И используйте разделение по дате, чтобы обеспечить эффективный DROP PARTITION вместо DELETE. Не более 50 разделов.
Если вы хотите обсудить более подробно, давайте начнем с одного из запросов, который сейчас так много сканирует.
В различных проектах, над которыми я работал, было от 2 до 7 сводных таблиц.
Имея 600 ГБ данных, вы можете выйти за пределы «приема». Если так, мы можем обсудить и это.
Я нашел ваш веб-сайт несколько месяцев назад, и мы основали на нем много нового дизайна. Спасибо, что написали это. Некоторое время мы использовали разделы, чтобы упростить обслуживание, облегчить запросы, а также улучшить скорость приема. Другие запланированные улучшения: -Используйте MySQL 8 и JSON для наших данных. Меры, которые мы получаем, регулярно меняются. Мы должны ИЗМЕНЯТЬ ТАБЛИЦЫ каждые несколько недель. -Держите SUM и COUNT для агрегатов (сводка), но также MIN, MAX, а также SUM_OF_SQUARES для инкрементной дисперсии / stddev.
Кроме того, нормализуйте ч ... из него. Это большой пробел в нашем текущем дизайне. Это значительно уменьшит размер наших таблиц. Я просто не уверен, как я могу применить эти рекомендации к нашему текущему дизайну, не проделав тонну работы, которая была бы лучше использована для воплощения будущего дизайна в реальность. Я просто искал быстрых побед, которые я мог упустить, чтобы выиграть время ... Мне удалось уменьшить частоту запросов, чтобы помочь серверу.
@Carl - «разделы .. улучшить скорость приема» - уточнить, пожалуйста. Это может быть преимущество, которое я еще не обнаружил.
@Carl - остерегайтесь "чрезмерной нормализации": не нормализируйте узкие столбцы или столбцы с «непрерывными» значениями (даты, числа с плавающей запятой и т. д.). В целом избегайте нормализации вещей, которые вы будете фильтровать или сортировать.
@Carl - Я рад слышать, что мой сайт ценится. (Вы первый, кто упомянуть использует sum_of_squares.)
@Carl - Вы знакомы с pt-online-schema-change? Это может быть «лучший» способ модифицировать изменения схемы для нормализации. Могут возникнуть некоторые хитрости, если столбцы имеют значение обновлено во время изменения схемы. (Но это другое обсуждение.)
Скорость приема: мы обнаружили, что, когда наши столы становятся большими, скорость приема замедляется. Я предполагаю, что это связано с тем, что наш первичный ключ - это varchar и отметка времени (в этом порядке), поэтому вставки не являются последовательными. Когда таблицы разделены, механизм работает с меньшими данными / индексами, чем с одной большой монолитной таблицей. Вероятно, этого не произошло бы, если бы первичный ключ позволял вставлять в конец таблицы, но это все равно может иметь некоторое влияние, если присутствуют другие индексы ... Но мне еще предстоит проверить.
"сверхнормализация": я намерен только нормализовать повторяющиеся символьные поля. Мне еще предстоит провести исчерпывающие испытания нового дизайна с большим объемом данных. Мой коллега намеревался нормализовать каждое повторение, что могло привести к множеству таблиц и сложным запросам соединения. Я думаю, что нужно соблюдать баланс, но в моих основных записях не будет никаких «строк», только временные метки, целые числа и массив чисел json. Поскольку мой набор данных определенно не помещается в памяти, и я застрял на вращающихся дисках, наличие небольших строк дает значительный выигрыш.
Я попробовал pt-online-schema-change, мне понравилось, как это работает, но его так и не удалось завершить из-за нагрузки на сервер и мер безопасности, которые приостанавливались при слишком большом количестве процессов. В конце концов. Мы просто ALTER TABLE и живем с последствиями. Несмотря на все свои ошибки, унаследованная система предназначена для обработки тайм-аутов, сбоев и ошибок любого рода.
PK(varchar, timestamp) - Если есть только «тысячи» различных варчаров, будут тысячи «горячих точек»; это не должно замедлять работу. Но с "миллионами" может быть ввод-вывод для чтения-изменения-записи случайных блоков.
Во многих (не во всех) случаях разделение - это просто компромисс - выбор раздела в сравнении с «меньшим индексом».
@Carl - неуникальные вторичные индексы имеют лишь ограниченное влияние. См. «Изменить буферизацию».
В худшей таблице около 65000 различных типов VARCHAR. Временные метки также не являются всемирным координированным временем (мы также изменим) и взяты из 6 разных часовых поясов. ВСТАВКИ становились все медленнее по мере увеличения размера таблицы, через пару дней скорость приема уменьшилась примерно вдвое. При разбиении по дням скорость приема сбрасывалась каждое утро, когда все вставки происходили в новом разделе.
65К - это неплохо. Посмотрим SHOW CREATE TABLE.
Более быстрые диски. Лучше ввод-вывод. Лучше дисковый кеш. Уменьшенный размер строки. Выбрасывать все столбцы не обязательно. При необходимости сделайте копию таблицы, которая более оптимизирована для запросов. Ответ, как вы знаете, индексы. Сканирование таблиц никогда не будет быстрым. Они просто будут менее медленными, если вы очень постараетесь.