10 лучших приемов для оптимизации работы с mysql

Введение

В своей инструкции по установке и настройке zabbix я вообще не затрагиваю вопрос базы данных mysql или производительности сервера в целом. Я просто беру дефолтные настройки mariadb, которые идут с установкой и использую их. Когда у вас не очень большая инфраструктура на мониторинге этого вполне достаточно, чтобы нормально пользоваться системой.

Если вы активно используете zabbix и внедряете его повсеместно во все используемые системы (а я рекомендую так делать), то вы рано или поздно столкнетесь с вопросом производительности системы мониторинга и размера базы данных zabbix.

Тема производительности zabbix очень индивидуальная. Она напрямую зависит от того, как вы его используете, а схемы мониторинга могут быть очень разные. Одно дело мониторить несколько серверов, а другое дело нагруженные свичи на 48 портов со съемом метрик с каждого порта раз в 30 секунд.

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

Оптимизация MySQL

Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:

Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.

Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:

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

Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.

Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.

Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64

Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное

Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.

Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.

Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.

Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:

Затем переместите файлы лога в /tmp:

И запустите сервис:

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

Очистка и уменьшение mysql базы zabbix

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

1. Первым делом надо внимательно просмотреть все используемые шаблоны и отключить там все, что вам не нужно. Например, если вам не нужен мониторинг сетевых соединений windows, обязательно отключите автообнаружение сетевых интерфейсов. Оно само по себе находит десятки виртуальных соединений, которые возвращают нули при опросе и не представляют никакой ценности. Тем не менее, все эти данные собираются и хранятся, создавая лишнюю нагрузку. Если же вам нужен мониторинг сети в windows, зайдите в  каждый хост и отключите руками лишние адаптеры, которые будут найдены. Этим вы существенно уменьшите нагрузку. По моему опыту, в стандартных шаблонах windows мониторинг всех сетевых интерфейсов дает примерно 2/3 всей нагрузки этих шаблонов.
2. Отредактируйте в используемых стандартных шаблонах время опроса и хранения данных. Возможно вам не нужна та частота опроса и время хранения, которые заданы. Там достаточно большие интервалы хранения. Чаще всего их можно уменьшить. В целом, не забывайте в своих шаблонах ставить адекватные реальной необходимости параметры. Не нужно хранить годами информацию, которая теряет актуальность уже через неделю.
3. После отключения лишних элементов в шаблонах, нужно очистить базу данных от значений неактивных итемов. По-умолчанию, они там остаются. Можно их очистить через web интерфейс, но это плохая идея, так как неудобно и очень долго. Чаще всего попытки очистить 50-100 элементов за раз будут сопровождаться ошибками таймаута или зависанием web интерфейса. Далее расскажу, как это сделать эффективнее.

Для очистки базы данных zabbix от значений неактивных итемов, можно воспользоваться следующими запросами. Запускать их можно как в консоли mysql сервера (максимально быстрый вариант), так и в phpmyadmin, кому как удобнее.

Данными запросами можно прикинуть, сколько данных будет очищено:

SELECT count(itemid) AS history FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS history_uint FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS history_str FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS history_text FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS history_log FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS trends FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS trends_uint FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

Если запросы нормально отрабатывают и возвращают счетчик данных, которые будут удалены, дальше можете их удалять из базы следующими sql запросами.

DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

Данными запросами вы очистите базу данных zabbix от значений неактивных элементов данных. Но реально размер базы данных у вас не уменьшится, потому что в дефолтной настройке базы данных используется формат innodb. Все данные хранятся в файле ibdata1, который автоматически не очищается после удаления данных из базы.

Администрировать такую базу неудобно. Нужно как минимум настроить хранение каждой таблицы в отдельном файле. Этим мы далее и займемся, а заодно обновим сервер базы данных до свежей версии mariadb и реально очистим базу, уменьшив ее размер.

Общие настройки

