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
slow_query are set off already.
log_output is set to
TABLE - I'm unclear if this affects the error log
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 DATABASE IF EXISTS my_db; CREATE DATABASE my_db; ALTER DATABASE my_db DEFAULT CHARACTER SET = 'utf8mb4' DEFAULT COLLATE = 'utf8mb4_unicode_ci';
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
Server version: 10.2.12-MariaDB-log MariaDB Server