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 (!!). 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 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 -- http://www.tmueller.com for pgp key (95702B3B)Title: SQL Query Analysis (generated Wed Mar 09 09:14:24 CET 2005)
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 queries262 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={ } |
