MySQL8 использует огромное количество дискового пространства.

Содержание

Вопрос или проблема
Ответ или решение
Теория
Примеры
Применение

Вопрос или проблема

Я перенес 3 из моих 11 баз данных с MySQL 5.7.44 на Ubuntu 16.04 на MySQL 8.0.40 на Ubuntu 24.04.

Использованное пространство на моей старой машине:

root@localhost:/var/lib/mysql# du -ha --max-depth=1
19M     ./bot
4.0K    ./private_key.pem
1.1M    ./performance_schema
48K     ./game
79M     ./master
4.0K    ./public_key.pem
4.0K    ./client-key.pem
4.0K    ./auto.cnf
5.0G    ./beauty
0       ./debian-5.7.flag
201M    ./devnote
11M     ./mysql
4.0K    ./ca.pem
8.0K    ./test
12M     ./mike1
12M     ./ibtmp1
176K    ./phpmyadmin
4.0K    ./server-cert.pem
680K    ./sys
48M     ./ib_logfile0
4.0K    ./ca-key.pem
127M    ./mike
4.0K    ./mysql_upgrade_info
4.0K    ./ib_buffer_pool
48M     ./ib_logfile1
4.0K    ./server-key.pem
186M    ./slogpost
76M     ./ibdata1
4.0K    ./client-cert.pem
41M     ./shar
5.9G    .

Использованное пространство на моей новой машине (новые базы данных: beauty, shar, slogpost):

