Speed Problem

leonh
2008-10-09
2013-01-23
  • leonh

    leonh - 2008-10-09

    Hi

    I have problem with speed of postfixadmin.
    I create virtual domain with about 10000 mailboxes, when i add or change mailbox in this domain must waitng very long time, also when i click on list domains, same situation (76 domains in postfixadmin).

    any solution?

     
    • GingerDog

      GingerDog - 2008-10-11

      I don't know why you'd see a problem when adding or changing a mailbox - that should be very quick - and 10000 mailboxes isn't all that many records (at least from a MySQL perspective).

      If possible, could you give me more information on your setup, and while you're waiting for "it" to do stuff could you look at the MySQL Processlist to see whether anything interesting stands out?

       
      • leonh

        leonh - 2008-10-14

        HI

        Here is information of my setup:

        I run 2 vserver machines on 1 physical server
        1. mysql server
        2. php, www, and mail

        Distro is Debian.

        If i send same query to mysql from phpmyadmin, i recive response and result very quick.
        So i think problem is with phpmyadmin functions speed.

        Regards

         
    • leonh

      leonh - 2008-10-14

      sorry postfixadmin functions :)

       
    • Gain Presence

      Gain Presence - 2009-05-06

      I am experiencing the same issues with newer Postfixadmin (>2) and 13000 mailboxes, it takes more than 20 minutes to display the domain list or virtuals list. Though asking Mysql directly returns the results immediately.

      Is this a problem with the new sorting? Why is it so slow selecting the 3 rows from the domain table?

      The older versions of postfixadmin were super quick, and the current stable and beta are quick until I add all of my mailboxes.

       
      • Christian Boltz

        Christian Boltz - 2009-05-06

        > Why is it so slow selecting the 3 rows from the domain table?

        Well, the code displaying the domain list isn't that simple ;-)
        You may have noticed that it includes the number of mailboxes and aliases each domain has - and querying these numbers has to be done.

        Having that said: Of course it isn't acceptable that postfixadmin needs 20 minutes to load a page, and it is obvious that there's something wrong with the way how this information is gathered.

        To find out what we have to optimize, can you please test a small change?
        Open list-domain.php in an editor, search for get_domain_properties (around line 57, it only appears once in this file) and replace the line with
            $domain_properties[$i] = array();

        You'll probably end up with lots of "undefined index XY" PHP warnings and won't see how many aliases, mailboxes etc. the domain has or may have. Just ignore all this and tell me if the page loads faster ;-)

        From reading the code in get_domain_properties() (see functions.inc.php), I see lots of potential optimization. But before I start to change anything, I'd like to know if I'm working at the right place.

         
    • Gain Presence

      Gain Presence - 2009-05-07

      Thanks for the reply Christian, I made the change you suggested in list-domain.php and the page now loads instantly, so I think you're on the right track.

      I'll also take a look at the get_domain_properties function and see if I can do anything with it (I'm a Perl guy, but PHP seems similar enough).

       
      • Christian Boltz

        Christian Boltz - 2009-05-08

        To give a short summary about get_domain_properties(): It does lots of database queries, and many of them are not relevant for list-domains.php.

        The solution is probably to write one "big" SQL query that fetches all needed information in one run. This should speed up list-domains extremely.

        I tried a bit. A query that does half of the job is:

        select domain*, count(alias.goto) as alias_count /*, count(mailbox.username) as mailbox_count */
        from domain
        left join alias on domain.domain = alias.domain
        /* left join mailbox on domain.domain = mailbox.domain */
        group by domain.domain

        This query lists the number of aliases for all domains.

        The perfect query would include mailbox and alias count in a single query. Unfortunately if I remove the comment marks from the above query, the result of count() is always the sum of mailboxes and aliases.

        (If any SQL expert can do the job in a single query: please speak up!)

         
        • Christian Boltz

          Christian Boltz - 2009-05-10

          I finally found the perfect query ;-)  - DISTINCT is the keyword that was missing before.

          SELECT domain . * , COUNT( DISTINCT alias.address ) AS alias_count, COUNT( DISTINCT mailbox.username )  AS mailbox_count
          FROM domain
          LEFT JOIN alias ON domain.domain = alias.domain
          LEFT JOIN mailbox ON domain.domain = mailbox.domain
          GROUP BY domain.domain
          ORDER BY domain.domain

          This query works in MySQL, including correct results ;-)

          Can someone test it with PostgreSQL, please? (Note: The number of aliases displayed in postfixadmin is alias_count-mailbox_count. Please keep this in mind when comparing the results.)

           
    • GingerDog

      GingerDog - 2009-05-11

      Postfix isn't happy with the above...

      and will complain with :

      "ERROR:  column "domain.description" must appear in the GROUP BY clause or be used in an aggregate function"

      But this works :

      SELECT domain.*, COUNT(DISTINCT alias.address ) AS alias_count, COUNT( DISTINCT mailbox.username ) AS mailbox_count
      FROM domain
      LEFT JOIN alias ON domain.domain = alias.domain
      LEFT JOIN mailbox ON domain.domain = mailbox.domain
      GROUP BY domain.domain, domain.description, domain.aliases, domain.mailboxes, domain.maxquota, domain.quota, domain.transport, domain.backupmx, domain.created, domain.modified, domain.active
      ORDER BY domain.domain;

       
      • Christian Boltz

        Christian Boltz - 2009-05-11

        > Postfix isn't happy with the above... 

        I assume this should read "_Postgresql_ isn't happy..." ;-)

        That said: your query works in MySQL also, so we can use it for both.

         
    • Christian Boltz

      Christian Boltz - 2009-05-12

      Good news: I found some time to tune list-domain, and the results are impressing.

      I created 55000 test mailboxes and their aliases as test data. Before my change, list-domain took forever (cancelled after some minutes).

      I replaced the get_domain_properties() call with two SQL queries. Yes, two queries - interestingly counting mailboxes and aliases in a single query (aka "the perfect query") is much slower than doing one query for counting the mailboxes and another one for counting the aliases. This brings down the query time to 14 seconds on my test data.

      I also added an index on the domain field in the mailbox and alias tables, which brings another performance boost (the indexes saves another 92% query time)

      After my changes, I'm down to about _one second_ (!) :-))

      Please update to SVN r655, run setup.php - and report back how long it takes to load the domain list now.

      @GingerDog:
      - please test that list-domain still works with PgSQL
      - please check that adding the indexes in PgSQL works as expected

       

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