diff options
Diffstat (limited to 'topics/systems/mariadb')
| -rw-r--r-- | topics/systems/mariadb/ProbeSetData.gmi | 35 | ||||
| -rw-r--r-- | topics/systems/mariadb/cleanup.gmi | 27 |
2 files changed, 51 insertions, 11 deletions
diff --git a/topics/systems/mariadb/ProbeSetData.gmi b/topics/systems/mariadb/ProbeSetData.gmi index 047cc3b..189a2eb 100644 --- a/topics/systems/mariadb/ProbeSetData.gmi +++ b/topics/systems/mariadb/ProbeSetData.gmi @@ -4,14 +4,15 @@ This is by far the largest table (~100Gb). I need to add disk space to be able t I disabled these and they need to be restored: -* [ ] binary log -* [ ] backup script -* [ ] flush db -* [ ] cron system-check -* [ ] systemd MYSQLD_OPTS setting +* [X] binary log +* [X] backup script +* [ ] flush db - leave that off for now +* [X] cron system-check +* [X] systemd MYSQLD_OPTS setting The following fetches the data to be used for mapping: +``` SELECT Strain.Name, ProbeSetData.value, ProbeSetSE.error, NStrain.count, Strain.Name2 FROM @@ -30,7 +31,7 @@ SELECT ProbeSetData.StrainId = Strain.Id Order BY Strain.Name; - +``` Prototocol from @@ -77,23 +78,35 @@ Original: New: ``` -DROP table mytest; -CREATE table mytest( +DROP table mytest2; +CREATE table mytest2( Id INT unsigned NOT NULL DEFAULT 0, - StrainId MediumINT unsigned NOT NULL DEFAULT 0, + StrainId MEDIUMINT unsigned NOT NULL DEFAULT 0, value float NOT NULL, primary key(Id,StrainId), - key (StrainId)) CHARACTER SET utf8mb4; + key (StrainId)) CHARACTER SET utf8mb4 ; ``` +Note loading latin1 is slower than utf8! I tried. + And fill it killing quite a few birds with one stone :). You may want to disable the binary logger before running: ``` -INSERT INTO mytest1 (Id,StrainId,value) SELECT Id,StrainId,value FROM ProbeSetData LIMIT 51631478; +INSERT INTO mytest2 (Id,StrainId,value) SELECT Id,StrainId,value FROM ProbeSetData LIMIT 51631478; ``` + Makes a table at 1% of size - a table of 2.3GB. I wonder why the full thing took up 330Gb before we ran out of disk space. +Show progress + +``` +SELECT TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH,AVG_ROW_LENGTH,TABLE_ROWS FROM information_schema.TABLE +S WHERE DATA_LENGTH>1000000000 order by data_length; +``` + +May add order by Id,StrainID next time. + Once the process is done, you can rename the tables: ``` diff --git a/topics/systems/mariadb/cleanup.gmi b/topics/systems/mariadb/cleanup.gmi new file mode 100644 index 0000000..73085da --- /dev/null +++ b/topics/systems/mariadb/cleanup.gmi @@ -0,0 +1,27 @@ + + +Find all larger tables + +SELECT TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH FROM information_schema.TABLES WHERE DATA_LENGTH>10000; + +The following 4 mysql tables live in + +MariaDB [mysql]> SELECT TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH FROM information_schema.TABLES WHERE ENGINE = 'InnoDB'; ++--------------+----------------------+-------------+ +| TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | ++--------------+----------------------+-------------+ +| db_webqtl | TraitMetadata | 16384 | +| db_webqtl | ProbeSetSE | 24177016832 | +| db_webqtl | metadata_audit | 49152 | +| db_webqtl | mytest1 | 1567621120 | +| db_webqtl | ProbeSetXRef | 2836398080 | +| db_webqtl | GeneInfo | 23642112 | +| db_webqtl | mytest2 | 56524537856 | +| mysql | transaction_registry | 16384 | +| mysql | innodb_index_stats | 16384 | +| mysql | innodb_table_stats | 16384 | +| mysql | gtid_slave_pos | 16384 | ++--------------+----------------------+-------------+ +11 rows in set (0.008 sec) + +for x in innodb_index_stats innodb_table_stats gtid_slave_pos transaction_registry ; do echo $x ; mysqldump -u webqtlout -pwebqtlout mysql $x > /export3/$x.sql ; done |