MySQL Вставка производительности ухудшается на большой таблице

Я работаю с огромной таблицей, содержащей 250 миллионов строк. Схема проста.

CREATE TABLE MyTable (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        oid INT NOT NULL,
        long1 BIGINT NOT NULL,
        str1 VARCHAR(30) DEFAULT NULL,
        str2 VARCHAR(30) DEFAULT NULL,
        str2 VARCHAR(200) DEFAULT NULL,
        str4 VARCHAR(50) DEFAULT NULL,
        int1 INT(6) DEFAULT NULL,
        str5 VARCHAR(300) DEFAULT NULL,
        date1 DATE DEFAULT NULL,
        date2 DATE DEFAULT NULL,
        lastUpdated TIMESTAMP NOT NULL,
        hashcode INT NOT NULL,
        active TINYINT(1) DEFAULT 1,
        KEY oid(oid),
        KEY lastUpdated(lastUpdated),
        UNIQUE KEY (hashcode, active),
        KEY (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1000000000;

Производительность вставки значительно снизилась. До 150 миллионов строк в таблице, для ввода 10 000 строк использовалось 5-6 секунд. Теперь он вырос в 2-4 раза. Файл Innodb ibdata вырос до 107 ГБ. Параметры конфигурации Innodb следующие.

innodb_buffer_pool_size = 36G # Machine has 48G memory
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 50M
innodb_log_buffer_size = 20M
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
expire_logs_days = 4

Время ожидания ввода-вывода увеличилось по сравнению с top. Я попытался изменить метод flush на O_DSYNC, но это не помогло. Диск вырезается из аппаратной конфигурации RAID 10. В более ранней установке с одним диском IO не было проблемой.

Разделяет ли только таблицу? Может ли разделение одного файла 100G на "маленькие" файлы? Существуют ли какие-либо переменные, которые необходимо настроить для RAID?

Обновление. Это тестовая система. У меня есть свобода вносить какие-либо изменения.

Ответы

Ответ 1

Вы не сказали, была ли это тестовая система или производство; Я предполагаю, что это производство.

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

Это означает, что InnoDB должен читать страницы во время вставок (в зависимости от распределения индексов ваших новых строк). Чтение страниц (случайных чтений) происходит очень медленно, и их следует избегать, если это возможно.

Разделение похоже на наиболее очевидное решение, но разбиение на разделы MySQL может не соответствовать вашему прецеденту.

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

Ваш первичный ключ смотрит на меня так, как будто он может не потребоваться (у вас есть еще один уникальный индекс), поэтому исключение - это один из вариантов.

Также рассмотрите плагин innodb и сжатие, это заставит ваш innodb_buffer_pool пойти дальше.

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

Внесение каких-либо изменений в это приложение может привести к новым проблемам производительности для ваших пользователей, поэтому вы хотите быть очень осторожными здесь. Если вы обнаружите способ улучшить производительность вставки, возможно, это снизит эффективность поиска или производительность других операций. Прежде чем выпустить такое изменение, вам нужно будет провести тщательный тест производительности на оборудовании производственного класса.

Ответ 2

Из моего опыта работы с Innodb, похоже, предел для интенсивных систем записи, даже если у вас действительно оптимизированная дисковая подсистема. Я удивлен, что вам удалось получить его до 100 ГБ.

Это то, что твиттер ударил некоторое время назад и понял, что нужно очертить - см. http://github.com/twitter/gizzard.

Все зависит от ваших вариантов использования, но вы также можете перейти от mysql к cassandra, поскольку он отлично работает для приложений с интенсивной записью. (http://cassandra.apache.org)

Ответ 3

Как отмечал MarkR выше, вставка производительности ухудшается, когда индексы больше не вписываются в ваш буферный пул. InnoDB имеет случайный механизм сокращения ввода-вывода (называемый буфером вставки), который предотвращает некоторые из этих проблем, но он не будет работать с вашим индексом UNIQUE. Индекс on (hashcode, active) должен быть проверен на каждой вставке, убедитесь, что не вставлены повторяющиеся записи. Если хеш-код не "следует" первичному ключу, эта проверка может быть случайной IO.

Есть ли у вас возможность изменить схему?

Лучше всего:

(a) Сделать hashcode кем-то последовательным или отсортировать по hashcode перед вставкой в объем (это само по себе поможет, так как случайные чтения будут уменьшены).

(b) Сделать (hashcode, active) первичный ключ - и вставить данные в отсортированном порядке. Я предполагаю, что ваше приложение, вероятно, читает хэш-код - и поиск первичного ключа выполняется быстрее.

Ответ 4

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

tokutek.com

http://tokutek.com/downloads/tokudb-performance-brief.pdf

Ответ 5

Я буду второй комментарий @MarkR о сокращении индексов. Еще одна вещь, на которую вы должны обратить внимание, - увеличить ваш innodb_log_file_size. Это увеличивает время восстановления после сбоя, но должно помочь. Помните, что перед перезагрузкой сервера вам необходимо удалить старые файлы.

Общие советы по настройке InnoDB: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Вы также должны знать LOAD DATA INFILE для ввода вставок. Это намного быстрее.

Ответ 6

Увеличение от innodb_log_file_size = 50M до innodb_log_file_size = 500M

И innodb_flush_log_at_trx_commit должно быть 0, если вы потеряете 1 секунду потери данных.