mantis_email_table = 2171 records

This is most likely the root cause of the problem - this table should be empty.

The fact that you have data in there, means that emails are not getting sent, and the performance degradation is caused by Mantis trying (and apparently failing) to send queued emails.

As a quick workaround, you can purge the e-mail queue (in the admin pages, there's a function for that).

Alternatively, you should setup your system to send e-mails asynchronously ($g_email_send_using_cronjob = ON, use scripts/send_emails.php to actually send them).

To fix the root cause, you need to find out why e-mails are not being sent. Check your system logs, and try also to set $g_log_level = LOG_EMAIL

Hello
Problem still exist after purge mantis_email_table:
This 2000 emails was exist because i migrate servers and he lose connect to SMTP.

MantisBT Database Statistics

mantis_bug_file_table = 154067 records
mantis_bug_history_table = 8567797 records
mantis_bug_monitor_table = 51490 records
mantis_bug_relationship_table = 52208 records
mantis_bug_revision_table = 276316 records
mantis_bug_table = 497997 records
mantis_bug_tag_table = 176 records
mantis_bug_text_table = 498075 records
mantis_bugnote_table = 1436638 records
mantis_bugnote_text_table = 1436638 records
mantis_category_table = 2730 records
mantis_news_table = 15 records
mantis_plugin_table = 3 records
mantis_project_file_table = 3 records
mantis_project_table = 345 records
mantis_project_user_list_table = 29895 records
mantis_project_version_table = 3 records
mantis_tag_table = 281 records
mantis_user_table = 1510 records
mantis_user_profile_table = 3 records
mantis_user_pref_table = 1469 records
mantis_user_print_pref_table = 0 records
mantis_custom_field_project_table = 2184 records
mantis_custom_field_table = 133 records
mantis_custom_field_string_table = 3744587 records
mantis_filters_table = 16221 records
mantis_sponsorship_table = 0 records
mantis_tokens_table = 600 records
mantis_project_hierarchy_table = 248 records
mantis_config_table = 750 records
mantis_email_table = 2 records

Marek

Hello
I check today and my mantis_email table looks fine - like i say that happened when i migrate server now looks ok but still working amazing slow - login to administrator account take 30+ sec

MantisBT Database Statistics

mantis_bug_file_table = 154129 records
mantis_bug_history_table = 8572010 records
mantis_bug_monitor_table = 51489 records
mantis_bug_relationship_table = 52240 records
mantis_bug_revision_table = 276497 records
mantis_bug_table = 498177 records
mantis_bug_tag_table = 176 records
mantis_bug_text_table = 498255 records
mantis_bugnote_table = 1437284 records
mantis_bugnote_text_table = 1437284 records
mantis_category_table = 2730 records
mantis_news_table = 15 records
mantis_plugin_table = 3 records
mantis_project_file_table = 3 records
mantis_project_table = 345 records
mantis_project_user_list_table = 29895 records
mantis_project_version_table = 3 records
mantis_tag_table = 281 records
mantis_user_table = 1510 records
mantis_user_profile_table = 3 records
mantis_user_pref_table = 1469 records
mantis_user_print_pref_table = 0 records
mantis_custom_field_project_table = 2184 records
mantis_custom_field_table = 133 records
mantis_custom_field_string_table = 3748702 records
mantis_filters_table = 16223 records
mantis_sponsorship_table = 0 records
mantis_tokens_table = 627 records
mantis_project_hierarchy_table = 248 records
mantis_config_table = 750 records
mantis_email_table = 0 records

You report that you're on version 1.2.4, which is quite outdated. Can you please upgrade to the latest version 1.2.17 and try again ?

That said, looking at your slow queries, it may be that a query is performing badly. I don't have a DB of comparable size available for testing so troubleshooting will be difficult.

It looks like (last 2 slow queries) you are executing a text search ('plva' and 'plvb') - is this a saved filter ? Can you try to reset/clear the filter ?

Is it only slow for administrator, or for all users ?
Is it slow on all pages, or just one/specific pages, and if so, which one(s) ?
What page is being opened when you login ?

Hello,
I try upgrade this to 1.2.17 ASAP but this is hard - this is production with ~1000 users.
System working slow for everyone not only for administrators.
Lags is everywhere when i open a ticket change status or login... on administrator when i try login or click "my view".
What You mean to reset/clear filters? like i say 100 active ppl working on this system right now....

This is a little more slow queries:

Time: 140819 11:36:37

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 10.979906 Lock_time: 0.000205 Rows_sent: 10 Rows_examined: 1058801

SET timestamp=1408440997;
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_project_table, mantis_bug_table WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( ( mantis_bug_table.project_id in (509, 520, 510, 513, 512, 516, 496, 529, 506, 504, 522, 498, 514, 501, 523, 450, 451, 452, 453, 454, 455, 456, 457, 313, 281, 282, 283, 284, 285, 286, 287, 288, 289, 528, 527, 521, 524, 511, 517, 525, 497, 530, 531, 507, 508, 505, 526, 500, 499, 515, 503, 502) ) AND ( ( mantis_bug_table.view_state = 10 ) OR ( mantis_bug_table.reporter_id = 784 ) ) ) OR ( mantis_bug_table.project_id in (81, 469, 182, 321, 268, 149, 470, 471, 472, 473, 474, 475, 476, 477, 465, 466, 467, 468, 334, 399, 479, 480, 481, 482, 483, 484, 485, 486, 93, 494, 487, 406, 400, 339, 374, 375, 376, 377, 364, 293, 147, 137, 393, 350, 379, 380, 381, 382, 184, 183, 395, 254, 253, 277, 278, 279, 280, 338, 336, 337, 387, 216, 269, 270, 271, 272, 273, 274, 275, 276, 322, 323, 324, 325, 326, 327, 328, 329, 176, 178, 539, 537, 536, 397, 398, 447, 448, 540, 407, 410, 408, 409, 493, 541, 542, 341, 342, 362, 361, 345, 302, 307, 303, 304, 305, 306, 356, 351, 357, 363, 359, 360, 352, 353, 354, 355, 346, 347, 348, 349) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT 0,10;

Time: 140819 11:36:43

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 5.295903 Lock_time: 0.000113 Rows_sent: 1549 Rows_examined: 259731

SET timestamp=1408441003;
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table
WHERE mantis_custom_field_string_table.field_id='29'
GROUP BY mantis_custom_field_string_table.value;

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 5.293749 Lock_time: 0.000135 Rows_sent: 1549 Rows_examined: 259731

SET timestamp=1408441003;
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table
WHERE mantis_custom_field_string_table.field_id='29'
GROUP BY mantis_custom_field_string_table.value;

Time: 140819 11:36:44

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 5.561588 Lock_time: 0.000120 Rows_sent: 1549 Rows_examined: 259731

SET timestamp=1408441004;
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table
WHERE mantis_custom_field_string_table.field_id='29'
GROUP BY mantis_custom_field_string_table.value;

Time: 140819 11:36:49

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 5.334093 Lock_time: 0.000090 Rows_sent: 7409 Rows_examined: 146926

SET timestamp=1408441009;
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table
WHERE mantis_custom_field_string_table.field_id='78'
GROUP BY mantis_custom_field_string_table.value;

Time: 140819 11:36:50

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 6.449360 Lock_time: 0.000083 Rows_sent: 7409 Rows_examined: 146926

SET timestamp=1408441010;
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table
WHERE mantis_custom_field_string_table.field_id='78'
GROUP BY mantis_custom_field_string_table.value;

Time: 140819 11:36:56

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 7.212283 Lock_time: 0.000090 Rows_sent: 7409 Rows_examined: 146926

SET timestamp=1408441016;
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table
WHERE mantis_custom_field_string_table.field_id='78'
GROUP BY mantis_custom_field_string_table.value;

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 7.575557 Lock_time: 0.000151 Rows_sent: 1532 Rows_examined: 199481

SET timestamp=1408441016;
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table
WHERE mantis_custom_field_string_table.field_id='28'
GROUP BY mantis_custom_field_string_table.value;

Time: 140819 11:36:57

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 6.646779 Lock_time: 0.000103 Rows_sent: 1532 Rows_examined: 199481

SET timestamp=1408441017;
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table
WHERE mantis_custom_field_string_table.field_id='28'
GROUP BY mantis_custom_field_string_table.value;

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 14.387190 Lock_time: 0.000511 Rows_sent: 10 Rows_examined: 185101

SET timestamp=1408441017;
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_project_table, mantis_bug_table WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id in (469, 387, 322, 323, 324, 325, 327, 272, 473, 355, 1, 482, 345, 349, 377, 453, 285, 360, 382) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT 0,10;

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 8.349124 Lock_time: 0.000134 Rows_sent: 10 Rows_examined: 149890

SET timestamp=1408441017;
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_project_table, mantis_bug_table WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id in (469, 387, 323, 324, 325, 271, 468, 355, 345, 349, 377, 452, 284, 360, 492, 382) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT 0,10;

Thanks

I mean, in View issues page select [Reset filter] from the selection list, then click 'Use Filter' button.

Also you have lots of projects, does the slowdown occur when you're on 'All Projects' or also when you have an individual project selected ?

If it's a general slow down, it may be necessary to tune your mysql DB. Unfortunately not something I can help you with. If there are individual queries that are systematically running slow, then they should be analyzed.

Had a VERY short look at it and didn't find the code where we use
GROUP BY mantis_custom_field_string_table.value;

I use verry old version 1.2.4 mayby that`s why?

You are right, this has been changed in 1.2.12, see 0014766

Of course, as said by dregad, you should upgrade.

I w8 for approve to do upgrade ASAP, but like i say this is hard for now because this is production.... :(
Do You remember mayby there was any changes in DB from 1.2.4 to 1.2.17?
Because last time when we do upgrade there was many many problems with DB upgrade...

There is no database schema change since 1.2.4

So if i replace files from 1.2.4 to 1.2.17 everything will be work with np?

I can't gurantee anything, but I don't expect any issues if you didn't change any code of MantisBT, didn't install any 3rd party plugin and didn't add any custom functions.

I recommend to upload 1.2.17 parallel to your existing 1.2.4, copy config_inc.php from 1.2.4 to 1.2.17, do some tests with 1.2.17.
If all is fine, rename our old version to something like 1.2.4.old and rename 1.2.17 to what 1.2.4 has been named before.

I run 1.2.17 in another directory and when i logging to Administrator i wait 30+ sec this is a log:

Time: 140819 14:53:10

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 33.916609 Lock_time: 1.867455 Rows_sent: 10 Rows_examined: 1080905

SET timestamp=1408452790;
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_project_table, mantis_bug_table WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( ( mantis_bug_table.project_id in (509, 520, 510, 513, 512, 516, 496, 529, 506, 504, 522, 498, 514, 501, 523, 528, 527, 521, 524, 511, 517, 525, 497, 530, 531, 507, 508, 505, 526, 500, 499, 515, 503, 502, 495) ) AND ( ( mantis_bug_table.view_state = 10 ) OR ( mantis_bug_table.reporter_id = 104 ) ) ) OR ( mantis_bug_table.project_id in (81, 469, 395, 254, 253, 268, 277, 278, 279, 280, 149, 470, 471, 472, 473, 474, 475, 476, 477, 465, 466, 467, 468, 184, 183, 334, 399, 1, 479, 480, 481, 482, 483, 484, 485, 486, 93, 494, 487, 406, 400, 339, 374, 375, 376, 377, 364, 293, 450, 451, 452, 453, 454, 455, 456, 457, 147, 313, 137, 393, 350, 281, 379, 380, 381, 382, 338, 336, 337, 387, 216, 269, 270, 271, 272, 273, 274, 275, 276, 322, 323, 324, 325, 326, 327, 328, 329, 176, 178, 539, 537, 536, 397, 398, 294, 301, 295, 298, 299, 300, 447, 448, 540, 407, 410, 408, 409, 493, 541, 542, 341, 342, 362, 361, 345, 396, 392, 302, 307, 303, 304, 305, 306, 356, 351, 357, 363, 359, 360, 352, 353, 354, 355, 346, 347, 348, 349, 282, 283, 284, 285, 286, 287, 288, 289) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT 0,10;

When i click my view:

Time: 140819 14:55:02

User@Host: root[root] @ mantis.local [172.30.30.30]

Query_time: 18.923060 Lock_time: 0.000202 Rows_sent: 10 Rows_examined: 821464

SET timestamp=1408452902;
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_project_table, mantis_bug_table WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( ( mantis_bug_table.project_id = 448 ) AND ( ( mantis_bug_table.view_state = 10 ) OR ( mantis_bug_table.reporter_id = 1450 ) ) ) OR ( mantis_bug_table.project_id in (81, 469, 182, 395, 254, 253, 268, 277, 278, 279, 280, 149, 470, 471, 472, 473, 474, 475, 476, 477, 465, 466, 467, 468, 537, 334, 479, 480, 481, 482, 483, 484, 485, 486, 93, 494, 406, 536, 364, 293, 450, 451, 452, 453, 454, 455, 456, 457, 137, 281, 351, 184, 183, 338, 336, 337, 269, 270, 271, 272, 273, 274, 275, 276, 397, 398, 294, 301, 295, 298, 299, 300, 540, 407, 410, 408, 409, 495, 493, 541, 542, 535, 302, 307, 303, 304, 305, 306, 282, 283, 284, 285, 286, 287, 288, 289, 352, 353, 354, 355, 346, 347, 348, 349) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT 0,10;

Thanks

Add the following lines to config_inc.php

$g_show_timer       = ON;

$g_show_queries_count   = ON;

$g_show_queries_list    = ON;

Click "My View"

Paste all content at the bottom of the page starting from
Time: x.xxx seconds.
to a text file and attach the file to the issue.

Furthermore you can try to add an index on table mantis_bug_table column view_state

Hello This is log.
Now logging to system or click View take ONLY ~18 sec because noone working with system... in Poland is now 21:36 so i think i am alone user now...
But like i say in middle day logging or click My view take 30+ sec.

Run the following SQL-Statement in phpMyAdmin or any other MySQL you use and attach the output to the issue.

EXPLAIN SELECT COUNT( DISTINCT mantis_bug_table.id ) AS idcnt

FROM mantis_bug_table

JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id

WHERE mantis_project_table.enabled =1;

EXPLAIN SELECT DISTINCT mantis_bug_table.*

FROM mantis_bug_table

JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id

WHERE mantis_project_table.enabled =1

ORDER BY mantis_bug_table.last_updated DESC , mantis_bug_table.date_submitted DESC;

Which version of MySQL do you use?

mysql> EXPLAIN SELECT COUNT( DISTINCT mantis_bug_table.id ) AS idcnt
-> FROM mantis_bug_table
-> JOIN mantis_project_table ON mantis_project_table.id =
-> mantis_bug_table.project_id
-> WHERE mantis_project_table.enabled =1;
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+--------+-------------+
| 1 | SIMPLE | mantis_bug_table | ALL | idx_project | NULL | NULL | NULL | 498609 | |
| 1 | SIMPLE | mantis_project_table | eq_ref | PRIMARY | PRIMARY | 4 | bugtracker.mantis_bug_table.project_id | 1 | Using where |
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+--------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT DISTINCT mantis_bug_table.*
-> FROM mantis_bug_table
-> JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id
-> WHERE mantis_project_table.enabled =1
-> ORDER BY mantis_bug_table.last_updated DESC , mantis_bug_table.date_submitted DESC;
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+--------+---------------------------------+
| 1 | SIMPLE | mantis_bug_table | ALL | idx_project | NULL | NULL | NULL | 498609 | Using temporary; Using filesort |
| 1 | SIMPLE | mantis_project_table | eq_ref | PRIMARY | PRIMARY | 4 | bugtracker.mantis_bug_table.project_id | 1 | Using where; Distinct |
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+--------+---------------------------------+
2 rows in set (0.00 sec)

Server version: 5.5.38-0+wheezy1-log (Debian)

Thanks

I am not a MySQL performance specialist. I have no knowledge about how the query optimizer works in MySQL.
I understand the output of the EXPLAIN command, that MySQL does a full table scan (type = ALL) on table mantis_bug_table.
This means accessing 498609 rows of the table and would explain the poor performance.

I tried on a test system, but was not able to reproduce the behaviour, I get
1 SIMPLE mantis_bug_table ref idx_project idx_project 4 m1201.mantis_project_table.id 634 NULL
This might be caused be different numbers of projects and issues in our databases.

Sorry, I can't invest more time in that. I recommend to contact a MySQL specialist.

You could deactivate the "Recently modified" box (and maybe even some more) on "My View" page as a temporary workaround.
To do that, add the following lines to config_inc.php

$g_my_view_boxes = array (

'assigned'      => '1',

'unassigned'    => '2',

'reported'      => '3',

'resolved'      => '4',

'recent_mod'    => '0',

'monitored'     => '6',

'feedback'      => '0',

'verify'        => '0',

'my_comments'   => '0'

);

Hello
We just upgrade Mantisbt from 1.2.4 to 1.2.17 for now looks working much more faster.... if i saw still problems i let You know - please close for now this bug i reopen if need it.

Marek

The number of displayed users didn't change from 1.2.4 to 1.2.17.
Maybe this happens because you changed manage_user_page.php in 1.2.4
Check for $p_per_page in this file.

Marek,
please don't mix different topics in one issue.

Can we close this one?

Please close for now this bug i reopen if we saw problem with 1.2.17.

Thanks