Shrinking MySQL database

Calculating disk space usage per MySQL DB:

sudo du -h /var/lib/mysql/
932M    /var/lib/mysql/beauty
179M    /var/lib/mysql/slogpost
485M    /var/lib/mysql/omegauto
8.0K    /var/lib/mysql/test
676K    /var/lib/mysql/sys
22G     /var/lib/mysql/bot
79M     /var/lib/mysql/master
11M     /var/lib/mysql/mysql
176K    /var/lib/mysql/phpmyadmin
21M     /var/lib/mysql/shar
127M    /var/lib/mysql/mike
1.1M    /var/lib/mysql/performance_schema
79M     /var/lib/mysql/devnote
12M     /var/lib/mysql/mike1
48K     /var/lib/mysql/game
24G     /var/lib/mysql/

The space used by the tables (prices_* are ISAM and others are INNODB):

sudo ls -lh /var/lib/mysql/bot
-rw-r----- 1 mysql mysql   61 Apr  1  2019 db.opt
-rw-r----- 1 mysql mysql 8.4K Apr 14  2019 exchange.frm
-rw-r----- 1 mysql mysql  96K Apr 14  2019 exchange.ibd
-rw-r----- 1 mysql mysql 8.5K Apr 14  2019 market.frm
-rw-r----- 1 mysql mysql 9.0M Dec  4 14:47 market.ibd
-rw-r----- 1 mysql mysql 8.5K Nov 12 20:32 price.frm
-rw-r----- 1 mysql mysql 1.7G Nov 12 20:37 price.ibd
-rw-r----- 1 mysql mysql 8.5K Jan 17 19:12 prices_binance_BNB_USDT.frm
-rw-r----- 1 mysql mysql 504M Apr  1 00:02 prices_binance_BNB_USDT.MYD
-rw-r----- 1 mysql mysql 862M Apr  1 00:02 prices_binance_BNB_USDT.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_BTC_USDT.frm
-rw-r----- 1 mysql mysql 4.2G Apr  1 00:02 prices_binance_BTC_USDT.MYD
-rw-r----- 1 mysql mysql 7.0G Apr  1 00:02 prices_binance_BTC_USDT.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_EOS_BTC.frm
-rw-r----- 1 mysql mysql 102M Apr  1 00:02 prices_binance_EOS_BTC.MYD
-rw-r----- 1 mysql mysql 176M Apr  1 00:02 prices_binance_EOS_BTC.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_ETH_BTC.frm
-rw-r----- 1 mysql mysql 473M Apr  1 00:02 prices_binance_ETH_BTC.MYD
-rw-r----- 1 mysql mysql 809M Apr  1 00:02 prices_binance_ETH_BTC.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_ETH_USDT.frm
-rw-r----- 1 mysql mysql 955M Apr  1 00:02 prices_binance_ETH_USDT.MYD
-rw-r----- 1 mysql mysql 1.6G Apr  1 00:02 prices_binance_ETH_USDT.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_RVN_BTC.frm
-rw-r----- 1 mysql mysql 5.2M Apr  1 00:02 prices_binance_RVN_BTC.MYD
-rw-r----- 1 mysql mysql 8.9M Apr  1 00:02 prices_binance_RVN_BTC.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_RVN_USDT.frm
-rw-r----- 1 mysql mysql  66M Apr  1 00:01 prices_binance_RVN_USDT.MYD
-rw-r----- 1 mysql mysql 113M Apr  1 00:01 prices_binance_RVN_USDT.MYI
-rw-r----- 1 mysql mysql 8.5K Jan 17 18:59 prices_binance_XMR_USDT.frm
-rw-r----- 1 mysql mysql  98M Apr  1 00:01 prices_binance_XMR_USDT.MYD
-rw-r----- 1 mysql mysql 168M Apr  1 00:01 prices_binance_XMR_USDT.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_XRP_BTC.frm
-rw-r----- 1 mysql mysql  98M Apr  1 00:02 prices_binance_XRP_BTC.MYD
-rw-r----- 1 mysql mysql 169M Apr  1 00:02 prices_binance_XRP_BTC.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_XRP_ETH.frm
-rw-r----- 1 mysql mysql 200M Apr  1 00:02 prices_binance_XRP_ETH.MYD
-rw-r----- 1 mysql mysql 346M Apr  1 00:02 prices_binance_XRP_ETH.MYI
-rw-r----- 1 mysql mysql 8.5K Dec 11 23:01 prices_binance_XRP_USDT.frm
-rw-r----- 1 mysql mysql 839M Apr  1 00:02 prices_binance_XRP_USDT.MYD
-rw-r----- 1 mysql mysql 1.4G Apr  1 00:02 prices_binance_XRP_USDT.MYI
-rw-r----- 1 mysql mysql 8.5K Nov 16 23:26 t1.frm
-rw-r----- 1 mysql mysql  96K Nov 16 23:26 t1.ibd
-rw-r----- 1 mysql mysql 8.9K Nov 17 13:06 tb_order.frm
-rw-r----- 1 mysql mysql 880K Mar 31 00:08 tb_order.ibd
-rw-r----- 1 mysql mysql 8.5K Apr 14  2019 user_exchange.frm
-rw-r----- 1 mysql mysql 112K Jul 10  2019 user_exchange.ibd
-rw-r----- 1 mysql mysql 8.5K Apr 14  2019 user.frm
-rw-r----- 1 mysql mysql 112K Apr 14  2019 user.ibd
-rw-r----- 1 mysql mysql 8.5K Apr 14  2019 user_market.frm
-rw-r----- 1 mysql mysql 128K Jan 17 19:10 user_market.ibd

Currently I have 1.5G of free space:

df -h
/dev/root        48G   46G  1.5G  97% /
devtmpfs        983M     0  983M   0% /dev
tmpfs           986M  2.1M  984M   1% /dev/shm
tmpfs           986M  8.7M  977M   1% /run
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           986M     0  986M   0% /sys/fs/cgroup
tmpfs           198M     0  198M   0% /run/user/1000

After deleting ‘price’ with

DROP TABLE price;

I got 3.1G of the free space.

Then I trimmed and optimized prices_* tables with a JavaScript code like this:

await this.db.execute(`DELETE FROM ${tableName} WHERE dt < NOW() - INTERVAL 30 DAY;`);
await this.db.execute(`OPTIMIZE TABLE ${tableName};`);

Now I have 21G of the free space.

Links

Leave a Reply

Your email address will not be published.