From ba75c3857a6922a91dcd2bede7ab4e4603440ec9 Mon Sep 17 00:00:00 2001
From: Pjotr Prins
Date: Sun, 2 Jan 2022 17:48:41 +0100
Subject: innodb

---
 topics/systems/mariadb/ProbeSetData.gmi | 35 ++++++++++++++++++++++-----------
 topics/systems/mariadb/cleanup.gmi      | 27 +++++++++++++++++++++++++
 2 files changed, 51 insertions(+), 11 deletions(-)
 create mode 100644 topics/systems/mariadb/cleanup.gmi

(limited to 'topics/systems/mariadb')

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
-- 
cgit v1.2.3