Оптимизация выборки из большой и постоянно обновляемой таблицы

[Leonid]

Есть таблица messages, содержащая сообщения между пользователями сайта:

id
date_time
from_user (int)
to_user (int)
content (text)
readed (tinyint)

сделаны индексы from_user, to_user и from_user+to_user

сообщений уже более 2 млн, таблица вместе с индексами занимает порядка 400 мб.

наиболее частые запросы - получить последние сообщения между двумя пользователями, и число непрочитанных сообщений для определенного пользователя:

select * from messages where (from_user = user1 and to_user = user2) or (from_user = user2 and to_user = user1) order by id desc limit 30

select count(*) as cnt from messages where to_user = user1 and readed = 0

иногда запросы выполняются быстро, менее 0.01 сек, а иногда порядка 0.5 - 2 секунд. И это естественно сильно подтормаживает сайт. Дело в размере таблицы, или можно что-то оптимизировать? Переходить на более мощный сервер или разделять таблицу, отправлять старые данные, например старше года в архив?

 

[Gremboloid]

А можно показать explain запросов в момент тормозоров?

 

[Leonid]

EXPLAIN SELECT *
FROM `messages`
WHERE (
from_user =12
AND to_user =15278
)
OR (
from_user =15278
AND to_user =12
)
ORDER BY id DESC
LIMIT 30

1.17 сек.

d select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE messages range from_user,to_user,from_user_2 from_user_2 8 NULL 975 Using where; Using filesort

 

[Leonid]

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

 

[Gas]

данные "вымываются" из кеша и mysql лезет за ними на диск.

чтобы я сделал:
0. убедился что innodb тип таблицы
1. увеличил innodb_buffer_pool_size если есть возможность
2. попробовал бы переписать запрос на union вместо or

(select * from messages where from_user =12 and to_user =15278 order by id desc limit 30)
union
(select * from messages where from_user =15278 and to_user =12 order by id desc limit 30)
order by id desc limit 30

 

[Leonid]

Gas
тип таблицы MyISAM, можно еще выбрать MEMORY и MRG MYISAM
union сейчас потестирую

 

[Leonid]

нет, с union то же самое

 

[Gas]

просто union конечно не поможет, так-как при сортировке опять-же будет Using filesort, а с innodb нет, так-как primary key есть в конце каждого индекса.

если myisam менять не хочется (но из-за него конкурентность доступа к таблице убивается - один запрос на чтение затупил, пошёл в таблицу insert/update и потом все остальные ридеры встали в очередь)
то добавь составной индекс from_user+to_user+id вместо from_user+to_user и увеличь key_buffer_size если он дефолтный

 

[Leonid]

Gas
сейчас на тестовой копии таблицы сделал индекс from_user+to_user+id вообще запросы стали выполняться еще медленнее...

 

[Фанат]

я думаю, что тормоза из-за обновления индекса при вставках

 

[Gas]

так сложно что-то сказать, какой explain, выложи show create table messages; и show variables like 'key_buffer_size';

 

[Gas]

Фанат
а ты сталкивался в реальности с таким? просто интересно.
я тоже о таком слышал всегда, но либо не сталкивался, либо не настолько сильно в моих случаях влияло,
даже в случае 100 insert'ов/sec в innodb таблицу 50M rows, правда при отключенной durability (innodb_flush_log_at_trx_commit=0), длина строки не более 100 байт, из индексов - primary + 2 составных индекса по 2-м полям.

всё-таки в одной ноде b-tree находится не 1 элемент, а больше (1024/байт_в_индексе ? где-то натыкался на такую информацию, но это не 100%), и разбитие нод с балансировкой будет происходить не при каждой модификации идексного дерева.

 

[Фанат]

Ну, я, увы, был не на этом проекте, а на соседнем. Это была игра APEHA, типа бк. Но она конкретно легла из-за индексов при вставке.
Впрочем, это было больше 10 лет назад

 

[Gas]

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

 

[Leonid]

CREATE TABLE `messages` (
`id` int(11) NOT NULL auto_increment,
`date_time` datetime NOT NULL default '0000-00-00 00:00:00',
`from_user` int(11) NOT NULL default '0',
`to_user` int(11) NOT NULL default '0',
`content` text NOT NULL,
`publish` int(1) NOT NULL default '1',
`readed` int(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `from_user` (`from_user`),
KEY `to_user` (`to_user`),
KEY `publish` (`publish`),
KEY `readed` (`readed`),
KEY `from_user_2` (`from_user`,`to_user`)
) ENGINE=MyISAM AUTO_INCREMENT=2238091 DEFAULT CHARSET=cp1251

тут еще поле publish, сейчас его не использую, всегда 1.


key_buffer_size 3145728

 

[Gas]

могу предложить:
- увеличить key_buffer_size ну хотя бы до 50MB (оптимальный размер конечно зависит от наличия свободной памяти и размера индексов других myisam таблиц);
- publish и readed сделать tinyint - 1 байт, вместо int(1) - 4 байта;
- удалить индекс from_user, так-как есть составной from_user_2, и в нём поле from_user стоит на первом месте, то для условий по from_user будет использоваться составной;
- не уверен в необходимости индекса readed, если поле используется только вместе с каким-то пользователем, то это лишний индекс.

для запросов select count(*) as cnt from messages where to_user = user1 and readed = 0;
в идеале нужен составной индекс (to_user, readed)

 

[Leonid]

readed = 1\0 = сообщение прочитано или нет. если в запросе есть where readed = 0, то explain показывает, что он используется

 

[Leonid]

удалил индекс KEY `from_user` (`from_user`), объем индекса сократился на 18 мб. медленные запросы вроде прекратились, посмотрю что будет дальше.
составной индекс (to_user, readed) попробую добавить в другой время, а то эти операции надолго блокирую таблицы и сайт подвисает