max_connections=64 — устанавливаем параметр минимальным возможным при необходимости экономить ресурсы сервера, при возникновении в логе записей вида «Too many connections…» увеличиваем значение. Не следует изменять значение этого параметра на старте. 4000 клиентов является максимумом. Можно довести максимальное количество клиентов до 7000, но для стандартных сборок 4000 является пределом.

open_files_limit = 2048 Устанавливать значение стоит опираясь на существующее количество открытых файлов MySQL:

В конфигурационном файле задается большее значение.

connect_timeout (MySQL pre-5.1.23: default 5, MySQL 5.1.23+: default 10) — количество секунд по прошествии которых сервер баз данных будет выдавать ошибку, при активном веб-сервере значение можно уменьшать чтобы увеличить скорость работы, на медленной машине — можно увеличивать. max_connect_errors (default 10) — максимальное количество единовременных соединений с сервером баз данных с хоста запрос блокируется если он прерывается запросами с того же хоста до момента окончания обработки запроса) блокируются навсегда, очистить можно только из командной оболочки MySQL:

В случае атаки на сервер нужно уменьшать (5) чтобы отсекать попытки соединения, при большой активности веб-сервера можно увеличивать max_allowed_packet (default 1M) — максимальный для буфера соединений и буфера результата при исполнении SQL инструкций. Каждый тред имеет свой буфер. Хорошим значением для начала будет 16М. tmp_table_size (system-specific default) — максимальный размер памяти выделяемой под хранение временных таблиц. 16М — довольно много.

Примеры готовых конфигураций для разных объёмов памяти можно посмотреть здесь.

Чтобы посомореть значения переменных можно воспользоваться SQL запросом:

или для конкретных переменных:

Чтобы проверить мониториг InnoDB, используте:

Чтобы узнать, не свопается ли память, используйте команду и смотрите строку swap:

Сжатие и оптимизация таблиц InnoDB

Файлы ibdata1 и ib_log

Большинство проектов с таблицами InnoDB имеют проблемы с большими файлами ibdata1 и ib_log. В большинстве случаев это связано с неправильной  конфигурацией MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого само не используется. Я предпочитаю хранить данные таблицы в отдельных  файлах ibd*. Для этого добавьте в my.cnf следующую строку:

innodb_file_per_table

или

innodb_file_per_table = 1

Если ваш сервер настроен и у вас есть продуктивные базы данных с таблицами InnoDB, сделайте следующее:

1. Сделайте резервную копию всех баз данных на вашем сервере (кроме mysql и performance_schema). Вы можете получить дамп базы данных с помощью этой команды:
2. После создания резервной копии базы данных остановите сервер mysql/mariadb;
3. Измените настройки в my.cfg;
4. Удалите  файлы ibdata1  и  ib_log;
5. Запустите демон mysql/mariadb;
6. Восстановить все базы из резервной копии:

После этого все таблицы InnoDB будут храниться в отдельных файлах, и ibdata1 перестанет экспоненциально расти.

Сжатие таблиц InnoDB

Вы можете сжимать таблицы с текстовыми данными / данными BLOB и экономить довольно много места на диске.

У меня есть база данных innodb_test, содержащая таблицы, которые потенциально могут быть сжаты, и поэтому я могу освободить место на диске. Прежде чем что-либо делать, я рекомендую сделать резервную копию всех баз данных. Подключитесь к серверу mysql:

# mysql -u root -p

Выберите нужную базу данных в консоли mysql:

# use innodb_test;

Чтобы отобразить список таблиц и их размеры, используйте следующий запрос:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;

Где innodb_test — имя вашей базы данных.

Некоторые таблицы могут быть сжаты. Возьмем для примера таблицу b_crm_event_relations. Запустите этот запрос:

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

После его запуска вы можете увидеть, что размер таблицы уменьшился с 26 МБ до 11 МБ из-за сжатия.

Сжимая таблицы, вы можете сэкономить много дискового пространства на вашем хосте. Однако при работе со сжатыми таблицами нагрузка на процессор возрастает. Используйте сжатие для таблиц db, если у вас нет проблем с ресурсами процессора, но есть проблема с дисковым пространством.

