Mantis responding too slow while posting new bugs or viewing

[asimahmedkhan]

hi all,

I've installed mantis on my RHEL5 server. My environment is as follows:

php-5.1.6-23.el5
httpd-2.2.3-22.el5
mysql Ver 14.14 Distrib 5.1.30
Mantis 1.1.6

Since about a month after installing it went really super fast. Everything was Ok. suddenly from last couple of days it has become a pain! Every time a bug is posted or a user is created (any write operation) it just dies! Response takes around 40 seconds to come out! My users are screaming. Even when viewing, it slows down like login takes around 15 to 20 seconds!

My mysql's my.cnf is as follows:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 24M
table_cache = 32M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
query_cache_limit = 64M
join_buffer_size = 8M
table_cache = 8M

tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 1M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

Any help is greatly appriciated!

-Asim Ahmed

[mjones]

I'm having similar issues when viewing "View Issues". I have isolated it to custom field, but that's as far as I can get.

I upgraded from 1.0.1 to that latest prod. version 1.1.7, which gave us about 50% improvement but that's still a 15-25 second wait! (

Code: Select all

4	0.0014	project_api.php:66 project_cache_row()
SELECT * FROM mantis_project_table WHERE id='48'
5	0.0012	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='29'

The queries are quick here... but go down a few more queries and that's where it all falls over.

Code: Select all

35	2.0160	custom_field_api.php:70 custom_field_cache_row()
SELECT * FROM mantis_custom_field_table WHERE id='18'
36	0.0011	custom_field_api.php:1010 custom_field_distinct_values()
SELECT type, possible_values FROM mantis_custom_field_table WHERE id='18'
37	1.4487	custom_field_api.php:1042 custom_field_distinct_values()
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table WHERE mantis_custom_field_string_table.field_id='18' GROUP BY mantis_custom_field_string_table.value
38	2.0570	custom_field_api.php:70 custom_field_cache_row()
SELECT * FROM mantis_custom_field_table WHERE id='31'

I have added indexs to id in the mantis_custom_field_table and that didn't squeeze much out of it. (

[mjones]

Anyone... anyone...
Bueller? Bueller?

Project with 10x times the amount of bugs, but no custom fields... Very fast.

Code: Select all

