MariaDB, фрагментация, varchar и печалька

Спасибо. Да, такое решение напрашивается. Однако в нашем случае получалось печально.
OPTIMIZE TABLE для InnoDB делает recreate + analyze, что требует блокировки таблицы.
Происходило следующее: для получения блокировки СУБД ждала завершения запросов и коммита транзакций. При этом в БД без остановки ходил Clickhouse для синхронизации словарей - длинные запросы были всегда, были и больше транзакции. Пока ждали - запросы от клиентов накапливались, причём как запросы в БД, так и запросы к веб-серверу. Сама таблица довольно большая, сервер неплохо нагружен - на перестроение требовалось время. Нагрузка на сервер и очередь в СУБД непрерывно росли. Если при этом на сервере запускалось что-то потреблявшее RAM мог появится oem killer, и появлялся, убивал СУБД, после чего она не могла стартануть тк появлялись битые данные. Значит стартуем СУБД в режиме восстановления, пока чиним не принимаем запросы в веб-сервер - лежим по полной программе. То есть нужна целая группа подготовительных операций, требующих частичной остановки сервисов, чтобы всё прошло гладко.
Поэтому нужно было другое решение.

Спасибо за интерес.

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

С "изначально плохой" архитектурой всё в проекте "хорошо", о чём есть пояснения в статье. Архитектуру и немалый беклог мы получили от аутсорсеров. В момент обострения проблемы (фаза 3), решались другие более важные для бизнеса проблемы.

Почему бы не использовать партиционирование для "удаленных" строк. 

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

Почему был выбран xml для хранения настроек?

Как это часто бывает он не был выбран. Он уже был, причём связывал 2 системы.

Как по мне, это совсем неудачный формат - большой объем плохо писать и читать.

Как раз с этим проблем в нашем случае не было, сам процесс записи и чтения занимал незначительную часть времени.

Насколько динамична структура файла настроек?

В обсуждаемом периоде 2-4 изменений в месяц.

ИМХО, вы пытаетесь лечить последствия, а не саму проблему.

На момент проблемы фаза 2(на графике) мы работали с условиях дефицита времени и ресурсов. В момент 3 и на начало фазы 4 добавился ещё дефицит персонала, решение позволило забыть о проблеме на продолжительное время. За это время были сделаны важные для бизнеса задачи. Когда появились ресурсы и время - фаза 5, время сократили в 10 раз до 2х минут и вопрос стал неактуален в любом разрезе, тк для ядра обновление чаще чем каждые 5 минут было излишним.

Если у вас был char и будет char иделическая картина сохранится, но не будет лучше чем была. В некоторых случаях можно перестать быть char, но это вероятно не ваш случай.

Вообще-то InnoDB - версионник. И при этом, внезапно, пишет новую версию на старое место. Просто старую версию уносит в другое место.

Oracle делает так же.

Паддинг не бесплатный, особенно, если у вас 95% записей имеют длину 7-10 символов, оставшиеся 5 процентов, скажем, под сотню.

Ситуацию as is не воспроизвести. Но планы посмотреть хотя бы на синтетически данных есть. Но это не точно.

У постгреса есть автовакуум, который будет снижать фрагментацию. А без него будет почти то же самое. Фактически recreate + analyze это постгресовый вакуум фулл.

В постгресе всё воспроизводится на 146%.

В нём любое изменение создаёт новую версию (читай - копию) строки. Даже UPDATE test_data_varchar_with_update SET height = height + 1 (изменение значения в int колонке) удвоит размер занимаемого места, а потом придёт дефрагментатор (автовакуум) и почистит мусор.

Погонял немного запросы, получил такое:

CREATE TABLE test_data_varchar_with_update (
     id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY ,
     id_pointer varchar(100) DEFAULT NULL,
     name text NOT NULL,
     width int NOT NULL DEFAULT 0,
     height int NOT NULL DEFAULT 0
)WITH (autovacuum_enabled=off);

insert into test_data_varchar_with_update (id_pointer, name, width, height)
select random()::text, round(random() * 100000)::text, (random() * 20000)::int, generate_series(1, 1000000);

create table t2 as select * from test_data_varchar_with_update;
alter table t2 add primary key (id);

UPDATE test_data_varchar_with_update SET height = (random() * 1000000)::int;
UPDATE test_data_varchar_with_update SET name = substr(repeat(md5(random()::text), 8), 0, 10 + (random() * 240)::int);
UPDATE test_data_varchar_with_update SET name = substr(repeat(md5(random()::text), 8), 0, 10 + (random() * 240)::int);

select pg_size_pretty(pg_relation_size('t2')); // 72 MB
select pg_size_pretty(pg_relation_size('test_data_varchar_with_update')); //526 MB

explain analyze select * from test_data_varchar_with_update where id in (select (random()*100000)::int as id from generate_series(1, 1000));
explain analyze select * from t2 where id in (select (random()*100000)::int as id from generate_series(1, 1000));

Последние два стартуют с 70 мс vs 20 мс, но после 5 - 10 запросов устаканивается на 15 - 30 мс vs 10 - 15 мс.