The filter code has been improved over the last few months. I've measured 25-50% faster in my experiments. Would it be possible to retest this with a CVS image?

I did grab the latest version of view_all_bug_page.php, view_all_inc.php, and filter_api.php from CVS. What else would you recommend that I try?

I'd have a look on MySQL configuration. I know of a large institution using mantis that had problems with large number of issues and MySQL was the problem, not Mantis.

I have experience poor Mantis peformance in many circumstances - I finally noticed the very large number of queries executed to do thing like display a list of bugs. Rendering such a screen should take 10 queries or less, even if it needs to gather configuration, rights, etc. data from the DB.

In particular things like this exhibit the dreaded "N+1" problem:

63 0.0004 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='136' ORDER BY last_modified DESC
64 0.0005 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='135' ORDER BY last_modified DESC

and other things that are just silly, like running the same query 3 times:

7 0.0005 SELECT id FROM mantis_custom_field_table ORDER BY name ASC
8 0.0004 SELECT id FROM mantis_custom_field_table ORDER BY name ASC
9 0.0004 SELECT id FROM mantis_custom_field_table ORDER BY name ASC

I will sponsor this issue, we will happily kick in $50 for a version that doesn't do these things.

Can you retest this with 1.0.0rc5? As I said, there have been significant changes in the filters since 0.19.2.

yes; I had tested it with Mantis 1.0.0rc5. Here is a list of SQL queries from Mantis 1.0.0rc5; 82 queries to populate the "View Issues" screen.

