Description:
# 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¬ify=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):
```sh
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
```
```sh
# 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
```
