diff options
| -rw-r--r-- | topics/systems/mariadb/ProbeSetData.gmi | 35 | ||||
| -rw-r--r-- | topics/systems/mariadb/cleanup.gmi | 27 | ||||
| -rw-r--r-- | topics/systems/reboot-tux01-tux02.gmi | 6 |
3 files changed, 57 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 diff --git a/topics/systems/reboot-tux01-tux02.gmi b/topics/systems/reboot-tux01-tux02.gmi index e3dbc91..afed594 100644 --- a/topics/systems/reboot-tux01-tux02.gmi +++ b/topics/systems/reboot-tux01-tux02.gmi @@ -2,6 +2,8 @@ I needed to add the hard disks in the BIOS to make them visible - one of the annoying aspects of these Dell machines. First on Tux02 I cheched the borg backups to see if we have a recent copy of MariaDB, GN2 etc. The DB is from 2 days ago and the genotypes of GN2 are a week old (because of a permission problem). I'll add a copy by hand of both - an opportunity to test the new 10Gbs router. +Something funny is going on. When eno4 goes down the external webserver interface is not working. It appears, somehow, that 128.169.4.67 is covering for 128.169.5.59. I need to check that! + # Tasks Before rebooting @@ -11,6 +13,10 @@ On Tux02: * [X] Check backups of DB and services * [X] Copy trees between machines +On Tux01: + +* [ ] Network confused. See above. + On both: * [X] Check network interface definitions (what happens on reboot) |