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?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
> 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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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!)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
"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;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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?
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?
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
sorry postfixadmin functions :)
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.
> 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.
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).
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!)
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.)
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;
> 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.
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