Terrible problems after upgrading from 1.1.8 to 1.2.6

[Konrad]

Hello,

After upgrading mantis from an old (and heavily used) instance running 1.1.8 I have run into big problems. MySQL CPU usage never seems to drop below 95% and some users are experiencing timeouts when attempting to use Mantis. We are about a day after the upgrade and now seeing the following (unnecessary bits removed):

Code: Select all

mysql> SHOW PROCESSLIST;
-----------------------------+----------+---------+------+----------------------+---------------------------------------------------------------
| Command | Time | State                | Info                                                                                                 |
-----------------------------+----------+---------+------+----------------------+---------------------------------------------------------------
| Query   | 1140 | Sending data         | SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt   FROM mantis_bug_text_table, mantis_project_t |
| Sleep   | 7933 |                      | NULL                                                                                                 |
| Query   |  868 | Copying to tmp table | SELECT DISTINCT mantis_bug_table.*  FROM mantis_bug_text_table, mantis_project_table, mantis_bug_tab |
| Query   |  733 | Locked               | SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM manti |
| Sleep   | 5051 |                      | NULL                                                                                                 |
| Sleep   | 5047 |                      | NULL                                                                                                 |
| Sleep   | 5040 |                      | NULL                                                                                                 |
| Sleep   | 4797 |                      | NULL                                                                                                 |
| Sleep   | 4796 |                      | NULL                                                                                                 |
| Query   |  903 | Copying to tmp table | SELECT DISTINCT mantis_bug_table.*  FROM mantis_bug_text_table, mantis_project_table, mantis_bug_tab |
| Sleep   | 3317 |                      | NULL                                                                                                 |
| Query   |  738 | Locked               | INSERT INTO mantis_bugnote_table
 (bug_id, reporter_id, bugnote_text_id, view_state, date_submitt |
| Sleep   | 1928 |                      | NULL                                                                                                 |
| Query   |  735 | Locked               | SELECT b.*, t.note
         FROM      mantis_bugnote_table b
         LEFT JOIN mantis_bug |
| Sleep   | 1650 |                      | NULL                                                                                                 |
| Query   |  738 | Locked               | SELECT view_state
 FROM mantis_bugnote_table
 WHERE id=0 LIMIT 1             |
| Sleep   | 1517 |                      | NULL                                                                                                 |
| Query   |  737 | Locked               | SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM manti |
| Query   |  492 | Locked               | SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM manti |
| Query   |    0 | NULL                 | SHOW PROCESSLIST                                                                                     |
-----------------------------+----------+---------+------+----------------------+---------------------------------------------------------------
28 rows in set (0.00 sec)

I'm somewhat at a loss as to what is wrong. The update from 1.1.8 -> 1.2.6 using the install script completed successfully and Mantis is usable by some users but not others. All data appears to be correct and Mantis appears to be (basically) functioning.

If anyone can point me in the direction of how I can fix this I'll be very grateful.

Further noteworthy information from diagnostics as follows:
- Seeing lots of slow queries
- Innodb_buffer_pool_reads is high (~9,000)
- Innodb_row_lock_waits is high (7)
- Handler_read_rnd is high (2,223 k)
- Handler_read_rnd_next is frighteningly high (36G !!!)
- Created_tmp_disk_tables is in the hundreds
- Select_full_join is in the hundreds
- Opened_tables is ~6,000
- Table_locks_waited is 55.

This all started immediately after the update.

UPDATE : It seems that this is not a constant phenomenon but occurs reproducibly when a text search is carried out on a large project with (~8000) issues. Mantis (or the DB backend) will stall and take minutes to return the result.

[Konrad]

Just to confirm 1.2.6->1.2.7 has not fixed this issue.

[atrol]

Just a guess:
Did you run admin/check.php ?
I think you will get some errors, showing that MantisBT wants to use UTF-8 encoding.

http://www.mantisbt.org/wiki/doku.php/m ... de_to_utf8

[Konrad]

Problem persists despite correcting issues uncovered by running admin/check.php . If there are any pointers as to how to tweak or configure MySQL / PHP to resolve this I'd be interested in hearing them, however I'm inclined to believe this is an issue in Mantis 1.2.6/1.2.7 since the problem only occured post update.

[andrea.mills]

What versions of MySQL/PHP are you running?

[atrol]

Enable database tracing by adding the following lines to config_inc.php

Code: Select all

    $g_show_queries_count = ON;
    $g_show_queries_list = ON;

This might help you to find the statement which is causing your performance issue.