Как улучшить производительность сканирования таблицы с помощью innodb
Вкратце: есть ли способ улучшить производительность сканирования таблиц в таблицах InnoDB?
Пожалуйста, не предлагайте добавлять индексы, чтобы избежать сканирования таблиц. (увидеть ниже)
innodb_buffer_pool_size находится на 75% памяти сервера (48 ГБ /64 ГБ). Я использую последнюю версию Percona (5.7.19), если это что-то меняет
Больше: у нас есть 600 Гб данных последних временных рядов (мы агрегируем и удаляем старые данные), распределенных по 50-60 таблицам. Так что большинство из них - "активные" данные, которые регулярно запрашиваются. Эти таблицы несколько большие (более 400 числовых столбцов), и многие запросы выполняются для ряда этих столбцов (тревожно), поэтому нецелесообразно добавлять индексы (как мы должны были бы добавить несколько десятков). Самые большие таблицы делятся на день.
Я полностью осознаю, что это проблема разработки приложения / таблицы, а не проблема "настройки сервера". В настоящее время мы работаем над тем, чтобы значительно изменить способ составления и запроса этих таблиц, но мы должны поддерживать существующую систему до тех пор, пока это не произойдет, поэтому я ищу способ немного улучшить вещи, чтобы выиграть нам немного времени.
Недавно мы разделили эту систему и перенесли часть ее на новый сервер. Ранее он использовал MyISAM, и мы попытались перейти на TokuDB, что казалось уместным, но столкнулось с некоторыми странными проблемами. Мы перешли на InnoDB, но производительность действительно плохая. У меня складывается впечатление, что MyISAM лучше сканирует таблицы, поэтому, если не будет лучшего варианта, мы вернемся к нему до тех пор, пока не будет установлена новая система.
Обновить
Все таблицы имеют почти одинаковую структуру: -timestamp -primary key (поле varchar(20)) -по 15 полям различных типов, представляющих другие вторичные атрибуты, по которым можно фильтровать (вместе с сначала соответствующим образом проиндексированными критериями) -И затем о несколько сотен мер (плавает), между 200-400.
Я уже обрезал длину строки настолько, насколько мог, не меняя саму структуру. Первичным ключом был varchar(100), все меры были двойными, у многих вторичных атрибутов были изменены типы данных.
Обновление оборудования на самом деле не вариант.
Создание небольших таблиц с нужным мне набором столбцов поможет некоторым процессам работать быстрее. Но за счет создания этой таблицы с помощью сканирования таблицы и дублирования данных. Может быть, если бы я создал его как таблицу памяти. По моей оценке, это займет пару ГБ от пула буферов. Также существуют процессы агрегации, которые регулярно читают столько же данных из основных таблиц, и им нужны все столбцы.
К сожалению, в тех запросах, которые я планирую рассмотреть в следующей версии, много дублирования. Тревожные процессы и процессы агрегирования в основном обрабатывают данные за весь день каждый раз, когда вставляются некоторые строки (каждые полчаса), вместо того, чтобы просто работать с новыми / измененными данными.
Как я уже сказал, большие таблицы разбиты на разделы, поэтому обычно выполняется сканирование дневного раздела, а не всей таблицы, что является небольшим утешением.
Реализация системы для хранения этого в памяти за пределами БД могла бы работать, но это повлекло бы за собой множество изменений в унаследованной системе и работе по разработке. Можно потратить это время на лучший дизайн.
Тот факт, что таблица InnoDB намного больше для тех же данных, что и MyISAM (в 2-3 раза больше в моем случае), действительно снижает производительность.
2 ответа
MyISAM немного лучше сканирует таблицы, потому что хранит данные более компактно, чем InnoDB. Если ваши запросы связаны с вводом / выводом, сканирование по меньшему количеству данных на диске выполняется быстрее. Но это довольно слабое решение.
Вы можете попробовать использовать сжатие InnoDB, чтобы уменьшить размер данных. Это может приблизить вас к размеру MyISAM, но вы все еще привязаны к I/O, так что это будет отстой.
В конечном счете, звучит так, будто вам нужна база данных, предназначенная для рабочей нагрузки OLAP, например хранилище данных. InnoDB и TokuDB предназначены для рабочих нагрузок OLTP.
Он пахнет как хранилище данных с "отчетами". Разумно выбирая, что нужно агрегировать (выбрано из ваших чисел с плавающей запятой) за какой период времени (типичный час или день), вы можете создавать и поддерживать сводные таблицы, которые работают гораздо эффективнее для отчетов. Это приводит к сканированию данных только один раз (для создания резюме), а не повторно. Сводные таблицы намного меньше, поэтому отчеты гораздо быстрее - возможно, 10х.
Также возможно дополнить итоговые таблицы, когда вводятся необработанные данные. (Увидеть INSERT .. ON DUPLICATE KEY UPDATE ..)
И использовать разделение по дате, чтобы обеспечить эффективное DROP PARTITION вместо DELETE, Не иметь более 50 разделов.
Если вы хотите обсудить более подробно, давайте начнем с одного из запросов, который сейчас сканируется.
В различных проектах, над которыми я работал, было от 2 до 7 сводных таблиц.
С 600 ГБ данных вы можете раздвинуть границы "приема пищи". Если это так, мы тоже можем это обсудить.