It's the "BETWEEN" construct, or what it does rather.
"AND message_idnr >= 253775 AND message_idnr <= 253775" did the same thing.
"message_idnr = 253775" brought a 70ms query, instead of 7+ seconds.
Sooo . . . . . . . ummmm. What would people that are smarter than me in this
arena suggest that I do about this? Obviously someone chose a range for a
reason.
Thank you for suggestions.
-B
Brian Neu <[EMAIL PROTECTED]> wrote: With just 50 imap users, my little server
is pushed to the max. 10+ second queries aren't uncommon. CPU utilization and
load are pegged.
I thought maybe the worker threads needed more memory and were being forced to
use temporary tables on disk, so I did some extensive memory increasing for
them (to 32M) for the server in all areas, restarted everything. Didn't work.
Turned off statistics collector. Didn't work either.
I pulled one of the worst offending queries, then ran it from the pgadmin query
tool.
Pardon the sloppiness of this output. Thanks.
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 1.570 ms statement:
SELECT login, sock_allow, sock_deny, userid FROM dbmail_usermap WHERE login in
('[EMAIL PROTECTED]','ANY') ORDER BY sock_allow, sock_deny
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 12.546 ms statement:
SELECT user_idnr FROM dbmail_users WHERE lower(userid) = lower('[EMAIL
PROTECTED]')
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 1.520 ms statement:
SELECT user_idnr, passwd, encryption_type FROM dbmail_users WHERE user_idnr = 17
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 419.197 ms statement:
SELECT message_idnr+1 FROM dbmail_messages WHERE mailbox_idnr=516 ORDER BY
message_idnr DESC LIMIT 1
2007-06-20 17:36:06 EDT-sqlgrey:sqlgrey LOG: duration: 477.869 ms statement:
UPDATE domain_awl SET last_seen = NOW(), first_seen = first_seen WHERE
sender_domain = 'volcanicsolution.com' AND src = '216.219.253'
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 1.311 ms statement:
SELECT alias FROM dbmail_aliases where alias='[EMAIL PROTECTED]';
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 1.759 ms statement:
SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name ILIKE 'Huntington
Bank/Delaware North' AND owner_idnr=10
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 0.718 ms statement:
SELECT owner_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = 308
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 0.434 ms statement:
SELECT
permission,seen_flag,answered_flag,deleted_flag,flagged_flag,recent_flag,draft_flag
FROM dbmail_mailboxes WHERE mailbox_idnr = 308
2007-06-20 17:36:06 EDT-dbmail:dbmail LOG: duration: 3.176 ms statement:
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=308 AND (status <
2) UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=308 AND
(status < 2) AND seen_flag=1 UNION SELECT 'c',COUNT(*) FROM dbmail_messages
WHERE mailbox_idnr=308 AND (status < 2) AND recent_flag=1
2007-06-20 17:36:06 EDT-postfix:postfix LOG: duration: 1.158 ms statement:
SELECT domain FROM domains WHERE domain = 'domain.com' AND active=TRUE;
2007-06-20 17:36:06 EDT-postfix:postfix LOG: duration: 0.806 ms statement:
SELECT transport FROM domains WHERE domain = 'domain.com' AND active=TRUE;
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 413.934 ms statement:
SELECT message_idnr+1 FROM dbmail_messages WHERE mailbox_idnr=308 ORDER BY
message_idnr DESC LIMIT 1
2007-06-20 17:36:07 EDT-sqlgrey:sqlgrey LOG: duration: 0.405 ms statement:
SELECT now()
2007-06-20 17:36:07 EDT-sqlgrey:sqlgrey LOG: duration: 0.412 ms statement:
EXECUTE <unnamed> [PREPARE: SELECT 1 FROM domain_awl WHERE sender_domain = $1
AND src = $2 AND last_seen > timestamp '2007-06-20 17:36:07.161995-04' -
INTERVAL '60 DAY']
2007-06-20 17:36:07 EDT-postgres:dbmail LOG: duration: 7216.642 ms statement:
SELECT message_idnr,headername,headervalue ^M
FROM dbmail_headervalue v JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id ^M
JOIN dbmail_headername n ON v.headername_id=n.id ^M
WHERE m.mailbox_idnr = 417 AND message_idnr BETWEEN 253775 AND
253775 AND lower(headername) ^M
IN
('from','to','cc','subject','date','message-id','priority','x-priority','references','newsgroups','in-reply-to','content-type')^M
2007-06-20 17:36:07 EDT-sqlgrey:sqlgrey LOG: duration: 52.956 ms statement:
UPDATE domain_awl SET last_seen = NOW(), first_seen = first_seen WHERE
sender_domain = 'volcanicsolution.com' AND src = '216.219.253'
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 1.269 ms statement:
SELECT alias FROM dbmail_aliases where alias='[EMAIL PROTECTED]';
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 786.817 ms statement:
UPDATE dbmail_users SET last_login = '2007-06-20 17:36:06' WHERE user_idnr = 17
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 1.433 ms statement:
SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name ILIKE 'INBOX' AND
owner_idnr=17
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 0.667 ms statement:
SELECT owner_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = 16
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 0.368 ms statement:
SELECT no_select FROM dbmail_mailboxes WHERE mailbox_idnr = 16
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 0.373 ms statement:
SELECT
permission,seen_flag,answered_flag,deleted_flag,flagged_flag,recent_flag,draft_flag
FROM dbmail_mailboxes WHERE mailbox_idnr = 16
2007-06-20 17:36:07 EDT-postgres:dbmail LOG: duration: 1.245 ms statement:
SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 20
2007-06-20 17:36:07 EDT-postgres:dbmail LOG: duration: 0.882 ms statement:
SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=20
2007-06-20 17:36:07 EDT-postgres:dbmail LOG: duration: 0.706 ms statement:
SELECT format_type(oid,104) as typname FROM pg_type WHERE oid = 1043
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 9.075 ms statement:
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=16 AND (status < 2)
UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=16 AND
(status < 2) AND seen_flag=1 UNION SELECT 'c',COUNT(*) FROM dbmail_messages
WHERE mailbox_idnr=16 AND (status < 2) AND recent_flag=1
2007-06-20 17:36:07 EDT-postgres:dbmail LOG: duration: 0.835 ms statement:
SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1043
2007-06-20 17:36:07 EDT-postgres:dbmail LOG: duration: 0.537 ms statement:
SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 25
2007-06-20 17:36:07 EDT-postgres:dbmail LOG: duration: 0.809 ms statement:
SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=25
2007-06-20 17:36:07 EDT-dbmail:dbmail LOG: duration: 41.047 ms statement:
SELECT message_idnr+1 FROM dbmail_messages WHERE mailbox_idnr=16 ORDER BY
message_idnr DESC LIMIT 1
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