root@localhost:/var/lib/mysql# du -ha --max-depth=1
101M    ./#innodb_redo
101M    ./binlog.000161
101M    ./binlog.000069
101M    ./binlog.000008
101M    ./binlog.000095
101M    ./binlog.000082
101M    ./binlog.000047
4.0K    ./localhost.pid
4.0K    ./public_key.pem
101M    ./binlog.000075
36K     ./mysql
101M    ./binlog.000177
101M    ./binlog.000101
101M    ./binlog.000122
4.0K    ./server-key.pem
101M    ./binlog.000107
101M    ./binlog.000158
101M    ./binlog.000079
101M    ./binlog.000057
101M    ./binlog.000028
101M    ./binlog.000173
101M    ./binlog.000020
4.0K    ./binlog.index
101M    ./binlog.000112
101M    ./binlog.000121
101M    ./binlog.000139
101M    ./binlog.000142
101M    ./binlog.000152
101M    ./binlog.000060
101M    ./binlog.000136
101M    ./binlog.000010
101M    ./binlog.000045
101M    ./binlog.000077
101M    ./binlog.000125
101M    ./binlog.000168
101M    ./binlog.000169
101M    ./binlog.000119
101M    ./binlog.000007
101M    ./binlog.000176
101M    ./binlog.000127
101M    ./binlog.000066
4.0K    ./client-key.pem
101M    ./binlog.000123
101M    ./binlog.000099
101M    ./binlog.000143
101M    ./binlog.000118
4.0K    ./private_key.pem
101M    ./binlog.000070
101M    ./binlog.000145
101M    ./binlog.000030
4.0K    ./binlog.000002
19M     ./shar
4.0K    ./auto.cnf
101M    ./binlog.000116
101M    ./binlog.000160
101M    ./binlog.000156
101M    ./binlog.000148
101M    ./binlog.000097
101M    ./binlog.000043
101M    ./binlog.000140
101M    ./binlog.000042
101M    ./binlog.000063
101M    ./binlog.000015
101M    ./binlog.000050
0       ./debian-5.7.flag
101M    ./binlog.000003
101M    ./binlog.000180
8.2M    ./#ib_16384_1.dblwr
4.0K    ./binlog.000036
101M    ./binlog.000026
12M     ./ibtmp1
101M    ./binlog.000034
1.7M    ./performance_schema
16M     ./undo_002
101M    ./binlog.000023
101M    ./binlog.000071
101M    ./binlog.000033
32K     ./binlog.000038
101M    ./binlog.000011
76K     ./binlog.000039
101M    ./binlog.000110
101M    ./binlog.000017
101M    ./binlog.000012
101M    ./binlog.000032
101M    ./binlog.000165
101M    ./binlog.000086
101M    ./binlog.000157
183M    ./slogpost
2.3G    ./binlog.000133
101M    ./binlog.000056
101M    ./binlog.000019
101M    ./binlog.000022
2.3G    ./binlog.000183
101M    ./binlog.000073
101M    ./binlog.000138
101M    ./binlog.000009
101M    ./binlog.000067
101M    ./binlog.000181
101M    ./binlog.000013
101M    ./binlog.000162
101M    ./binlog.000088
101M    ./binlog.000120
101M    ./binlog.000054
101M    ./binlog.000064
8.6M    ./binlog.000035
101M    ./binlog.000085
101M    ./binlog.000144
101M    ./binlog.000091
101M    ./binlog.000061
101M    ./binlog.000141
4.0K    ./binlog.000135
101M    ./binlog.000109
101M    ./binlog.000103
101M    ./binlog.000132
101M    ./binlog.000094
101M    ./binlog.000151
101M    ./binlog.000076
101M    ./binlog.000149
101M    ./binlog.000104
101M    ./binlog.000163
101M    ./binlog.000027
101M    ./binlog.000114
116K    ./sys
101M    ./binlog.000182
8.0K    ./ib_buffer_pool
101M    ./binlog.000115
101M    ./binlog.000164
101M    ./binlog.000084
2.4G    ./beauty
101M    ./binlog.000016
101M    ./binlog.000153
101M    ./binlog.000172
101M    ./binlog.000041
101M    ./binlog.000174
101M    ./binlog.000100
8.0K    ./binlog.000185
101M    ./binlog.000113
101M    ./binlog.000046
101M    ./binlog.000178
101M    ./binlog.000124
101M    ./binlog.000074
101M    ./binlog.000105
101M    ./binlog.000179
28M     ./mysql.ibd
101M    ./binlog.000171
101M    ./binlog.000155
101M    ./binlog.000062
101M    ./binlog.000021
101M    ./binlog.000044
101M    ./binlog.000049
101M    ./binlog.000059
101M    ./binlog.000147
60K     ./binlog.000134
101M    ./binlog.000055
4.0K    ./binlog.000040
101M    ./binlog.000126
101M    ./binlog.000170
101M    ./binlog.000089
101M    ./binlog.000006
101M    ./binlog.000080
101M    ./binlog.000081
101M    ./binlog.000068
172K    ./binlog.000037
12M     ./ibdata1
101M    ./binlog.000146
101M    ./binlog.000031
101M    ./binlog.000129
101M    ./binlog.000150
4.0K    ./binlog.000001
804K    ./#innodb_temp
101M    ./binlog.000065
75M     ./binlog.000004
4.0K    ./ca-key.pem
101M    ./binlog.000025
101M    ./binlog.000108
4.0K    ./server-cert.pem
101M    ./binlog.000090
101M    ./binlog.000051
101M    ./binlog.000048
101M    ./binlog.000052
101M    ./binlog.000024
101M    ./binlog.000053
101M    ./binlog.000130
101M    ./binlog.000014
8.0K    ./binlog.000184
101M    ./binlog.000117
101M    ./binlog.000096
101M    ./binlog.000005
101M    ./binlog.000175
101M    ./binlog.000131
101M    ./binlog.000078
101M    ./binlog.000167
101M    ./binlog.000058
101M    ./binlog.000083
101M    ./binlog.000087
101M    ./binlog.000154
101M    ./binlog.000166
101M    ./binlog.000102
4.0K    ./client-cert.pem
101M    ./binlog.000128
4.0K    ./ca.pem
101M    ./binlog.000137
101M    ./binlog.000098
16M     ./undo_001
101M    ./binlog.000029
101M    ./binlog.000018
101M    ./binlog.000092
101M    ./binlog.000106
101M    ./binlog.000072
101M    ./binlog.000111
2.3G    ./binlog.000159
192K    ./#ib_16384_0.dblwr
101M    ./binlog.000093
27G     .

du -csh binlog* | tail -n 1
24G     total

(База данных beauty на старой машине занимает 5.0G, потому что в ней есть дополнительные данные)

