mariadb to innodb notes

1 files changed, 0 insertions, 132 deletions

diff --git a/issues/database-not-responding.gmi b/issues/database-not-responding.gmi
index 58cfba3..cdb0ee9 100644
--- a/issues/database-not-responding.gmi
+++ b/issues/database-not-responding.gmi
@@ -168,135 +168,3 @@ 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.