Hi All,
I just want to share my recent experience in optimizing Postgresql
backend performance for DBMail.
Firstly, DBMail 2.2.x seems to work beautifully with pgbouncer in
transaction pooling mode, where transactions coming over a bunch of
concurrent connections from clients are funneled down just a few
connections to the actual database. Understandably, transaction pooling
works for basic SQL only but fortunately DBMail seems, for the sake of
its SQL portability, to use no complex features that would break
transaction pooling.
The SQL feature matrix for pgbouncer pooling modes can be found here:
http://wiki.postgresql.org/wiki/PgBouncer#Feature_matrix_for_pooling_modes
The point of transaction pooling is to reduce the number of large
postgres processes running, as PostgreSQL forks a process for each
client connection albeit those processes share disk bufs etc. In
addition, PostgreSQL before 9.x is known to be suboptimal with respect
to socket I/O handling so a lot of direct conns to backend processes can
waste quite a bit of CPU time. And the DBMail daemons in a large system
can open many hundreds of connections to the database.
Secondly, with the stock set of indexes on dbmail_messages, having many
clients out there check for new mail frequently can max out the database
CPU with this type of request:
SELECT max(message_idnr)+1 FROM dbmail_messages WHERE mailbox_idnr=123
The problem is that this query can only be satisfied with a sweeping
index scan and, as soon as whatever index used for that no longer fits
in Postresql's own buffers, the kernel has to be involved.
What solved the issue for me was adding this index:
CREATE INDEX dbmail_messages_mbox_msg ON dbmail_messages
(mailbox_idnr,message_idnr);
In my case, the speedup was 1000-fold, ~100ms vs ~0.1ms, and the load on
the DB dropped significantly because checks for new mail can easily
become the dominant access pattern when there is a throng of users out
there with the check intervals set to a minute or less.
That's all for now.
Regards,
Yar
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
