1

I have a database for an app, that is currently using MariaDB with about 220 InnoDB tables. The entire database is about 30GB of data. However when attempting to move it to a new server that has the most recent MariaDB version I find that the database is consuming all available disk space (which is currently 800GB). I know that InnoDB can be expected to use 2-3x the database by size but that is like 26x the size of the database. What could be causing this issue? Below is what my config file looks like. This is the only thing I am running on the database server at this point so it has to be this database causing the issue. Any thoughts on what is going on?

[mysqld]
datadir=/mysqldata
tmpdir=/mysqldata/tmp
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
[mysqld]
bind-address    = x.x.x.x
skip_name_resolve

back_log = 1024
max_connections = 5000
key_buffer_size = 2048M
myisam_sort_buffer_size = 512M
myisam_max_sort_file_size = 4096M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_definition_cache = 8000
table_open_cache = 8000
thread_cache_size = 1024
wait_timeout = 60
interactive_timeout = 60
connect_timeout = 10
tmp_table_size = 768M
max_heap_table_size = 768M
max_allowed_packet=268435456
#max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 768K
bulk_insert_buffer_size = 8M
query_cache_limit = 1536K
query_cache_size = 50M
query_cache_type = 0
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM

innodb_large_prefix=1
innodb_purge_threads = 4
innodb_file_format = Barracuda
innodb_file_per_table = ON
innodb_open_files = 2000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 15

innodb_log_files_in_group = 2
innodb_log_file_size = 768M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

1 Answer 1

0
max_connections = 5000  --> 500
thread_cache_size = 1024  --> 100
key_buffer_size = 2048M  --> 40M (once your tables are converted to InnoDB)
table_definition_cache = 8000  --> 500
table_open_cache = 8000  --> 500
default-storage-engine = MyISAM  --> InnoDB
innodb_buffer_pool_size = 20G  -- This assumes you have 32GB of RAM?

None of the above recommendations explain the huge increase in disk space used.

Please elaborate -- which table is now the "biggest"? (SHOW TABLE STATUS) What is the biggest file on disk in the MariaDB directory (possibly named "mysql")? Are there any FULLTEXT indexes? For some big table please provide SHOW CREATE TABLE; maybe I can spot something else. Are any tables PARTITIONed?

Did you use mysqldump? What was the size of the dump file? Did you upgrade in place? What old version; what new version?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.