При проектировании таблиц в базах данных может возникнуть вопрос, я надеюсь, как хранить строки в 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 решит вашу проблему.
В представленной ситуации это решение оказалось удивительно эффективным.