Сжатие таблиц MyISAM в MySQL / MariDB

Для сжатия  таблиц Myisam используйте специальный запрос в консоли сервера вместо консоли mysql. Чтобы сжать таблицу, запустите следующее:

# myisampack -b /var/lib/mysql/test/modx_session

Где /var/lib/mysql/test/modx_session — это путь к вашей таблице. К сожалению, у меня не было большой таблицы и пришлось сжимать маленькие, но результат все равно можно было увидеть (файл был сжат с 25 МБ до 18 МБ):

# du -sh modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

# du -sh modx_session.MYD

Я использовал в команде ключ -b. Когда вы добавляете его, таблица создается перед сжатием и помечается меткой OLD:

# ls -la modx_session.OLD

# du -sh modx_session.OLD

Как спланировать нагрузку на Zabbix

Под небольшой структурой, упомянутой в начале, я подразумеваю 50-100 узлов (не сетевое оборудование с десятками портов) сети на мониторинге и примерно 2000-4000 активных элементов данных, которые записывают 20-40 новых значений в секунду. Под такую сеть вам будет достаточно небольшой виртуальной машины с 2 ядрами и 4 гб памяти. База данных на преимущественно стандартных шаблонах будет расти примерно на 2-4 Гб в год. Дальше еще меньше, так как будет автоматически очищаться.

Для мониторинга такой сети можно вообще не выполнять никаких дополнительных настроек. Мониторинг будет вполне нормально работать. Если же нагрузка начнет расти, то первое, с чем вы столкнетесь — это с размером и производительностью базы данных. База zabbix будет расти пропорционально подключению к ней хостов. И с этим придется что-то делать.

Для решения вопроса производительности нужно будет двигаться в двух направлениях:

1. Очистка базы от ненужных данных.
2. Увеличение производительности сервера mysql.

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

5 ответов

• 122 рейтинг

  MySQL не уменьшает размер ibdata1. Когда-либо. Даже если вы используете , чтобы освободить место, используемое для удаленных записей, он будет использовать его позже.

  Альтернативой является настройка сервера на использование , но для этого потребуется создать резервную копию, удалить базу данных и восстановить. Положительной стороной является то, что. ibd файл для таблицы уменьшается после .

  ответ дан Leonel Martins, с репутацией 2205, 14.08.2009

• 31 рейтинг

  Просто у меня была такая же проблема.

  Что происходит, так это то, что даже если вы удалите базу данных, innodb все равно не освободит дисковое пространство. Мне пришлось экспортировать, остановить MySQL, удалить файлы вручную, запустить MySQL, создать базу данных и пользователей, а затем импортировать. Слава богу, у меня было только 200 МБ строк, но это сэкономило 250 ГБ файла innodb.

  Сбой по замыслу.

  ответ дан gilm, с репутацией 3870, 25.12.2009

• 21 рейтинг

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

  How To довольно глубокий — но я вставил соответствующую часть ниже.

  Обязательно сохраните копию вашей схемы в вашем дампе.

  ответ дан FlipMcF, с репутацией 9090, 19.01.2012

• 0 рейтинг

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

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

  ответ дан Anand Immannavar, с репутацией 179, 10.02.2015

