Mysql – AWS MariaDB reports via CloudWatch that disk space free storage is 0 but 40% unaccounted for

I have 3 AWS MariaDB instances – one for production, one for user testing and one development – and I set up a script to clone the production data across to the testing and dev DBs using mysqldump and a migration script to change the schema as required.

I start off the operation using DROP DATABASE and CREATE DATABASE, set the character set and collation, and load up the dump. These are all InnoDB tables and there's no database replication involved.

Now I've done it a few times, I'm running out of space on my dev and test DBs and the AWS CloudWatch metrics show missing space.

I am in an awkward situation because the corporation that I'm doing this for does not allow me AWS or host access, so everything I do is via the client command prompt. The corporation's DBAs are unfortunately quite ignorant of MariaDb and MySQL, being mostly SQL Server trained, and are new to AWS as well.

I can relay requests to them via chat and they then come back with the answers.

This is how it looks now (still waiting for dev db info):

DB    Provisioned    Used        Free        Unknown!
dev   100GB          -           -           -
test  100GB          42.1GB      15.9GB      42.0GB
prod  100GB          60.3GB      29.3GB      10.4GB

general_log and slow_query are set off already.
log_output is set to TABLE – I'm unclear if this affects the error log
log_error is /rdsdbdata/log/error/mysql-error.log
innodb_file_per_table is ON

I can't tell from the AWS docs at https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/mon-scripts.html what their scripts use to generate the stats:

… collects and sends the DiskSpaceUsed metric for the selected disks. The metric is reported by default in gigabytes.

Due to reserved disk space in Linux operating systems, disk space used and disk space available might not accurately add up to the amount of total disk space.

So AWS warns that they might not add up, but I would expect any 'unreported space' in linux to remain constant and it seems pretty obvious that it's my cloning activities that are causing MariaDB to lose its free disk space.

I have found this very informative: I have delete database from mysql but storage is not freed but neither I nor my DBAs have permissions to run PURGE BINARY LOGS which looks like the first thing we should try.

I dropped my database and the DBA helping me just did a MariaDB restart (or perhaps an AWS host restart, not sure) and I have all the free space back again.

So how do I solve this or work around it?

It would also be good for me to be able to check the space in use directly from the SQL prompt.

I can do this:

SELECT table_schema AS "Database",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size(MB)"
FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------+
| Database           | Size(MB) |
+--------------------+----------+
| information_schema |     0.17 |
| mysql              |     5.76 |
| performance_schema |     0.00 |
| my_db              | 32643.52 |
+--------------------+----------+
4 rows in set (0.02 sec)

but it doesn't show all the extra used space I'm losing.

This screenshot provided by CloudWatch shows free disk storage. There are 4 times where it shoots up. The first 3 are DROP DATABASE events, the last is where the DBA decided to restart the host. The declines back down to zero are where the load from a dump are running.

Here is the exact script of what I'm doing:

mysql -h $db -u $DB_USERNAME -p$pw  -D my_db \
                   --max-allowed-packet=536M \
                   --default-character-set=utf8mb4 < ./drop-recreate-db.sql

/usr/bin/mysqldump -h $DBPROD \
                   -u $DB_USERNAME \
                   -p$PWPROD \
                   --default-character-set utf8mb4 \
                   --single-transaction \
                   -ev my_db \
| mysql -h $db -u $DB_USERNAME -p$pw -D my_db \
                   --max-allowed-packet=536M \
                   --default-character-set=utf8mb4

mysql -h $db -u $DB_USERNAME -p$pw  -D my_db \
                   --max-allowed-packet=536M \
                   --default-character-set=utf8mb4 < ./group-migration.sql

and the two SQL scripts are:

drop-recreate-db.sql

DROP DATABASE IF EXISTS my_db;

CREATE DATABASE my_db;

ALTER DATABASE my_db
   DEFAULT CHARACTER SET = 'utf8mb4'
   DEFAULT COLLATE = 'utf8mb4_unicode_ci';

group-migration.sql

CREATE TABLE ad_group (
id INT UNSIGNED NOT NULL AUTO_INCREMENT, --
name CHAR(100) NOT NULL UNIQUE,
created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT pk_ad_group PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  MAX_ROWS 1000;

ALTER TABLE widget DROP COLUMN globalRead;
ALTER TABLE widget DROP COLUMN globalWrite;
ALTER TABLE widget DROP FOREIGN KEY fk_widget_unit;
ALTER TABLE widget DROP COLUMN unitId;
ALTER TABLE widget ADD COLUMN read_group_id INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE widget ADD COLUMN write_group_id INT UNSIGNED NOT NULL DEFAULT 2;
ALTER TABLE widget ADD COLUMN owner_group_id INT UNSIGNED NOT NULL DEFAULT 3;
ALTER TABLE widget ADD CONSTRAINT fk_widget_read_group
    FOREIGN KEY (read_group_id)
    REFERENCES ad_group(id);
ALTER TABLE widget ADD CONSTRAINT fk_widget_write_group
    FOREIGN KEY (write_group_id)
    REFERENCES ad_group(id);
ALTER TABLE widget ADD CONSTRAINT fk_widget_owner_group
    FOREIGN KEY (owner_group_id)
    REFERENCES ad_group(id);

-- rename old modification table
ALTER TABLE modification RENAME old_mod;

-- create new one
CREATE TABLE modification (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username CHAR(30) NOT NULL,
  display_name VARCHAR(255) NULL,
  epoch_time BIGINT NOT NULL,
  widget_id INT UNSIGNED NOT NULL,
  widget_symbol VARCHAR(255) NOT NULL,
  group_id INT UNSIGNED NOT NULL,
  group_name CHAR(100) NOT NULL,
  description CHAR(12) NOT NULL,
  aux_text TEXT NULL,
  aux_date DATE NULL,
  CONSTRAINT pk_modification PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  MAX_ROWS 500123123;


-- add username to modification via userid
INSERT INTO modification (username, epoch_time, widget_id, widget_symbol,
                          group_id, group_name,
                          description, aux_text, aux_date)
SELECT u.name, m.epochTime, m.widgetId, f.name, 1, 'Not recorded',
       m.description, m.auxText, m.auxDate
FROM old_mod m
     LEFT JOIN `user` u ON u.id = m.userId
     LEFT JOIN widget f ON f.id = m.widgetId;

-- drop table so we can re-use the same constraint names
DROP TABLE IF EXISTS old_mod;

-- create constraints and indices
CREATE INDEX ix_modification_widget_symbol
ON modification (widget_symbol);

CREATE INDEX ix_modification_query_by_user_and_widget
ON modification (username, widget_id, description, aux_date, epoch_time);

CREATE INDEX ix_modification_last_save_query
ON modification (widget_id, description, aux_date);



ALTER TABLE data CHANGE COLUMN widgetId widget_id INT UNSIGNED NOT NULL;
ALTER TABLE data CHANGE COLUMN modifiedDate modified_date DATE NOT NULL;
ALTER TABLE data CHANGE COLUMN valueDate value_date DATE NOT NULL;

DROP TABLE IF EXISTS usergroup;
DROP TABLE IF EXISTS groupwidget;
DROP TABLE IF EXISTS userwidget;
DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS `group`;
DROP TABLE IF EXISTS unitconversion;
DROP TABLE IF EXISTS unit;

Originally I made the changes to table modification in place, but I kept getting other errors – see here if interested: MariaDB "ERROR 1034 (HY000) at line 41: Index for table 'xyz' is corrupt" on importing dump

This is: Server version: 10.2.12-MariaDB-log MariaDB Server