You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@roller.apache.org by Milo <mi...@moscowmail.com> on 2008/03/07 11:41:07 UTC
Problem with database load
Hi,
My company has a blog service with 30k blogs and 70k unique browsers per week.
At the moment we use a heavily customized Roller 1.1 which was developed a couple of years ago.
We are upgrading to Roller 4.0 but our database server cannot take the load from Roller.
We have implemented several different page cache mechanims and we are currently evaluating them:
- default LRU cache
- ehcache 1.4.1
- memcached + Greg Whalin java client (http://www.whalin.com/memcached/)
- memcached + spymemcached (http://code.google.com/p/spymemcached/)
The performance gains are very high using ehcache and memcached, but we still have problems with some queries in Roller.
Example:
SELECT t0.id, t0.content, t0.contenttype, t0.email, t0.name, t0.notify, t0.plugins, t0.posttime, t0.referrer, t0.remotehost, t0.status, t0.url,
t0.useragent, t1.id, t1.allowcomments, t1.anchor, t2.id, t2.description,
t2.image, t2.name, t3.id, t3.description, t3.image, t3.name, t3.path,
t3.websiteid, t2.path, t4.id, t4.about, t4.isactive, t4.allowcomments,
t4.blacklist, t5.id, t5.description, t5.image, t5.name, t5.path, t5.websiteid,
t6.id, t6.activationcode, t6.datecreated, t6.emailaddress, t6.isenabled,
t6.fullname, t6.locale, t6.passphrase, t6.screenname, t6.timeZone, t6.username,
t4.customstylesheet, t4.datecreated, t4.defaultallowcomments, t7.id,
t7.description, t7.image, t7.name, t7.path, t7.websiteid, t4.defaultcommentdays,
t4.defaultpageid, t4.defaultplugins, t4.description, t4.editorpage,
t4.editortheme, t4.emailaddress, t4.emailcomments, t4.emailfromaddress,
t4.enablebloggerapi, t4.enablemultilang, t4.isenabled, t4.displaycnt, t4.handle,
t4.icon, t4.lastmodified, t4.locale, t4.commentmod, t4.name, t4.pagemodels,
t4.showalllangs, t4.timeZone, t4.weblogdayid, t1.commentdays, t1.content_src,
t1.content_type, t8.id, t8.activationcode, t8.datecreated, t8.emailaddress,
t8.isenabled, t8.fullname, t8.locale, t8.passphrase, t8.screenname, t8.timeZone,
t8.username, t1.link, t1.locale, t1.pinnedtomain, t1.plugins, t1.pubtime,
t1.righttoleft, t1.status, t1.summary, t1.text, t1.title, t1.updatetime, t9.id,
t9.about, t9.isactive, t9.allowcomments, t9.blacklist, t9.bloggercatid,
t9.userid, t9.customstylesheet, t9.datecreated, t9.defaultallowcomments,
t9.defaultcatid, t9.defaultcommentdays, t9.defaultpageid, t9.defaultplugins,
t9.description, t9.editorpage, t9.editortheme, t9.emailaddress,
t9.emailcomments, t9.emailfromaddress, t9.enablebloggerapi, t9.enablemultilang,
t9.isenabled, t9.displaycnt, t9.handle, t9.icon, t9.lastmodified, t9.locale,
t9.commentmod, t9.name, t9.pagemodels, t9.showalllangs, t9.timeZone,
t9.weblogdayid 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
As you can see several tables are joined multiple times. Hideous SQL queries such as these are frequent when loading a blog page and can take as long as 30 seconds each in our test environment. This results in blog pages taking 3-30 seconds to load the first time, before the page is cached. I guess JPA is generating the SQL queries, and I wonder if we can replace JPA with Hibernate without changing the source code?
Is anyone else having problems with Roller's data layer?
Cheers!
--
Want an e-mail address like mine?
Get a free e-mail account today at www.mail.com!
RE: Problem with database load
Posted by Milo <mi...@moscowmail.com>.
Thanks, Boris. I'll add the indexes and see if they make any difference.
----- Original Message -----
From: "Boris Milikič"
To: user@roller.apache.org
Subject: RE: Problem with database load
Date: Fri, 7 Mar 2008 16:15:24 +0100
Hi
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 );
Boris
-----Original Message-----
From: Milo [mailto:milo@moscowmail.com]
Sent: Friday, March 07, 2008 11:41 AM
To: user@roller.apache.org
Subject: Problem with database load
Hi,
My company has a blog service with 30k blogs and 70k unique browsers
per week.
At the moment we use a heavily customized Roller 1.1 which was
developed a couple of years ago.
We are upgrading to Roller 4.0 but our database server cannot take
the load from Roller.
We have implemented several different page cache mechanims and we are
currently evaluating them:
- default LRU cache
- ehcache 1.4.1
- memcached + Greg Whalin java client
(http://www.whalin.com/memcached/)
- memcached + spymemcached (http://code.google.com/p/spymemcached/)
The performance gains are very high using ehcache and memcached, but
we still have problems with some queries in Roller.
Example:
SELECT t0.id, t0.content, t0.contenttype, t0.email, t0.name,
t0.notify, t0.plugins, t0.posttime, t0.referrer, t0.remotehost,
t0.status, t0.url, t0.useragent, t1.id, t1.allowcomments, t1.anchor,
t2.id, t2.description, t2.image, t2.name, t3.id, t3.description,
t3.image, t3.name, t3.path, t3.websiteid, t2.path, t4.id, t4.about,
t4.isactive, t4.allowcomments, t4.blacklist, t5.id, t5.description,
t5.image, t5.name, t5.path, t5.websiteid, t6.id, t6.activationcode,
t6.datecreated, t6.emailaddress, t6.isenabled, t6.fullname,
t6.locale, t6.passphrase, t6.screenname, t6.timeZone, t6.username,
t4.customstylesheet, t4.datecreated, t4.defaultallowcomments, t7.id,
t7.description, t7.image, t7.name, t7.path, t7.websiteid,
t4.defaultcommentdays, t4.defaultpageid, t4.defaultplugins,
t4.description, t4.editorpage, t4.editortheme, t4.emailaddress,
t4.emailcomments, t4.emailfromaddress, t4.enablebloggerapi,
t4.enablemultilang, t4.isenabled, t4.displaycnt, t4.handle, t4.icon,
t4.lastmodified, t4.locale, t4.commentmod, t4.name, t4.pagemodels,
t4.showalllangs, t4.timeZone, t4.weblogdayid, t1.commentdays,
t1.content_src, t1.content_type, t8.id, t8.activationcode,
t8.datecreated, t8.emailaddress, t8.isenabled, t8.fullname,
t8.locale, t8.passphrase, t8.screenname, t8.timeZone, t8.username,
t1.link, t1.locale, t1.pinnedtomain, t1.plugins, t1.pubtime,
t1.righttoleft, t1.status, t1.summary, t1.text, t1.title,
t1.updatetime, t9.id, t9.about, t9.isactive, t9.allowcomments,
t9.blacklist, t9.bloggercatid, t9.userid, t9.customstylesheet,
t9.datecreated, t9.defaultallowcomments, t9.defaultcatid,
t9.defaultcommentdays, t9.defaultpageid, t9.defaultplugins,
t9.description, t9.editorpage, t9.editortheme, t9.emailaddress,
t9.emailcomments, t9.emailfromaddress, t9.enablebloggerapi,
t9.enablemultilang, t9.isenabled, t9.displaycnt, t9.handle, t9.icon,
t9.lastmodified, t9.locale, t9.commentmod, t9.name, t9.pagemodels,
t9.showalllangs, t9.timeZone, t9.weblogdayid 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
As you can see several tables are joined multiple times. Hideous SQL
queries such as these are frequent when loading a blog page and can
take as long as 30 seconds each in our test environment. This results
in blog pages taking 3-30 seconds to load the first time, before the
page is cached. I guess JPA is generating the SQL queries, and I
wonder if we can replace JPA with Hibernate without changing the
source code?
Is anyone else having problems with Roller's data layer?
Cheers!
--
Want an e-mail address like mine?
Get a free e-mail account today at www.mail.com!
--
Want an e-mail address like mine?
Get a free e-mail account today at www.mail.com!
Putting login links on front page
Posted by abhay srivastava <ab...@yahoo.com>.
Hello folks,
I am creating my own them and I want to put username and password links on my front page. Can anyone help me here ? I wanted to know which method or action I have to call to do so ?
Thanks,
abhay
---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
Re: Problem with database load
Posted by Dave <sn...@gmail.com>.
On Thu, Mar 13, 2008 at 12:31 PM, David Fisher <df...@jmlafferty.com> wrote:
> 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?
The 4.0 data model is different from 1.1, less complex in some ways
(category & bookmark hierarchy) and more complex in others (group
blogs).
> 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?
The SQL generation is done by the ORM, which used to be Hibernate and
is now OpenJPA. I hope upgrading from OpenJPA 0.9.7 to 1.1 will
address some of these problems, if not then we should make sure that
Roller can be used with Hibernate/JPA,TopLink Essentials/JPA and/or
EclipseLink/JPA.
- Dave
Re: Problem with database load
Posted by David Fisher <df...@jmlafferty.com>.
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č
>>>>> <bo...@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
>>>
>
RE: Problem with database load
Posted by Boris Milikič <bo...@mf-rs.si>.
I agree with Allen to fix the query. Creating indexes, was my idea to solve this problem for now.
I ran explain plan on Oracle 10gR2 and got the same plan with and without those two indexes.
Boris
-----Original Message-----
From: Allen.Gilliland@Sun.COM [mailto:Allen.Gilliland@Sun.COM]
Sent: Thursday, March 13, 2008 5:07 PM
To: user@roller.apache.org
Subject: Re: Problem with database load
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č
>>>> <bo...@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
>>
>
Re: Problem with database load
Posted by Allen Gilliland <Al...@Sun.COM>.
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č
>>>> <bo...@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
>>
>
Re: Problem with database load
Posted by David Fisher <df...@jmlafferty.com>.
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
>
Re: Problem with database load
Posted by Allen Gilliland <Al...@Sun.COM>.
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č <bo...@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
RE: Problem with database load
Posted by Boris Milikič <bo...@mf-rs.si>.
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č <bo...@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
Re: Problem with database load
Posted by Allen Gilliland <Al...@Sun.COM>.
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č <bo...@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
RE: Problem with database load
Posted by Boris Milikič <bo...@mf-rs.si>.
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č <bo...@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
Re: Problem with database load
Posted by Dave <sn...@gmail.com>.
On Fri, Mar 7, 2008 at 10:15 AM, Boris Milikič <bo...@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
RE: Problem with database load
Posted by Boris Milikič <bo...@mf-rs.si>.
Hi
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 );
Boris
-----Original Message-----
From: Milo [mailto:milo@moscowmail.com]
Sent: Friday, March 07, 2008 11:41 AM
To: user@roller.apache.org
Subject: Problem with database load
Hi,
My company has a blog service with 30k blogs and 70k unique browsers per week.
At the moment we use a heavily customized Roller 1.1 which was developed a couple of years ago.
We are upgrading to Roller 4.0 but our database server cannot take the load from Roller.
We have implemented several different page cache mechanims and we are currently evaluating them:
- default LRU cache
- ehcache 1.4.1
- memcached + Greg Whalin java client (http://www.whalin.com/memcached/)
- memcached + spymemcached (http://code.google.com/p/spymemcached/)
The performance gains are very high using ehcache and memcached, but we still have problems with some queries in Roller.
Example:
SELECT t0.id, t0.content, t0.contenttype, t0.email, t0.name, t0.notify, t0.plugins, t0.posttime, t0.referrer, t0.remotehost, t0.status, t0.url, t0.useragent, t1.id, t1.allowcomments, t1.anchor, t2.id, t2.description, t2.image, t2.name, t3.id, t3.description, t3.image, t3.name, t3.path, t3.websiteid, t2.path, t4.id, t4.about, t4.isactive, t4.allowcomments, t4.blacklist, t5.id, t5.description, t5.image, t5.name, t5.path, t5.websiteid, t6.id, t6.activationcode, t6.datecreated, t6.emailaddress, t6.isenabled, t6.fullname, t6.locale, t6.passphrase, t6.screenname, t6.timeZone, t6.username, t4.customstylesheet, t4.datecreated, t4.defaultallowcomments, t7.id, t7.description, t7.image, t7.name, t7.path, t7.websiteid, t4.defaultcommentdays, t4.defaultpageid, t4.defaultplugins, t4.description, t4.editorpage, t4.editortheme, t4.emailaddress, t4.emailcomments, t4.emailfromaddress, t4.enablebloggerapi, t4.enablemultilang, t4.isenabled, t4.displaycnt, t4.handle, t4.icon, t4.lastmodified, t4.locale, t4.commentmod, t4.name, t4.pagemodels, t4.showalllangs, t4.timeZone, t4.weblogdayid, t1.commentdays, t1.content_src, t1.content_type, t8.id, t8.activationcode, t8.datecreated, t8.emailaddress, t8.isenabled, t8.fullname, t8.locale, t8.passphrase, t8.screenname, t8.timeZone, t8.username, t1.link, t1.locale, t1.pinnedtomain, t1.plugins, t1.pubtime, t1.righttoleft, t1.status, t1.summary, t1.text, t1.title, t1.updatetime, t9.id, t9.about, t9.isactive, t9.allowcomments, t9.blacklist, t9.bloggercatid, t9.userid, t9.customstylesheet, t9.datecreated, t9.defaultallowcomments, t9.defaultcatid, t9.defaultcommentdays, t9.defaultpageid, t9.defaultplugins, t9.description, t9.editorpage, t9.editortheme, t9.emailaddress, t9.emailcomments, t9.emailfromaddress, t9.enablebloggerapi, t9.enablemultilang, t9.isenabled, t9.displaycnt, t9.handle, t9.icon, t9.lastmodified, t9.locale, t9.commentmod, t9.name, t9.pagemodels, t9.showalllangs, t9.timeZone, t9.weblogdayid 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
As you can see several tables are joined multiple times. Hideous SQL queries such as these are frequent when loading a blog page and can take as long as 30 seconds each in our test environment. This results in blog pages taking 3-30 seconds to load the first time, before the page is cached. I guess JPA is generating the SQL queries, and I wonder if we can replace JPA with Hibernate without changing the source code?
Is anyone else having problems with Roller's data layer?
Cheers!
--
Want an e-mail address like mine?
Get a free e-mail account today at www.mail.com!