LINUX.ORG.RU

[mysql][оптимизация] Очень (ОЧЕНЬ!) медленные INSERT'ы.


0

1

Значит так, сперва дисклеймер. Жирная система, хорошо (смею надеяться) настроенный MySQL-сервер, который обеспечивает тысячи INSERT'ов в секунду на таблицах с миллионами записей. Прекрасно работают и InnoDB, и MyISAM. Никаких нареканий.

Но!

Собственно проблема.

Есть простенькие таблицы, вида:

CREATE TABLE `warnings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT '0',
  `user_name` varchar(255) NOT NULL,
  `time` int(11) NOT NULL DEFAULT '0',
  `score` int(11) NOT NULL DEFAULT '0',
  `type_id` int(3) NOT NULL,
  `moderator_id` int(11) NOT NULL DEFAULT '0',
  `moderator_name` varchar(255) NOT NULL,
  `uri` varchar(255) NOT NULL DEFAULT '',
  `comment` text NOT NULL,
  `warn_class_id` int(10) DEFAULT '0',
  `warn_object_id` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_2` (`user_id`,`warn_class_id`,`warn_object_id`),
  KEY `uri` (`uri`),
  KEY `moderator_id` (`moderator_id`),
  KEY `score` (`score`),
  KEY `time` (`time`),
  KEY `user_id` (`user_id`),
  KEY `warn_class_name_id` (`warn_class_id`),
  KEY `warn_class_object_id` (`warn_object_id`),
  KEY `type_id` (`type_id`),
  KEY `user_name` (`user_name`),
  KEY `moderator_name` (`moderator_name`)
) ENGINE=InnoDB AUTO_INCREMENT=9506 DEFAULT CHARSET=utf8

Как видно - менее 10000 записей. Так вот, такая простая вставка:

INSERT IGNORE warnings  (`user_id`,`time`,`score`,`type_id`,`moderator_id`,`moderator_name`,`warn_class_id`,`warn_object_id`) VALUES ('7854','1286095509','2','7','843','Fakir','1','2250227')

может длиться 10-80(!) секунд.

С чтением из этой таблицы никаких проблем, сколько угодно и быстро.

Внешних ключей нет.

Есть мысли, с чем может быть связано?

★★★★★

А побольше ключей не мог сделать? Попробуй их дропнуть и сравни скорость вставки.

AnDoR ★★★★★ ()

Оно честно и благородно проверяет ключи на уникальность. В принципе обычно индекс появляется автоматически, но вдруг внезапно здесь что-то не так. Какие модификации таблицы избавляют ее от тормозов?

vertexua ★★★★★ ()
Ответ на: комментарий от AnDoR

А побольше ключей не мог сделать?

Большая часть из них добавлена в попытках оптимизации. Была мысль, что тормоза происходят от неоптимального поиска уже имеющихся записей по ignore. И, действительно, стало чуть-чуть лучше. Раньше, когда не было индексов, вставки до нескольких минут могли длиться :)

...

Но идея оказалась несостоятельной, так как INSERT и без IGNORE длится долго. Вот свежий тест:

mysql> INSERT warnings  (`user_id`,`time`,`score`,`type_id`,`moderator_id`,`moderator_name`,`warn_class_id`,`warn_object_id`) VALUES ('71106','1286095509','10','','10000','Balancer','1','2250230');
Query OK, 1 row affected, 3 warnings (5.79 sec)

KRoN73 ★★★★★ ()
Ответ на: комментарий от AnDoR

В любом случае, на машине, которая без проблем тянет тысячи записей в секунду в миллионные таблицы гиговых размеров и с куда большим числом индексов (машина там Q9440 и 16Гб оперативки, MySQL жрёт чуть не до половины оперативки) обновить десяток ключей в таблице с 10000 записей за десятки секунд - это бред.

Тут проблема не в производительности как таковой, а что-то со структурой не то.

И это не единственный случай. Вот ещё проблемная (хотя и много менее) таблица:

