|
From: SourceForge.net <no...@so...> - 2011-04-10 20:24:48
|
Bugs item #2967410, was opened at 2010-03-10 15:43 Message generated for change (Comment added) made by christian_boltz You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2967410&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Core Group: v 2.3 Status: Open Resolution: None Priority: 9 Private: No Submitted By: Milorad Vasilev (lory) Assigned to: Nobody/Anonymous (nobody) Summary: virtual list terribly slow with lots of mailboxes/aliases Initial Comment: I have 100068 mailboxes in mailbox table. when select this domain I see mysql select like this | 19443 | postfix | localhost:52787 | postfix | Query | 1 | Sorting result | SELECT mailbox.username FROM mailbox WHERE mailbox.domain='cablemail.bg' ORDER BY mailbox.username LIMIT 3180, 1 | after a while | 19443 | postfix | localhost:52787 | postfix | Query | 1 | Sorting result | SELECT mailbox.username FROM mailbox WHERE mailbox.domain='cablemail.bg' ORDER BY mailbox.username LIMIT 3190, 1 | and so like 100000, it is a long time. Domain with 250 mailboxes work fine. I think that is a problem with paging accounts and aliases. Regards: M.Vasilev ---------------------------------------------------------------------- >Comment By: Christian Boltz (christian_boltz) Date: 2011-04-10 22:24 Message: It took a bit ;-) longer, sorry for that. I just commited the last parts of the fast pagebrowser generation to SVN trunk r1034. I'm not sure if I will/can backport this to the 2.3 branch - it's a quite intrusive change... ---------------------------------------------------------------------- Comment By: Christian Boltz (christian_boltz) Date: 2010-09-28 01:48 Message: Good news: I have a working solution to make generating the page browser much faster. "much" means 300 times faster - it now needs 0.1s instead of 30s to generate an index with 500 pages of mailboxes. The trick is to let MySQL number the rows and just read the label fields of the relevant rows. That's much faster than doing a separate query for each row, which meant 1000 queries (500 * first + last row of each page). I'll finish it in the next days and then commit it to SVN trunk. That said: I'm afraid that the SQL I use won't work with postgres - but I hope that's a solvable problem ;-) GingerDog: Can you please test if the following queries work with postgres? SET @row=-1 SELECT * FROM (SELECT $idxfield AS label, @row := @row + 1 AS row FROM mailbox ) idx WHERE MOD(idx.row, 10) IN (0,9)" ---------------------------------------------------------------------- Comment By: Christian Boltz (christian_boltz) Date: 2010-05-19 00:24 Message: The problem (in get_domain_properties(), functions.inc.php) is that there's a loop querying every $entries_per_page'th entry (and its previous entry) to build the nice "aa-ax, ay-cu" page browser. Doing lots of (even small) queries is a guarantee for slowness. I guess it would be even faster to fetch all mailboxes into an array (one SELECT with a biiiig result) and loop over it on the PHP side - but that's not a good solution either. The number of queries can be cut down by 50% by using "LIMIT ($start_entry -1), 2" (read end of previous section and beginning of the new section in one SELECT - except for the very first "aa" query) without any function loss. Displaying more entries per page also reduces the number of queries, but I believe you that it still isn't fun with 100000 mailboxes ;-) (BTW: What's your $CONF('page_size') setting? How long does it take to load the page with the "nice", but slow pagebrowser?) ---------------------------------------------------------------------- Comment By: Milorad Vasilev (lory) Date: 2010-03-11 09:11 Message: Now I edit functions.inc.php and made thi changes 1. // if ( $list['alias_count'] > $page_size ) if ( 0 ) 2. // if ( $list['mailbox_count'] > $page_size ) if ( 0 ) Now I havn't mailbox index like this: :: Mailboxes aa-am am-bi bk-bt ce-dd di-dp ds-er es-gk gm-im im-it it-kd kd-ks kv-ma ma-mg mh-na na-no nt-pp pr-rm rr-rt rz-sk sk-ss sv-tl tm-vg vh-vl vm-vv vv-zo zs-zt But I can managed mailboxes and aliases. Lory. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2967410&group_id=191583 |
