DBMail 2.2.10 Postgresql 8.2.7 Thunderbird 2.0.0.14 When searching in the body text for sim, the imap reaction became very slow. We checked the server and it is using 80 percent CPU.
I checked pg_stat_activity and found 30 rows with the exact same current_query: "SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages m ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN (0,1 ) AND k.is_header = '0' GROUP BY m.message_idnr,k.messageblk HAVING ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%'" I stopped the search in Thunderbird and those rows still showed up in the pg_stat_activity. I turned off my Thunderbird client and the rows still showed up in the pg_stat_activity and the server was still going very slowly. After some time, each connection slowly finished (as shown in the log below, more then 20 minutes). After each one of these log messages, the number of rows in pg_stat_activity was one less. 2 Questions: 1) Is this supposed to be happening and how can I prevent it from happening again. 2) Wouldn't the query be much faster if the ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' was in the WHERE clause instead of the HAVING clause? 3) Does anybody have imap search working on some reasonable level? Jul 8 11:36:54 mailer dbmail/imap4d[23251]: Warning:[db] dbmodule.c,db_query(+151): slow query [SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages m ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN (0,1 ) AND k.is_header = '0' GROUP BY m.message_idnr,k.messageblk HAVING ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%'] took [1376] seconds Jul 8 11:39:15 mailer dbmail/imap4d[23159]: Warning:[db] dbmodule.c,db_query(+151): slow query [SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages m ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN (0,1 ) AND k.is_header = '0' GROUP BY m.message_idnr,k.messageblk HAVING ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%'] took [1453] seconds Jul 8 11:39:44 mailer dbmail/imap4d[23316]: Warning:[db] dbmodule.c,db_query(+151): slow query [SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages m ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN (0,1 ) AND k.is_header = '0' GROUP BY m.message_idnr,k.messageblk HAVING ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%'] took [1418] seconds Thank you Sim _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