• -1 рейтинг

  Есть несколько способов восстановить дисковое пространство после удаления данных из таблицы для движка MySQL Inodb

  Если вы не используете innodb_file_per_table с самого начала, дамп всех данных, удаление всех файлов, воссоздание базы данных и повторный импорт данных — это единственный способ (проверьте ответы FlipMcF выше)

  Если вы используете innodb_file_per_table, вы можете попробовать

  1. Если вы можете удалить все данные, команда truncate удалит данные и освободит место на диске для вас.
  2. Команда «Изменить таблицу» удалит и заново создаст таблицу, чтобы можно было восстановить дисковое пространство. Поэтому после удаления данных выполните команду alter table, которая ничего не меняет, чтобы освободить жесткий диск (т. Е. У таблицы TBL_A есть кодировка uf8, после удаления данных запустите ALTER TABLE TBL_A charset utf8 — & gt; эта команда ничего не меняет в вашей таблице, но она заставляет mysql воссоздать вашу таблицу восстановить дисковое пространство
  3. Создайте TBL_B как TBL_A. Вставьте выбранные данные, которые вы хотите сохранить из TBL_A в TBL_B. Удалите TBL_A и переименуйте TBL_B в TBL_A. Этот способ очень эффективен, если TBL_A и данные, которые нужно удалить, большие (команда удаления в MySQL innodb очень плохая производительность)

  ответ дан Khánh Bùi Đức, с репутацией 661, 25.04.2016

  

  

  

  

  

  

  

  

  

  

9 ответов

Лучший ответ

MySQL не уменьшает размер ibdata1. Всегда. Даже если вы используете , чтобы освободить место, используемое для удаленных записей, он будет повторно использовать его позже.

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

150

3limin4t0r
15 Мар 2021 в 16:19

У меня была такая же проблема.

Что происходит, так это то, что даже если вы отбрасываете базу данных, innodb все равно не освобождает дисковое пространство. Мне пришлось экспортировать, остановить mysql, удалить файлы вручную, запустить mysql, создать базу данных и пользователей, а затем импортировать. Слава богу, у меня было только 200 МБ строк, но это сэкономило 250 ГБ файла innodb.

Неудачный проект .

46

gilm
25 Дек 2009 в 20:55

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

Как сделать довольно подробно, но я вставил соответствующая часть ниже.

Не забудьте также сохранить копию своей схемы в дампе.

27

FlipMcF
3 Фев 2014 в 19:02

Десять лет спустя и у меня была такая же проблема. Я решил это следующим образом:

• Оптимизировал, все базы остались.
• Я перезапустил свой компьютер и MySQL в службах (Windows + r -> services.msc)

Это все

7

Matheus Douglas
13 Авг 2019 в 08:49

Разобрался с этой проблемой сегодня (через 11 лет после того, как вопрос был первоначально задан), и смог исправить ее, отбросив таблицу и создав ее снова . Мне не нужно было переустанавливать БД или дамп и восстановление, изменять хранилище, изменять таблицы и т. Д. — ничего из этого.

Я использую InnoDB, но не innodb_file_per_table, поэтому даже после того, как я удалил 900K строк из таблицы, размер БД не сдвинулся с места. Поэтому я отбросил таблицу и создал ее снова.

В моем случае моя таблица была очищена до нуля строк, поэтому мне было легко отбросить таблицу, но сохранить структуру, которую я запустил

С последующим

Это уменьшило размер моей БД с 5G до 400MB.

3

SuhasM
12 Мар 2021 в 13:07

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

Когда вы введете разделение для своей таблицы, потребуются некоторые изменения в схеме таблицы, например: — Уникальные ключи, индексы для включения столбца раздела и т. Д.

1

Anand Immannavar
10 Фев 2015 в 07:22

Год назад я также столкнулся с той же проблемой в версии mysql5.7, и ibdata1 занимал 150 ГБ. поэтому я добавил табличные пространства отмены

Сделайте резервную копию Mysqldump Остановить службу mysql Удалить все данные из каталога данных Добавьте ниже параметр отмены табличного пространства в текущий my.cnf

Запустить службу mysql сохранить резервную копию mysqldump

Проблема решена !!

Gajendra
21 Май 2020 в 14:51

Если ОПТИМИЗАЦИЯ не решает вашу проблему, попробуйте:

Или

Wellington1993
23 Июн 2021 в 15:36

Есть несколько способов освободить дисковое пространство после удаления данных из таблицы для движка MySQL Inodb.

Если вы не используете innodb_file_per_table с самого начала, сброс всех данных, удаление всего файла, воссоздание базы данных и повторный импорт данных — единственный способ (проверьте ответы FlipMcF выше)

Если вы используете innodb_file_per_table, вы можете попробовать

1. Если вы можете удалить все данные, команда truncate удалит данные и освободит дисковое пространство за вас.
2. Команда Alter table удалит и воссоздает таблицу, чтобы освободить дисковое пространство. Поэтому после удаления данных запустите команду alter table, которая ничего не меняет, чтобы освободить жесткий диск (например: таблица TBL_A имеет кодировку uf8, после удаления данных запустите ALTER TABLE TBL_A charset utf8 -> эта команда ничего не меняет в вашей таблице, но она заставляет mysql воссоздать вашу таблицу и восстановить дисковое пространство
3. Создайте TBL_B как TBL_A. Вставьте выбранные данные, которые вы хотите сохранить, из TBL_A в TBL_B. Отбросьте TBL_A и переименуйте TBL_B в TBL_A. Этот способ очень эффективен, если TBL_A и данные, которые необходимо удалить, большие (команда удаления в MySQL innodb очень плохая производительность)

-2

Bùi Đức Khánh
25 Апр 2016 в 17:07

1 ответ

94

Помните, что самым загруженным файлом в инфраструктуре InnoDB является /var /lib /mysql /ibdata1

Этот файл обычно содержит много классов информации (когда — 0)

• Данные таблицы
• Табличные индексы

• MVCC (многоуровневое управление параллелизмом).

  • Откат сегментов
  • Отменить табличное пространство
• Метаданные таблицы
• См. Изобразительное представление

Многие люди создают несколько файлов ibdata, надеясь на лучшее управление дисками и производительность. Это не помогает.

К сожалению, OPTIMIZE TABLE против таблицы InnoDB, хранящейся в ibdata1, выполняет две вещи:

• Делает данные таблицы и индексы смежными внутри ibdata1
• Это увеличивает ibdata1, потому что смежные данные добавляются к ibdata1

Вы можете отделить данные таблицы и таблицы таблицы от ibdata1 и управлять ими независимо, используя .
Чтобы сжать ibdata1 раз и навсегда, вы должны сделать следующее

Шаг 01) MySQLDump все базы данных в текстовый файл SQL (назовите его SQLData.sql) ()

Шаг 02) Отбросьте все базы данных (кроме , и )

