Как оптимизировать большую таблицу mysql, у которой очень много update/insert встают в очередь?

Question

[Steely]

Есть таблица логов которая обрабатывает в час 67,080 запросов (90% UPDATE, 10% INSERT). Постоянное обновление информации раз в несколько минут.

Тип таблицы: MyISAM
Записей на данный момент 21,595,014 (будет расти).

Все жутко тормозит из-за того что много UPDATE запросов висят в очереди и SELECT ждет пока они обработаются. Оптимизированный запрос к БД может обрабатываться за 0.001 сек, либо за 300 сек, если идет обновление данных (а оно происходит довольно часто).

Вопросов у меня два:
1. Поможет ли переход на движок InnoDB и как его лучше произвести? Alter table сколько примерно будет обрабатываться на таком кол-ве записей?

2. Какие другие методы есть для подобной ситуации, все UPDATE и INSERT запросы вносят изменения в текущие данные (за сегодняшний день) и не трогают прошлые. Есть ли какая-то методика переноса записей в архив из активной таблицы раз в сутки или раз в час? Может кто-то подсказать правильный путь, как поступить?

Заранее спасибо.

Answer 1

[Kerman]

1. Поможет. InnoDB при Update блокирует запись, а не таблицу целиком, как MyISAM. Alter Table будет пересоздавать таблицу, так что это будет обрабатываться долго.
2. В другую таблицу вынести можно. И ночью делать INSERT INTO Archive SELECT * FROM Dayly; DELETE FROM Dayly;
Но это не поможет без смены движка.

Comments

[Steely]

2. А вообще такая практика существует? Если я сделаю архивную таблицу MyISAM, а текущую InnoDB и буду раз в сутки скидывать данные - это нормально?

[Kerman]

Нормально. Только не обязательно MyISAM, Можно использовать Innodb. У MyIsam есть полнотекстовый поиск, она компактнее на диске, знает точно сколько рядов в таблице, но нет полноценных транзакций и блокируется вся таблица при Update.

[Kerman]

Можно даже сделать View с Union, который будет объединять архив и ежедневную таблицу, на случай, если для запросов нужна полная таблица.

[Kerman]

Кажется, я понял Ваш хитрый план. Чтобы не делать Alter Table начать писать в новую таблицу (дневную на InnoDB), а потом, при необходимости можно конвертнуть архивную таблицу.

[Steely]

Транзакции и UPDATE не требуются для архивной таблицы, фактически только INSERT и SELECT, поэтому, наверное, в InnoDB нет смысла.

[Steely]

@Kerman, да, честно говоря, план такой :) Текущую таблицу сделать архивной, а рядом положить будущую рабочую дневную.

[maximich_017]

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

[maximich_017]

Еще помог переход на MariaDB

Answer 2

[shagguboy]

есть вариант написать всем апдейтам LOW_PRIORITY
либо запустить сервер с параметром --low-priority-updates
dev.mysql.com/doc/refman/5.5/en/server-options.htm...

Answer 3

[Владимир Мерк]

Добрый день.
Можно разгрузить базу таким образом:
При апдейте, или инсерте, вместо того, чтобы писать в базу, пишем в файл, с меткой, например:
i|значение1|значение2
u|значение1|значение2

И каждые несколько минут кроном лочить файл, загружать данные, очищать, а потом разбирать данные (пара explode) и генерировать запросы с массовым INSERT (что тоже в разы ускорит их вставку), и UPDATE.