Как организовать умное кеширование MYSQL?

Question

[Soniked]

Добрый день
Есть таблица сообщений в бд mysql
В таблице более 2 миллионов записей, а так же 30+ столбцов
Так же, конечно есть индексация
Суммарный объем таблицы составляет более гигабайта. В пике нагрузки потребление памяти превышает 7 гигабайт, что довольно много

По таблице регулярно происходит поиск, однако задействованы зачастую последние 2-3 тысячи записей, изредка доходит до последних 100 тысяч, не более
Какие есть простые решения для оптимизации подобных таблиц? MYSQL FORCE и OPTIMIZE не особо помогают
На ум приходит только создание таблицы "старых сообщений", однако тогда придётся переписывать все взаимодействия с данными на стороне сервиса, что уже сразу делает решение "непростым". Так же читал про redis и подобные кеши, однако, насколько я понимаю, это так же потребует переделывания всей таблицы, вынос сообщений в отдельный сервис и переделка всего взаимодействия с таблицей

Что можете посоветовать?

Comments

[Everything_is_not_so_bad]

1. покажи show create table table_name1
2.

По таблице регулярно происходит поиск

какой запрос? Нам нужно гадать? Покажи EXPLAIN запроса.
3. покажи какие-то графики о том что происходит с СУБД во время простоя и поиска.

[shurshur]

rrambo, это логотип языка программирования.

[Дмитрий]

https://www.google.ru/search?q=%D0%BF%D0%B0%D1%80%...

Answer 1

[Ипатьев]

Как всегда, в заголовке одно, а в тексте вопроса совсем другое. И никакое кэширование автору делать на самом деле неохота - это же переделывать всё придется.

При этом чего именно нужно автору, из вопроса непонятно. То ли проблема с потреблением памяти, то ли скорость запросов, то ли вообще никаких проблем нет, а просто поговорить на с кем.

Ускорение запросов решается за счет создания индексов. Не "есть индексация", а конкретные осмысленные индексы для каждого используемого запроса. Если конкретный запрос тормозит, ему надо сделать explain, и на основе полученного результата подумать и реализовать индексы.
При этом нормально индексы работают только если все они помещаются в памяти. А это значит, что по поводу "7 гигов занято" надо не плакать а радоваться.
Существование "таблиц" для новых и старых сообщений в рамках одной таблицы - это партиционирование, например по дате. Но опять же, сначала надо определиться задачей, которую мы решаем.

Comments

[mayton2019]

Кеширование с использованием Redis имеет смысл. Но надо сначала исчерпать все возможности
бд MySQL. Мы использовали Redis в одном проекте для крупной торговой сети косметических
магазинов США. И идея заключалась в том чтобы материализовать все возможные запросы
от клиента в том числе к рекомендательным системам в оффлайне. И предоставлять готовые
ответы в онлайне и без использования например CosmosDb как основной системы хранения.

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

По сабжу автор еще не рассказал нам архитектуру своей системы и не показал execution plans.
Поэтому имеет смысл пока подождать. Надо больше собрать сведений.

Лучшее решение - это компросисс между разными видами ресурса. Мы выигрываем в чтении
данных но проигрываем в реакции системы на их обновление к примеру.

[Soniked]

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

[Vitsliputsli]

mayton2019, специализированное решение которое подходит для "крупной торговой сети косметических
магазинов США" совершенно не обязательно подходит для всех проектов.
И, как правильно заметил Ипатьев, у автора просто не получается сформулировать реальную задачу, а значит все варианты сейчас это просто гадание.
Soniked, не надо писать как вы хотите решать неизвестную нам задачу, напишите саму задачу, чего вы хотите добиться. По последнему комментарию, можно предположить, что вам жалко оперативы для MySQL и вы хотите уменьшить ее потребление. Тогда установите размер buffer pull таким, каким хотите его видеть. И только после этого, если окажется, что MySQL его не хватает (т.е. появятся постоянные чтения с диска), вот тогда и нужно будет искать решение.

[Ипатьев]

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

[Soniked]

Vitsliputsli как раз постоянные чтения диска и заставили изначально увеличивать память
Ипатьев в целом, согласен с вами, однако тенденция не очень радует - приходится добавлять почти по гигабайту в месяц, что значит через год сервис будет просить уже 20+ гигабайтов оперативной памяти с околонулевой нагрузкой на процессор, что, как минимум, нерационально

