Paul,
I have an alternativ query for you for the message count.
atm dbmail uses this query in dm_mailboxstate.c
"SELECT 0,COUNT(*) FROM %smessages WHERE mailbox_idnr=%llu "
"AND (status < %d) UNION "
"SELECT 1,COUNT(*) FROM %smessages WHERE mailbox_idnr=%llu "
"AND (status < %d) AND seen_flag=1 UNION "
"SELECT 2,COUNT(*) FROM %smessages WHERE mailbox_idnr=%llu "
"AND (status < %d) AND recent_flag=1"
this query will be called for every mailbox a imap client syncs correct?
This query takes less time:
SELECT
IF ( seen_flag+recent_flag = 0, 0,
IF ( seen_flag = 1, 1,
IF ( recent_flag = 1, 2, 0)
)
) AS flag,
COUNT(*)
FROM
%smessages
WHERE
mailbox_idnr=%llu
AND
(status < %d)
GROUP BY
seen_flag,
recent_flag;
At heavy server load the current query needs 4.81 seconds the new query
tooks 1.14 seconds.
At "idle" state the query tooks 0.07 seconds the new query tooks 0.04
seconds.
The sourcecode have to be modified so that it checks the flag column for
the correct number (0 = all, 1 = seen, 2 = recent).
I haven't tested it, because I can't write c and don't have testing
environment. Also I don't know how the query performance is on other
database then mysql percona 5.5.
Am 05.03.2013, 17:36 Uhr, schrieb Harald Leithner
<[email protected]>:
Hi,
I have sometimes a problem on my dbmail server, it seams that one client
kills the server.
top - 17:16:51 up 11 days, 15:46, 1 user, load average: 9.63, 6.54,
4.99
Tasks: 128 total, 2 running, 126 sleeping, 0 stopped, 0 zombie
%Cpu(s): 5.9 us, 1.5 sy, 0.0 ni, 80.7 id, 11.9 wa, 0.0 hi, 0.0 si,
0.0 st
KiB Mem: 32992140 total, 29264076 used, 3728064 free, 296492 buffers
KiB Swap: 0 total, 0 used, 0 free, 11474428 cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6441 mysql 20 0 26.6g 15g 7028 S 800.0 49.3 1757:31 mysqld
19234 dbmail 20 0 1482m 686m 3964 S 427.3 2.1 56:05.00
dbmail-imapd
After restart dbmail everything is fine again, I tried to check the
mysql queries running in this situation, but I found only some of them
for example:
---
SELECT keyword FROM dbmail_keywords k LEFT JOIN dbmail_messages m ON
k.message_idnr=m.message_idnr LEFT JOIN dbmail_mailboxes b ON
m.mailbox_idnr=b.mailbox_idnr WHERE b.mailbox_idnr=6;
--- The sometimes need a few seconds
More Problems I have with this one
---
SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=1211 AND
(status < 2) UNION SELECT 1,COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=1211 AND (status < 2) AND seen_flag=1 UNION SELECT
2,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=1211 AND (status < 2)
AND recent_flag=1;
---
This could take up to 42 seconds, running multiple times for different
mailboxes.
Server running 32 GB Ram on 60 GB DB Storage, so not everything is in
memory but the message tables has only 128 MB so should be in Memory
completely.
mysqltuner says everything is fine except this:
[!!] Total fragmented tables: 11
[!!] Query cache efficiency: 7.2% (1M cached / 17M selects)
[!!] Table cache hit rate: 7% (130 open / 1K opened)
[!!] InnoDB data size / buffer pool: 57.8G/24.0G
it suggests:
query_cache_limit (> 256M, or use smaller result sets)
table_cache (> 6000)
innodb_buffer_pool_size (>= 57G)
The Total fragmented tables could be a problem or?
Query cache efficiency say nothing in my opinion
Table cache hit rate should not be a problem if I have table_cache 6000
or?
Database Size should not be more then phys memory ;-)
So I would like to find out which client it is and what it does and how
I could fix it.
Also I'm thinking that dbmail should cache mail_count, seen_flag_count
and recent_flag_count, maybe by adding columns in mailbox table with
triggers?
--
Harald Leithner
ITronic
Vogelweidplatz 12, 1150 Wien, Austria
Tel: +43-1-786 23 88
Fax: +43-1-98 52 077
Mobil: +43-699-123 78 4 78
Mail: [email protected] | itronic.at
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
