diff options
Diffstat (limited to 'issues')
| -rw-r--r-- | issues/database-not-responding.gmi | 132 |
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. |