1 0.0591 SELECT id FROM mantis_user_table WHERE cookie_string='e974aa6fca8cf495770a24c929245a8aa83d452d2c3b2ea07e7d948923d704a6'
2 0.0009 SELECT id FROM mantis_user_table WHERE cookie_string='e974aa6fca8cf495770a24c929245a8aa83d452d2c3b2ea07e7d948923d704a6'
3 0.0020 SELECT FROM mantis_user_pref_table WHERE user_id='2' AND project_id='0'
4 0.0010 SELECT FROM mantis_user_table WHERE id='2'
5 0.0010 SELECT FROM mantis_project_table WHERE id='1'
6 0.0014 SELECT config_id, user_id, project_id, type, value, access_reqd FROM mantis_config_table
7 0.0010 SELECT FROM mantis_filters_table WHERE id='0'
8 0.0009 SELECT id, name FROM mantis_custom_field_table ORDER BY name ASC
9 0.0015 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
10 0.0018 SELECT cft.id, cft.name, cfpt.sequence FROM mantis_custom_field_table cft, mantis_custom_field_project_table cfpt WHERE cfpt.project_id = 1 AND cft.id = cfpt.field_id ORDER BY sequence ASC, name ASC
11 0.0060 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=1 ) AND ( mantis_bug_table.status in (10, 20, 30, 40, 50, 80) )
12 0.0123 SELECT DISTINCT mantis_bug_table. FROM mantis_bug_table WHERE mantis_bug_table.id in (8, 9, 12, 13, 14, 17, 19, 20, 21, 23, 25, 28, 30, 31, 32, 33, 35, 40, 41, 42, 43, 46, 47, 48, 50, 52, 53, 57, 58, 59, 60, 63, 64, 65, 66, 68, 72, 78, 82, 84, 85, 87, 88, 89, 90, 91, 92, 95, 96, 97, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 124, 125, 126, 127, 128, 129, 131, 132, 133, 135, 136, 137, 138, 139, 140, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 155, 156, 157, 158, 159, 160, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 196, 198, 199, 200, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 214, 215, 216, 217, 218, 220, 221, 222, 223, 224, 225, 226, 227, 229, 230, 231, 232, 234, 237, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 269, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 282, 283, 284, 285, 286, 287, 288, 289, 290, 291, 292, 294, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 371, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 357, 358, 359, 360, 361, 362, 363, 417, 364, 365, 366, 367, 368, 369, 370, 372, 373, 374, 375, 376, 377, 378, 399, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 400, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 418, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 434, 435, 436, 437, 438, 439, 440, 441, 442, 443) ORDER BY sticky DESC, last_updated DESC, date_submitted DESC
13 0.0011 SELECT FROM mantis_project_table
14 0.0015 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
15 0.0012 SELECT FROM mantis_filters_table WHERE id='0'
16 0.0012 SELECT FROM mantis_filters_table WHERE id='0'
17 0.0011 SELECT id, name FROM mantis_custom_field_table ORDER BY name ASC
18 0.0014 SELECT cft.id, cft.name, cfpt.sequence FROM mantis_custom_field_table cft, mantis_custom_field_project_table cfpt WHERE cfpt.project_id = 1 AND cft.id = cfpt.field_id ORDER BY sequence ASC, name ASC
19 0.0025 SELECT FROM mantis_custom_field_table WHERE id='1'
20 0.0022 SELECT type, possible_values FROM mantis_custom_field_table WHERE id='1'
21 0.0220 SELECT mantis_custom_field_string_table.value FROM mantis_custom_field_string_table WHERE mantis_custom_field_string_table.field_id='1' GROUP BY mantis_custom_field_string_table.value
22 0.0085 SELECT FROM mantis_project_version_table WHERE project_id=1 ORDER BY date_order DESC
23 0.0021 SELECT FROM mantis_filters_table WHERE (project_id='1' OR project_id='0') AND name!='' ORDER BY is_public DESC, name ASC
24 0.0019 SELECT FROM mantis_filters_table WHERE id='0'
25 0.0016 SELECT id, name FROM mantis_custom_field_table ORDER BY name ASC
26 0.0049 SELECT FROM mantis_filters_table WHERE (project_id='1' OR project_id='0') AND name!='' ORDER BY is_public DESC, name ASC
27 0.0188 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='187' ORDER BY last_modified DESC
28 0.0039 SELECT FROM mantis_user_table WHERE id='5'
29 0.0064 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='443' ORDER BY last_modified DESC
30 0.0066 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='442' ORDER BY last_modified DESC
31 0.0064 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='227' ORDER BY last_modified DESC
32 0.0041 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='159' ORDER BY last_modified DESC
33 0.0008 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='157' ORDER BY last_modified DESC
34 0.0012 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='291' ORDER BY last_modified DESC
35 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='251' ORDER BY last_modified DESC
36 0.0010 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='120' ORDER BY last_modified DESC
37 0.0010 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='146' ORDER BY last_modified DESC
38 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='370' ORDER BY last_modified DESC
39 0.0012 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='426' ORDER BY last_modified DESC
40 0.0009 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='121' ORDER BY last_modified DESC
41 0.0015 SELECT FROM mantis_user_table WHERE id='3'
42 0.0008 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='176' ORDER BY last_modified DESC
43 0.0010 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='441' ORDER BY last_modified DESC
44 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='437' ORDER BY last_modified DESC
45 0.0021 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='440' ORDER BY last_modified DESC
46 0.0014 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='390' ORDER BY last_modified DESC
47 0.0012 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='432' ORDER BY last_modified DESC
48 0.0021 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='423' ORDER BY last_modified DESC
49 0.0008 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='439' ORDER BY last_modified DESC
50 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='438' ORDER BY last_modified DESC
51 0.0010 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='436' ORDER BY last_modified DESC
52 0.0010 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='435' ORDER BY last_modified DESC
53 0.0024 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='380' ORDER BY last_modified DESC
54 0.0031 SELECT FROM mantis_user_table WHERE id='4'
55 0.0018 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='433' ORDER BY last_modified DESC
56 0.0012 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='434' ORDER BY last_modified DESC
57 0.0014 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='431' ORDER BY last_modified DESC
58 0.0016 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='427' ORDER BY last_modified DESC
59 0.0016 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='430' ORDER BY last_modified DESC
60 0.0015 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='429' ORDER BY last_modified DESC
61 0.0008 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='428' ORDER BY last_modified DESC
62 0.0009 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='374' ORDER BY last_modified DESC
63 0.0008 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='420' ORDER BY last_modified DESC
64 0.0008 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='200' ORDER BY last_modified DESC
65 0.0012 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='113' ORDER BY last_modified DESC
66 0.0012 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='425' ORDER BY last_modified DESC
67 0.0012 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='414' ORDER BY last_modified DESC
68 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='359' ORDER BY last_modified DESC
69 0.0010 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='108' ORDER BY last_modified DESC
70 0.0015 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='85' ORDER BY last_modified DESC
71 0.0010 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='126' ORDER BY last_modified DESC
72 0.0013 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='119' ORDER BY last_modified DESC
73 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='279' ORDER BY last_modified DESC
74 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='110' ORDER BY last_modified DESC
75 0.0010 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='269' ORDER BY last_modified DESC
76 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='151' ORDER BY last_modified DESC
77 0.0012 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='152' ORDER BY last_modified DESC
78 0.0007 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='183' ORDER BY last_modified DESC
79 0.0011 SELECT last_modified FROM mantis_bugnote_table WHERE bug_id='262' ORDER BY last_modified DESC
80 0.0009 SELECT cft.id, cft.name, cfpt.sequence FROM mantis_custom_field_table cft, mantis_custom_field_project_table cfpt WHERE cfpt.project_id = 1 AND cft.id = cfpt.field_id ORDER BY sequence ASC, name ASC
81 0.0010 UPDATE mantis_user_table SET last_visit= '2006-01-19 14:16:26' WHERE id='2'
82 0.0020 SELECT * FROM mantis_user_table WHERE id='2'

