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
