Hi,
one note before you continue reading: I'm not familiar with xdebug yet
- so you may need to
re-verify me findings. also this mail is not a wish to immediately fix some performance-lag, but more some info to the developers/project managers so they can plan correctly :)
because I was interested.. I installed xdebug today [1] so.. I was cleaning up my mailbox and I deleted 200 messages (at once) according to firebug this took ~60 seconds which is quite much...
then I did some tests... after checking xdebug's profiler output I found something strange: there were 745 queries made by rcube_mdb2->query (which calls _query and this calls the real MDB2 stuff...) 745 queries - and I just profiled the click on the delete button (NOTE: the at this point the messages were in folder X and now they were moved to "Trash")
NOTE: there was one huge call to rcube_imap->set_flag() which is because I have $rcmail_config['read_when_deleted'] = TRUE; in my config maybe this can also be optimized (not sure if it can be - but this should have a lower prio) also read_when_deleted s a real performance-killer, I'll try to explain later
small break here: as I had message caching enabled I wanted to check if really all messages were in my (MySQL) DB.. looked fine after like 10 seconds (deleting was still being done by roundcube) I refreshed the phpMyAdmin page... and what was that? now there were less messages in the table than before.. - even if they were just moved to "Trash" now.. I waited some seconds - and the row count started to rise again
let's try to explain so many database queries (NOTE: keep in mind I had read_when_deleted enabled): when user deletes message 1 from mailbox X the following happens:
- $IMAP->set_flag($uids, 'SEEN'); reads and deletes all messages from
the caching table... that already makes 400 queries
- (message being moved through IMAP)
- (message being parsed through IMAP)
- message being inserted into the cache table (in the DB) again - that
makes another 200 queries -> we're at a total of 600 now
plus some others (had no time yet to fully check this)
now.. I'm pretty sure that set_flag kills so much performance because it removes the messages from the cache. wouldn't it be easier to just update the header column in the database? (ok, I see it's needed that you unserialize the field.. change some value, then serialize it again and I'm not sure if that costs even more time) also move_message() should do the same, currently it just gets the mail from the caching table and removes it from there (if it exists) - later it has to be inserted into the DB again
I may do some more tests later.. feel free to suggest other parts which I could profile :)
Regards, Martin
[1] http://www.xdebug.org/docs/ _______________________________________________ List info: http://lists.roundcube.net/dev/
darklight wrote:
let's try to explain so many database queries (NOTE: keep in mind I had read_when_deleted enabled): when user deletes message 1 from mailbox X the following happens:
- $IMAP->set_flag($uids, 'SEEN'); reads and deletes all messages from
the caching table... that already makes 400 queries
- (message being moved through IMAP)
- (message being parsed through IMAP)
- message being inserted into the cache table (in the DB) again - that
makes another 200 queries -> we're at a total of 600 now
So, it kills performance only when caching is enabled. Did you test with current svn-trunk? There were some changes lately. I'll investigate this a little bit.
Hi,
So, it kills performance only when caching is enabled. Did you test with current svn-trunk? There were some changes lately. I'll investigate this a little bit.
first: thanks for you reply :) yes, I did test with latest trunk (but I must admit I'm using some patches... the only one that _could_ affect caching performance is the message threading patch by Chris January - but in the folder where I deleted the messages I had threading disabled)
Regards, Martin _______________________________________________ List info: http://lists.roundcube.net/dev/
Hi,
thanks for your changes, not the request only takes 30 seconds simply displaying the messages also takes 30 seconds - so you improved the performance heavily!
big thanks for this
PS: if I have more test results (from other parts) I'll post them here on the mailing list
Regards, Martin _______________________________________________ List info: http://lists.roundcube.net/dev/
darklight wrote:
thanks for your changes, not the request only takes 30 seconds simply displaying the messages also takes 30 seconds - so you improved the performance heavily!
Please test also r2674. I see there's at least one place for improvement. When message is added to cache then SELECT and INSERT/UPDATE are executed. I think there're situations when we could skip SELECT query. This should speed up messages and messages list displaying. I'll investigate it later.
Hi,
Please test also r2674. I see there's at least one place for improvement. When message is added to cache then SELECT and INSERT/UPDATE are executed. I think there're situations when we could skip SELECT query. This should speed up messages and messages list displaying. I'll investigate it later.
deleting 200 messages now takes ~0.7 seconds which is a great improvment! (before it took ~30 seconds)
with r2674 displaying the message list is also faster... 5 seconds improvment for uncached messages, and like 10 to 15 seconds improvment for cached messages
huge thanks so far please let me know if I can test more code :)
Regards, Martin _______________________________________________ List info: http://lists.roundcube.net/dev/
darklight wrote:
huge thanks so far please let me know if I can test more code :)
Please test next set of fixes in r2676.
Hi,
Please test next set of fixes in r2676.
very nice, that speeds up (uncached) folder listening by 10 seconds (when having "max. mails per page" set to 200) performance for cached folders is almost the same (~0.5 seconds faster
- but this
could also have other reasons)
there was one problem though: I first had to 'DELETE FROM messages' first, before I couldn't get roundcube to display all folders correctly (it always said that some folders were empty, even if they weren't...) but empty'ing that table seems to have fixed it
huge thanks so far
Regards, Martin _______________________________________________ List info: http://lists.roundcube.net/dev/
-
A.L.E.C -
darklight