diff options
| -rw-r--r-- | issues/systems/mariadb/ProbeSetData.gmi | 30 | ||||
| -rw-r--r-- | topics/setting-up-local-development-database.gmi | 70 |
2 files changed, 93 insertions, 7 deletions
diff --git a/issues/systems/mariadb/ProbeSetData.gmi b/issues/systems/mariadb/ProbeSetData.gmi index 672b64c..91179c3 100644 --- a/issues/systems/mariadb/ProbeSetData.gmi +++ b/issues/systems/mariadb/ProbeSetData.gmi @@ -12,13 +12,16 @@ This is by far the largest table (~200Gb). I need to add disk space to be able to host it on the NVME and move stuff around. Final move is GN2 code and we have over 400Gb free. -This time I failed porting to InnoDB. Next time: +This time I failed porting to InnoDB (see Migration below): -* [ ] Move database to large drive -* [ ] Run second instance of mariadb, upgrade too -* [ ] Export a small version of ISAM to ISAM +* [ ] Move database to larger drive +* [ ] Run second instance of mariadb using a Guix container, upgrade too? +* [ ] Stop binary log +* [ ] Drop the indices * [ ] Try different sizes of innodb exports * [ ] Make (ordered) conversion and test performance +* [ ] Rebuild indices +* [ ] Restart binary log * [ ] Muck out ibdata1 and transaction logs I disabled these and they need to be restored: @@ -62,7 +65,6 @@ Every table update has to follow the template: => ../reboot-tux01-tux02.gmi - ## Table upgrade ### Check recent backups @@ -266,6 +268,8 @@ ALTER TABLE ProbeSetData ENGINE = InnoDB; ALTER TABLE ProbeSetSE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ``` +Use utf8 instead! + ### Update fulltext For those cases see bottom of move-to-innodb.gmi. @@ -284,6 +288,22 @@ RENAME TABLE orig TO orig_old, mytest TO orig; ### Check test +## Migration (20230312) + +ProbeSetData is the final table that needs to be migrated to innodb. The main problem is that it is huge and transforming the table runs out of disk space. In the first step I made a borg snapshot on + +``` +root@tux01:/export2/backup# borg create --progress --stats borg-mariadb-snapshot::migrate1a /export/local/home/mariadb/ +``` + +next copy the database to a new partition: + +``` +root@tux01:/export4/local/home/mariadb/database/db_webqtl# rsync -vaP /var/lib/mysql/db_webqtl/* . --delete --bwlimit=20M +``` + +Note I throttle the speed because the system can become quite unusable at full copy speed. + ## Notes I found it is a bad idea to remove large .ibd files by hand because mariadb wants to recreate them to play the transaction log. diff --git a/topics/setting-up-local-development-database.gmi b/topics/setting-up-local-development-database.gmi index 910c8de..26cdadd 100644 --- a/topics/setting-up-local-development-database.gmi +++ b/topics/setting-up-local-development-database.gmi @@ -2,7 +2,7 @@ You need to set up a quick local database for development without polluting your environment. -## Method 1 (Using Guix) +## Method 1 (Using Guix system containers) Setting up mariadb in a Guix container is the preferred and easier method. But, you need root access to run the container. The genenetwork2 repo comes with a guix system container definition to run MariaDB and Redis. From the genenetwork2 repo, you can build and run the container using: ``` @@ -25,7 +25,73 @@ Since this is a develpoment server accessible only from localhost, it is ok to u SQL_URI="mysql://root@127.0.0.1:3306/db_webqtl_s" ``` -## Method 2 (Manual method without Guix) +## Method 2 (running a local container) + +A local container runs as a normal user. It requires some juggling of dirs to load an existing database. Make sure to not run on the same dirs as another mariadb instance(!) + +``` +cd ~/tmp/mariadb +mkdir var +~/tmp/mariadb$ ~/opt/guix-pull/bin/guix shell -C -N coreutils binutils sed mariadb --expose=var +``` + +inside the container + +``` +mkdir -p /var/lib/data +mkdir -p /var/lib/mysql +mkdir /var/run +``` + +Initialize with + +``` +mysql_install_db +``` + +and run + +``` +mysqld_safe --datadir='./data' --user=$USER --nowatch --socket=/var/run/mysqld/mysqld.sock +``` + +Now from outside the container you should be able to connect with + +``` +stromboli:~/tmp/mariadb$ mysql --socket=var/run/mysqld/mysqld.sock +``` + +and + +``` +Welcome to the MariaDB monitor. Commands end with ; or \g. +Your MariaDB connection id is 3 +Server version: 10.5.12-MariaDB MariaDB Server + +Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. + +Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. + +MariaDB [(none)]> show databases; ++--------------------+ +| Database | ++--------------------+ +| information_schema | +| mysql | +| performance_schema | +| test | ++--------------------+ +4 rows in set (0.001 sec) +``` + +If you need to tweak the server configuration you can load the my.cnf file with the `--defaults-file=var/my.cnf` inside and outside the container. + + + + +## Method 3 (Manual method without Guix) + +/This is not recommended/ * An assumption is made that the GeneNetwork2 profile is in ~/opt/gn_profiles/gn2_latest for the purposes of this documentation. Please replace as appropriate. * We install the database files under ~/genenetwork/mariadb. Change as appropriate. |