CREATE TABLE `bors_thumb_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `target_user_id` int(11) NOT NULL,
  `target_class_name` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `target_object_id` varchar(255) DEFAULT NULL,
  `score` int(11) NOT NULL,
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`target_class_name`,`target_object_id`),
  KEY `target_score` (`target_class_name`,`target_object_id`,`score`),
  KEY `target_class_name` (`target_class_name`),
  KEY `create_time` (`create_time`),
  KEY `target_user_id` (`target_user_id`),
  KEY `user_id_2` (`user_id`,`target_user_id`),
  CONSTRAINT `bors_thumb_votes_ibfk_1` FOREIGN KEY (`target_class_name`) REFERENCES `bors_class_names` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=204284 DEFAULT CHARSET=utf8

Тут 146 тыс. записей и время вставки такого же порядк, как и топикстарте. От десятка секунд до минуты. Когда записей было менее 100 тыс. обновлялось всё мгновенно.

KRoN73 ★★★★★ ()
Ответ на: комментарий от KRoN73

> Большая часть из них добавлена в попытках оптимизации.

Если ты оптимизировал выборки, то правильно. А вставки как раз наоборот.

optimize давно делал?

AnDoR ★★★★★ ()
Ответ на: комментарий от AnDoR

>optimize давно делал?

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

Если ты оптимизировал выборки, то правильно. А вставки как раз наоборот.


Но после введения лишних индексов стало, хоть и не принципиально, но заметно легче. Хотя, да, по идее, ignore и replace относятся только к конфликтам в уже имеющихся индексах.

KRoN73 ★★★★★ ()
Ответ на: комментарий от KRoN73

Упс. Пардон. Два раза. Во-первых, перепутал. Второй пример, как раз, случай, когда всё работает идеально. Вот другой пример тормозного варианта:

CREATE TABLE `bors_favorites` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_class_name` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Имя класса владельца. ',
  `user_id` int(10) unsigned NOT NULL COMMENT 'ID владельца. ',
  `target_title` varchar(255) DEFAULT NULL COMMENT 'Название объекта. ',
  `target_class_name` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Имя класса объекта. ',
  `target_object_id` int(10) unsigned NOT NULL COMMENT 'ID объекта. ',
  `target_create_time` int(10) unsigned NOT NULL COMMENT 'Дата создания объекта. ',
  `create_time` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_class_name__user_id__target_class_name__target_object_id` (`user_class_name`,`user_id`,`target_class_name`,`target_object_id`),
  KEY `create_time` (`create_time`),
  KEY `target_class_name` (`target_class_name`),
  KEY `target_create_time` (`target_create_time`),
  CONSTRAINT `bors_favorites_ibfk_1` FOREIGN KEY (`target_class_name`) REFERENCES `bors_class_names` (`name`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=481 DEFAULT CHARSET=utf8

Тут, вообще, менее 500 записей, а тормоза бывают, как было сказано, на десятки секунд. Во втором же от начала примере, с 146 тыс. записей, вставки проходят за доли секунды.

Второй пардон - optimize к innodb-таблицам неприменим.

KRoN73 ★★★★★ ()
Ответ на: комментарий от AnDoR

Не похоже, чтобы дело в структуре таблиц было. Хотя - х.з.

KRoN73 ★★★★★ ()
Ответ на: комментарий от cobold
| Warning | 1364 | Field 'user_name' doesn't have a default value            |
| Warning | 1364 | Field 'comment' doesn't have a default value              |
| Warning | 1366 | Incorrect integer value: '' for column 'type_id' at row 1 |

Чуть позже сравню вариант с полностью корректными данными.

KRoN73 ★★★★★ ()

>InnoDB

http://blog.johnjosephbachir.org/2006/10/22/everything-you-need-to-know-about...

The primary key determines the order in which the data is physically stored in the main data file, aka “the clustered index”. Another way of saying this: the main data file is a B-tree index that directly contains all of a table’s columns, and the key on this B-tree is the primary key.

Попробуй заменить PRIMARY KEY(id) на UNIQUE KEY. Также поможет уменьшение размера записи, но последнее скорее workaround, чем решение.

P. S. Было бы неплохо посмотреть, не подскакивает ли iowait при вставке на машинке с БД.

P. P. S. стесняюсь спросить, но неужели JOIN кажется настолько ужасным, что приходится делать user_name varchar(255), moderator_name varchar(255)?

linuxfan ()
Ответ на: комментарий от linuxfan

>Было бы неплохо посмотреть, не подскакивает ли iowait при вставке на машинке с БД

Не заметно. Машина остаётся совершенно отзывчивой. По top'у случаи долгого зависа отлавливаются очень трудно (после подобной операции на некоторое время всё перестаёт тормозить - то ли кешируется, то ли оптимизируется, так что одиночные тесты приходится проводить с большим интервалом), по munin криминала особого не заметно: http://admin.airbase.ru/munin/airbase/airbase/

Попробуй заменить PRIMARY KEY(id) на UNIQUE KEY


autoincrement же только с primary работает.

стесняюсь спросить, но неужели JOIN кажется настолько ужасным, что приходится делать user_name varchar(255), moderator_name varchar(255)?


JOIN усложняет структуру ORM больше, чем пара лишних полей :) Ну и историческая нелюбовь к JOIN'ам у MySQL на больших таблицах. Но на будущее, действительно, можно подумать о такой оптимизации.

KRoN73 ★★★★★ ()
Ответ на: комментарий от KRoN73

Грохнул неиспользуемое поле user_name, прописал для comment NULL. Тестовая вставка прошла за 0.03 sec. То ли недавно уже проходила вставка и данные закешировались, то ли проблема была в этом. Нужно будет ещё попозже проверить...

KRoN73 ★★★★★ ()
Ответ на: комментарий от linuxfan

>К чему здесь синтетический PRIMARY KEY?

Какой синтетический PRIMARY KEY? Мне нужен ID записи, с которым проходит потом масса работы. Обычный авоинкрементный ID.

Уникальный ключ нужен чисто по логике - за один объект системы один пользователь может получать не более одного штрафа. Это уже другая история.

К размышлению


Неудобно для моих случаев. При работе с записям придётся обмениваться вместо простого унифицированного ID объекта (в данных случаях - ID предупреждения или ID закладки) левым искуственным ID для уникального ключа (например, user_id-warn_class_id-warn_object_id). И вместо передачи простого целочисленного параметра придётся заниматься сборкой/разборкой искусственного ключа. Нафига?

KRoN73 ★★★★★ ()
Ответ на: комментарий от KRoN73

>Уникальный ключ нужен чисто по логике - за один объект системы один пользователь может получать не более одного штрафа. Это уже другая история.

ID по-прежнему выглядит суррогатным первичным ключом и притянутой за уши сущностью.

И вместо передачи простого целочисленного параметра придётся заниматься сборкой/разборкой искусственного ключа.

Хм. Искусственный ключ — это твой id.

Нафига?

Чтобы можно было использовать убогий InnoDB, ибо см выше замечания о PRIMARY KEY.

linuxfan ()
Ответ на: комментарий от linuxfan

>Хм. Искусственный ключ — это твой id.

Оба варианта искуственные. Один - в рамках БД, другой - в рамках работы с записями. Вариант с автоинкрементым праймери при этом проще.

Чтобы можно было использовать убогий InnoDB


Но в других-то случаях работает же!

KRoN73 ★★★★★ ()

у меня сегодня replace на значительно более простой табличке (четыре integer-а) десять минут провисел. подозреваю фрагментацию памяти, mysql-ю 32 гига доступно. думаю подождать пока нагрузка спадет и просто передернуть его.

dmiceman ★★★★★ ()
Ответ на: комментарий от KRoN73

>Но в других-то случаях работает же!

Потому что в других случаях размер записи маленький.

А вообще запусти-ка iostat -x 1 и посмотри на загруженность дисков при инсертах и остальных операциях. Может и правда не в дисках дело (хотя на практике всегда в них).

linuxfan ()
Ответ на: комментарий от dmiceman

>подозреваю фрагментацию памяти

Подозревай локи (если MyISAM) и фрагментацию файла БД, кучу индексов. Сверхбыструю RAM подозревай в последнюю очередь, потому как за 10 минут через современную память пролетело бы стролько петабайт, сколько у гмейла почты нет.

linuxfan ()
Ответ на: комментарий от linuxfan

>Потому что в других случаях размер записи маленький.

Как переупорядочивание по первичному ключу связано с размером записей? Тем более, что в варианте на http://www.linux.org.ru/jump-message.jsp?msgid=5574146&cid=5574403 размер записи тоже не маленький, а тормозов таких нет (напомню, что я этот пример привёл в качестве тормозного ошибочно, потом ниже исправлялся).

А вообще запусти-ка iostat -x 1 и посмотри на загруженность дисков при инсертах и остальных операциях


Посмотрю попозже, сейчас система загружена подготовкой к экспериментам с PBXT и XTRADB.

KRoN73 ★★★★★ ()
Ответ на: комментарий от linuxfan

>Подозревай локи (если MyISAM)

InnoDB, о чём не раз писалось. При обновлении в processlist висит статус «update». Т.е. тормозит именно сама операция.

KRoN73 ★★★★★ ()
Ответ на: комментарий от KRoN73

>Как переупорядочивание по первичному ключу связано с размером записей?

Потому что в InnoDB записи хранятся (см выше). Как производительность дисковой подсистемы связана с потоком записываемых данных, думаю, понятно?

Тем более, что в варианте на http://www.linux.org.ru/jump-message.jsp?msgid=5574146&cid=5574403 размер записи тоже не маленький

Меньше 512 байт. Почему 512? Потому что это размер сектора, а минимальный юнит при общении с диском — это сектор.

linuxfan ()
Ответ на: комментарий от linuxfan

Потому что в InnoDB записи хранятся

Сейчас для теста снёс не используемый в системе первичник в одной из активно используемых таблиц с периодическими тормозами. Лучше не стало.

5 секунд:

INSERT IGNORE bors_access_log  (`user_ip`,`url`,`server_uri`,`class_name`,`object_id`,`access_time`,`operation_time`,`has_bors`,`has_bors_url`,`user_agent`,`is_bot`) VALUES ('217.14.6.210','http://balancer.ru/_bors/igo?o=forum_post__2022077','http://balancer.ru/_bors/igo?o=forum_post__2022077','bors_system_go_internal','forum_post__20
22077','1290082313','0.50945115089417','1','1','Mozilla/5.0 (compatible; MJ12bot/v1.3.3; http://www.majestic12.co.uk/bot.php?+)','Majestic12Bot');

Таблица (после удаления первичника):

CREATE TABLE `bors_access_log` (
  `id` int(10) unsigned NOT NULL,
  `user_ip` varchar(16) NOT NULL,
  `user_id` int(11) NOT NULL,
  `url` varchar(255) NOT NULL,
  `server_uri` varchar(255) NOT NULL,
  `referer` varchar(255) NOT NULL,
  `class_name` varchar(64) NOT NULL,
  `object_id` varchar(255) NOT NULL,
  `access_time` int(11) NOT NULL,
  `operation_time` float NOT NULL,
  `has_bors` tinyint(1) unsigned NOT NULL,
  `has_bors_url` tinyint(1) unsigned NOT NULL,
  `user_agent` varchar(255) NOT NULL,
  `is_bot` varchar(64) NOT NULL,
  `was_counted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  KEY `user_ip` (`user_ip`),
  KEY `access_time` (`access_time`),
  KEY `user_ip_2` (`user_ip`,`access_time`),
  KEY `was_counted` (`was_counted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Меньше 512 байт. Почему 512? Потому что это размер сектора

MySQL не работает с диском напрямую. Там прослойка в виде жирного системного кеша. Реально работающего кеша - я терял ряд последних записей при внезапных отказах железки. Иногда данные терялись за достаточно долгий срок.

Но даже если бы была б прямая запись, потеря производительности была бы в разЫ, но не на порядки.

KRoN73 ★★★★★ ()
Ответ на: комментарий от KRoN73

>Там прослойка в виде жирного системного кеша. Реально работающего кеша - я терял ряд последних записей при внезапных отказах железки.

Любая уважающая себя БД периодически флашит кэши. Для InnoDB даже соответствующая настройка есть.

Сейчас для теста снёс не используемый в системе первичник в одной из активно используемых таблиц с периодическими тормозами. Лучше не стало.

Ты делал это на ненагруженной машине? Запусти уже iostat

linuxfan ()
Ответ на: комментарий от KRoN73

>iowait средний 0..2%, пиковый - 5-10%.

У меня, значешь, то iowait был в районе 5-10% при 100% disk load. Запусти, наконец, iostat. Или тебе надо на кофейной гуще гадать, а не проблему решать?

linuxfan ()
Ответ на: комментарий от KRoN73

>>EXPLAIN для INSERT? «Но как??» (c) :)

не знаю как в этом вашем MySQL а в pgsql вот так:
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

где statement:
SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or CREATE TABLE AS statement, whose execution plan you wish to see.

exception13 ★★★★★ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.