Hmm, SQL...
Whats with triggers and holding this information hot?
for example adding 3 columns to the mailbox table with msg_count,
msg_count_seen, msg_count_recent
adding a trigger to the messages table to updated this values on update in
the messages table.
Another thing are the VIEWs in MySQL are not MATERIALIZED so there is no
performance gain using it, using a table with triggers would bring much
better performance.
Or is there a reason not using this, for example your sql schema for
oracles doesn't use MATERIALIZED VIEWs.
Am 07.03.2013, 09:23 Uhr, schrieb Paul J Stevens <[email protected]>:
On 03/06/2013 02:15 PM, Harald Leithner wrote:
This query takes less time:
Does that provide the same information as the UNION?? I don't think so.
Also, the GROUP BY clause won't be accepted by Postgresql - and most
likely Oracle. And the IF function is not supported by Sqlite3, but
using the CASE expression would be equivalent.
Welcome to the wonderful world of standard SQL.
I've taken a slightly different approach by changing that query to a
prepared statement. That should improve cache hits.
Another approach would be to change the mailbox state information to a
stored procedure. I did cut a branch once to experiment with a patch
someone submitted, but I didn't have time to port and test it on the
supported backends.
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
