Вставки в следующую таблицу занимают до 70 секунд:
CREATE TABLE IF NOT EXISTS `productsCategories` (
`categoriesId` int(11) NOT NULL,
`productsId` int(11) NOT NULL,
PRIMARY KEY (`categoriesId`,`productsId`),
KEY `categoriesId` (`categoriesId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
В таблице около 100 000 строк, и она занимает 7 МБ на диске.
Есть ли в MySQL какие-то настройки, которые могут улучшить производительность записи?
Мой my.cnf файл выглядит следующим образом:
log-slow-queries="/var/log/mysql/slow-query.log"
long_query_time=1
log-queries-not-using-indexes
innodb_buffer_pool_size=4G
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
query_cache_size = 6G
key_buffer_size = 284M
query_cache_limit = 1024M
thread_cache_size = 128
table_cache = 12800
sort_buffer_size=2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
read_buffer_size=128K
open_files_limit = 1000
table_definition_cache = 1024
table_open_cache = 6000
max_heap_table_size=512M
tmp_table_size=4096M
max_connections=1000
thread_concurrency = 24
Вот настройка оборудования:
Учитывая это оборудование, я не ожидал, что проблема будет узким местом в оборудовании.
Первое, что бросается в глаза, - это структура таблицы.
CREATE TABLE IF NOT EXISTS `productsCategories` (
`categoriesId` int(11) NOT NULL,
`productsId` int(11) NOT NULL,
PRIMARY KEY (`categoriesId`,`productsId`),
KEY `categoriesId` (`categoriesId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Обратите внимание, что categoriesId index и PRIMARY KEY начинаются с одного и того же столбца. Это избыточный индекс. Поскольку эта таблица - InnoDB, categoriesId index является избыточным по другой причине: все вторичные индексы содержат ключи в gen_clust_index (также известный как кластерный индекс; см. для чего используется gen_clust_index в mysql?)
Если вы удалите categoriesId индекс с
ALTER TABLE productsCategories DROP INDEX categoriesId;
это улучшит INSERT резко из-за отсутствия необходимости выполнять дополнительное обслуживание вторичного и кластерного индекса.
Если вы выполняете какие-либо операции массовой вставки, вам понадобится большой буфер объемной вставки.
Пожалуйста, посмотрите мои прошлые сообщения по этому поводу:
Размер вашего файла журнала слишком мал !!! Это должно быть 25% от пула буферов InnoDB, который в вашем случае должен быть 1 ГБ. Видеть мой пост о том, как изменить размер файлов журнала InnoDB.
Пожалуйста, не устанавливайте innodb_thread_concurrency !!! Я не понаслышке узнал в Percona Live NYC, что нужно оставить эту настройку. По умолчанию он отключен в MySQL 5.5, MySQL 5.1 InnoDB Plugin и Percona Server 5.1+.
Вам нужно использовать innodb_file_per_table. Если это отключено, я превращаю обслуживание файлов на ibdata1 в кошмар. Пожалуйста прочти мой пост о том, как очистить InnoDB, чтобы реализовать это.
Если вы используете MySQL 5.5 или Percona Server, вы должны установить определенные параметры, чтобы InnoDB использовал несколько процессоров / несколько ядер. Посмотри пожалуйста мой пост об этих настройках.
У тебя есть innodb_log_buffer_size=4M. По умолчанию - 8M. Это вызовет в два раза больше промывки журналов повторного выполнения. Это также будет противодействовать вашему innodb_flush_log_at_trx_commit=2настройка. Установите его на 32M. Также см. Документация MySQL по innodb_log_buffer_size.
В свете этих наблюдений добавьте или замените следующие настройки:
[mysqld]
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_file_per_table
innodb_log_file_size=1G
innodb_log_buffer_size=1G
bulk_insert_buffer_size = 256M
Вы должны проверить innodb_log_file_size, значение по умолчанию - 5 МБ, что довольно мало для настроек с интенсивной записью. Попробуйте установить его на 100M. Вам придется удалить старый ib_logfile* файлы, чтобы запустить БД с новыми настройками. Пожалуйста, не удаляйте файлы журналов во время работы сервера БД, вам нужно сначала остановить его. Вероятно, вам следует сначала сделать резервную копию старых файлов журнала, а не просто удалить их.