Шаг 03) Завершение работы mysql

Шаг 04) Добавьте следующие строки в /etc/my.cnf

Sidenote: независимо от вашего набора для innodb_buffer_pool_size, убедитесь, что innodb_log_file_size составляет 25% от innodb_buffer_pool_size.

Шаг 05) Удалите ibdata1, ib_logfile0 и ib_logfile1

В этот момент должна быть только схема mysql в /var /lib /mysql

Шаг 06) Перезапустить mysql

Это будет воссоздать ibdata1 в 10MB, ib_logfile0 и ib_logfile1 по 1G каждый

Шаг 07) Перезагрузите SQLData.sql в mysql

ibdata1 будет расти, но будет содержать только метаданные таблицы

Каждая таблица InnoDB будет существовать вне ibdata1

Предположим, что у вас есть таблица InnoDB с именем mydb.mytable. Если вы войдете в /var /lib /mysql /mydb, вы увидите два файла, представляющих таблицу

• mytable.frm (Заголовок двигателя хранения)
• mytable.ibd (Главная таблицы и индексы таблицы для mydb.mytable)

ibdata1 больше не будет содержать данные и индексы InnoDB.

С опцией innodb_file_per_table в файле /etc/my.cnf вы можете запустить и файл будет фактически сокращаться.

Я делал это много раз в своей карьере в качестве DBA MySQL

Фактически, в первый раз, когда я это сделал, я свернул 50GB ibdata1-файл в 500 МБ.

Попробуй. Если у вас есть дополнительные вопросы по этому поводу, напишите мне. Доверьтесь мне. Это будет работать в краткосрочной и долгосрочной перспективе. !!!

Если вы хотите узнать, сколько фактических данных хранится в MyISAM и InnoDB, запустите этот запрос: