При проектировании таблиц в базах данных может возникнуть вопрос, я надеюсь, как хранить строки в char или varchar. Совсем недолго помучавшись почти всегда выбирается varchar, по причине того, что места занимает меньше.
Собственно о последствиях этого выбора на реальном примере и поговорим , а так же о причинах по которым эти последствия возникают, и о неидеальных решениях этой проблемы.
Ситуация
Вводная часть
Есть проект, код и БД спроектированные и реализованные аутсорсерами junior’ми, есть новая инхаус команда разработки и 100500 проблем. Как обычно бывает в такой ситуации задач много, ресурсов мало, надо делать новое, молится чтобы не развалилось старое, наводить порядок во всём. Суть проекта коротко – есть портал и api для создания, сохранения, редактирования, настройки ядра волшебной системы, которая приносит деньги. Настройки надо собрать, скомпоновать, немного отформатировать и отправить в виде большого XML файла.
Для тех у кого возник вопрос - зачем файл?
Файл нужен для того, чтобы:
– был простой срез состояния системы на случай катастроф и расследований;
– для быстрого отката к определённому состоянию;
– для запуска новых нод.
На существующих нодах настройки применялись только для измененных параметров.
Одна из проблем - уверенный рост времени генерации файла настроек. Однако, время генерации файла было далеко не главной проблемой (новый функционал важнее) и приоритетом (деньги) - поэтому вопросом специально никто не занимался. Проблему отодвигало на дальний план ещё и то, что запросы стали проверять на время исполнения, ключи стали делать заранее, а не после катастроф - в общем более разумно подходить к вопросу, что резко уменьшало вероятность катастрофы.
Технические вводные
База данных: MariaDB 10.4
Engine: InnoDB, буфер до 12GB
Размер БД: 28 GB
Машина: 8 ядер, 32 RAM, SSD
На машине работают и другие сервисы, CPU ниже 30% не падает, оперативная память в пике занята полностью, la ниже 4 не опускается.
Для тех у кого возник вопрос - почему не добавить машин?
Деньги. Особая экономия на всём что не грозит падением ядру.
Время генерации файла настроек от 20 до 40 минут. Большая часть времени генерации - выборки из БД. Самая продолжительная выборка связана с таблицей, хранящей неделимые сущности, назовём её sections. В таблице инкрементальный id типа int и 36 столбцов, 7 столбцов типа varchar, остальные фиксированной длинны флаги-tinyint, int, enum, float. Самая большая по количеству записей и чтений таблица, количество строк от 2 млн до 4+ млн.
Данные из БД не удаляются (ставится флаг "удалено" для строки), так как во-первых такова политика, во-вторых даже удалённые сущности могут быть воскрешены без вмешательства тех отдела и должны работать как до удаления в полном объеме.
Развитие ситуации
График развития ситуации
Фаза 1 – аутсорсеры. Данных относительно немного, но объем данных постоянно растёт. Что тут было рассказать не могу, но и так видно что скучно не было.
Фаза 2 – начало инхаус разработки, данных всё больше, добавляются новые сущности в файл настройки. Приходят большие клиенты, ужесточаются требования к стабильности сервиса.
Уменьшение времени генерации происходило после ALTER’ов с добавлением полей таблицы sections, что объяснимо тк такая операция приводит к перестроению таблицы, своего рода recreate+analyze. (Напомню что данные из таблицы не удалялись.) Процесс на загруженной машине не быстрый и приводит к блокировке таблицы, отчего часть функций проекта не работала довольно продолжительное время. Поэтому старались таблицу не трогать.
Красными линиями обозначена тенденция на увеличение времени генерации файла, как видно не самая радужная.
Долго ли коротко ли, ситуация привела к катастрофе, обозначенной пиком, за которым последовал полный отказ генератора, который график не отображает, так как регистрирует только удачные генерации.
Тут ваш покорный слуга был вынужден заняться вопросом серьёзно. Внезапно и руководство решило что вопрос надо решать, вроде не стартап уже.
Фаза 3 – борьба с последствиями падения, вынужденный recreate таблиц.
Видно что время генерации упало до докатастрофических времён, однако скорость нарастания времени генерации не предвещала ничего хорошего.
Изучение запросов, структуры таблиц, действий партнёров и истории событий.
Фаза 4 – ALTER с отказом от varchar для части столбцов в таблице sections и части связанных таблиц.
Скучная стабильная жизнь БД. Без alter и recreate. Объем данных растёт сравнимыми с предыдущими периодами темпом.
Фаза 5 – решаем проблемы скучно, по взрослому.
Слейв, отдельная машина, перенос генератора на крайние версии языка, много RAM, незагруженный SSD. Даже перечислять неинтересно и сказ совсем не про это.
Отказ от varchar – полностью исправил ситуацию
После изучения таблиц, запросов, порядка работы с системой – было принято решение изменить тип строковых полей с varchar на char для столбцов, которые вероятно будут изменены, в нагруженных таблицах. Результаты решения превзошли все ожидания. Скорость нарастания времени генерации значительно упала, к тому же сократилось время генерации. Пропали выбросы по времени. При этом в плане железа и загрузки по сравнению с предыдущими периодами ничего не изменилось.
Технический разбор ситуации
Фрагментация таблицы.
InnoDB хранит данные в страницах по 16k (по дефолту), в странице содержится столько строк сколько может поместиться в этот объем, индекс содержит указание на страницу в которой хранятся данные. Страницы с данными при этом размещаются в файле данных по primary key. (Более подробно о механизме можно почитать тут .) Такая организация данных может привести к фрагментации таблицы при операциях изменения/добавления данных.
Итак в нашем случае фрагментация не может возникнуть:
– при delete, тк строки из таблицы не удаляются;
– при insert тк всегда вставляются новые сущности с большим id.
Возникает она из-за update и того что в строке есть varchar.
Как происходит фрагментация при update.
Пусть у нас есть таблица test из 3х столбцов id int pk, name varchar(10), val int, при этом страница InnoDB допустим вмещает 2 строки длины 2 int +4 символа
Итого у нас 2 страницы на 3 записи, индекс с двумя страницами и данные в файле уложены последовательно.
Далее UPDATE test SET name=’row1new’ WHERE id=1 строка стала длиннее и не помещается не только на место старой строки с id=1, но и в страницу в целом. Для того чтобы данные были уложены по pk надо разместить строку после апдейта в начало, но это невозможно. Значит данные надо уложить по другому, например id=1 перенести на новую страницу так
Индекс при этом будет для id=1 указывать на страницу 3, для id=2 на страницу 1, а для id=3 на страницу 2. В первой и третьей странице при этом образуется свободное место, которое не может быть занято, тк укладка данных по pk будет нарушена.
В итоге мы получили 3 страницы вместо двух, удлинение индекса с уменьшением его эффективности, данные уложенные непоследовательно, что ухудшает скорость чтения с диска.
Частота, задержки и характер изменения строк с varchar
В нашей ситуации и при проектировании таблиц – это немаловажный параметр для выбора типа данных в нагруженной таблице. Рассмотрим 2 фактора:
1) уменьшение или увеличение длинны строки varchar;
2) задержка update’а строки.
По пункту 1. Если строка уменьшится то не надо менять индекс и не надо создавать/пересобирать страницы. Например для нашей таблицы test сделаем UPDATE test SET name=’r1′ WHERE id=1, в первой строке появятся 2 свободные позиции, никаких изменений индекса или положения данных.
Проверим это на реальной БД, создаем таблицу
create of test
CREATE TABLE ‘test’ (создаем в ней 2 записисоздаем в ней 2 записисоздаем в ней 2 записи
‘id’ int(11) NOT NULL AUTO_INCREMENT,
‘name’ varchar(10) NOT NULL,
‘val’ int(11) NOT NULL,
PRIMARY KEY (‘id’)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=ascii COLLATE=ascii_general_ci
создаем в ней 2 записи
пойдём смотреть что в файле
и обнаружим что файл пуст.
Вот тут мы подошли к пункту 2. СУБД не пишет данные на диск сразу, сначала данные накапливаются в буфере, где они могут быть выровнены и отсортированы перед записью на диск и проблемы фрагментации не будет. Давайте заставим СУБД записать данные. Смотрим
Вот наши строки в файле.
Делаем UPDATE test SET name=’r1′ WHERE id=1 и видим в файле
строка 2 не сместилась, строка 1 осталась на месте, просто длинна её уменьшилась на 2 символа и образовалось свободное место.
Сделаем UPDATE test SET name=’row1new’ WHERE id=1, смотрим в файл
Первая строка не поместилась перед второй и была записана после второй, тк перед второй строкой места для неё не хватает.
В рассматриваемой ситуации строки вставлялись либо с пустыми значениями для полей типа varchar, либо со значениями минимальной длины. Затем по прошествии времени достаточного для заполнения страницы InnoDB данные обновлялись, что приводило к необходимости изменения страниц. Практически во всех сценариях, где строка изменялась - длина строковых значений увеличивалась с задержкой относительно момента вставки, для одного или нескольких столбцов.
На практике это выглядело так – большой клиент через api создавал группу сущностей с пустыми значениями типа varchar для того чтобы получить id сущности. Затем для полученных id в его системе формировались настройки, идентификаторы и проводилась интеграция с другими сервисами. После всех этих операций сущность в нашей системе получала текстовый идентификатор(varchar) значительной длины, настройки(varchar) и активировалась. И далее в процессе жизни записи значения varchar старых записей могли увеличиваться за счёт добавления указателей в текстовый идентификатор записи. Через интерфейс процесс мог выглядеть примерно так же, но объемы были незначительны. Таким образом заводилось от 30 до 50% записей.
Эксперимент
Создадим таблицу побольше test_data_varchar_with_update с одним изменяемым полем ‘name’ varchar(255)
create of test_data_varchar_with_update
CREATE TABLE ‘test_data_varchar_with_update’ (
‘id’ bigint(20) unsigned NOT NULL AUTO_INCREMENT,
‘id_pointer’ varchar(100) DEFAULT NULL,
‘width’ int(10) unsigned NOT NULL DEFAULT 0,
‘height’ int(10) unsigned NOT NULL DEFAULT 0,
‘name’ varchar(255) NOT NULL,
‘parent_id’ int(10) unsigned NOT NULL,
‘price’ double unsigned NOT NULL DEFAULT 0,
‘price_typ3’ char(10) NOT NULL DEFAULT ”,
‘category’ int(10) unsigned DEFAULT NULL,
‘price_split’ double unsigned NOT NULL DEFAULT 0,
‘fall_num’ int(10) unsigned NOT NULL DEFAULT 0,
‘fall_enable’ tinyint(4) NOT NULL DEFAULT 0,
‘player’ enum(‘p1′,’p2′,’pp’) NOT NULL DEFAULT ‘p1’,
‘position_x’ enum(‘left’,’center’,’right’) NOT NULL,
‘position_y’ enum(‘top’,’center’,’bottom’) NOT NULL,
‘c_id’ int(11) DEFAULT 0,
‘control’ enum(‘Manual’,’Auto’) DEFAULT ‘Auto’,
‘is_r’ tinyint(4) NOT NULL DEFAULT 0,
PRIMARY KEY (‘id’)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_general_ci
Вставим миллион записей в таблицу со средней длинной строки в колонке name в 5-10 символов, после вставки тысячи записей обновим тридцать процентов строк, перезаписав колонку name строками длинной 11-255 символов.
псевдокод вставки
for (i=1; i < 1000001; $i++){
INSERT INTO test.test_data_varchar_with_update(…, name, …)VALUES(… ‘rowNi’, …”);
if(i % 1000 == 0){
for (j=0; j < 300; j++){
exec(“UPDATE test.test_data_varchar_with_update SET name = ‘”randString(rand(11,255))”‘ where id=”rand(i-1000,i));
}
}
}
Создадим её копию test_data_varchar_no_update и внесём в неё данные из таблицы test_data_varchar_with_update импортом.
Для сравнения создадим копию test_data_char на основе test_data_varchar_with_update, но поле ‘name’ сделаем типа char(255) и внесём в неё данные из таблицы test_data_varchar_with_update импортом.
Таблица с апдейтами должна быть фрагментирована, смотрим на размеры таблиц
таблица с апдейтами практически на 45% больше (что ожидаемо тк по дефолту MERGE_THRESHOLD установлен в 50%). Таблица с фиксированной длиной строки больше всех, тк под строку всегда выделяется 255 байт, однако разница между таблицей с апдейтами не так уж и велика.
Теперь давайте посмотрим что творится непосредственно в файлах с данными. Сначала в файл таблицы без апдейта на границе первой и второй страницы
скрин из *no_update
последняя запись на первой странице с id=81.
Теперь в файл таблицы с апдейтом
скрин из *with_update
последняя запись на первой странице с id=78, разрыв между страницами явно больше и может содержать несколько строк. Это разрыв возможно никогда не будет заполнен.
А что со скоростью выборки из 3х указанных таблиц?
(Во всех тестах InnoDB буфер значительно меньше таблиц.)
Сделаем скрипт который выбирает 100 случайных id в цикле 100 раз. Выведем среднее время исполнения для запроса.
Cycle random, Cycles:100, Ids: 100
Table test_data_varchar_with_update: 0.017486 sec
Table test_data_varchar_no_update: 0.014363 sec
Table test_data_char: 0.016273 sec
Сделаем скрипт выбирающий 1000 последовательных id в цикле 100 раз. Выведем среднее время исполнения для запроса.
Cycle diapason, Cycles:100, Ids: 1000
Table test_data_varchar_with_update: 0.006068 sec
Table test_data_varchar_no_update: 0.005136 sec
Table test_data_char: 0.005806 sec
При случайной выборке таблица test_data_varchar_with_update ощутимо медленнее, чем test_data_varchar_no_update что логично, однако и у большей по размеру test_data_char время исполнения запроса ниже.
Похожий результат и при выборке последовательно диапазона, и тут таблица с test_data_char не проигрывает, хотя требует чтения большего объема данных и содержит в странице меньше строк, а значит может иметь менее эффективный индекс.
Я прогонял тесты на нескольких машинах с разной конфигурацией и чем сильнее загружена машина тем заметнее разница. При этом соотношение времени исполнения сохраняется. Не единожды проявилась неприятная особенность таблицы test_data_varchar_no_update – меньшая стабильность.
Например прогоны на слабом VPS сервере.
Cycle random, Cycles:100, Ids: 100
Table test_data_varchar_with_update:0.453521 sec
Table test_data_varchar_no_update: 0.169505 sec
Table test_data_char: 0.170396 sec
Таблицы с char и no_update менее склонны к значительному изменению времени исполнения относительно друг друга.
Чем мощнее машина, и чем меньше она загружена, тем меньше разницы между таблицами.
Все тестовые машины на SSD, к сожаление найти машину на HDD уже проблематично, однако эффект на HDD должен быть более ярко выражен.
Технический итог
Строки переменной длины в больших нагруженных таблицах, при их частом обновлении – приводят к фрагментации таблицы, что замедляет её работу и может ухудшить работу индекса. Так же приводят к необходимости перестраивать данные при возникновении значительных пробелов в страницах, что для большой таблицы при постоянных запросах может привести к нестабильной работе. Экономия дискового пространства тоже страдает.
Строки фиксированной длины безусловно имеют значительные минусы, однако не требуют переноса данных при обновлении, позволяют сохранить длину строки постоянной и соответственно количество строк на странице постоянным. При отсутствии удалений не требуют перестроения данных в таблице.
Заключение
Не всегда при проектировании мы можем предугадать как будут использоваться строковые поля в БД, будут ли строки обновляться и по какому алгоритму это будет происходить. Даже если можем выявить приоритетный сценарий, то оценить влияние нашего выбора не всегда возможно. Поэтому проблемы порой приходится решать постфактум, однако откуда может быть исходить корень проблемы – надо понимать.
Если таблица в БД после recreate начинает работать быстрее, но при этом начинает быстро уставать – посмотрите есть ли в ней поля переменной длины и насколько часто они меняются, возможно изменение типа varchar на char решит вашу проблему.
В представленной ситуации это решение оказалось удивительно эффективным.
Комментарии (17)
onegreyonewhite
31.05.2023 16:37Побольше бы таких статей на Хабре. Михаил, спасибо за интересное чтиво.
Конечно всю статью можно сократить до заголовка, что динамическая длина плохо для больших данных и кеша в памяти у СУБД, но так более развёрнуто для джунов что-ли. В этом плане меня всегда восхищали архитекторы систем, которые смотрели вперёд и проектировали систему так, что после их ухода из проекта сталкиваясь с их решениями ты вспоминаешь их добрым словом. Это бывает крайне редко, конечно, но хочется быть таким человеком.
Пока читал вспомнил случай с этой же проблемой.
Байка
В общем, меня знакомый позвал помочь с решением одной проблемы похожего плана, что таблица начинала нещадно тормозить на запросах через какое-то время. Это была база какой-то самописной системы управления инцидентами (что иронично) и мониторинга, в которой после деплоя новой версии появлялось очень много уведомлений, которые закрывались с каким-то комментарием. На основе этой таблицы происходила некоторая аналитика и срез её раз в месяц отправлялся на почту.
Так вот, в один прекрасный день этот отчёт вместо одного часа начал завис почти на сутки. Сначала мой знакомый обратился по моему совету к тому, кто на мой взгляд "шарил" в MariaDB. Он как раз получил совет - OPTIMIZE TABLE. Собственно это и было сделано. Но проблема возникла снова уже через месяц и задача уже зависла на почти 2 суток. Сначала в лоб было предложено решение делать OPTIMIZE TABLE перед каждым запуском таски. Но как оказалось, операция была тоже не быстрая и хотелось найти проблему.
Собственно, я спросил что менялось за последние 3-4 месяца в системе. Ничего существенного не было. Изменения были в разработке - стали чаще релизить на прод, от чего количество уведомлений в систему увеличилось. Ещё одно изменение было в том, что из-за частых релизов, начали происходить частые откаты. Не буду в даваться в подробности, но мы снова обратились к знакомому "эксперту". Вариант вызывать OPTIMIZE TABLE каждый день не понравился нам обоим, поэтому мы начали копать дальше.
Самое забавное, когда оказалось, что при переходе этой самописной системы с чего-то там и палок на фреймворк, они создали ORM соответствующий имеющимся таблицам. Но фреймворк по умолчанию как раз для миграций использует varchar, поэтому при скромном изменении длины в большую сторону, миграция создалась автоматически и изменила тип поля. Это было много раньше (почти год до начала событий), но проявилось после того как стали чаще релизить проект.
Я проникся уважением к админу, который писал изначально эту систему под себя, когда мы подключили его к проблеме (он и указал на решение). Он как раз сказал, что при планировании таблицы у него было желание сразу использовать varchar, но он заметил, что комментарий после закрытия всегда есть и он осмысленный (занимает почти весь размер), поэтому такая оптимизация бессмысленна, а char "ровнее в память ложится" (цитата). Вот что значит понимать как устроен современный CPU!
Мне тогда так стыдно стало, что я даже не подумал об этом. И правда, свободное место на диске прям не драматично изменилось, а скорость вернулась на круги свои.
nicolas_d
31.05.2023 16:37От статьи сложилось мнение, что вы пожинаете плоды изначально плохой архитектуры. Какой rps на обновление данных? Почему бы не использовать партиционирование для "удаленных" строк. Объемы базы весьма скромные, что бы создавать такие проблемы. Почему был выбран xml для хранения настроек? Как по мне, это совсем неудачный формат - большой объем плохо писать и читать. Насколько динамична структура файла настроек?
Сама статья хорошо показывает, что динамический размер строки в БД, в часто обновляемой таблице, является злом, но, ИМХО, вы пытаетесь лечить последствия, а не саму проблему.
mv28jam Автор
31.05.2023 16:37Спасибо за интерес.
От статьи сложилось мнение, что вы пожинаете плоды изначально плохой архитектуры.
С "изначально плохой" архитектурой всё в проекте "хорошо", о чём есть пояснения в статье. Архитектуру и немалый беклог мы получили от аутсорсеров. В момент обострения проблемы (фаза 3), решались другие более важные для бизнеса проблемы.
Почему бы не использовать партиционирование для "удаленных" строк.
Проблема была решена так, больше о ней не вспоминали. Время генерации практически не росло, выбросы пропали. Если бы решение не помогло - искали бы другие.
Почему был выбран xml для хранения настроек?
Как это часто бывает он не был выбран. Он уже был, причём связывал 2 системы.
Как по мне, это совсем неудачный формат - большой объем плохо писать и читать.
Как раз с этим проблем в нашем случае не было, сам процесс записи и чтения занимал незначительную часть времени.
Насколько динамична структура файла настроек?
В обсуждаемом периоде 2-4 изменений в месяц.
ИМХО, вы пытаетесь лечить последствия, а не саму проблему.
На момент проблемы фаза 2(на графике) мы работали с условиях дефицита времени и ресурсов. В момент 3 и на начало фазы 4 добавился ещё дефицит персонала, решение позволило забыть о проблеме на продолжительное время. За это время были сделаны важные для бизнеса задачи. Когда появились ресурсы и время - фаза 5, время сократили в 10 раз до 2х минут и вопрос стал неактуален в любом разрезе, тк для ядра обновление чаще чем каждые 5 минут было излишним.
i1958081
31.05.2023 16:37Спасибо, что поделились опытом !
По ходу чтению возник вопрос: если возникнет потребность увеличить размер поля : char(44) -> char(96) , что произойдет в таком случае, испортится ли описаная идеалистическая картина хранения ?
mv28jam Автор
31.05.2023 16:37Если у вас был char и будет char иделическая картина сохранится, но не будет лучше чем была. В некоторых случаях можно перестать быть char, но это вероятно не ваш случай.
fraks
31.05.2023 16:37+3Хотел бы обратить внимание что сия проблема в этом виде рождается и таким способом решается (замена varchar на char) исключительно в блокировочниках. В версионниках новая версия записи никогда не пишется на старое место, всегда на новое, и озвученная проблема либо не возникнет, либо возникнет, но таким методом не решится.
Это камент, на случай если кто-то попытается сделать вывод что "varchar это плохо". Это плохо только на конкретном сервере, на конкретном движке, в определенного типа приложениях.
funny_falcon
31.05.2023 16:37Вообще-то InnoDB - версионник. И при этом, внезапно, пишет новую версию на старое место. Просто старую версию уносит в другое место.
Oracle делает так же.
BEERsk
31.05.2023 16:37Из чисто спортивного интереса любопытно было-бы узнать как в аналогичной ситуации покажет себя Postgres.
mv28jam Автор
31.05.2023 16:37Ситуацию as is не воспроизвести. Но планы посмотреть хотя бы на синтетически данных есть. Но это не точно.
Fafhrd
31.05.2023 16:37У постгреса есть автовакуум, который будет снижать фрагментацию. А без него будет почти то же самое. Фактически recreate + analyze это постгресовый вакуум фулл.
Roman2dot0
Для тега dba оставлю это тут
OPTIMIZE TABLE
mv28jam Автор
Спасибо. Да, такое решение напрашивается. Однако в нашем случае получалось печально.
OPTIMIZE TABLE для InnoDB делает recreate + analyze, что требует блокировки таблицы.
Происходило следующее: для получения блокировки СУБД ждала завершения запросов и коммита транзакций. При этом в БД без остановки ходил Clickhouse для синхронизации словарей - длинные запросы были всегда, были и больше транзакции. Пока ждали - запросы от клиентов накапливались, причём как запросы в БД, так и запросы к веб-серверу. Сама таблица довольно большая, сервер неплохо нагружен - на перестроение требовалось время. Нагрузка на сервер и очередь в СУБД непрерывно росли. Если при этом на сервере запускалось что-то потреблявшее RAM мог появится oem killer, и появлялся, убивал СУБД, после чего она не могла стартануть тк появлялись битые данные. Значит стартуем СУБД в режиме восстановления, пока чиним не принимаем запросы в веб-сервер - лежим по полной программе. То есть нужна целая группа подготовительных операций, требующих частичной остановки сервисов, чтобы всё прошло гладко.
Поэтому нужно было другое решение.
eto_on
А почему не воспользовались percona toolkit? Там утилиты для подобных работ, которые не блокируют таблицы
mv28jam Автор
Найдено рабочее, нативное решение - зачем усложнять.
Впрочем без перконы не обошлось - бэкапы одно время делали.
xeonvs
OPTIMIZE TABLE для InnoDB делает recreate + analyze, что требует блокировки таблицыВ MySQL 8 уже не требует полной блокировки и работает местами даже не в один поток.