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? >> > > -- ________________________________________________________________ Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin * Premium Hosting Services and Web Application Consultancy * www.nfg.nl/[email protected]/+31.85.877.99.97 ________________________________________________________________ _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
