On Wed, 2005-03-09 at 11:51 +0100, Thomas Mueller wrote:
> Hi,
>
> I get more and more user complaints that my IMAP server is terrible
> slow, opening a mailbox takes very long.
>
> I've analyzed that using PQA (http://pgfoundry.org/projects/pqa/, great
> tool!), the output is attached if anyone is interested (includes all
> databases, not only dbmail).
>
> The main problem is in db.c, db_getmailbox():
> SELECT COUNT(message_idnr), COUNT(message_idnr) - SUM(seen_flag),
> SUM(recent_flag) FROM dbmail_messages WHERE mailbox_idnr = '5' AND
> status < '2'
> That query takes up to 15 seconds (!!).
A better query would be:
SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
(status='0' OR status='1') UNION
SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
(status='0' OR status='1') AND seen_flag=1 UNION
SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
(status='0' OR status='1') AND recent_flag=1;
Then having the following indexes:
mailbox_idnr
mailbox_idnr,status
mailbox_idnr,status,seen_flag
mailbox_idnr,status,recent_flag
Then having an index on mailbox_idnr AND status will mean we never have
to look at the database contents to answer the query (only examining the
index.
If you feel REALLY wary about using UNION- make union-ability a flag and
do each query separately if the backend says it can't do UNION.
We should also include the following indexes:
mailbox_idnr,message_idnr,status
mailbox_idnr,message_idnr
with SQLite, this plan never touches the records (only the index) - even
though the plain is much longer (139 ops instead of 57) it's SO MUCH
faster.
It would take [me] some time to actually test this with PgSQL - and I'll
do that if someone who has an active dbmail+pg complains.
BTW: I added this to my sql/sqlite/create_tables.sqlite for this.
CREATE INDEX dbmail_messages_7 ON dbmail_messages
(mailbox_idnr,status,seen_flag);
CREATE INDEX dbmail_messages_8 ON dbmail_messages
(mailbox_idnr,status,recent_flag);
> Obviously that doesn't scale at all - dbmail is not usable on PostgreSQL
> with lot of mails and/or users.
>
> If I replace COUNT(message_idnr) with the value I get the query is about
> 30 (!) times faster and takes about 0.4 seconds:
>
> SELECT 611, 611 - SUM(seen_flag), SUM(recent_flag) FROM dbmail_messages
> WHERE mailbox_idnr = '5' AND status < '2';
>
> So the solution is easy: add a row to dbmail_mailboxes that contains the
> number of messages. The row is updated by a trigger when messages are
> inserted or deleted. That is a quick and easy fix (only one query in the
> dbmail code has to be changed) - but that doesn't work for MySQL, I know :-/
I do not think this would work as well as you think this would work.
> I think in this case database abstraction simply doesn't work. We have
> to use two different queries in the code, but I might be wrong. Does
> anyone have a better solution?
>
>
> Thomas
> HTML page attachment (pg.html)
> SQL Query Analysis (generated Wed Mar 09 09:14:24 CET 2005)
>
>
> ______________________________________________________________________
> Reports
> Overall statistics
> Queries by type
> Queries that took up the most time
> Slowest queries
> Most frequent queries
>
> ______________________________________________________________________
> Overall statistics
> 375375 queries
> 262 unique queries
> Total query duration was 7389.75 seconds
> Longest query (SELECT pnd."SeqId",pnd."TableName",
> pnd."Op",pnddata."IsKey", pnddata."Data" AS "Data" FROM "Pending" pnd,
> "PendingData" pnddata WHERE pnd."SeqId" = pnddata."SeqId" AND
> pnd."XID"={ } ORDER BY "SeqId", "IsKey" DESC) ran in 84.216 seconds
> Shortest query (SET DATESTYLE = ISO) ran in 0.000 seconds
> Log file parsed in 358.1 seconds
> Queries by type
> Type
> Count
> Percentage
> SELECT
> 306035
> 82
> INSERT
> 46531
> 12
> UPDATE
> 10028
> 3
> DELETE
> 10415
> 3
> Queries that took up the most time
> Rank
> Total time
> (seconds)
> Times executed
> Query text
> 1
> 2532.248
> 9593
> SELECT COUNT
> (message_idnr),
> COUNT
> (message_idnr) -
> SUM(seen_flag),
> SUM(recent_flag)
> FROM
> dbmail_messages
> WHERE
> mailbox_idnr =
> { } AND status <
> { }
> 2
> 1398.038
> 9526
> SELECT
> message_idnr FROM
> dbmail_messages
> WHERE
> mailbox_idnr =
> { } AND status <
> { } ORDER BY
> message_idnr ASC
> 3
> 515.294
> 9933
> INSERT INTO
> "PendingData" ("SeqId","IsKey","Data") VALUES(currval({ }),{ },$1)
> 4
> 340.673
> 32
> SELECT
> filename,md5sum,size,mtime FROM filelist WHERE owner={ } AND filename LIKE {
> } EXCEPT SELECT filename,md5sum,size,mtime FROM filelist WHERE owner={ } AND
> filename LIKE { } ORDER BY filename ASC LIMIT { } OFFSET { }
> 5
> 312.432
> 3375
> select
> a.oid,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del
> from pg_class a, pg_stat_all_tables b where a.oid=b.relid and a.relkind = {
> } and schemaname not like { }
> 6
> 302.986
> 1815
> SELECT seen_flag,
> answered_flag,
> deleted_flag,
> flagged_flag,
> draft_flag,
> recent_flag,
> TO_CHAR
> (internal_date,
> { } ), rfcsize,
> message_idnr FROM
> dbmail_messages
> msg,
> dbmail_physmessage pm WHERE pm.id = msg.physmessage_id AND message_idnr
> BETWEEN { } AND { } AND mailbox_idnr = { } AND status < { } ORDER BY
> message_idnr ASC
> 7
> 209.489
> 8932
> INSERT INTO
> "MirroredTransaction" ("XID","LastSeqId","MirrorHostId") VALUES ({ },{ },{ })
> 8
> 202.152
> 41707
> SELECT messageblk
> FROM
> dbmail_messageblks blk, dbmail_messages msg WHERE blk.physmessage_id =
> msg.physmessage_id AND msg.message_idnr = { } ORDER BY blk.messageblk_idnr
> ASC LIMIT { }
> 9
> 198.843
> 8932
> DELETE FROM
> "Pending" WHERE
> "XID"={ } AND
> (SELECT COUNT(*)
> FROM
> "MirroredTransaction" WHERE "XID"={ })=(SELECT COUNT(*) FROM "MirrorHost")
> 10
> 196.429
> 8988
> SELECT
> pnd."SeqId",pnd."TableName", pnd."Op",pnddata."IsKey", pnddata."Data" AS
> "Data" FROM "Pending" pnd, "PendingData" pnddata WHERE pnd."SeqId" =
> pnddata."SeqId" AND pnd."XID"={ } ORDER BY "SeqId", "IsKey" DESC
> 11
> 111.962
> 9068
> SELECT DISTINCT
> name FROM
> tmm_customer_domains WHERE name={ }
> 12
> 73.809
> 3837
> SELECT MIN
> (message_idnr)
> FROM
> dbmail_messages
> WHERE
> mailbox_idnr =
> { } AND status <
> { } AND seen_flag
> = { }
> 13
> 70.220
> 1884
> commit
> 14
> 65.325
> 5948
> SELECT
> block.messageblk
> FROM
> dbmail_messageblks block, dbmail_messages msg WHERE block.physmessage_id =
> msg.physmessage_id AND msg.message_idnr = { } ORDER BY block.messageblk_idnr
> 15
> 60.511
> 45277
> select
> content,ttl,prio,type,domain_id,name from records where type={ } and name={ }
> Slowest queries
> Rank
> Time
> Query text
> 1
> 84.216
> SELECT
> pnd."SeqId",pnd."TableName", pnd."Op",pnddata."IsKey", pnddata."Data" AS
> "Data" FROM "Pending" pnd, "PendingData" pnddata WHERE pnd."SeqId" =
> pnddata."SeqId" AND pnd."XID"={ } ORDER BY "SeqId", "IsKey" DESC
> 2
> 81.209
> SELECT
> pnd."SeqId",pnd."TableName", pnd."Op",pnddata."IsKey", pnddata."Data" AS
> "Data" FROM "Pending" pnd, "PendingData" pnddata WHERE pnd."SeqId" =
> pnddata."SeqId" AND pnd."XID"={ } ORDER BY "SeqId", "IsKey" DESC
> 3
> 57.863
> SELECT seen_flag,
> answered_flag,
> deleted_flag,
> flagged_flag,
> draft_flag,
> recent_flag, TO_CHAR
> (internal_date, { } ),
> rfcsize, message_idnr
> FROM dbmail_messages
> msg, dbmail_physmessage
> pm WHERE pm.id =
> msg.physmessage_id AND
> message_idnr BETWEEN
> { } AND { } AND
> mailbox_idnr = { } AND
> status < { } ORDER BY
> message_idnr ASC
> 4
> 45.004
> SELECT seen_flag,
> answered_flag,
> deleted_flag,
> flagged_flag,
> draft_flag,
> recent_flag, TO_CHAR
> (internal_date, { } ),
> rfcsize, message_idnr
> FROM dbmail_messages
> msg, dbmail_physmessage
> pm WHERE pm.id =
> msg.physmessage_id AND
> message_idnr BETWEEN
> { } AND { } AND
> mailbox_idnr = { } AND
> status < { } ORDER BY
> message_idnr ASC
> 5
> 37.403
> SELECT seen_flag,
> answered_flag,
> deleted_flag,
> flagged_flag,
> draft_flag,
> recent_flag, TO_CHAR
> (internal_date, { } ),
> rfcsize, message_idnr
> FROM dbmail_messages
> msg, dbmail_physmessage
> pm WHERE pm.id =
> msg.physmessage_id AND
> message_idnr BETWEEN
> { } AND { } AND
> mailbox_idnr = { } AND
> status < { } ORDER BY
> message_idnr ASC
> 6
> 32.138
> SELECT seen_flag,
> answered_flag,
> deleted_flag,
> flagged_flag,
> draft_flag,
> recent_flag, TO_CHAR
> (internal_date, { } ),
> rfcsize, message_idnr
> FROM dbmail_messages
> msg, dbmail_physmessage
> pm WHERE pm.id =
> msg.physmessage_id AND
> message_idnr BETWEEN
> { } AND { } AND
> mailbox_idnr = { } AND
> status < { } ORDER BY
> message_idnr ASC
> 7
> 29.513
> SELECT seen_flag,
> answered_flag,
> deleted_flag,
> flagged_flag,
> draft_flag,
> recent_flag, TO_CHAR
> (internal_date, { } ),
> rfcsize, message_idnr
> FROM dbmail_messages
> msg, dbmail_physmessage
> pm WHERE pm.id =
> msg.physmessage_id AND
> message_idnr BETWEEN
> { } AND { } AND
> mailbox_idnr = { } AND
> status < { } ORDER BY
> message_idnr ASC
> 8
> 28.300
> SELECT seen_flag,
> answered_flag,
> deleted_flag,
> flagged_flag,
> draft_flag,
> recent_flag, TO_CHAR
> (internal_date, { } ),
> rfcsize, message_idnr
> FROM dbmail_messages
> msg, dbmail_physmessage
> pm WHERE pm.id =
> msg.physmessage_id AND
> message_idnr BETWEEN
> { } AND { } AND
> mailbox_idnr = { } AND
> status < { } ORDER BY
> message_idnr ASC
> 9
> 20.188
> SELECT usr.user_idnr,
> sum(pm.messagesize),
> usr.curmail_size FROM
> dbmail_users usr LEFT
> JOIN dbmail_mailboxes
> mbx ON mbx.owner_idnr =
> usr.user_idnr LEFT JOIN
> dbmail_messages msg ON
> msg.mailbox_idnr =
> mbx.mailbox_idnr LEFT
> JOIN dbmail_physmessage
> pm ON pm.id =
> msg.physmessage_id AND
> msg.status < { } GROUP
> BY usr.user_idnr,
> usr.curmail_size HAVING
> ((SUM(pm.messagesize)
> <> usr.curmail_size) OR
> (NOT (SUM
> (pm.messagesize) IS NOT
> NULL) AND
> usr.curmail_size <>
> { }))
> 10
> 19.975
> DELETE FROM
> net_packets_raw WHERE
> timestamp < { }
> 11
> 17.378
> SELECT
> filename,md5sum,size,mtime FROM filelist WHERE owner={ } AND filename LIKE {
> } EXCEPT SELECT filename,md5sum,size,mtime FROM filelist WHERE owner={ } AND
> filename LIKE { } ORDER BY filename ASC LIMIT { } OFFSET { }
> 12
> 15.587
> SELECT COUNT
> (message_idnr), COUNT
> (message_idnr) - SUM
> (seen_flag), SUM
> (recent_flag) FROM
> dbmail_messages WHERE
> mailbox_idnr = { } AND
> status < { }
> 13
> 15.554
> SELECT message_idnr
> FROM dbmail_messages
> WHERE mailbox_idnr =
> { } AND status < { }
> ORDER BY message_idnr
> ASC
> 14
> 15.186
> SELECT
> filename,md5sum,size,mtime FROM filelist WHERE owner={ } AND filename LIKE {
> } EXCEPT SELECT filename,md5sum,size,mtime FROM filelist WHERE owner={ } AND
> filename LIKE { } ORDER BY filename ASC LIMIT { } OFFSET { }
> 15
> 14.212
> SELECT
> filename,md5sum,size,mtime FROM filelist WHERE owner={ } AND filename LIKE {
> } EXCEPT SELECT filename,md5sum,size,mtime FROM filelist WHERE owner={ } AND
> filename LIKE { } ORDER BY filename ASC LIMIT { } OFFSET { }
> Most frequent queries
> Rank
> Times executed
> Query text
> 1
> 45277
> select
> content,ttl,prio,type,domain_id,name from records where type={ } and name={ }
> 2
> 41707
> SELECT messageblk FROM
> dbmail_messageblks blk,
> dbmail_messages msg
> WHERE
> blk.physmessage_id =
> msg.physmessage_id AND
> msg.message_idnr = { }
> ORDER BY
> blk.messageblk_idnr ASC
> LIMIT { }
> 3
> 22776
> SELECT no_select FROM
> dbmail_mailboxes WHERE
> mailbox_idnr = { }
> 4
> 20731
> INSERT INTO
> "PendingData" ("SeqId","IsKey","Data") VALUES(currval({ }),{ },$1)
> 5
> 19014
> SELECT owner_idnr FROM
> dbmail_mailboxes WHERE
> mailbox_idnr = { }
> 6
> 18917
> SELECT no_inferiors
> FROM dbmail_mailboxes
> WHERE mailbox_idnr =
> { }
> 7
> 18917
> SELECT name FROM
> dbmail_mailboxes WHERE
> mailbox_idnr = { }
> 8
> 16366
> SELECT mailbox_idnr
> FROM dbmail_mailboxes
> WHERE mailbox_idnr =
> { } AND owner_idnr =
> { }
> 9
> 12984
> INSERT INTO
> "Pending" ("TableName","Op","XID") VALUES ($1,$2,$3)
> 10
> 9593
> SELECT COUNT
> (message_idnr), COUNT
> (message_idnr) - SUM
> (seen_flag), SUM
> (recent_flag) FROM
> dbmail_messages WHERE
> mailbox_idnr = { } AND
> status < { }
> 11
> 9593
> SELECT message_idnr +
> { } FROM
> dbmail_messages ORDER
> BY message_idnr DESC
> LIMIT { }
> 12
> 9593
> SELECT
> permission,seen_flag,answered_flag,deleted_flag,flagged_flag,recent_flag,draft_flag
> FROM dbmail_mailboxes WHERE mailbox_idnr = { }
> 13
> 9526
> SELECT message_idnr
> FROM dbmail_messages
> WHERE mailbox_idnr =
> { } AND status < { }
> ORDER BY message_idnr
> ASC
> 14
> 9154
> SELECT indkey FROM
> pg_index WHERE
> indisprimary={ } AND
> indrelid={ }
> 15
> 9068
> SELECT DISTINCT name
> FROM
> tmm_customer_domains
> WHERE name={ }
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
--
Internet Connection High Quality Web Hosting
http://www.internetconnection.net/
