Вопрос по optimization, indexing, mysql – Как оптимизировать индексы mysql, чтобы операции INSERT происходили быстро на большой таблице с частыми операциями записи и чтения?

2

У меня есть столwatchlist содержащие сегодня почти 3Mil записей.

mysql>  select count(*) from watchlist;
+----------+
| count(*) |
+----------+
|  2957994 |
+----------+

Он используется в качестве журнала для записи просмотров страниц товара на большом сайте электронной коммерции (более 50 000 товаров). Он записывает productID просматриваемого продукта, IP-адрес и USER_AGENT зрителя. И отметка времени, когда это произойдет:

mysql> show columns from watchlist;
+-----------+--------------+------+-----+-------------------+-------+
| Field     | Type         | Null | Key | Default           | Extra |
+-----------+--------------+------+-----+-------------------+-------+
| productID | int(11)      | NO   | MUL | 0                 |       |
| ip        | varchar(16)  | YES  |     | NULL              |       |
| added_on  | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |       |
| agent     | varchar(220) | YES  | MUL | NULL              |       |
+-----------+--------------+------+-----+-------------------+-------+

Затем данные представляются на нескольких страницах сайта как на внутренней стороне (например, для проверки того, что GoogleBot индексирует), так и на внешней стороне (например, на боковой панели «Недавно просмотренные товары») и на странице, показывающей пользователям, что "Люди из вашего региона также понравились" и т. д.).

Так что это "отчет" страницы и боковые панели загружаются быстро Я ставлю индексы на соответствующие поля:

mysql> show indexes from watchlist;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| watchlist |          1 | added_on  |            1 | added_on    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| watchlist |          1 | productID |            1 | productID   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| watchlist |          1 | agent     |            1 | agent       | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Без INDEXES страницы с боковой панелью, например, потратили бы около 30-45 секунд на выполнение запроса, чтобы получить 7 самых последних ProductID. С индексами это занимает менее 0,2 сек.

Проблема в том, чтоwith ИНДЕКСЫ сами страницы продукта загружаются все дольше и дольше, потому что с ростом таблицы операции записи занимают более 5 секунд. Кроме того, есть всплеск наmysqld процесс, составляющий 10-15% доступного процессора каждый раз при просмотре страницы продукта (примерно раз в 2 с). Мы уже должны были обновить серверное оборудование, потому что на предыдущем сервере оно достигало 100% и вызывало сбой mysqld.

Мой план состоит в том, чтобы попробовать решение за 2 столами. Одна таблица для операций INSERT, а другая для операций SELECT. Я планируюpurge таблицу INSERT, когда она достигает 1000 записей, используя TRIGGER, и копируйте самые старые 900 записей в таблицу SELECT. Страницы отчета представляют собой смесь в реальном времени (недавно просмотренные) и аналитики (в каком регионе), но страницам в реальном времени, как правило, требуется лишь несколько свежих записей, в то время как аналитические страницы не должны знать о самых последних тренд (т.е. последние 1000 просмотров). Поэтому я могу использовать небольшую таблицу для первой и большую таблицу для последних отчетов.

My question: Это идеальное решение этой проблемы?

Also: С TRIGGERS в MySQL возможно лиnice trigger_statement, чтобы он занимал больше времени, но не потреблял много ресурсов процессора? Бы создаюcron job Каждые 30 минут, которые проходят, и которые выполняют очистку, если требуется, будут лучшим решением?

одна запись на вставку. Я могу попробовать InnoDB rwired
Просто чтобы уточнить, сколько записей одновременно вы пытаетесь вставить? Вы массовая загрузка, как предлагает Адам? RichardOD
Рассматривали ли вы использование INNOdb, чтобы получить блокировку на уровне строк вместо блокировки всей таблицы, как MyISAM. Kurt

Ваш Ответ

6   ответов
0

е нужно записывать данные в рабочую таблицу.

-2

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

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

not займет 5 секунд, независимо от размера стола.

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

Я бы предложил написать вtwo таблицы: одна долгосрочная таблица, одна таблица краткосрочных отчетов с незначительным индексированием или без индексации, которая затем выводится по мере необходимости.

Другим решением может быть использование memcached или базы данных в оперативной памяти для данных оперативных отчетов, так что в производственной базе данных нет попаданий.

Еще одна мысль: как именно «жить» должен ли быть один из этих отчетов? Возможно получение нового списка по времени, а не один раз дляevery просмотр страницы будет достаточно.

вы правы. 5сек было преувеличением rwired
0

при записи в базу данных. Вы можете делегировать запись в некоторый фоновый процесс через асинхронную очередь (например, ActiveMQ). Вставка сообщения в очередь ActiveMQ выполняется очень быстро. Мы используем ActiveMQ и выполняем около 10-20 Кб операций вставки на тестовой платформе (и это однопоточное тестовое приложение! Так что вы можете иметь больше).

0

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

Проблема заключается в том, что вставка / обновление в большую главную таблицу занимает некоторое время и еще хуже, если в очереди ожидают несколько обновлений или вставок, даже если включены параметры INSERT DELAYED или UPDATE [LOW_PRIORITY].

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

Работает как чудо, гораздо меньше, чем за 5 секунд, чтобы прочитать из огромной мастер-записи 20 миллионов и записать во вторую небольшую таблицу от 100К до 300К записей менее чем за секунду.

Это работает просто отлично.

С уважением

0

INSERT DELAYED синтаксис, который позволяет mysql ставить в очередь вставки и выполнять их, когда у него есть время. Это, вероятно, не очень масштабируемое решение, хотя.

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

Обновление: INSERT DELAYED больше не поддерживается MySQL.

Похожие вопросы