I also noticed, at the bottom of this very page, that it 1.0.0rc5 (running here) uses 53 queries to get the data for this bug detail page. Wow.

The many references to bugnote_table is fixed in 1.1 (CVS HEAD). The multiple reads of the custom_field table needs to be fixed.

I am happy to hear the worst of it (the N+1 fetch of that field) is fixed. I will continue to sponsor any effort towards fixing the rest of the superflous queries.

Could you note which files were modified for the bugnote_table fix (describe in Note 0011978)? I tried examining the nightly tarball, but there don't appear to have been any changes since the release of 1.0.0rc5. Thanks!

fix for custom field caching submitted to CVS

core/custom_field_api.php -> 1.58

I've written a query which eliminates query 12 and queries 29-79 (with the exception of the mantis_user_table queries). This can be further modified to return all of this data in one query, but I lost motivation once this was complete.

I'll post the entire thing here in the hopes that someone more familiar with the Mantis structure can incorporate it. Note that this was written using the query output, so will probably need heavy updating.

SELECT DISTINCT mantis_bug_table.*, count(mantis_bugnote_table.id) as num_notes
FROM mantis_bug_table
LEFT JOIN mantis_bugnote_table
ON mantis_bug_table.id = mantis_bugnote_table.bug_id
WHERE mantis_bug_table.id
in (
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.status in (
10, 20, 30, 50, 60, 80, 85
)
)
)
GROUP BY mantis_bug_table.id
ORDER BY sticky DESC, last_updated DESC, date_submitted DESC

Hi all,

Looking at the above:
Mantis 0.19.2
75 total queries executed.
63 unique queries executed.

And comparing to the bugs.mantisbt.org on 50 queries:
Mantis 1.1.0a3-CVS[^]
41 total queries executed.
34 unique queries executed.

kylecordes: how do you find 1.1.0a3 ?

Paul

Do we still have issues to fix with the 1.1.0a3?

I am having a similar performance problem with the "View Issues" page (not any other pages). Consistent timings of up to 21sec to load page, however SQL queries only appear to take up a small portion of that total time:

Time: 11.468594 seconds.
226 total queries executed.
89 unique queries executed.
...
0.1451 (sql time)

We only have 120 bugs!

System info:
Linux server
MySQL v4.1.11
PHP v4.3.9
Mantis v1.0.8
LDAP enabled
http compress enabled
persistent db connections enabled
only displaying basic fiels in view (i.e. not bugnote count)
default of 50 bugs per page

Please help, my users are threatening to go back to a paper-based system!

I have changed the following setting in config_inc.php

OLD
$g_use_ldap_email = ON;

NEW
$g_use_ldap_email = OFF;

We are using LDAP for authentication. I do not understand why this would affect performance, but now our View Issues page loads in under 1 sec! I have concerns about new users, having read posts that this setting provides a confirmation email and the ability to log into the system.

I have had to do the same thing regarding the LDAP setting. I was activating LDAP on our development box today and when it has email=ON it slows to a crawl. With it OFF it runs like a champ.

Option 1:
I am expecting that Mantis does the LDAP queries to include the user email link on the View Issues page. What about doing the following setting:

This specifies the access level that is needed to get the mailto: links.

$g_show_user_email_threshold = NOBODY;

Option 2:
I've added a diff file (ldap_email.diff) and I would interested to know how much savings it provides. This doesn't avoid the ldap queries, but it doesn't do multiple queries for the same user.

To Do:

