Bug Slow queries

[ecforum]

Hi support,

We have installed the Advanced post Thanks/like product on our site to replace vBSEO likes, but it has some very slow queries that are overwhelming the server. Is there a way of handling the count(*) and other resource intensive queries? Can they be handed off to another process? Is there a better way of making use of caching?
We need to get this working before we can move vBSEO to DradonByte SEO.

Thanks,
Toby

 

[Fillip H.]

Could you be a little bit more specific as to what query is causing this issue?

 

[ecforum]

Hi Fillip H.,

The queries are for example:

SELECT COUNT(*)
FROM vbdbtech_thanks_entryAS entry
WHERE ?=?
AND entry.varname = ?AND entry.contentid = ?
LIMIT ?

 

[ecforum]

That one was taking 93,000ms and was the most common.

Other examples:

SELECT 
						COUNT(*) AS entrycount,
						post.title AS posttitle,
						thread.title AS threadtitle,
						thread.title AS title,
						post.postid,
						thread.forumid
					FROM vbdbtech_thanks_entry AS entry
					LEFT JOIN vbpost AS post ON(post.postid = entry.contentid)
					LEFT JOIN vbthread AS thread ON(thread.threadid = post.threadid)
					WHERE contenttype = ?
						AND varname = ?
						AND thread.title IS NOT NULL
						
						
					GROUP BY entry.contentid
					ORDER BY entrycount DESC
					LIMIT ?

taking 224,000ms
and:

DELETE FROM vbdbtech_thanks_entry WHERE entryid = ?

taking 100,000ms

There are plenty more examples.

Is there anything you can suggest to make these manageable on a large table of likes? We have ~16,000,000

Thanks,
Toby

 

[Fillip H.]

SELECT COUNT(*)
FROM vbdbtech_thanks_entryAS entry
WHERE ?=?
AND entry.varname = ?AND entry.contentid = ?
LIMIT ?

I'm unable to find this query anywhere in the product. Can you please post the full query, without the ? replacements?

SELECT 
						COUNT(*) AS entrycount,
						post.title AS posttitle,
						thread.title AS threadtitle,
						thread.title AS title,
						post.postid,
						thread.forumid
					FROM vbdbtech_thanks_entry AS entry
					LEFT JOIN vbpost AS post ON(post.postid = entry.contentid)
					LEFT JOIN vbthread AS thread ON(thread.threadid = post.threadid)
					WHERE contenttype = ?
						AND varname = ?
						AND thread.title IS NOT NULL
						
						
					GROUP BY entry.contentid
					ORDER BY entrycount DESC
					LIMIT ?

Go to the Settings page and disable the Enable "Hottest Threads / Post" page.

DELETE FROM vbdbtech_thanks_entry WHERE entryid = ?

If deleting a single entry via the Primary Key takes 100 seconds, your database server is either
a) Backlogged, causing query queues
b) Severely underpowered for the demands you have of it
c) Severely misconfigured.

To put it into perspective, the vBCredits "Transaction" table here @ DBTech currently sits at 5,754,112 rows.

1 queries executed, 1 success, 0 errors, 0 warnings

Query: delete from vb_credits_transaction where transactionid = 34069

1 row(s) affected

Execution Time : 0.164 sec
Transfer Time : 0 sec
Total Time : 0.165 sec

That was a query ran from my work computer in the UK, to the database server located in Tampa, Florida, USA. It completed a round trip pretty much to the opposite side of the world in 165ms.

If your database server can't complete a similar query when it doesn't even have to travel outside your server's LAN, then there's a severe issue somewhere, and it's not in our mod.