Thanks, Boris. I'll add the indexes and see if they make any difference. ----- Original Message ----- From: "Boris Milikič" To: [email protected] 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:[EMAIL PROTECTED] Sent: Friday, March 07, 2008 11:41 AM To: [email protected] 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!
