Что можно сделать с таблицей в которой постоянно 700т записей и 300 запросов в секунду?

Question

[WiNNeR_tig]

Что можно сделать с таблицей в которой постоянно 700т записей и 300 запросов в секунду ?
Тип таблицы memory индексы вроде правильные.

Думал разделить на несколько таблиц и обращаться к каждой или это не улучшит ситуацию ?

Comments

[sim3x]

Каких запросов?
Какие индексы?
В чем собственно проблема?

[xmoonlight]

sim3x, Чётко! )

[Philipp]

https://ruhighload.com/post/%D0%A8%D0%B0%D1%80%D0%...

Answer 1

[Владимир Скибин]

Разделите таблицу по какому либо критерию и разнесите по нескольким таблицам, это должно будет улучшить ситуацию. К примеру, в postgresql это можно было бы сделать через наследуемые таблицы и insert через триггер. В Вашем случае - можно попробовать сделать представление для чтения, а для записи уже разные таблицы.
Но это улучшит ситуацию только в том случае, если при разнесении на несколько таблиц - запросы в итоге будут идти на разные таблицы, т.е. произойдет распараллеливание. Если все также они будут долбиться в ту же таблицу, то существенно ситуация улучшена не будет.

Comments

[WiNNeR_tig]

Например так

num_rows1 = SELECT * FROM таблица1 WHERE что то ишем 
num_rows2 = SELECT * FROM таблица2 WHERE что то ишем
num_rows3 = SELECT * FROM таблица3 WHERE что то ишем
num_rows4 = SELECT * FROM таблица4 WHERE что то ишем
num_rows5 = SELECT * FROM таблица5 WHERE что то ишем

if(num_rows1  == 0 AND num_rows2  == 0 AND num_rows3  == 0 AND num_rows4  == 0 AND num_rows5  == 0){
ничего не нашли
}

А при записи
INSERT таблица.rand(1,5)

По идее получится что будет 5 таблиц и в каждой чуть больше 150т записей.

Поможет ли это ?

[Владимир Скибин]

Нет, Вы не поняли аналогии.

К примеру, у Вас там хранятся некие логи. У каждой записи лога можно выделить характеристику, например, тип записи лога ("new_message", "login", etc). И таблица `log` уже делится на `log_new_message`, `log_login`.
Либо если такой характеристики нет, то можно построить аналог DHT. У каждой записи лога есть, к примеру, пользователь, который сгенерировал эту запись. Посчитав хэш от его id и через XOR преобразования - можно выбрать одну из нескольких таблиц, в которую пойдет запись. Полностью это проблему не решит, а возможно, создаст перегрузку лишь одной таблицы, если пользователи (у которых хэш будет "ближе по DHT" к запрашиваемой таблицу) создают львиную долю запросов. Т.е. по факту я так понимаю вам нужно сделать некий "маршрутизатор" из запросов и каждый должен идти в соответсвующую таблицу. Чтобы не менять логику приложения сильно - то можно сделать view на эти несколько таблиц. Т.е. это уже более тонкая оптимизация.
Я бы все таки попробовал реализовать первый описанный метод, а DHT и прочее оставить на "десерт" и попробовать поиграть с ним в свободное время.

[WiNNeR_tig]

Владимир Скибин, Данные там однотипные их не разделить... Мне не понятно есть ли для бд разница. Читать 5 таблиц по 150т записей или читать одну на 750т записей ?

[Владимир Скибин]

WiNNeR_tig, У Вас в раз идет чтение всех 750к записей? или EXPLAIN показывает, что запрос при обработке использует все 750к записей? Попробуйте переопределить запрос таким образом, чтобы "окно" обработки было более меньшим. Также в данном случае лучше не использовать SELECT *, а перечислить нужные поля, это даст небольшой прирост.

[WiNNeR_tig]

Владимир Скибин, там не нужно читать сами поля, нужно найти есть ли уже запись. Т.е. я делаю так

