summaryrefslogtreecommitdiff
path: root/topics/systems/mariadb
diff options
context:
space:
mode:
authorPjotr Prins2022-01-02 17:48:41 +0100
committerPjotr Prins2022-01-02 17:48:41 +0100
commitba75c3857a6922a91dcd2bede7ab4e4603440ec9 (patch)
treeecc49632796831301d64b80309c87cb0bfdce48f /topics/systems/mariadb
parente52fb211d5f8533495a0b3c57caff0b401c95695 (diff)
downloadgn-gemtext-ba75c3857a6922a91dcd2bede7ab4e4603440ec9.tar.gz
innodb
Diffstat (limited to 'topics/systems/mariadb')
-rw-r--r--topics/systems/mariadb/ProbeSetData.gmi35
-rw-r--r--topics/systems/mariadb/cleanup.gmi27
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