11 баз данных на новой машине занимают 5.9G, но 3 базы данных на старой машине занимают 27G.

Почему так много?

Чтобы сделать резервную копию базы данных, я просто выполняю это:

mysqldump -u $MUSER -h $MHOST -p$MPASS $MDB --no-tablespaces | sed -e 's/DEFINER=[^*]*\*/\*/' > $DBDUMP

чтобы восстановить, я выполняю это:

tar -Oxzf $ARCHIVE_FILE $DBDUMP | mysql -u $MUSER -h $MHOST -p$MPASS $MDB

Ответ или решение

Проблема, которую вы описали, касательно огромного использования дискового пространства MySQL 8.0.40 по сравнению с MySQL 5.7.44, является актуальной и, к сожалению, достаточно распространенной среди администраторов баз данных при обновлении до новой версии MySQL. Однако, причины этого явления довольно хорошо изучены и могут быть объяснены рядом изменений и улучшений, воплощенных в MySQL 8, которые, в свою очередь, влияют на использование дискового пространства. Рассмотрим теоретические обоснования, примеры и возможные применения для решения этой проблемы.

Теория

Изменения в структуре метаданных и системных таблиц: MySQL 8 ввел множество изменений в способ хранения системных таблиц и метаданных. В этой версии они хранятся не в MyISAM, как было ранее, а в формате InnoDB. Это изменение приводит к увеличению требований к дисковому пространству, поскольку InnoDB требует дополнительных ресурсов для поддержки характеристик ACID и многоверсионного управления.
Увеличение использования журнальных файлов: Одной из ключевых особенностей MySQL 8 является улучшенная система журналирования (transaction log), которая увеличивает надежность и консистентность базы данных. Однако это также приводит к увеличению использования дискового пространства, так как теперь хранится больше информации для восстановления транзакций.
Функции для поддержки транзанкционных процессов и индексов: MySQL 8 обладает расширенной функциональностью, такой как улучшенные индексы, поддержка функциональных индексов и больше сложных типов данных. Это, безусловно, улучшает производительность, но также может приводить к увеличению объема данных в базах.

Примеры

На приведенных вами данных видна четкая разница в размерах между старой и новой установленной версией сервера. На старом сервере общая занимаемая база "beauty" занимала 5ГБ, в то время как на новом сервере тот же банк данных занимает всего 2.4ГБ, но при этом вес всей папки с базами увеличился со старых 5.9ГБ до более 27ГБ за счет как раз за счет бинлогов и других метаданных.

Применение

Для того чтобы минимизировать использование дискового пространства при использовании MySQL 8, рассмотрите следующие шаги:

Управление бинарными логами: В ваших данных видно, что бинарные логи занимают значительное количество пространства. Вы можете настроить срок хранения бинарных логов, используя параметр expire_logs_days в конфигурационном файле MySQL. Настройте его, чтобы автоматически удалять старые логи, которые больше не нужны для восстановления.
Оптимизация конфигурации MySQL: Проверьте параметры конфигурации, такие как innodb_log_file_size и innodb_buffer_pool_size, которые могут влиять на размер файлов журналов и использование памяти. Убедитесь, что они настроены в соответствии с вашими потребностями и не занимают чрезмерное пространство.
Использование утилит для управления пространством: Регулярное использование утилит, таких как mysqltuner, может помочь выявить настройки, влияющие на производительность и использование дискового пространства, и предоставить рекомендации по оптимизации.
Мониторинг и очистка неиспользуемых данных: Иногда причиной большого объема данных могут быть старые или неиспользуемые таблицы. Проведите аудит данных и удалите ненужные таблицы или строки.
Компактирование и оптимизация таблиц: Регулярное использование команд OPTIMIZE TABLE может помочь в освобождении неиспользуемого пространства, особенно если ваши таблицы подвергаются частым изменениям.

Заключение, силовое дисковое пространство MySQL 8 может быть проблемой, если она не управляется должным образом. Однако, приняв всесторонний подход к управлению ресурсами, включая тщательную настройку конфигурации, грамотное использование логов и регулярное обслуживание баз данных, можно существенно снизить использование дискового пространства без ущерба для функциональности и производительности.