diff options
Diffstat (limited to 'topics/systems/mariadb')
| -rw-r--r-- | topics/systems/mariadb/ProbeSetXRef.gmi | 99 | ||||
| -rw-r--r-- | topics/systems/mariadb/move-to-innodb.gmi | 84 |
2 files changed, 179 insertions, 4 deletions
diff --git a/topics/systems/mariadb/ProbeSetXRef.gmi b/topics/systems/mariadb/ProbeSetXRef.gmi new file mode 100644 index 0000000..d9adad1 --- /dev/null +++ b/topics/systems/mariadb/ProbeSetXRef.gmi @@ -0,0 +1,99 @@ +## Table ProbeSetXRef + +Prototocol from + +=> ./move-to-innodb.gmi + +### Check table structure + +``` +SHOW CREATE TABLE ProbeSetXRef; +``` + +``` +| ProbeSetXRef | CREATE TABLE 'ProbeSetXRef' ( + 'ProbeSetFreezeId' smallint(5) unsigned NOT NULL DEFAULT 0, + 'ProbeSetId' int(10) unsigned NOT NULL DEFAULT 0, + 'DataId' int(10) unsigned NOT NULL DEFAULT 0, + 'Locus_old' char(20) DEFAULT NULL, + 'LRS_old' double DEFAULT NULL, + 'pValue_old' double DEFAULT NULL, + 'mean' double DEFAULT NULL, + 'se' double DEFAULT NULL, + 'Locus' varchar(50) DEFAULT NULL, + 'LRS' double DEFAULT NULL, + 'pValue' double DEFAULT NULL, + 'additive' double DEFAULT NULL, + 'h2' float DEFAULT NULL, + UNIQUE KEY 'ProbeSetId' ('ProbeSetFreezeId','ProbeSetId'), + UNIQUE KEY 'DataId_IDX' ('DataId'), + KEY 'Locus_IDX' ('Locus') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +``` + +For every probe set (read dataset measuring point): + + +``` +select * from ProbeSetXRef limit 2; +| ProbeSetFreezeId | ProbeSetId | DataId | Locus_old | LRS_old | pValue_old | mean | se | Locus | LRS | pValue | additive | h2 | ++------------------+------------+--------+------------+--------------------+------------+------------------+---------------------+------------+------------------+--------+--------------------+------+ +| 1 | 1 | 1 | 10.095.400 | 13.3971627898894 | 0.163 | 5.48794285714286 | 0.08525787814808819 | rs13480619 | 12.590069931048 | 0.269 | -0.28515625 | NULL | +| 1 | 2 | 2 | D15Mit189 | 10.042057464356201 | 0.431 | 9.90165714285714 | 0.0374686634976217 | rs29535974 | 10.5970737900941 | 0.304 | -0.116783333333333 | NULL | ++------------------+------------+--------+------------+--------------------+------------+------------------+---------------------+------------+------------------+--------+--------------------+------+ +``` + +where ProbeSetFreezeId is the dataset (experiment). ProbesetId refers +to the probe set information (measuring point). DataId points to the +data point. The other values are used for search. + +``` +MariaDB [db_webqtl]> select count(*) from ProbeSetXRef; ++----------+ +| count(*) | ++----------+ +| 48076905 | ++----------+ +``` + +### Check GN1,2,3 code for use of table + +``` +rg ProbeSetXRef --color=always |less -R +``` + +### Create test + +Some select statement and maybe a page of GN2. + +### Create primary key + +``` +ALTER TABLE ProbeSetXRef + ADD PRIMARY KEY(col1,col2); +``` + +### Create indices +### Convert to innodb + +``` +ALTER TABLE ProbeSetXRef ENGINE = InnoDB; +``` + +### Change charset + +``` +ALTER TABLE ProbeSetSE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; +``` + +### Update fulltext + +For those cases see bottom of move-to-innodb.gmi. + +### Run optimiser + +``` +OPTIMIZE NO_WRITE_TO_BINLOG TABLE ProbeSetXRef; +``` + +### Check test diff --git a/topics/systems/mariadb/move-to-innodb.gmi b/topics/systems/mariadb/move-to-innodb.gmi index 13ee8ae..f93aace 100644 --- a/topics/systems/mariadb/move-to-innodb.gmi +++ b/topics/systems/mariadb/move-to-innodb.gmi @@ -11,13 +11,15 @@ Initial good candidates are (from issues/database-not-responding): * Good candidates + 2.1G Dec 4 22:15 ProbeSetXRef.MYD + 2.3G Dec 18 14:56 ProbeSet.MYD - with fulltext column - + 2.6G Aug 27 2019 ProbeSE.MYD - + 7.1G Nov 2 05:07 ProbeSetSE.MYD - + 11G Aug 27 2019 ProbeData.MYD + + 2.6G Aug 27 2019 ProbeSE.MYD (used?) + + 7.1G Nov 2 05:07 ProbeSetSE.MYD (done) + + 11G Aug 27 2019 ProbeData.MYD (used?) + 63G Dec 4 22:15 ProbeSetData.MYD I am starting with the two SE tables first - because they are small. +Actually ProbeData and ProbeSE (containing the Affy data) are not referenced in GN2. I need to check that. + ## Report With the SQL database we need to move from myisam to innodb format, @@ -69,6 +71,14 @@ one that hurt when locked: ProbeSetSE is done. So ProbeData and ProbeSetData are the main candidates right now. +Note I set buffers to 16GB for now + +``` ++innodb_buffer_pool_size=16G ++innodb_ft_min_token_size=3 ++# innodb_use_sys_malloc=0 ++innodb_file_per_table=ON +``` ## Check database @@ -206,7 +216,6 @@ To convert to InnoDB we should: * ascertain primary key * change charset -* set page size to 4K * change engine to InnoDB * make space on disk drive(s) @@ -331,6 +340,73 @@ It took your browser 2.119 second(s) to render this page and it shows practically the same results. +## Table template + +Prototocol from + +=> ./move-to-innodb.gmi + +Every table update has to follow the template: + +### Check table structure + +``` +SHOW CREATE TABLE MYTABLE; +``` + +``` +select * from MYTABLE limit 2; +``` + +``` +select count(*) from MYTABLE; +``` + +### Check GN1,2,3 code for use of table + +``` +rg MYTABLE --color=always |less -R +``` + +### Create test + +Some select statement and maybe a page of GN2. + +### Create primary key + +``` +ALTER TABLE MYTABLE + ADD PRIMARY KEY(col1,col2); +``` + +### Create indices +### Convert to innodb + +``` +ALTER TABLE MYTABLE ENGINE = InnoDB; +``` + +### Change charset + +``` +ALTER TABLE ProbeSetSE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; +``` + +### Update fulltext + +For those cases see bottom of move-to-innodb.gmi. + +### Run optimiser + +``` +OPTIMIZE NO_WRITE_TO_BINLOG TABLE MYTABLE; +``` + +### Check test + +## ProbeSetXRef + +=> ProbeSetXRef.gmi ## Notes captured from Trello: |