[Koha] MySQL using 99% CPU and making web site slow to a crawl

[Greg Vickers]

Hi Chris,

> Chris Cormack <chris at katipo.co.nz> wrote:
> 
> show processlist \g;  Is a great command to run in mysql, it will show
> you what is causing it to lock up. 
> So if you can find the offending query with that command, then we can
> start to look into why its going bad.

OK this problem has occurs when a Koha admin user was trying to resave a Biblio. Please find attached a file containing the mysql processes, top and vmstat.

>From what I understand, it looks like biblio record 2802 or 2671 was being updated, and someone tried to change it while it was being updated? Maybe they navigated back on their browser to a previous web page and changed some details and resubmitted the record? (I don't know, I don't use Koha, I just try to support it! :) )

Thanks,
Greg


-------------- next part --------------
mysql> show processlist \g
+------+-----------+-----------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id   | User      | Host      | db   | Command | Time | State                | Info                                                                                                 |
+------+-----------+-----------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 6163 | kohaadmin | localhost | Koha | Sleep   | 7    |                      | NULL                                                                                                 |
| 7120 | kohaadmin | localhost | Koha | Query   | 805  | Copying to tmp table | select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_wo |
| 7126 | kohaadmin | localhost | Koha | Query   | 548  | Locked               | Update marc_biblio SET frameworkcode='' WHERE bibid=2671                                             |
| 7131 | kohaadmin | localhost | Koha | Query   | 494  | Locked               | Update marc_biblio SET frameworkcode='' WHERE bibid=2671                                             |
| 7133 | kohaadmin | localhost | Koha | Query   | 455  | Locked               | Update marc_biblio SET frameworkcode='' WHERE bibid=2802                                             |
| 7135 | kohaadmin | localhost | Koha | Query   | 435  | Locked               | select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_wo |
| 7142 | kohaadmin | localhost | Koha | Query   | 399  | Locked               | select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_wo |
| 7144 | kohaadmin | localhost | Koha | Query   | 346  | Locked               | select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_wo |
| 7149 | kohaadmin | localhost | Koha | Query   | 309  | Locked               | Update marc_biblio SET frameworkcode='' WHERE bibid=2671                                             |
| 7152 | kohaadmin | localhost | Koha | Query   | 235  | Locked               | Update marc_biblio SET frameworkcode='' WHERE bibid=2802                                             |
| 7156 | kohaadmin | localhost | Koha | Query   | 225  | Locked               | select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1 where biblio.biblionumb |
| 7157 | kohaadmin | localhost | Koha | Query   | 200  | Locked               | Update marc_biblio SET frameworkcode='' WHERE bibid=2802                                             |
| 7160 | kohaadmin | localhost | Koha | Query   | 164  | Locked               | select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_wo |
| 7164 | kohaadmin | localhost | Koha | Query   | 110  | Locked               | Update marc_biblio SET frameworkcode='' WHERE bibid=2802                                             |
| 7168 | kohaadmin | localhost | Koha | Query   | 93   | Locked               | select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1 where biblio.biblionumb |
| 7169 | root      | localhost | NULL | Query   | 0    | NULL                 | show processlist                                                                                     |
+------+-----------+-----------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
16 rows in set (0.01 sec)

================================================================================

top - 15:03:29 up 202 days, 20:39,  1 user,  load average: 1.50, 1.39, 1.16
Tasks:  91 total,   1 running,  90 sleeping,   0 stopped,   0 zombie
Cpu(s): 26.5% us, 23.5% sy,  0.0% ni, 49.9% id,  0.0% wa,  0.0% hi,  0.2% si
Mem:   1033360k total,   931636k used,   101724k free,   160764k buffers
Swap:   979924k total,        0k used,   979924k free,   591936k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 4081 mysql     16   0  119m  34m 3500 S 98.7  3.5 226:36.73 mysqld
25874 xxxxx     15   0  8288 2456 1808 S  1.0  0.2   0:00.05 smbd
    1 root      16   0  1588  484  424 S  0.0  0.0   0:25.97 init

================================================================================

# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 3  0      0 103012 160764 592456    0    0     1     0    1     1  0  0 100  0
 1  0      0 103004 160764 592456    0    0     0     0  905   660 29 22 49  0
 1  0      0 103004 160764 592456    0    0     0    44  867   638 26 25 49  0
 2  0      0 102944 160764 592456    0    0    56    40  873   659 28 22 47  2
 1  0      0 102944 160764 592456    0    0    28     0  886   657 29 21 49  0
 1  0      0 102884 160772 592708    0    0    32    68  822   598 28 23 49  0