[Building Sakai] MFR_MESSAGE_T table performance enhancement
branden at uwindsor.ca
branden at uwindsor.ca
Mon Apr 27 11:28:39 PDT 2009
There is a bit of discussion right now in a JIRA ticket revolving around
optimizing Forums statistics:
http://bugs.sakaiproject.org/jira/browse/SAK-12754
This information would be nice to add.
Best,
------------------------------------------
Branden Visser
LMS Application Programmer - Information Systems Services
Information Technology Services
University of Windsor
Phone: 519-253-3000 x4412
E-mail: branden at uwindsor.ca
sakai-dev-bounces at collab.sakaiproject.org wrote on 27/04/2009 02:20:33 PM:
> Earle Nietzel <Earle.Nietzel at marist.edu>
> Sent by: sakai-dev-bounces at collab.sakaiproject.org
>
> 27/04/2009 02:20 PM
>
> To
>
> sakai-dev at collab.sakaiproject.org
>
> cc
>
> Subject
>
> Re: [Building Sakai] MFR_MESSAGE_T table performance enhancement
>
> After digging into this some more I have come across a better solution
for
> those slow queries.
>
> I began to break up the query into its different parts and execute them
> individually until I found what was actually causing the slow down in
> Forums.
>
> My recommendation is to have index created on USER_ID for table
> MFR_PVT_MSG_USR_T.
>
> CREATE INDEX MFR_PVT_MSG_USR_T_USER_ID_I on MFR_PVT_MSG_USR_T (USER_ID);
>
> This reduced query times from 1.5 seconds to .012 seconds.
>
> BEFORE:
> +----+-------------+--------------+--------
> +----------------------------+---------+---------
> +-----------------------------------------+--------
> +----------------------------------------------+
>
> | id | select_type | table | type | possible_keys |
> key | key_len | ref | rows |
> Extra |
> +----+-------------+--------------+--------
> +----------------------------+---------+---------
> +-----------------------------------------+--------
> +----------------------------------------------+
>
> | 1 | SIMPLE | recipients1_ | ALL | PRIMARY,FKC4DE0E1473D286ED
|
> NULL | NULL | NULL | 299781 |
> Using where; Using temporary; Using filesort |
> | 1 | SIMPLE | privatemes0_ | eq_ref | PRIMARY |
> PRIMARY | 8 | ilearn.recipients1_.messageSurrogateKey | 1 |
> Using where |
> +----+-------------+--------------+--------
> +----------------------------+---------+---------
> +-----------------------------------------+--------
> +----------------------------------------------+
>
>
> AFTER:
> +----+-------------+--------------+--------
> +--------------------------------------------------------
> +-----------------------------+---------
> +-----------------------------------------+------
> +----------------------------------------------+
>
> | id | select_type | table | type | possible_keys
> | key | key_len | ref
> | rows | Extra |
> +----+-------------+--------------+--------
> +--------------------------------------------------------
> +-----------------------------+---------
> +-----------------------------------------+------
> +----------------------------------------------+
>
> | 1 | SIMPLE | recipients1_ | ref |
> PRIMARY,FKC4DE0E1473D286ED,MFR_PVT_MSG_USR_T_USER_ID_I |
> MFR_PVT_MSG_USR_T_USER_ID_I | 767 | const
> | 93 | Using where; Using temporary; Using filesort |
> | 1 | SIMPLE | privatemes0_ | eq_ref | PRIMARY
> | PRIMARY | 8 |
> ilearn.recipients1_.messageSurrogateKey | 1 | Using where
> |
> +----+-------------+--------------+--------
> +--------------------------------------------------------
> +-----------------------------+---------
> +-----------------------------------------+------
> +----------------------------------------------+
>
>
>
> Earle
>
> PS The index I mentioned previously is not needed.
>
> -----sakai-dev-bounces at collab.sakaiproject.org wrote: -----
>
> To: sakai-dev at collab.sakaiproject.org
> From: Earle Nietzel <Earle.Nietzel at marist.edu>
> Sent by: sakai-dev-bounces at collab.sakaiproject.org
> Date: 04/24/2009 11:47AM
> Subject: [Building Sakai] MFR_MESSAGE_T table performance enhancement
>
>
> Found a query that was appearing in our slow query log rather frequently
> recently and did some testing with the mysql profiler tool and found a
> speed up for the MFR_MESSAGE_T table.
>
> The query in question is a rather large query but i will list the
important
> pieces:
> select ..... (left out for to save space)
> from MFR_MESSAGE_T privatemes0_ left outer join MFR_PVT_MSG_USR_T
> recipients1_ on privatemes0_.ID=recipients1_.messageSurrogateKey
> where privatemes0_.MESSAGE_DTYPE='PM' and
> recipients1_.USER_ID='27024526-9c61-4cef-9fd4-0840b52a300b' and
> recipients1_.TYPE_UUID='e6d3664d-d75e-46c0-00f1-81cf3288a29e' and
> recipients1_.CONTEXT_ID='a722e35d-ca3f-4192-a5de-0f2444a40b27'
> order by privatemes0_.CREATED desc;
>
> This query produce the following profiler ouput:
>
> +--------------------+-----------+
> | Status | Duration |
> +--------------------+-----------+
> | (initialization) | 0.000001 |
> | Opening tables | 0.00001 |
> | System lock | 0.000004 |
> | Table lock | 0.000005 |
> | init | 0.0000660 |
> | optimizing | 0.00002 |
> | statistics | 0.000025 |
> | preparing | 0.000014 |
> | executing | 0.000002 |
> | Sorting result | 0.539899 |
> | Sending data | 1.096705 |
> | end | 0.000011 |
> | query end | 0.000004 |
> | freeing items | 0.000019 |
> | closing tables | 0.000005 |
> | logging slow query | 0.000002 |
> +--------------------+-----------+
>
> Notice the Sorting result took approx .54 seconds everything else
appears
> to be normal.
>
> After adding the following index:
>
> create index MFR_MESSAGE_T_CREATED_I on MFR_MESSAGE_T (CREATED);
>
> The profiler then shows that Sorting was reduced to 0.000005 and the
> overall query was reduced by .5 seconds.
>
> +--------------------+----------+
> | Status | Duration |
> +--------------------+----------+
> | (initialization) | 0.000001 |
> | Opening tables | 0.00001 |
> | System lock | 0.000003 |
> | Table lock | 0.000006 |
> | init | 0.000072 |
> | optimizing | 0.000021 |
> | statistics | 0.000029 |
> | preparing | 0.000017 |
> | executing | 0.000002 |
> | Sorting result | 0.000005 |
> |*Sending data | 1.444712 |
> | end | 0.000012 |
> | query end | 0.000003 |
> | freeing items | 0.000019 |
> | closing tables | 0.000006 |
> | logging slow query | 0.000002 |
> +--------------------+----------+
>
> *note although the sending data time did go up i believe that to be
linked
> to the client I am using as I have seen varying times depending on the
> client that is used.
>
> Comments are welcome,
>
> Earle
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to
sakai-dev-unsubscribe at collab.sakaiproject.org
> with a subject of "unsubscribe"
>
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-
> unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090427/c8754713/attachment.html
More information about the sakai-dev
mailing list