if($mysqli->query("SELECT `id` FROM `таблица` WHERE `поле` = 'номер который ишем' LIMIT 1 ")->num_rows > 0){ запись есть

[Владимир Скибин]

WiNNeR_tig, А что EXPLAIN показывает на Ваш запрос?

[WiNNeR_tig]

[Владимир Скибин]

WiNNeR_tig, как видно из скрина - у Вас используется только индекс по полю status, а далее каждая из 55170 строк сравнивается в "ручном" режиме. Попробуйте сделать составной индекс по полям (именно в этом порядке) `status`, `idad`. Что в этом случае покажет EXPLAIN ? Т.е. нужно добиться того, чтобы в key был нужный индекс и rows было минимальным. Также желательно, чтобы Extra вообще было пустым.

[WiNNeR_tig]

Владимир Скибин, А как поменять порядок индекса ?

[Владимир Скибин]

WiNNeR_tig, лучше тогда пересоздать индекс.

ALTER TABLE `tb_visits` ADD INDEX `idx_status_idad` (`status`, `idad`);

При порядке важно понимать по какому критерию меньше "окно" запроса. Для этого можно посмотреть

SELECT `status`, count(*) FROM `tb_visits` GROUP BY `status`;
SELECT `idad`, count(*) FROM `tb_visits` GROUP BY `idad`;

Чем выше селективность (для выборки по ключу меньше значений) у запроса - тот индекс и надо ставить первым. Т.е. проще сначала выбрать 1000, а потом из этой 1000 выбрать 20, чем выбрать 50000, а потом из этих 50000 выбрать 20

[WiNNeR_tig]

Владимир Скибин, https://yadi.sk/i/fZ0ajp-i3N9TTE

[Владимир Скибин]

WiNNeR_tig, А что теперь EXPLAIN показывает?

[WiNNeR_tig]

Владимир Скибин, переделал немного индексы и программу

https://yadi.sk/i/qH0vmWhM3N9bw8

https://yadi.sk/i/bzUn6tQw3N9c3D

[Владимир Скибин]

WiNNeR_tig, EXPLAIN вместе с запросом обычно показывают )
А так у Вас получается что из 2-х возможных индексов мускул выбрал тот, по которому уже лучше делать выборку.
Долно получиться что то вроде такого: https://www.screencast.com/t/7fcjTkrazAbT

[WiNNeR_tig]

Владимир Скибин,

https://yadi.sk/i/sk5z4xuI3N9dKW

Но там несколько вариантов , такой чаше всего

[WiNNeR_tig]

Владимир Скибин, забила базу на 700т записей в ручную , при новых изменениях нагрузки пока нет...

[WiNNeR_tig]

Владимир Скибин, забил на 2мл нагрузки нет... видимо сделал правильно

Answer 2

[RidgeA]

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

Comments

[WiNNeR_tig]

Каким образом ?

[RidgeA]

В приложении - код, который обращается в бд обернуть в кеширующий слой, где ключем кеширования будет запрос, а значением - результат. + надо ttl выставлять. Хранить это все можно в memcached/redis. При запросе проверять наличие значения в кеше по ключу (кстати это будет работать для select запросов, в вопросе не уточнено какой характер запросов).
Если запросы идуть сильно разные - то кеша может быть много.

В вебсервере - аналогично, ключ кеширования - параметры запроса, значение - ответ. В таком случае не будет подниматься даже PHP что бы глянуть в кеш или в базу.

Недостаток - данные могуть быть определенное время неактуальны (про сроку кеша)

PS в вопросе не написано, в чем собственно проблема.
Возможно будет достаточно тюнинга конфигов бд или добавления индексов.
Если проблема в скорости ответа - действительно ли БД это узкое горлышко или может PHP?

[WiNNeR_tig]

Дело точно в бд так как отчистив эту таблицу я получаю нагрузку с 80% до 15% пока она вновь не наполнится.
Подобный кеш не подходит, так как не только читают, а ещё и создают новые записи и обновляют те что есть.
Там всего 6 полей которые все содержат числовые значения.

Answer 3

[Alexey Epsilon]

Можно использовать банальное кэширование на уровне кода. Можно посмотреть зачем вам столько запросов и какие они.
У меня есть большое сомнение, что подобное использование оправдано.

Answer 4

[Boris Köln]

300 запросов в секунду

Если у вас сотни http-запросов в секунду - значит, у вас отличное приложение, раз оно выдерживает такую нагрузку. Похвастайтесь своим сайтом.

Если у вас единицы или десятки http-запросов в секунду - значит, ваше приложение - говнокод, раз он сам создает такую нагрузку.

Comments

[WiNNeR_tig]

В общем по сайту мускул выдаёт в секунду: 1,964 Эти запросы создают посетители, т.е. люди. Сайт на php 7 и mysql 5.7 Пиковые нагрузки когда народ активен, это начинается после обеда и длится до 8 вечера.

[Boris Köln]

Я спрашивал про http-запросы.
Ну или одновременное количество посетителей на сайте.
В крайнем случае и общее количество посетителей за сутки подойдет.

[WiNNeR_tig]

В среднем 25т в сутки Пиковый онлайн около 2т

[RidgeA]

2к пользователей создают до 300 запросов в секунду в одну таблицу при чем как на запись так и на чтение? Либо что-то не так либо что-то специфическое.
Может тогда не MySQL+PHP?
Часть времени тратится на установку соединения в бд при каждом запросе.
Та же nodejs может открыть соединение при старте и работать через него (через уже готовый пул соединений).

[WiNNeR_tig]

2к пользователей создают до 300 запросов в секунду

2к общий онлайн, на сайте много всего. Но именно в ту таблицу от 300 запросов.
С nodejs дело имел и понял что не всё понимаю и забросил эту идею....

[AlikDex]

конечно нужно учитывать специфику запросов, но все же 300 в секунду при простых селектах и апдейтах это совсем ниочем даже для хостинга за 2$.

Answer 5

[xmoonlight]

WiNNeR_tig, проблема или в некорректной индексации, или в самих запросах.
Если база настолько оперативна - попробуйте перевести в Redis её полностью, выставив относительно небольшой интервал сброса данных из памяти на диск.