Bug #102758 Disk usage increasing exponentially
Submitted: 1 Mar 2021 1:37 Modified: 2 Mar 2021 15:16
Reporter: Jinho Jung Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:v8.0.23 OS:Ubuntu (18.04)
Assigned to: CPU Architecture:x86 (64bit )
Tags: disk uage

[1 Mar 2021 1:37] Jinho Jung
# Disk usage increasing exponentially

Hello. We are studying DBMS from GeorgiaTech and reporting the disk usage problem that we found during the previous bug report (https://bugs.mysql.com/bug.php?id=102707&thanks=2&notify=67).

During our testing, we only executed SELECT queries and we noticed that MySQL increases the disk usage exponentially. For example, when we run the reported queries (about 23K queries), we observed MySQL consumed about 300GB disk. If we re-run those query sets again, it consumed another 600GB disk, and then the daemon is terminated with "No Space Error".

In another test, we stopped the executions when MySQL used 90% of the disk. Since the disk usage could be temporary, we let the server idle for 6 hours but the disk usage was not changed after that.

We are wondering if this is an intended behavior of MySQL. If so, it would be great if we can run how to avoid the disk usage problem.

* Testing queries (random SELECT-intensive queries):

wget https://gts3.org/~jjung/report1/mysql.tar.gz

# Evaluation environment

* H/W
  * 24 cores of Intel(R) Xeon(R) CPU E5-2670 v3 @ 2.30GHz
  * 256GB RAM
  * 1TB HDD
* Server OS: Ubuntu 18.04 (64bit)
* MySQL: v8.0.23

How to repeat:
# Reproduce: install DBMSs, import TPCC benchmark, run query

### Mysql (install V8.0.)

# Our mysqld.conf setting
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 4
innodb_buffer_pool_size         = 4G
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 1G
innodb_stats_on_metadata        = 0

key_buffer_size                 = 32M
low_priority_updates            = 1
concurrent_insert               = 2

innodb_sort_buffer_size         = 2M
join_buffer_size                = 4M
read_buffer_size                = 3M
read_rnd_buffer_size            = 4M
sort_buffer_size                = 4M

table_definition_cache          = 40000
table_open_cache                = 40000
open_files_limit                = 60000

# install
wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb
 # then select mysql 8.0 server
sudo apt update
sudo apt install mysql-client mysql-community-server mysql-server

# check
mysql -u root -p

# create user mysql
 CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql';
 alter user 'root'@'localhost' identified by 'mysql';

# modify the conf
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# optimize
# e.g., https://gist.github.com/fevangelou/fb72f36bbe333e059b66

# import DB
wget https://gts3.org/~jjung/tpcc-perf/tpcc_my.tar.gz
tar xzvf tpcc_my.tar.gz
mysql -u mysql -pmysql -e "create database mysqldb"
mysql -u mysql -pmysql mysqldb < tpcc_my.sql

# download test queries
wget https://gts3.org/~jjung/report1/mysql.tar.gz

# run the reported queries 2-3 tiems
#  - using script
[2 Mar 2021 14:46] MySQL Verification Team
Hi Mr. Jung,

Thank you for your bug report.

Although your information is interesting, we are cognisant of this behaviour. We might also add that your settings are not poised to decrease disk usage statistics.

Ever increasing usage of the permanent storage is also described in our Reference Manual, chapter on InnoDB storage engine.

Not a bug.
[2 Mar 2021 15:16] Jinho Jung
Thanks for the response. We also noticed that innoDB increases the disk and it needs disk management option. Since the disk saturation speed is much faster than the normal user's point (e.g., 1TB in 4-5 hours), we filed the report. 

But with the confirmation of "Not a bug", we will not report further on this case.