1	0.0006	database_api.php:76 db_connect()
SET NAMES UTF8
2	0.0010	authentication_api.php:582 auth_is_cookie_valid()
SELECT id FROM mantis_user_table WHERE cookie_string='73a1c16a8e22a55a9b608867dc302810c11662297ae31e33327a3d6af8b60577'
3	0.0008	authentication_api.php:613 auth_get_current_user_id()
SELECT id FROM mantis_user_table WHERE cookie_string='73a1c16a8e22a55a9b608867dc302810c11662297ae31e33327a3d6af8b60577'
4	0.0011	project_api.php:66 project_cache_row()
SELECT * FROM mantis_project_table WHERE id='16'
5	0.0012	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='29'
6	0.0020	config_api.php:96 config_get()
SELECT config_id, user_id, project_id, type, value, access_reqd FROM mantis_config_table
7	0.0018	user_pref_api.php:133 user_pref_cache_row()
SELECT * FROM mantis_user_pref_table WHERE user_id='29' AND project_id='0'
8	0.0013	filter_api.php:2860 filter_db_get_filter()
SELECT * FROM mantis_filters_table WHERE id='469'
9	0.0040	filter_api.php:2900 filter_db_get_project_current()
SELECT * FROM mantis_filters_table WHERE user_id='29' AND project_id='-16' AND name=''
10	0.0013	filter_api.php:2860 filter_db_get_filter()
SELECT * FROM mantis_filters_table WHERE id='181'
11	0.0013	custom_field_api.php:724 custom_field_get_ids()
SELECT id, name FROM mantis_custom_field_table ORDER BY name ASC
12	0.0059	user_api.php:828 user_get_accessible_subprojects()
SELECT DISTINCT p.id, p.name, ph.parent_id FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 AND ph.parent_id IS NOT NULL ORDER BY p.name
13	0.0012	custom_field_api.php:697 custom_field_get_linked_ids()
SELECT cft.id, cft.name, cfpt.sequence FROM mantis_custom_field_table cft, mantis_custom_field_project_table cfpt WHERE cfpt.project_id = 16 AND cft.id = cfpt.field_id ORDER BY sequence ASC, name ASC
14	0.0230	filter_api.php:1280 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.id AS id 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 (16, 38, 17, 37, 53) ) AND ( mantis_bug_table.handler_id=33 ) AND ( mantis_bug_table.status in (10, 20, 30, 40, 50) )
15	0.0065	filter_api.php:1414 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.* FROM mantis_bug_table WHERE mantis_bug_table.id in (2920, 2978, 3358, 3359, 3561, 3635, 3723, 3892, 3923, 4390, 5970, 6600, 6619, 6669, 6737, 7050, 7150, 7235, 7283, 7345, 7664, 7676, 7851, 7873, 7890, 7891, 7981, 8077, 8094, 8105, 8128) ORDER BY last_updated DESC, date_submitted DESC
16	0.0208	filter_api.php:1440 filter_get_bug_rows()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (8128, 8094, 8105, 8077, 3358, 7981, 6619, 7873, 7890, 7891, 7851, 7676, 7664, 7235, 7345, 7150, 7283, 3635, 7050, 2920, 6737, 6669, 6600, 5970, 4390, 2978, 3723, 3561, 3359, 3923, 3892) GROUP BY bug_id
17	0.0020	project_api.php:95 project_cache_all()
SELECT * FROM mantis_project_table
18	0.0062	project_hierarchy_api.php:106 project_hierarchy_cache()
SELECT DISTINCT p.id, ph.parent_id, p.name FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 ORDER BY p.name
19	0.0010	user_api.php:553 user_get_id_by_name()
SELECT id FROM mantis_user_table WHERE username='mjones'
20	0.0007	tokens_api.php:258 token_purge_expired()
DELETE FROM mantis_tokens_table WHERE '2009-06-03 08:29:57' > expiry
21	0.0010	tokens_api.php:75 token_get()
SELECT * FROM mantis_tokens_table WHERE type='3' AND owner='29'
22	0.0013	bug_api.php:132 bug_cache_row()
SELECT * FROM mantis_bug_table WHERE id='8110'
23	0.0014	bug_api.php:132 bug_cache_row()
SELECT * FROM mantis_bug_table WHERE id='8112'
24	0.0039	filter_api.php:2900 filter_db_get_project_current()
SELECT * FROM mantis_filters_table WHERE user_id='29' AND project_id='-16' AND name=''
25	0.0039	filter_api.php:2900 filter_db_get_project_current()
SELECT * FROM mantis_filters_table WHERE user_id='29' AND project_id='-16' AND name=''
26	0.0012	version_api.php:322 version_get_all_rows_with_subs()
SELECT * FROM mantis_project_version_table WHERE project_id IN (16,38,17,37,53) ORDER BY date_order DESC
27	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='33'
28	0.0073	filter_api.php:3019 filter_db_get_available_queries()
SELECT * FROM mantis_filters_table WHERE (project_id='16' OR project_id='0') AND name!='' ORDER BY is_public DESC, name ASC
29	0.0044	filter_api.php:2900 filter_db_get_project_current()
SELECT * FROM mantis_filters_table WHERE user_id='29' AND project_id='-16' AND name=''
30	0.0075	filter_api.php:3019 filter_db_get_available_queries()
SELECT * FROM mantis_filters_table WHERE (project_id='16' OR project_id='0') AND name!='' ORDER BY is_public DESC, name ASC
31	0.0012	user_api.php:999 user_update_last_visit()
UPDATE mantis_user_table SET last_visit= '2009-06-03 08:29:58' WHERE id='29'
32	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='29'
	0.1194	SQL Queries Total Time
	1.1498	Page Request Total Time