[Vitsliputsli]

Soniked, тогда действительно странно, у вас только 1 таблица в БД? Если обращаетесь только к 100тыс, то только они и должны лежать в buffer pool, с индексами примерно также. Конечно, если запросы действительно так работают, а не перелопачивают все данные.
Для начала, посмотрите сколько занимают места на диске все таблицы и индексы, и что пишет стандартный монитор InnoDB.

[mayton2019]

Если синьор-девелопер занимался задачей уже несколько дней - то считай что уже оплачено
две планки Kingston Fury 32Gb. Стоит ли в самом деле упарываться оптимизацией если
есть другие пути.

Answer 2

[mayton2019]

Можно попробовать материализовать какие-то срезы главной таблицы для
быстрого извлечения информации. Классифицировать
оперативные типы запросов и для каждого класса создать материализованную view читать оттуда.
Структура mat-view может быть денормализованной например

CREATE TABLE mview1 (id varchar primary key, doc JSON);

Формат документа может быть произвольным но главное что он должен
собирать ровно те сведенья которые нужны для responce ни больше ни меньше.

По поводу быстрого извлечения 100 тысяч datarows из 2 млн. Это вызывает у меня
большое изумление. Для кого эта выборка? Человек-оператор не успеет прочитать
эти строки за разумное время. А для фоновых задач типа jobs скорость отлика вообще
не важна. Особенно если джобы работают ночью например и еще и объединяются в пакет.

И если у вас идет неравномерный доступ к таблице то возможно имеет смысл разделить
ее на union из двух таблиц типа hot_data + historical_data. Это потребует переписывания
софта но зато у вас будет очень рациональное использование индексов. Они будут маленькие
и соотв. не будел вытеснения индексных страниц из кеша страниц.

Вот. Разделить можно по дате создания бизнес-факта или по другим вризнакам где есть date+time.

Comments

[Ипатьев]

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

[mayton2019]

Ипатьев, да. Но автор пока нам не предоставил ни планов
ни схем и поэтому я считаю что он уже давно уже сделал тюнинг использования индексов
по максимуму и поэтому я предлагаю ему уже не MySQL-ные шаблоны оптимизации
а ... прикладные что-ли.

[Ипатьев]

Клиповое мышление - бич современной молодёжи.
Вспоминаем написанное 25 минут назад

По поводу быстрого извлечения 100 тысяч datarows из 2 млн. Это вызывает у меня
большое изумление. Для кого эта выборка?

Никакой выборки скорее всего нет. Автор говорит про глубину просмотра таблицы, а не выборку.

[mayton2019]

Может быть. Но в таком случае он мог бы писать про time range. А он вполне себе точно указывает
на количество штук.

[Soniked]

Спасибо за развёртнутый ответ

Действительно, какое-то быстрое извлечение N запросов не нужно, нужно просто уменьшение объемов невыгружаемого кэша MYSQL.
Классически, кэш используется для ускорения запросов, я сбил с толку изначальной постановкой вопроса
Как я отвечал выше, медленных запросов менее 0.1% (25 тысяч на 25 милллионнов)

Просто для микросервиса слишком много суммарное потребление под 10 гигабайт оперативной памяти, как мне кажется.

Скорее всего, буду делать union из двух таблиц

[shurshur]

Soniked,

для микросервиса слишком много суммарное потребление под 10 гигабайт

Нет, для микросервиса нормально, что ему нужно для работы столько памяти, сколько ему нужно для работы. Реальные задачи должны диктовать условия и ограниения для оркестрации, а не наоборот.

Answer 3

[Freeman]

Суммарный объем таблицы составляет более гигабайта. В пике нагрузки потребление памяти превышает 7 гигабайт, что довольно много

Что можете посоветовать?

Начать использовать, наконец-то, связываемые переменные в запросах?

Comments

[Ипатьев]

Это ответ от ИИ или оригинальная искромётная шутка, которую вы сами придумали?

[shurshur]

Ипатьев, скорее отвечающий не понял вопроса. Для ИИ ответ слишком короткий.

[mayton2019]

Не понятно вообще при чем здесь bind variables.