Move file

2 files changed, 159 insertions, 2 deletions

diff --git a/issues/database-not-responding.gmi b/issues/database-not-responding.gmi
index e66c939..58cfba3 100644
--- a/issues/database-not-responding.gmi
+++ b/issues/database-not-responding.gmi
@@ -1,12 +1,37 @@
 # Hanging database
 
-Mariadb occassionally stops responding.
+Mariadb occassionally stops responding. We think updating myisam to InnodB is the solution because it prevents full table locking.
 
-## Tasks
+Also it is better to use mydumper, mydumper locks MyISAM tables and does not lock InnoDB so the dump is consistent.
+
+## Tags
 
 * assigned: pjotrp, zsloan
 * bug
 
+## Tasks
+
+### for Penguin2, Tux01 and Tux02
+
+* First on Penguin2
+* Update mariadb to latest
+* Convert fulltext tables (see below)
+  + ProbeSet
+  + GeneRIF_BASIC
+  + pubmedsearch
+* Good candidates
+  + 2.1G Dec  4 22:15 ProbeSetXRef.MYD
+  + 2.3G Dec 18 14:56 ProbeSet.MYD
+  + 2.6G Aug 27  2019 ProbeSE.MYD
+  + 7.1G Nov  2 05:07 ProbeSetSE.MYD
+  +  11G Aug 27  2019 ProbeData.MYD
+  +  63G Dec  4 22:15 ProbeSetData.MYD
+* Create test for every table that is going to switch
+* Convert largest tables to innodb
+* After some testing do same for Tux01 and Tux02
+
+### Tux02
+
 # Info
 
 ## Mariadb is 'hanging'
@@ -143,3 +168,135 @@ some ideas here
 => https://dba.stackexchange.com/questions/98725/mariadb-innodb-what-to-do-on-locks-in-status-log-but-no-locked-table-found
 
 we are still using MyISAM for these tables: a switch to InnoDB may help.
+
+## Notes from Trello:
+
+In an earlier track I wrote how to deal with Fulltext fields
+
+pjotrp 16 Oct 2019 at 10:47
+
+Fulltext
+
+To list fulltext info for one table do:
+
+```
+select group_concat(distinct column_name) from information_schema.STATISTICS where table_schema = 'db_webqtl' and table_name = 'Probeset' and index_type = 'FULLTEXT';
+```
+
+Or
+
+```
+SHOW CREATE TABLE ProbeSet;
+
+E.g.
+
+  FULLTEXT KEY `SEARCH_GENE_IDX` (`Symbol`,`alias`),
+  FULLTEXT KEY `SEARCH_FULL_IDX` (`Name`,`description`,`Symbol`,`alias`,`GenbankId`,`UniGeneId`,`Probe_Target_Description`),
+  FULLTEXT KEY `RefSeq_FULL_IDX` (`RefSeq_TranscriptId`)
+```
+
+To see all:
+
+```
+root@tux02:/var/lib/mysql/db_webqtl# for x in ls -1 *.MYD|sed -e 's,\.MYD,,' ; do echo $x ; mysql -u webqtlout -pwebqtlout db_webqtl -e "select group_concat(distinct column_name) from information_schema.STATISTICS where table_schema = 'db_webqtl' and table_name = '$x' and index_type = 'FULLTEXT'" ; done
+```
+
+So we have to fix only
+
+```
+ProbeSet
+GeneRIF_BASIC
+pubmedsearch
+```
+
+```
+REPAIR TABLE ProbeSet QUICK;
+REPAIR TABLE GeneRIF_BASIC QUICK;
+REPAIR TABLE pubmedsearch QUICK;
+```
+
+Note that GN1 search only appears to use ProbeSet. Reindexing takes
+about 10 minutes on Tux02.
+
+After updating to ProbeSet to innodb the following query failed
+
+
+```
+SELECT distinct ProbeSet.Name as TNAME, 0 as thistable, ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL, ProbeSet.name_num as TNAME_NUM FROM ProbeSetXRef, ProbeSet WHERE ((MATCH (ProbeSet.Name, ProbeSet.description, ProbeSet.symbol, alias, GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('shh' IN BOOLEAN MODE)) or (MATCH (ProbeSet.symbol) AGAINST ('"Hx" "ShhNC" "9530036O11Rik" "Dsh" "Hhg1" "Hxl3" "M100081"' IN BOOLEAN MODE))) and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = 112 ORDER BY ProbeSet.symbol ASC;
+```
+
+with
+
+```
+ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
+```
+
+The shorter version works
+
+```
+SELECT distinct ProbeSet.Name as TNAME, 0 as thistable, ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL, ProbeSet.name_num as TNAME_NUM FROM ProbeSetXRef, ProbeSet WHERE ((MATCH (ProbeSet.Name, ProbeSet.description, ProbeSet.symbol, alias, GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('"Shh" "ShhNC" "9530036O11Rik" "Dsh" "Hhg1"' IN BOOLEAN MODE))) and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = 112;
+```
+
+when you remove any column in the MATCH statement we get this
+error. Which kinda makes sense I suppose. We need to add an index
+for the single ProbeSet.symbol match. Create it with
+
+```
+CREATE FULLTEXT INDEX ft_ProbeSet_Symbol ON ProbeSet(Symbol);
+```
+
+Now we have
+
+```
+FULLTEXT KEY `SEARCH_GENE_IDX` (`Symbol`,`alias`),
+FULLTEXT KEY `SEARCH_FULL_IDX` (`Name`,`description`,`Symbol`,`alias`,`GenbankId`,`UniGeneId`,`Probe_Target_Description`),
+FULLTEXT KEY `RefSeq_FULL_IDX` (`RefSeq_TranscriptId`),
+FULLTEXT KEY `ft_ProbeSet_Symbol` (`Symbol`)
+```
+
+and the query works.
+
+pjotrp 16 Oct 2019 at 09:24 (edited)
+
+Full text search is the least straightforward, see
+
+and
+
+Now for ProbeSet - one of the critical locked tables we need this.
+
+pjotrp 15 Oct 2019 at 16:38 (edited)
+
+```
+time mysqlcheck -c -u webqtlout -pwebqtlout db_webqtl
+db_webqtl.Docs
+warning : 1 client is using or hasn't closed the table properly
+status : OK
+
+real 16m52.567s
+```
+
+## Convert to InnoDB
+
+The largest tables are
+
+```
+1.6G Aug 27  2019 Probe.MYD
+2.1G Aug 27  2019 LCorrRamin3.MYD
+2.1G Dec  4 22:15 ProbeSetXRef.MYD
+2.3G Dec 18 14:56 ProbeSet.MYD
+2.6G Aug 27  2019 ProbeSE.MYD
+7.1G Nov  2 05:07 ProbeSetSE.MYD
+8.3G Aug 28  2019 SnpPattern.MYD
+ 11G Aug 27  2019 ProbeData.MYD
+ 11G May 22  2020 GenoData.MYD
+ 11G Aug 27  2019 SnpAll.MYD
+ 63G Dec  4 22:15 ProbeSetData.MYD
+```
+
+On Penguin2 we are already runing ProbeSetData as
+
+```
+238G Jul 10  2020 ProbeSetData.ibd
+```
+
+which is pretty massive! It includes the index, which is 180G, so the difference is not that great. Also we should try a 4kb page size. Also make sure to enable innodb_file_per_table.
diff --git a/topics/systems/mariadb.gmi b/topics/systems/mariadb/mariadb.gmi
index c2c927b..c2c927b 100644
--- a/topics/systems/mariadb.gmi
+++ b/topics/systems/mariadb/mariadb.gmi