(one) of the problem projects... and it takes a looonnnggg time.

Code: Select all

56 total queries executed.
50 unique queries executed.
1	0.0006	database_api.php:76 db_connect()
SET NAMES UTF8
2	0.0011	authentication_api.php:582 auth_is_cookie_valid()
SELECT id FROM mantis_user_table WHERE cookie_string='73a1c16a8e22a55a9b608867dc302810c11662297ae31e33327a3d6af8b60577'
3	0.0008	authentication_api.php:613 auth_get_current_user_id()
SELECT id FROM mantis_user_table WHERE cookie_string='73a1c16a8e22a55a9b608867dc302810c11662297ae31e33327a3d6af8b60577'
4	0.0011	project_api.php:66 project_cache_row()
SELECT * FROM mantis_project_table WHERE id='48'
5	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='29'
6	0.0020	config_api.php:96 config_get()
SELECT config_id, user_id, project_id, type, value, access_reqd FROM mantis_config_table
7	0.0018	user_pref_api.php:133 user_pref_cache_row()
SELECT * FROM mantis_user_pref_table WHERE user_id='29' AND project_id='0'
8	0.0014	filter_api.php:2860 filter_db_get_filter()
SELECT * FROM mantis_filters_table WHERE id='469'
9	0.0041	filter_api.php:2900 filter_db_get_project_current()
SELECT * FROM mantis_filters_table WHERE user_id='29' AND project_id='-48' AND name=''
10	0.0014	custom_field_api.php:724 custom_field_get_ids()
SELECT id, name FROM mantis_custom_field_table ORDER BY name ASC
11	0.0064	user_api.php:828 user_get_accessible_subprojects()
SELECT DISTINCT p.id, p.name, ph.parent_id FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 AND ph.parent_id IS NOT NULL ORDER BY p.name
12	0.0016	custom_field_api.php:697 custom_field_get_linked_ids()
SELECT cft.id, cft.name, cfpt.sequence FROM mantis_custom_field_table cft, mantis_custom_field_project_table cfpt WHERE cfpt.project_id = 48 AND cft.id = cfpt.field_id ORDER BY sequence ASC, name ASC
13	0.0210	filter_api.php:1280 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.id AS id 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 = 48 ) AND ( mantis_bug_table.status in (10, 20, 30, 40, 50, 80) )
14	0.0275	filter_api.php:1414 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.* FROM mantis_bug_table WHERE mantis_bug_table.id in (5062, 5071, 5105, 5112, 5175, 5237, 5523, 5563, 5654, 5678, 5717, 5723, 5724, 5729, 5734, 5743, 5768, 5769, 5770, 5775, 5777, 5866, 6009, 6132, 6173, 6321, 6446, 6455, 6471, 6477, 6478, 6485, 6652, 6911, 6912, 6955, 6978, 7061, 7124, 7147, 7262, 7322, 7330, 7332, 7371, 7418, 7441, 7442, 7443, 7444, 7452, 7457, 7465, 7480, 7501, 7527, 7539, 7544, 7561, 7594, 7595, 7596, 7602, 7610, 7613, 7635, 7636, 7638, 7641, 7643, 7684, 7686, 7688, 7696, 7697, 7699, 7700, 7702, 7703, 7706, 7710, 7713, 7715, 7717, 7725, 7726, 7727, 7730, 7731, 7735, 7741, 7744, 7749, 7752, 7755, 7756, 7761, 7762, 7766, 7770, 7776, 7782, 7784, 7792, 7804, 7808, 7812, 7822, 7829, 7830, 7831, 7838, 7840, 7841, 7844, 7845, 7846, 7847, 7848, 7850, 7854, 7856, 7858, 7860, 7862, 7863, 7865, 7869, 7871, 7872, 7879, 7881, 7885, 7886, 7916, 7919, 7920, 7922, 7925, 7927, 7928, 7931, 7932, 7933, 7940, 7945, 7946, 7949, 7950, 7954, 7955, 7957, 7962, 7968, 7971, 7979, 8005, 8012, 8013, 8014, 8020, 8022, 8024, 8030, 8036, 8041, 8042, 8043, 8045, 8046, 8050, 8051, 8053, 8064, 8057, 8058, 8065, 8067, 8071, 8081, 8082, 8090, 8092, 8093, 8102, 8107, 8109) ORDER BY sticky DESC, last_updated DESC, date_submitted DESC
15	0.0066	filter_api.php:1440 filter_get_bug_rows()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (7124, 7596, 7595, 7749, 8041, 7636, 7594, 7841, 8107, 7920, 8090, 7418, 8082, 8109, 8093, 6477, 8092, 5654, 7710, 8102, 7727, 8081, 7561, 7703, 8071, 7696, 8067, 7957, 8030, 7725, 8057, 8065, 7635, 8053, 8058, 7932, 7916, 8064, 7856, 7770, 8051, 7613, 8005, 8050, 7804, 8024, 7860, 7544, 8036, 8045) GROUP BY bug_id
16	0.0018	project_api.php:95 project_cache_all()
SELECT * FROM mantis_project_table
17	0.0065	project_hierarchy_api.php:106 project_hierarchy_cache()
SELECT DISTINCT p.id, ph.parent_id, p.name FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 ORDER BY p.name
18	0.0010	user_api.php:553 user_get_id_by_name()
SELECT id FROM mantis_user_table WHERE username='mjones'
19	0.0008	tokens_api.php:258 token_purge_expired()
DELETE FROM mantis_tokens_table WHERE '2009-06-03 08:30:31' > expiry
20	0.0010	tokens_api.php:75 token_get()
SELECT * FROM mantis_tokens_table WHERE type='3' AND owner='29'
21	0.0013	bug_api.php:132 bug_cache_row()
SELECT * FROM mantis_bug_table WHERE id='8110'
22	0.0014	bug_api.php:132 bug_cache_row()
SELECT * FROM mantis_bug_table WHERE id='8105'
23	0.0013	bug_api.php:132 bug_cache_row()
SELECT * FROM mantis_bug_table WHERE id='8112'
24	0.0013	bug_api.php:132 bug_cache_row()
SELECT * FROM mantis_bug_table WHERE id='8094'
25	0.0040	filter_api.php:2900 filter_db_get_project_current()
SELECT * FROM mantis_filters_table WHERE user_id='29' AND project_id='-48' AND name=''
26	0.0036	filter_api.php:2900 filter_db_get_project_current()
SELECT * FROM mantis_filters_table WHERE user_id='29' AND project_id='-48' AND name=''
27	0.0014	custom_field_api.php:70 custom_field_cache_row()
SELECT * FROM mantis_custom_field_table WHERE id='30'
28	0.0008	custom_field_api.php:1010 custom_field_distinct_values()
SELECT type, possible_values FROM mantis_custom_field_table WHERE id='30'
29	1.2127	custom_field_api.php:1042 custom_field_distinct_values()
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table WHERE mantis_custom_field_string_table.field_id='30' GROUP BY mantis_custom_field_string_table.value
30	1.8979	custom_field_api.php:70 custom_field_cache_row()
SELECT * FROM mantis_custom_field_table WHERE id='29'
31	0.0009	custom_field_api.php:1010 custom_field_distinct_values()
SELECT type, possible_values FROM mantis_custom_field_table WHERE id='29'
32	0.0015	custom_field_api.php:70 custom_field_cache_row()
SELECT * FROM mantis_custom_field_table WHERE id='16'
33	0.0008	custom_field_api.php:1010 custom_field_distinct_values()
SELECT type, possible_values FROM mantis_custom_field_table WHERE id='16'
34	1.3610	custom_field_api.php:1042 custom_field_distinct_values()
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table WHERE mantis_custom_field_string_table.field_id='16' GROUP BY mantis_custom_field_string_table.value
35	1.9120	custom_field_api.php:70 custom_field_cache_row()
SELECT * FROM mantis_custom_field_table WHERE id='17'
36	0.0009	custom_field_api.php:1010 custom_field_distinct_values()
SELECT type, possible_values FROM mantis_custom_field_table WHERE id='17'
37	1.3403	custom_field_api.php:1042 custom_field_distinct_values()
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table WHERE mantis_custom_field_string_table.field_id='17' GROUP BY mantis_custom_field_string_table.value
38	1.8133	custom_field_api.php:70 custom_field_cache_row()
SELECT * FROM mantis_custom_field_table WHERE id='18'
39	0.0010	custom_field_api.php:1010 custom_field_distinct_values()
SELECT type, possible_values FROM mantis_custom_field_table WHERE id='18'
40	1.3198	custom_field_api.php:1042 custom_field_distinct_values()
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table WHERE mantis_custom_field_string_table.field_id='18' GROUP BY mantis_custom_field_string_table.value
41	1.9039	custom_field_api.php:70 custom_field_cache_row()
SELECT * FROM mantis_custom_field_table WHERE id='31'
42	0.0009	custom_field_api.php:1010 custom_field_distinct_values()
SELECT type, possible_values FROM mantis_custom_field_table WHERE id='31'
43	1.2599	custom_field_api.php:1042 custom_field_distinct_values()
SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table WHERE mantis_custom_field_string_table.field_id='31' GROUP BY mantis_custom_field_string_table.value
44	2.0156	version_api.php:322 version_get_all_rows_with_subs()
SELECT * FROM mantis_project_version_table WHERE project_id=48 ORDER BY date_order DESC
45	0.0088	filter_api.php:3019 filter_db_get_available_queries()
SELECT * FROM mantis_filters_table WHERE (project_id='48' OR project_id='0') AND name!='' ORDER BY is_public DESC, name ASC
46	0.0040	filter_api.php:2900 filter_db_get_project_current()
SELECT * FROM mantis_filters_table WHERE user_id='29' AND project_id='-48' AND name=''
47	0.0144	filter_api.php:3019 filter_db_get_available_queries()
SELECT * FROM mantis_filters_table WHERE (project_id='48' OR project_id='0') AND name!='' ORDER BY is_public DESC, name ASC
48	0.0016	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='78'
49	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='10'
50	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='36'
51	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='34'
52	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='15'
53	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='38'
54	0.0018	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='47'
55	0.0011	user_api.php:999 user_update_last_visit()
UPDATE mantis_user_table SET last_visit= '2009-06-03 08:30:48' WHERE id='29'
56	0.0013	user_api.php:60 user_cache_row()
SELECT * FROM mantis_user_table WHERE id='29'
	16.1868	SQL Queries Total Time
	17.4618	Page Request Total Time

