diff options
| author | Pjotr Prins | 2021-12-28 15:33:07 +0100 |
|---|---|---|
| committer | Pjotr Prins | 2021-12-28 15:33:07 +0100 |
| commit | 8527cfb6db5acb2392d36eb0e866a7cbca6ea3b5 (patch) | |
| tree | bfbe9cc249f22b18ddc499a67fcaac3389439149 | |
| parent | 5bf33d5f8e808949e9bfa06fce2cb280a3f5a418 (diff) | |
| download | gn-gemtext-8527cfb6db5acb2392d36eb0e866a7cbca6ea3b5.tar.gz | |
mariadb to innodb notes
| -rw-r--r-- | topics/systems/mariadb/move-to-innodb.gmi | 29 |
1 files changed, 28 insertions, 1 deletions
diff --git a/topics/systems/mariadb/move-to-innodb.gmi b/topics/systems/mariadb/move-to-innodb.gmi index 3462cf9..498228b 100644 --- a/topics/systems/mariadb/move-to-innodb.gmi +++ b/topics/systems/mariadb/move-to-innodb.gmi @@ -137,6 +137,9 @@ so, instead, I exported the CSV from => http://genenetwork.org/show_trait?trait_id=1427571_at&dataset=HC_M2_0606_P +It took the server 2.02924s seconds to process this page. +It took your browser 1.577 second(s) to render this page. + This is our test 'setup'. ## Backup and convert table @@ -218,22 +221,46 @@ Unfortunately we can't swith to 4k page tables because we have existing tables. ``` MariaDB [db_webqtl]> ALTER TABLE ProbeSetSE ENGINE = InnoDB; +Query OK, 688744613 rows affected (1 hour 51 min 36.273 sec) +Records: 688744613 Duplicates: 0 Warnings: 0 ``` +2 hours for a lousy table conversion! + The new file sizes are: +``` +-rw-rw---- 1 mysql mysql 1.5K Dec 28 11:33 ProbeSetSE.frm +-rw-rw---- 1 mysql mysql 51G Dec 28 13:25 ProbeSetSE.ibd +``` + +So the has tripled (including a new index) and during conversion it has both the old and the new on disk. I'll need to make space for this baby. ## Run tests again +What about performance? + ``` MariaDB [db_webqtl]> select count(*) from ProbeSetSE where strainid<20 and error<0.10; ++----------+ +| count(*) | ++----------+ +| 61625074 | ++----------+ +1 row in set (3 min 22.958 sec) ``` +Whoah. 3 times slower - which makes sense if you know the physical size of the data. + Again I exported the CSV from => http://genenetwork.org/show_trait?trait_id=1427571_at&dataset=HC_M2_0606_P -and it shows the ... +It took the server 1.46351s seconds to process this page. +It took your browser 2.119 second(s) to render this page + +and it shows practically the same results. + ## Notes captured from Trello: |