1. We currently have a debugging mode where we should the SQL queries that are done by a page, we should add to that the LDAP queries and their times.
2. Implement LDAP caching in ldap_api.php, this will make sure that an LDAP query is not issued multiple times in a single page request.
3. Consider update the user's email from LDAP (maybe on login) and then from then on use the internal email address and hence avoid LDAP queries all together. This means that if a user's email address changes in LDAP (rare), then this won't be reflected until they login into Mantis. We will need to handle the case where a user has remember login ON or the browser open for a very long time. Ideas for when to update the email address are welcome.

Since adding view_user_page.php I think the LDAP stuff won't be causing too much grief.

I'd be interesting to know if i'm right on this, i.e. ldap_email can be set to ON now.

If i'm correct, I think this issue can probably be marked as resolved.

Paul

Option 1: Try to use mysql 5.x and then optimize my.ini for myISAM not innoDB
Option 2: Keep mysql 4.x but still optimize my.ini
Option 3: THIS WILL NOT ADDRESS ROOT CAUSE BUT MAY BE GOOD WORKAROUND. Use phpmyadmin or your favorite mysql gui tool and do this.. Charge Storage Engine type for the folowing tables:

mantis_bugnote_table
mantis_custom_field_table
mantis_project_table
mantis_filters_table
mantis_user_table

Do one by one and test.. NOTE: I do not know what effect this will have on overall mantis. I dont think it will do anything since the engine type is controlled on the mysql db program level and does NOT effect connection type ect in php. Will there be a relational effect? I dont know. Backup and test.. if problem restore.

BACKUP
mysqldump -a -u <your username> -p<your password> gsetracking > C:\MySQL\Backups\mysqlbackup_XXXX-XX-XX.sql

RESTORE
mysql -u <your username> -p<your password> gsetracking < C:\MySQL\Backups\mysqlbackup_XXXX-XX-XX.sql

NOTE: In windows there is NO space between the -p switch and the password. Dont know what is up with that. I think in NIX this is not a problem.

ANYWAY, 226 total queries executed. 89 unique queries executed. 11 seconds.. HOLY COW!!!.. Using version 1.1.4 with mysql 5.x on php 5.x, I have a 40 row view all page.. I have 17 columns of data most of which are custom feilds.. I have moded filters to include searches for custom feilds which increaes the query overhead slightly.. I currently get the following:

Time: 1.221699 seconds.
540 total queries executed.
252 unique queries executed.

This is on a slow core 2 e2140 (like 1.6 ghz x 2) with 2 gig ram test box with vista (not even server 2008 which is roughly 30% faster kernel). My time is slightly better using my IIS7 test server versus apache 2.x.. Yes IIS7 is FINALLY FASTER than apache. I am supprised as well. Has to do with compression and a well tuned fast-cgi which beats apache now.

PROBLEM BACKGROUND. See http://www.mantisbt.org/bugs/view.php?id=9876 ..Basicaly I resolved a simular performance issue that made my page load time 25 times faster.. had 2000 projects grouped into 8 subprojects.. load times went from 30 seconds to 1.x seconds on a 40 row view all page. I dont have an explaination other than innodb is known to provide faster performance for many items.

P.S. If you have a dual core processor on your sever.. make sure to check affinity to see if both CPUs are used when php is exacuted.

Sorry - I forgot to tell you the storage engine type for option 3.

"Charge Storage Engine type for the folowing tables.. to INNODB</>:

tmatijas: I'm somewhat surprised, as my understanding with myisam was slightly faster than innodb. What optimisation/changes had been made to my.cnf ?

regarding IIS vs apache - with the optimisations MS have done to php/fastcgi, I'm not sure i'm necessarily surprised at this.

540 total queries executed.
252 unique queries executed

Is that rnning 1.1 ? I wouldn't expect to be seeing those sorts of query numbers in 1.2.x alphas. If you are, i'd be interested to know on what page.

Paul

Hi grangeway. I am not a mysql expert. But my undertsanding is that for small amounts of data, INNODB is faster than MYISAM by 6 to 9%.. With large transactions, MyISAM is supposed to be better. However, for some reason and I do not know why, in my test when i had the 2000 projects ungrouped into subprojects VERSUS 2000 subprojects that are now grouped into 8 projects; the difference was night and day... 30 second load time with myISAM versus 2 seconds with INNODB. I am still unclear as to why. Perhaps it had to do with the ">>" character in subprojects. I applied a fix for the strang characters with amphersands (&). This uses a nonstandard character. I read somewhere about problems in myISAM with nonstandard "foreign" characters. Other than that, I cannot explain why this is.. But i am confident that it is replicateable using info in http://www.mantisbt.org/bugs/view.php?id=9876