Anyone have any ideas?

Please help...

[vboctor]

Try disabling the email notifications and see if this fixes the problem. This could fix the user sign-up and issue reporting.

[dylanc]

I recently upgraded to mantis 1.1.8 (still using php4) and I am hitting this slow down (which we didn't have before with 1.0rc3) - I have turned on cronjob batching but it isn't the physical sending of the e-mail that is slow, what is slow is the generation of the mails themselves.

If I turn off mail notification everything suddenly works perfectly fast.

Is there something in the 1.1.8 codebase for e-mail generation that is causing a sub-optimal lookup into mySql? Is there an easy way to see what request is causing the slow down?

Did perhaps one of the tables in mysql get upgraded incorrectly and is lacking some kind of fast indexing settings?

Any help you can provide is much appreciated.

[atrol]

set this in your config_inc.php to see more details
$g_show_queries_list = ON;

[dylanc]

This is the output when I try to add a simple bug note to a bug that has a parent relationship.

I presume these figures are in seconds?

If so, it thinks it is only taking 0.6 seconds to do the page request? (it took about 30 I think)
We have a custom field that is a utf-8 string as you can see in no. 69 - could this be the culprit for some reason (although the timing figures don't reflect that)?
The database is now entirely utf-8 and this is a major difference from 1.0rc3.

Code: Select all

71 total queries executed.
63 unique queries executed.

<<deleted for security - these figures were irrelevant at the end of the day - see my later posts >>

0.0559	SQL Queries Total Time
0.6408	Page Request Total Time

[atrol]

Did you optimze all tables after the upgrade?
Give it a try, but as you wrote: the output does not reflect your 30 seconds, so there is no great hope

I also would try using PHP 5.2.x (not 5.3!)

[dylanc]

Yes, I optimized all the tables.

So do you think it is the php code that is running slowly? Unfortunately I can't upgrade php as we have some other legacy apps on the server and we didn't have this problem before moving to 1.1.8 so there must be something obviously new in the way it is preparing these e-mails to send compared to 1.0rc3. (and from a search of the forums it seems like a no. of other people are hitting this problem)

[dylanc]

Does anyone who knows that email code have any ideas as to what has caused it to considerably (as in x100) slow down since 1.0rc3?
From the timings it seems to have nothing to do with the database, but of course the database is no longer latin, it is utf8, as is php and mantis, so is there something slowing down because of utf8 processing perhaps? (just clutching at straws here as this slowdown is crippling our work with mantis)

[atrol]

why not try a parallel installation of PHP 4 and 5?
I never tried myself, but there are various ways to get it run
Depends whether you want to change names of the source from *.php to *.php5 or not,
whether you use one webserver or more, ....
Just one exampe how to confugure
http://www.gentoo.org/proj/en/php/php4- ... ration.xml

[dylanc]

SOLVED!

Ok, after a whole day of going through the code looking for the slow down, I tracked it to the function email_queue_add and the db_query that exists in that function. (for some reason that query isn't shown when show_queries is on)

And... I found the culprit - MySQL 4.1.8 (I don't know about later versions, I haven't tried them) has really bad SLOW handling of longtext records when utf-8 is enabled. (it might even be really bad with normal text). From reading stuff around the net, if you have the database in InnoDB mode you don't see this longtext slowdown.

Considering we don't need 4 trillion bytes of data stored for each e-mail's body and metadata, I changed the fields to mediumtext, a mere 16 million bytes, and voila! Speed has been resumed!

I bet a lot of the complaints about mailing notifications being slow are caused by this, the ones where they say they tried to switching on the cronjob without effect.

Anyway, if 2.0rc3 still has the database being set up to use longtext, I suggest switching the code to create mediumtext fields instead. (maybe even if modifying in the upgrade process?)

[atrol]

Thank you for posting your solution.
I also think this can help others with similiar problems.

I don't know whether the devleopers will have great interest in changing this.
Using PHP 4 and mySQL 4.1 will not be the typical environment for MantisBT 1.2
For example, when installing 1.2 you get a mesage that PHP 4 is not supported.
I didn't try, whether there is also a mySQL version check.

Nethertheless feel free to open a new issue in MantisBT own tracker.
Maybe one of the devleopers has an explaination why LONGTEXT is used, or agrees with changing to MEDIUMTEXT in 1.2

At first sight I agree with you, that MEDIUMTEXT should be enough.

[dylanc]

I'm not sure if this is a problem solely with mysql 4? I think the problem is improved (according to reports on the net) if you use the InnoDb backend for MySql (for 4 or 5) - maybe it has better support for resizing/inserting the longtext field type.

The version of php is irrelevant too. (I initially thought perhaps it was a php problem but it wasn't)

Looking on the net regarding longtext, they always suggest (regardless of v4 or v5) that you put longtext fields into their own separate table for speed, and by having a longtext in a regular table will cause huge slow down even for the other non-longtext fields.

I'll post a bug report regarding this.

[atrol]

I found another weakness
There is a primary key for email_id and an unnecessary index on it.
I don't know whether this has great impact, but by removing the index this should be a little bit faster than before.