| Author |
|
rob
Groupie
Joined: 03 September 2008
Location: Canada
Online Status: Offline
Posts: 60
|
| Posted: 21 January 2011 at 1:30pm | IP Logged
|
|
|
Hello,
We have been using the webmail pro system for quite some time now and have been noticing a considerable slow down in performance. Currently we have approx. 202GB of email in the mailbee_awm_messages_body table and the mailbee_awm_messages table has aprox. 2.2gb in it.
We are using mysql 5.0.77 with php 5.2.10 on centos.
When the following query is executed, if I watch the mysql process-list i can see that the query hangs for a long time with a "copying to tmp table". Until the query is done, the system hangs because of the table locking that occurs.
Any ideas on what is causing this and what can be done to resolve it?
Query:
SELECT p.id_folder, p.id_parent, p.type, p.name, p.full_path, p.sync_type, p.hide, p.fld_order,
COUNT(messages.id) AS message_count, COUNT(messages_unread.seen) AS unread_message_count,
SUM(messages.size) AS folder_size, MAX(folder_level) AS level
FROM (mailbee_awm_folders as n, mailbee_awm_folders_tree as t, mailbee_awm_folders as p)
LEFT OUTER JOIN mailbee_awm_messages AS messages ON p.id_folder = messages.id_folder_db AND messages.gmx_hidden != "yes"
LEFT OUTER JOIN mailbee_awm_messages AS messages_unread ON
p.id_folder = messages_unread.id_folder_db AND
messages.id = messages_unread.id AND messages_unread.seen = 0
WHERE n.id_parent = -1
AND n.id_folder = t.id_parent
AND t.id_folder = p.id_folder
AND p.id_acct = 136
GROUP BY p.id_folder
ORDER BY p.fld_order
|
| Back to Top |
|
| |
rob
Groupie
Joined: 03 September 2008
Location: Canada
Online Status: Offline
Posts: 60
|
| Posted: 21 January 2011 at 1:49pm | IP Logged
|
|
|
Further on this, we removed the ORDER BY p.fld_order from the query and see quite an increase in speed of the query. It seems that the order by is causing this problem.
|
| Back to Top |
|
| |
Igor
AfterLogic Support
Joined: 24 June 2008
Location: United States
Online Status: Offline
Posts: 6092
|
| Posted: 21 January 2011 at 10:01pm | IP Logged
|
|
|
I'll check with the developers whether they have any idea what's causing this; the response is probably expected on Monday.
Please note that WebMail Pro 6.0 is on its way, it was significantly rewritten and is planned for release till the end of this month.
--
Regards,
Igor, AfterLogic Support
|
| Back to Top |
|
| |
rob
Groupie
Joined: 03 September 2008
Location: Canada
Online Status: Offline
Posts: 60
|
| Posted: 27 January 2011 at 9:55am | IP Logged
|
|
|
So we have traced this down to the queries being used, and possibly the way the
tables have been indexed. We re-wrote a couple of the queries to speed things up,
but at this point will not be touching the indexes as the tables are too large to
do so.
Might want to look at optimizing the queries and indexes though if they havent
been already.
|
| Back to Top |
|
| |
Igor
AfterLogic Support
Joined: 24 June 2008
Location: United States
Online Status: Offline
Posts: 6092
|
| Posted: 28 January 2011 at 6:02am | IP Logged
|
|
|
We would really like to take a look at the SQL queries' modifications you've performed. In upcoming 6.0
release, we have added several indexes, and also made a number of improvements towards SQL queries.
--
Regards,
Igor, AfterLogic Support
|
| Back to Top |
|
| |
MailBee.NET Objects 
MailBee.NET Queue 
MailBee Objects 
WebMail Pro PHP 
AfterLogic Aurora 
MailSuite Pro for Linux 
Unified Messaging Solution 
Search
Register
Login
Topic: Critical! Slow query
Privacy policy |
Refund policy