Right now I am developing around 1.1.4 since there is something about 1.2x that I dont like. I do have test versions for 1.1.1, 1.1.4, 1.1.5 and 1.2.x that I have been experimenting with.

The reason so many queries I think is because

1. I applied a filter modification that was designed for earlier mantis to 1.1.4 which makes you able to filter custom feilds for searching content. This was left out of mantis 1.1x and 1.2x all togther. I dont know why. THis is the coolest thing and many users have complined about it in the past so I would urge the development into at least discussing if it is worth putting in. See http://www.mantisbt.org/bugs/view.php?id=6325 and download the version I made for 1.1.4

2. You are more of an expert than I am so please correct me if I am wrong... In your view issues page on THIS mantisbt.com, you have 10 rows of feilds. In mine, I have 17 and most are custom feilds. For every collumn you are adding queries from my understanding.. Also, same applies for every row you are adding queries (as long as he feilds are populated with some kind of data). Right now I have 40 rows and 17 colums in my default veiw defined in config_inc.php.

Essentially ROWS x COLUMNS = TOTAL QUERIES.

I think this is the way it works. I also understand that this changes with optimizations in the mantis core that I guess groups query data into some kind of array - I am not sure how this works but I do know that the community has come up with ways to reduce the load on sql by doing less queries.

Question: Lets say I want to optimize queries and continue to use 1.1.x which is my real choice at this point until the dev team (and community) fixes all the problems with 1.2.x.. What code (or files) from 1.2.x in terms of optimization code for queries can I try to merge/replace in 1.1.4 to make this more efficient.

With my dual core test server (which is really my desktop), 1.22 seconds to veiw 40 rows is considered acceptable. However, ON the final server, I am running a pair of xeons of older design and they are slower and I will run on apache web server NOT IIS which will also run a little slower.. 1.2 seconds will probably become 4 seconds under normal load which is what I expect all the time since the end users will be realtivly small group with no anonymous transactions or viewings. Perhaps using mantis 1.2x optimizaions is actually a very good idea in any case.

As for my.ini.. I mainly tweaked it for use with myisam and not even innodb per recommendations on www.mysqlperformanceblog.com .. Main ones include the following:

default-storage-engine=MyISAM
query_cache_size=64
table_cache=4096
key_buffer_size=300M

At some point I tried to juice BOTH myisam and innodb performance since I am using a mixed environment that uses both.. I got realy good results with the following:

default-storage-engine=MyISAM
query_cache_size=64
table_cache=4096
tmp_table_size=102M
thread_cache_size=16
key_buffer_size=300M
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
innodb_buffer_pool_size=640M
innodb_log_file_size=10M

Many of these depend on how much system memory you have. www.mysqlperformanceblog.com gives EXCELLENT explainations. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

I'm going to try and do some testing with your structure - against 1.2.

i.e. a large number of projects, a large number of subprojects and a large number of custom fields.
Quick question: how many users/bugs are we talking about ?

If you don't mind pasting it, record counts from /admin/db_stats.php would be interesting to see.

I've just assigned 0006325 to me to remind me to take a look at it.

And final question, given that 1.2 will replace 1.1, 'there is something about 1.2x that I dont like' -> i'm just wondering what you don't like about the direction 1.2 is heading

Paul

ABOUT THE DB STATS:
Hi Paul. Please give me a little time to produce what you need. The 2000 projects was just a test against scalability just because I was curious what this system can do. I no longer have them inputted into the database and only have 200 projects with about 50 test bug reports currently. However, I will reproduce what you need and get you the results.

