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.