diff options
| -rw-r--r-- | issues/database-not-responding.gmi | 161 | ||||
| -rw-r--r-- | topics/systems/mariadb/mariadb.gmi (renamed from topics/systems/mariadb.gmi) | 0 |
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 |