Database overload

Found something that doesn't work as expected? Encountered PHP errors? Submit your bug reports here!
HobbySpacer
Posts: 1
Joined: Sat Jan 12, 2013 3:16 pm

Database overload

Postby HobbySpacer » Sat Jan 12, 2013 3:33 pm

My hosting site has suspended access to my Nucleus database due to"excessive overloading". I only get about 1500 hits a day or so I cannot see why this is happening. I've been blogging for several years so the DB has grown but I don't know why it would suddenly have this problem.

Their message says that the following query "examines 48,048 rows and 4 times per second":

SELECT i.inumber as itemid, i.ititle as title, i.ibody as body, m.mname as author, m.mrealname as authorname, i.itime, i.imore as more, m.mnumber as authorid, m.memail as authormail, m.murl as authorurl, c.cname as category, i.icat as catid, i.iclosed as closed FROM nuc_nucleus_item as i, nuc_nucleus_member as m, nuc_nucleus_category as c WHERE i.iblog=1 and i.iauthor=m.mnumber and i.icat=c.catid and i.idraft=0 and i.itime<="2013-01-12 01:03:49" ORDER BY i.itime DESC LIMIT 0,5

EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE i ref itime,iblog,iauthor,icat,idraft iblog 4 const 16016 Using where; Using temporary; Using filesort
1 SIMPLE m ALL PRIMARY,mnumber 3 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 NucelusNewDB.i.icat 1

--
Is there any reason that Nucleus would repeatedly hit the DB with this query? I have little DB experience so any suggestions as to what is going on and how I can fix it?
ftruscot
Nucleus Guru
Nucleus Guru
Posts: 7430
Joined: Wed Feb 22, 2006 6:19 pm
Location: Massachusetts
Contact:

Postby ftruscot » Wed Jan 16, 2013 2:04 pm

That looks like the query for showing the main index page. It would only repeat if someone is trying to load the page. It shouldn't be examining 48000 rows unless you have that many items, members, and categories. Perhaps you have a lot of comments (some of which are spam) and a subsequent query to display number of comments for each displayed item is running through a lot of rows.

Either way, perhaps you may want to install the NP_BadBehavior plugin which will cut down on spam and keep bad bots from even loading your page (cut down on queries). Also, check what's in your database tables and see if one is really large.
Is your question not solved yet?
Search our FAQ,
read the Documentation, or
browse the list of available plugins.

Check out my plugins

Return to “Bug Reports & Feature Requests”