ABOUT VERSION 1.2
Well, its not so much that I dont like or dont agree with the direction but I have gotten used to 1.1.4.. I like using plug-in manager by the French guy (deboutv) who runs the site. It does not work in 1.2 (maybe it does but I know in talking to him that he doesn’t officially support it yet).. I understand that mantis dev team has started making their own plug-in system but I do enjoy the other one. Next, customizations... On the one hand it is nice for end-users to be able to customize their environment (such as view all page) but on the other hand it takes away from the system admins ability to control what the end user sees and how the info is presented. This should at least be controlled with permissions if it isnt already. The interface for changing it should be improved as well. maybe use of drop downs or check boxes instead of a laundry list of text would improve the feel a bit. Next, too many interfaces that look like thet are not controlled by authentic mantis code such as mantweet, wki etc.. Maybe they were written by the mantis team but from an asthetic standpint they dont use the same CSS design and look like they were just thrown in. (BTW -dont like the use of gravatar. It seems it would have been just as easy to drop gravatar and use your own pic upload system) Also, there seems to be some bugs in 1.2 that are not in 1.1.x... This is notmal when taking on such a huge overhaul but I feel 1.1.x is still a bit more stable although this is objective and I may be wrong. Lastly, it seems that the core was changed so much in 1.2 that it would be difficult for someone with a trully custom build of 1.1x with MANY of their own customization to port changes to the new build since doing an automated upgrade may be out of the question.

One thing you guys are missing is a real-time chatting system. I have integrated the one by crafty which is an excellent solution. It allows you to have a live operator that can answer questions.. As you know, there are a ton of open source GPL version... Would probably be very easy for you guys to integrate one of your own design I’m sure. From what I can see, mantis is one of the most well designed open source php projects I have come across. You guys have put a lot of thought into the engineering of global controls ect. and I am thoroughly impressed. You guys get a 10 out of 10 in my book. As soon as I can, I am going to start donating a few bucks here and there to you guys to help support the effort.

Paul I do have an off topic question. Is there some sort of backdoor alerting system that notifies you of every implementation of mantis (built into mail_api perhaps)? Funny thing happened.. I am not yet complete with my project and my mantis builds are preproduction. I have not published this site officially and only have 12 test accounts and only 1 (mine) is in use. However, someone from mantistracker@yahoo.com who calls himself clipper12 has tried to sign up for an account. I traced his IP to Romania... 79.117.11.115 I then did a Google search using keywords "mantis clipper12" and what do you know - there are countless versions of mantis all over the place in many countries who have a registered user using that clipper12 name. Try yourself: http://www.google.com/search?hl=en&rls=com.microsoft:*&q=mantis+clipper12&start=0&sa=N

I really don’t care all that much as we have nothing to hide.. but this seems a little suspicious and I am not sure I want management members of our team accidentally giving permissions that they should not give to this person. If you want to address this personally, please send me an email at portcharlottedisposal - AT - hotmail.com. Please let me know.

Best,
Tom

P.S. One more thing about having a ton of projects.. The project management page lists all projects in an array. I had to set the timeouts on php and mysql higher to finish loading the page when I was running the 2000 project test. Perhhaps breaking it down alphabetically like in user mangament would be a worthwhile effort for those with lots of projects. This is particularly helpfull for developers who develop plugins for like photoshop ect. It is not uncommon for them to have 20 or 30 open developments at one time. I bet you can rehash much of the same code from user management to make it so. I will see what I can slap together as well as soon as I get some free time. My php kindoff sucks but I have some degree of skill.

Ok thanks tom - regarding the other points, I'll move to email as it's gone off topic.

Regarding notifications - afaik, we have no way of knowing where mantis is installed, so no.

Paul

Sound good Paul. Please feel free to email me any time. As for clipper12, I think I might know what is going on.. but it is just a hunch. Will discuss in email. Best, Tom

Hi paul - I have the results.. keep in mind I only have about 50 bug report tickets give or take in both tests:

-- db_stats.php on 2000 projects NOT grouped into subprojects:
mantis_bug_file_table = 33 records
mantis_bug_history_table = 567 records
mantis_bug_monitor_table = 1 records
mantis_bug_relationship_table = 0 records
mantis_bug_table = 39 records
mantis_bug_text_table = 39 records
mantis_bugnote_table = 10 records
mantis_bugnote_text_table = 10 records
mantis_config_table = 5 records
mantis_custom_field_project_table = 4000 records
mantis_custom_field_string_table = 390 records
mantis_custom_field_table = 10 records
mantis_filters_table = 45 records
mantis_news_table = 2 records
mantis_project_category_table = 3208 records
mantis_project_file_table = 0 records
mantis_project_hierarchy_table = 0 records
mantis_project_table = 1933 records
mantis_project_user_list_table = 24 records
mantis_project_version_table = 0 records
mantis_sponsorship_table = 0 records
mantis_tokens_table = 0 records
mantis_user_pref_table = 18 records
mantis_user_print_pref_table = 0 records
mantis_user_profile_table = 37 records
mantis_user_table = 18 records

