diff options
| author | Pjotr Prins | 2022-03-13 08:50:57 +0100 |
|---|---|---|
| committer | Pjotr Prins | 2022-03-13 08:50:57 +0100 |
| commit | bb01f5b625e25df0bb2ac519285774de82c00f10 (patch) | |
| tree | de7e8a5e9c00deec25b3b7cfdb9dc9e3b4eb8859 | |
| parent | 3e27285b9248a835a00f1a18813494d12ad4cca4 (diff) | |
| download | gn-gemtext-bb01f5b625e25df0bb2ac519285774de82c00f10.tar.gz | |
issue: database: do a quick assessment
| -rw-r--r-- | issues/database-not-responding.gmi | 49 |
1 files changed, 48 insertions, 1 deletions
diff --git a/issues/database-not-responding.gmi b/issues/database-not-responding.gmi index cdb0ee9..cb7c600 100644 --- a/issues/database-not-responding.gmi +++ b/issues/database-not-responding.gmi @@ -96,7 +96,14 @@ waiting for tables to flush! at the top of the process list we find ``` -Id User Host db Command Time State Info Progress 1 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000 2 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000 227365 webqtlout 127.0.0.1:33950 db_webqtl Sleep 13015 NULL 0.000 245634 webqtlout 127.0.0.1:38098 db_webqtl Sleep 23180 NULL 0.000 +Id User Host db Command Time State Info Progress +1 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000 +2 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 +3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 +4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 +5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000 +227365 webqtlout 127.0.0.1:33950 db_webqtl Sleep 13015 NULL 0.000 +245634 webqtlout 127.0.0.1:38098 db_webqtl Sleep 23180 NULL 0.000 ``` This is quite informative: @@ -168,3 +175,43 @@ 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. + +## Another round of mariadb + +Arthur complained again that the DB is slow. The standard performance tests are not failing. The slow log shows slow queries: + +``` +# Thread_id: 1715564 Schema: db_webqtl QC_hit: No +# Query_time: 399.159339 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 +SET timestamp=1647055599; +UPDATE ProbeSet SET description = REPLACE(description, ";", "") WHERE ChipId=11; + +# Query_time: 2006.780492 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 +SET timestamp=1647057611; +update ProbeSetXRef set mean = (select AVG(value) from ProbeSetData where ProbeSetData.Id = ProbeSetXRef.DataId) where ProbeSetXRef.ProbeSetFreezeId = 385; + +# Query_time: 1158.804376 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 +use db_webqtl; +SET timestamp=1647142312; +update ProbeSet set description="ArfGAP with SH3 domain, ankyrin repeat and PH domain 3" where Id=1719426 AND ChipId=11; +``` + +so we should be able to reproduce that. + +* mariadb was restarted 1 week ago +* mariadb is not eating CPU or RAM, so the general state looks healthy. +* Disk state looks healty - but there are files in /tmp which do not belong there. +* The ps table shows a backup is ongoing (after 3 hours). +* There are no tables in use according to `show open tables where in_use > 1;` +* There are no blocking prcesses according to `SHOW FULL PROCESSLIST;` +* Number of threads is a healthy 4 with `show status where `variable_name` = 'Threads_connected';` + +So, no issues. And this query runs at + +``` +MariaDB [db_webqtl]> update ProbeSet set description="ArfGAP with SH3 domain, ankyrin repeat and PH domain 3" where Id=1719426 AND ChipId=11; +Query OK, 1 row affected (0.210 sec) +Rows matched: 1 Changed: 1 Warnings: 0 +``` + +There must have been an update block. Best to do diagnostics when the system is blocking. |