#185 virtual list terribly slow with lots of mailboxes/aliases

v_2.3
closed-fixed
nobody
Core (82)
9
2014-01-05
2010-03-10
No

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

Discussion

  • Milorad Vasilev

    Milorad Vasilev - 2010-03-10
    • priority: 5 --> 9
    • status: open --> open-wont-fix
     
  • Milorad Vasilev

    Milorad Vasilev - 2010-03-11

    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.

     
  • Christian Boltz

    Christian Boltz - 2010-05-18

    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?)

     
  • Christian Boltz

    Christian Boltz - 2010-05-18
    • summary: virtual list --> virtual list terribly slow with lots of mailboxes/aliases
    • status: open-wont-fix --> open
     
  • Christian Boltz

    Christian Boltz - 2010-09-27

    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)"

     
  • Christian Boltz

    Christian Boltz - 2011-04-10

    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...

     
  • Christian Boltz

    Christian Boltz - 2011-04-10

    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...

     
  • Christian Boltz

    Christian Boltz - 2011-04-10
    • status: open --> closed-fixed
     
  • Kevin in Burlington

    It still seems to be affecting the current 2.3 branch. The changes to functions.inc.php take care of the symptoms. Would it be possible to add a config variable to disable this feature in config.local.php?

     
  • Christian Boltz

    Christian Boltz - 2014-01-05

    There is already a config option - $CONF[page_size] ;-)

    I don't have any plans to add a workaround (or backport the code from trunk) to 2.3.x, sorry.

    That said, feel free to upgrade to SVN trunk (which we'll release as 3.0 beta in about two weeks) which has this problem fixed, and some more ;-)

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks