Почему после импорта базы из .sql файлов таблицу с 13Гб раздуло до 55Гб?

Question

[Растишка]

На обоих серверах одинаковые:

• Версия Mysql Ver 14.14 Distrib 5.7.44-48, for Linux (x86_64) using 6.2
  
• Структура выросшей таблицы (поля, типы, collation).
  
• Индексы в таблице.
  
• Колво строк одинаковое.
  
• Выборочно сравнил некоторые строки - данные одинаковые.
  

Старый mysql был в докере, новый - в виртуалке.
Но вроде не должно влиять же?

Бекап делал и восстанавливал через Битрикс24 (но там же просто sql?).

Проблема в том что запросы в БД сейчас иногда в рандомное время подвисают до 1минуты.
Запросы простые, с индексами. Есть подозрение что распухшая БД не влезает в память/буферы и много IO на диск.

Comments

[alexalexes]

Демон mysqld одинаково кушает память в обеих системах?

[Akina]

После восстановления из бэкапа прогоните OPTIMIZE LOCAL TABLE, и только потом смотрите на размер файла таблицы.

[Растишка]

Akina, OPTIMIZE LOCAL TABLE же только удаленные строки схлопнет? Но их нет раз мы только залили.

[Антон Шаманов]

файловая система может отличаться

[rPman]

настройки размера индексов в базах данных, просто сравни файлы настроек базы данных на сервере

при создании таблиц наверное не указали ROW_FORMAT=COMPRESSED (типовой пример - огромные текстовые поля ничем не заполненные)

еще вариант, вместо cp1251 включили utf8, и все русские тексты распухли четырехкратно

[Ипатьев]

rPman, откуда там "четырёхкратно"? Русские тексты "распухнут" только в два, но во-первых, это только часть данных, а не вся база, а во-вторых, в вопросе написано что collation (как обычно, впрочем, неграмотно вместо charset) осталась та же.

[Ипатьев]

У меня два вопроса к автору

1. Как считали размер? Точно ли у старой базы 13 гигов? Откуда конкретно цифра?
2. В память может не влезать и старая БД. Буфера в Mysql вообще настраивались? Какое значение в новой базе у innodb_buffer_pool_size?

[Растишка]

rPman, хорошая гипотеза, но и там и там DYNAMIC

[Растишка]

Ипатьев, размер смотрю запросом в INFORMATION_SCHEMA.TABLES. Через ANALYZE TABLE чинить стату пробовал - не помогло.

[Ипатьев]

Растишка, в INFORMATION_SCHEMA.TABLES показывается ерунда. Я думал вы размер файлов смотрите.
Akina, я прав?

[Ипатьев]

Растишка, вы не ответили на второй вопрос. Почему?

[Akina]

Ипатьев,

For InnoDB, DATA_LENGTH is the approximate amount of space allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.

Ну не то чтобы совсем ерунда, но плюс-минус лапоть. Для обновления используем ANALYZE TABLE? при отсутствии конкурентов есть шанс получить точные данные.

[Растишка]

Ипатьев, Да, сорян, был занят другими задачами. Сравнили физические файлы на дисках:
47Гб (который 11Гб показывал)
58Гб который поле импорта

Ну хотя бы теперь сравнимо.....

Answer 1

[Ипатьев]

Судя по всему, у нас тут опять дети, заблудившиеся в лесу, АКА "проблема XY".

Реальной проблемой является "запросы в БД подвисают". При этом посмотрев на какую-то непонятную циферку, дети приходят к закономерному выводу, что проблема в "раздутой базе". И начинают искать решение не для реальной проблемы, а для воображаемой.

Если "БД не влезает в память/буферы" - это значит, что и надо настраивать "память/буферы". А не по INFORMATION_SCHEMA шариться.

В первую очередь смотрим значение innodb_buffer_pool_size, и если там стандартные птичкины слёзки в 128 мегабайт, то восхищаемся мощью своего сервера, который вообще без доступа к памяти нормально вывозит, только иногда "подвисая", и ставим innodb_buffer_pool_size равное 80% от памяти доступной на сервере.

Comments

[Михаил Ливач]

innodb_buffer_pool_size равное 80% от памяти доступной на сервере

- доступной с учётом остального софта. (пояснение для топикстартера на всякий случай)

[Растишка]

Сравнили физические файлы на дисках:
47Гб (который 11Гб показывал)
58Гб который поле импорта
Ну хотя бы теперь сравнимо.....

innodb_buffer_pool_size на старом 18Гб, на новом (который из бекапа) - 14Гб

[Ипатьев]

Растишка, ну как минимум на новом довести до 18
как максимум - запустить на обоих SHOW ENGINE INNODB STATUS
и смотреть различия

[Растишка]

Ипатьев, Будем пробовать, спасибо.