innodb

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: