roller-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Fisher <dfis...@jmlafferty.com>
Subject Re: Problem with database load
Date Thu, 13 Mar 2008 16:31:59 GMT
BTW - I had jumped in on the thread to explain how indexes can help  
you deal with such horrible queries. Users can't always wait for the  
developers.

I am using 4.0 final and I inspected the dbscripts for the original  
poster's mysql. I then noted what was present and confirmed what was  
missing.

> So in this case I would prefer to see us work to fix the query  
> rather than just try and throw new indexes at the problem.

Absolutely - unless it is too large a task. Either way it is probably  
a JIRA issue for Roller.

Maybe in the switch from Hibernate to JPA some setting was missed?  
Maybe the 4.0 object model is more complicated that the poster's  
prior 1.1?

Perhaps Dave can explain the reasons why the query is so complex. I  
suspect it has to with creating a Velocity model in a single query?

Regards,
Dave


On Mar 13, 2008, at 11:07 AM, Allen Gilliland wrote:

> Yes, I am convinced that sql statement is an abomination.
>
> You said this is with 4.0 final running with the JPA backend correct?
>
> Adding the indexes is an option, but I think the bigger problem is  
> that query is ridiculous.  It is obviously trying to load way more  
> data than it should need to in a single query because a 9 way join  
> in 1 query is insane.  I'm wondering if this is a configuration  
> problem with OpenJPA not properly doing lazy fetching.  I would  
> think that at the very most you should see 3 tables involved in a  
> single query.
>
> The reason I questioned the need for indexes on the 2 columns below  
> is that they are really only associations that should be fetched  
> lazily, specifically for reasons like this.  There is no need for  
> the object model to fetch and populate those category objects when  
> you are querying for a list of weblog entries.
>
> So in this case I would prefer to see us work to fix the query  
> rather than just try and throw new indexes at the problem.
>
> -- Allen
>
>
> David Fisher wrote:
>>  From my experience the need for an index on a database table is  
>> more a function of the database configuration and the current size  
>> of a table. Many times the need has cropped up in my own webapp  
>> development. One of the reasons why we rolled our own set of  
>> classes is that it is easy to get to and manipulate the sql.  
>> Tuning is a fine art, sometimes an index is indicated and other  
>> times a rewrite of the statement with a hint so that it is joined  
>> in another manner. A third option is found with Oracle - analyze  
>> the tables - this allows the Database to decide how best to  
>> organize the table's index and that can improve plans. If MySQL  
>> offers a similar feature perhaps this will avoid the index.
>> Let's look at the way the SQL is constructed:
>> FROM roller_comment t0 INNER JOIN weblogentry t1 ON t0.entryid =
>> t1.id LEFT OUTER JOIN weblogcategory t2 ON t1.categoryid = t2.id  
>> LEFT OUTER JOIN
>> rolleruser t8 ON t1.userid = t8.id LEFT OUTER JOIN website t9 ON  
>> t1.websiteid =
>> t9.id LEFT OUTER JOIN weblogcategory t3 ON t2.parentid = t3.id  
>> LEFT OUTER JOIN
>> website t4 ON t2.websiteid = t4.id LEFT OUTER JOIN weblogcategory  
>> t5 ON
>> t4.bloggercatid = t5.id LEFT OUTER JOIN rolleruser t6 ON t4.userid  
>> = t6.id LEFT
>> OUTER JOIN weblogcategory t7 ON t4.defaultcatid = t7.id WHERE  
>> (t1.websiteid =
>> '8a926693072c38bf010741c83fcb36d6' AND t0.status = 'APPROVED')  
>> ORDER BY
>> t0.posttime DESC LIMIT 0, 10
>> This rewrites as:
>> FROM roller_comment t0, weblogentry t1, weblogcategory t2,  
>> rolleruser t8, website t9, weblogcategory t3, website t4,  
>> weblogcategory t5, rolleruser t6, weblogcategory t7
>> WHERE t1.categoryid = t2.id AND t1.userid = t8.id AND t1.websiteid  
>> = t9.id AND t2.parentid = t3.id AND t2.websiteid = t4.id AND
>> t4.bloggercatid = t5.id AND t4.userid = t6.id AND t4.defaultcatid  
>> = t7.id AND
>> t1.websiteid = '8a926693072c38bf010741c83fcb36d6' AND t0.status =  
>> 'APPROVED' ORDER BY
>> t0.posttime DESC LIMIT 0, 10
>> These indexes are provided:
>>  website.userid
>>  weblogentry.categoryid
>>  weblogentry.userid
>>  weblogentry.websiteid
>>  weblogcategory.id (primary key)
>>  weblogcategory.parentid
>>  weblogcategory.websiteid
>>  rolleruser.id (primary key)
>>  roller_comment.status
>>> create index ws_bloggercatid_idx    on website(bloggercatid);
>> is for "t4.bloggercatid = t5.id"
>>> create index ws_defaultcatid_idx    on website(defaultcatid );
>> is for "t4.defaultcatid = t7.id"
>> In other words every other possible index that would help make  
>> this horrible join function except for the two missed:
>>> My company has a blog service with 30k blogs and 70k unique  
>>> browsers per week.
>> This clearly indicates that without the two indexes the 30K blogs  
>> impacts every JOIN significantly! The factor is roughly by 30,000  
>> x 30,000 or 900,000,000. Maybe not that bad, but you can see the  
>> EXPLOSION!
>> Are you convinced?
>> Regards,
>> Dave
>> On Mar 12, 2008, at 11:36 AM, Allen Gilliland wrote:
>>> Yes, I understand why we use indexes, what I meant was that when  
>>> you are suggesting that we need a new index that you provide some  
>>> evidence about why that particular index is necessary.
>>>
>>> Your example below is completely valid, but the ur_userid_idx  
>>> index has always been there, so that's not a new index.
>>>
>>> For example, you said we need to add these 2 indexes ...
>>>
>>> create index ws_bloggercatid_idx    on website(bloggercatid);
>>> create index ws_defaultcatid_idx    on website(defaultcatid );
>>>
>>> why?  off the top of my head I can't think of a reason those  
>>> indexes would be of benefit because I don't believe we run any  
>>> standard queries which do a lookup or join on those columns.  I  
>>> may be wrong, but that's why I would like some evidence before we  
>>> would just add the indexes for no reason.
>>>
>>> -- Allen
>>>
>>>
>>> Boris Milikič wrote:
>>>> 1) I've got "Impossible WHERE noticed after reading const  
>>>> tables" message, when I ran explain on sql from prevoious e-mail:
>>>>  SELECT t0.id, t0.content, t0.contenttype, t0.email, t0.name,  
>>>> t0.notify, t0.plugins, t0.posttime,  ....
>>>> 2) So I run explain on this simple query:
>>>> explain select a.rolename ,b.username from rolleruser b,  
>>>> userrole a where b.id=a.userid;
>>>> 3) Explanations of why  indexes are necessary
>>>> Column  "type" in following table is the join type. Type ref  
>>>> means, that all rows with matching index values are read from  
>>>> this table for each combination of rows from the previous  
>>>> tables, example a).
>>>> For a tables that are  completely read in sequence from the hard  
>>>> drive EXPLAIN lists "ALL" in the "type" column. To the second  
>>>> table in the join plan for a two table query, EXPLAIN lists  
>>>> type: ALL,  as for table without index, example b). In example  
>>>> b)  when EXPLAIN lists type: ALL for each table in a join "this  
>>>> output indicates that MySQL is generating a Cartesian product of  
>>>> all the tables; that is, every combination of rows" (MySQL  
>>>> manual). In simpler terms: Two tables of 10 rows each joined  
>>>> together does not result in 20 rows, it results in 100 rows (10  
>>>> multiplied by 10).
>>>> a) with index
>>>> select_type     table    type    possible_keys    key     
>>>> key_len    ref    rows     
>>>> extra                                    1    SIMPLE    b     
>>>> ALL    PRIMARY    <NULL>    <NULL>    <NULL>    3   1 
   
>>>> SIMPLE    a    ref    ur_userid_idx    ur_userid_idx    144     
>>>> roller.b.id    1   b) without index (drop index ur_userid_idx on  
>>>> userrole( userid );)                                   1     
>>>> SIMPLE    a    ALL    <NULL>    <NULL>    <NULL>    <NULL>
    
>>>> 4   1    SIMPLE    b    ALL    PRIMARY    <NULL>    <NULL>  
  
>>>> <NULL>    3    Using where
>>>> Using too many indexes on tables can make things worse, as you  
>>>> said. In many cases, MySQL can calculate the best possible query  
>>>> plan. In very large database partitioning help.
>>>> -- Boris
>>>> -----Original Message-----
>>>> From: Allen.Gilliland@Sun.COM [mailto:Allen.Gilliland@Sun.COM]  
>>>> Sent: Friday, March 07, 2008 6:04 PM
>>>> To: user@roller.apache.org
>>>> Subject: Re: Problem with database load
>>>> Can you also provide explanations of why you think those indexes  
>>>> are necessary?  Having too many indexes on tables can actually  
>>>> make things worse, so you don't want to just flood the db with  
>>>> tons of indexes.
>>>> -- Allen
>>>> Boris Milikič wrote:
>>>>> I just found  one more missing:
>>>>>
>>>>> create index ws_posttime_idx    on roller_comment (posttime);
>>>>>
>>>>> Next weekend I will walk through sql log and  dbcreate.sql  
>>>>> script and record in JIRA if I find something.
>>>>>
>>>>> Boris
>>>>>
>>>>> -----Original Message-----
>>>>> From: Dave [mailto:snoopdave@gmail.com]
>>>>> Sent: Friday, March 07, 2008 4:31 PM
>>>>> To: user@roller.apache.org
>>>>> Subject: Re: Problem with database load
>>>>>
>>>>> On Fri, Mar 7, 2008 at 10:15 AM, Boris Milikič  
>>>>> <boris.milikic@mf-rs.si> wrote:
>>>>>>  Did you  created missing indexes in rollerdb (4.0):
>>>>>>
>>>>>>  create index ws_bloggercatid_idx    on website(bloggercatid);
>>>>>>  create index ws_defaultcatid_idx    on website(defaultcatid );
>>>>> Thanks Boris,
>>>>>
>>>>> I just opened an issue for this:
>>>>> https://issues.apache.org/roller/browse/ROL-1687
>>>>>
>>>>> Are there any other 4.0 database issues that you know of that  
>>>>> are not on record in JIRA?
>>>>>
>>>>> - Dave
>>>
>


Mime
View raw message