-- db_stats.php on 2000 projects GROUPED into 8 subprojects:
Rather than post this again.. All items are identical to the one above EXCEPT mantis_project_hierarchy_table = 1924 records

For these time tests below, i am only changing innodb for mantis_project_hierarchy_table ONLY.. I have not bothered to retweek my.ini for performance either. I could probbaly gain 30% boost overall performace if I wanted to which would match what I reported earlier. However this is not nessesary for testing purposes and I will eliminate those tweaking variables. In any case, these tests are for 2000 projects grouped into 8 subprojects
Note: You can get better performance with no grouping no matter what! You need not change hierarchy table at all since it is subgrouping which cases this severe latency.

Load time - news page with myisam results (HOLY COW!):
Time: 26.404002 seconds.
17 total queries executed.
14 unique queries executed.

Load time - news page with innodb results:
Time: 4.261364 seconds.
17 total queries executed.
14 unique queries executed.

Load time - view issues page with myisam (20 rows of data):
Time: 1.200140 seconds ( 4.28% ) for begin loop -to- end loop
Time: 0.344095 seconds ( 1.23% ) for end loop -to- END
Time: 28.062049 seconds.
540 total queries executed.
264 unique queries executed.

Load time - view issues page with myisam (40 rows of data):
Time: 2.388752 seconds ( 8.28% ) for begin loop -to- end loop
Time: 0.353073 seconds ( 1.22% ) for end loop -to- END
Time: 28.834147 seconds.
996 total queries executed.
450 unique queries executed.

Load time - view issues page with innodb (20 rows of data):
Time: 1.240801 seconds ( 22.04% ) for begin loop -to- end loop
Time: 0.357455 seconds ( 6.35% ) for end loop -to- END
Time: 5.628865 seconds.
540 total queries executed.
264 unique queries executed.

Load time - view issues page with innodb (40 rows of data):
Time: 2.455530 seconds ( 35.55% ) for begin loop -to- end loop
Time: 0.345036 seconds ( 5.00% ) for end loop -to- END
Time: 6.906781 seconds.
996 total queries executed.
450 unique queries executed.

Load time - view issues page with myisam (default of 10 tickets shown):
Time: 0.024095 seconds ( 0.09% ) for begin loop -to- end loop
Time: 0.042923 seconds ( 0.16% ) for end loop -to- begin loop
Time: 0.094375 seconds ( 0.35% ) for begin loop -to- end loop
Time: 0.042012 seconds ( 0.16% ) for end loop -to- begin loop
Time: 0.094962 seconds ( 0.35% ) for begin loop -to- end loop
Time: 0.044990 seconds ( 0.17% ) for end loop -to- begin loop
Time: 0.085991 seconds ( 0.32% ) for begin loop -to- end loop
Time: 0.033733 seconds ( 0.13% ) for end loop -to- begin loop
Time: 0.000062 seconds ( 0.00% ) for begin loop -to- end loop
Time: 0.336224 seconds ( 1.25% ) for end loop -to- END
Time: 26.842785 seconds.
49 total queries executed.
43 unique queries executed.

Load time - view issues page with innodb (default of 10 tickets shown):
Time: 0.024715 seconds ( 0.51% ) for begin loop -to- end loop
Time: 0.038423 seconds ( 0.79% ) for end loop -to- begin loop
Time: 0.073372 seconds ( 1.52% ) for begin loop -to- end loop
Time: 0.037423 seconds ( 0.77% ) for end loop -to- begin loop
Time: 0.096674 seconds ( 2.00% ) for begin loop -to- end loop
Time: 0.037443 seconds ( 0.77% ) for end loop -to- begin loop
Time: 0.078073 seconds ( 1.62% ) for begin loop -to- end loop
Time: 0.027897 seconds ( 0.58% ) for end loop -to- begin loop
Time: 0.000040 seconds ( 0.00% ) for begin loop -to- end loop
Time: 0.313023 seconds ( 6.48% ) for end loop -to- END
Time: 4.833343 seconds.
49 total queries executed.
43 unique queries executed.

NOTE: On a hunch, I changed Collation from default which is latin1 swedish to utf8 general with no significant change in performance.

OK THIS IS WIERD.. I TESTED FURTHER AND CHANGED HIERARCHY TABLE TO "MRG_MYISAM" IN PHPMYADMIN.. CHECK THIS OUT - EVEN BETTER PERFORMANCE THAN WITH INNODB BY ROUGHLY 25%!!! SIMULAR RESULTS CHANGING TO "MEMORY" AS WELL. NOTE: I think MRG_MYISAM makes a table read only where as MEMORY does not but as you would expect is volitile.

Load time - news page with myisam results
Time: 3.307722 seconds.
17 total queries executed.
14 unique queries executed.

Load time - view issues page with MRG_myisam (20 rows of data):
Time: 1.223681 seconds ( 34.71% ) for begin loop -to- end loop
Time: 1.624360 seconds ( 46.07% ) for end loop -to- END
Time: 3.525725 seconds.
540 total queries executed.
264 unique queries executed.

Load time - view issues page with MRG_myisam (40 rows of data):
Time: 2.348007 seconds ( 50.45% ) for begin loop -to- end loop
Time: 1.626912 seconds ( 34.96% ) for end loop -to- END
Time: 4.653895 seconds.
996 total queries executed.
450 unique queries executed.

Load time - view issues page with MRG_MYISAM (default of 10 tickets shown):
Time: 0.024092 seconds ( 0.62% ) for begin loop -to- end loop
Time: 0.041207 seconds ( 1.07% ) for end loop -to- begin loop
Time: 0.068654 seconds ( 1.78% ) for begin loop -to- end loop
Time: 0.039443 seconds ( 1.02% ) for end loop -to- begin loop
Time: 0.082429 seconds ( 2.14% ) for begin loop -to- end loop
Time: 0.037953 seconds ( 0.98% ) for end loop -to- begin loop
Time: 0.069796 seconds ( 1.81% ) for begin loop -to- end loop
Time: 0.026341 seconds ( 0.68% ) for end loop -to- begin loop
Time: 0.000041 seconds ( 0.00% ) for begin loop -to- end loop
Time: 1.507235 seconds ( 39.06% ) for end loop -to- END
Time: 3.858745 seconds.
49 total queries executed.
43 unique queries executed.

Let me know if you need more results.. I would be happy to oblidge.

So what can this be? Why would default MyISAM suffer so much in terms of performace while MRG_MyISAM, INNODB, and MEMORY does not. What other impact is this having on mantis. Can mantis performance under normal cirumstances (without 2000 projects grouped )perform better if you guys set the default to MEMORY, INNODB, or MRG_MyISAM? Many questions.. This is an interesting one that could be beneficial to the project. Who the heck doesnt want a better FASTER mantis?

P.S. Since manage projects has no grouping by alphbetical character simular to user accounts, load time is poor. HOWEVER, as you would expect from the above results, perfomance is increased when hierarchy is set to mrg_myisam versus innodb and myisam:

• Manage Projects Page.. projects ARE grouped!

• mantis_project_hierarchy_table=mrg_myisam..
  Time: 106.011839 seconds.
  1951 total queries executed.
  16 unique queries executed.

• Manage Projects Page.. projects NOT grouped!

• mantis_project_hierarchy_table=mrg_myisam..
  Time: 117.922159 seconds.
  1951 total queries executed.
  16 unique queries executed.

INTERESTING!!! I changed mantis_project_table to INNODB.. What do you know.. 25% BETTER FORFORMANCE AGAIN! See result below

• Manage Projects Page.. projects NOT grouped!
• mantis_project_hierarchy_table=innodb..
• mantis_project_table=innodb..
  Time: 88.913836 seconds ( 100.00% ) for START -to- END
  Time: 88.913836 seconds.
  1960 total queries executed.
  20 unique queries executed.

Strage.. This is an oddity.. when subprojects are enabled but you change hierarchy and project tables to both INNODB.. You would expect better results right? No, the project managment page times out.. Same result if you change hieracrchy to memory but leave project table at innodb.

NOTE: changing mantis_project_table type to MRG_MYISAM will delete all data in that table only!! Changing to Memory will give you an error "# 1163 - The used table type doesn't support BLOB/TEXT columns" BUT it does not delete the table data.

I believe the issues covered in this bug report are mainly fixed in git trunk i.e. (ldap +lots of bugnote queries).

There's a seperate issue tracking how we handle large number of projects

If there's any issues remaining in git trunk, please open a new issue covering those specific problems.

Paul