Question Advanced Tagging

[Rick Reeno]

I'm looking for suggestions on a problem that we're having.

We have a busy forum, 225,000 users and over 17 million posts.

But we started having an issue where there is a delay between our master server and our slave.

Our server team said these queries were causing the delay.

Any suggestions on how to avoid this??



| 152195629 | boxing_slave | 10.170.246.5:58500 | boxing_newboxing | Query | 17 | Sending data | SELECT COUNT()
FROM dbtech_usertag_quote AS entry
WHERE (entry.quotedid = '124672' )
|
| 152195636 | boxing_slave | 10.170.246.5:58511 | boxing_newboxing | Query | 17 | Sending data | SELECT COUNT( )
FROM dbtech_usertag_quote AS entry
WHERE (entry.quotedid = '27514' )
|
| 152195642 | boxing_slave | 10.170.246.5:58525 | boxing_newboxing | Query | 16 | Sending data | SELECT COUNT()
FROM dbtech_usertag_quote AS entry
WHERE (entry.quotedid = '129904' )
|
| 152195646 | boxing_slave | 10.170.246.4:42787 | boxing_newboxing | Query | 16 | Sending data | SELECT COUNT( )
FROM dbtech_usertag_quote AS entry
WHERE (entry.quotedid = '42455' )
|
| 152195647 | boxing_slave | 10.170.246.5:58535 | boxing_newboxing | Query | 15 | Sending data | SELECT COUNT()
FROM dbtech_usertag_quote AS entry
WHERE (entry.quotedid = '97793' )
|

 

[Rick Reeno]

I believe the server team said this was the exact query, at least during one of the delays.

There's a query taking a long time, which is using up CPU resources to process. It just finished, but it looks like your primary problem here is you have large queries that are regularly locking or slowing down table transactions. In this case the problem query was:


| 1 | system user | | boxing_newboxing | Connect | 492 | update | INSERT IGNORE INTO dbtech_usertag_quote
(userid, quotedid, postid, type, dateline)
VALUES (
'190448',
'10981',
18498096,
'post',
1518988251
)

 

[Rick Reeno]

Basically the problem of what's happening, is we have people making posts, threads, etc - but they are not coming up right away. There are moments when the slave is 20-30 seconds delayed from the master.

They said that query in question takes so long to go through that it's causing that delay for the slave to catch up. When there are too many of those queries the slave needs the extra time to digest them all.

We never did any pruning before, and I have no idea if that will help, so I pruned everything that was older than 3 months (we've had the mod since late 2015 at least and I assume that there may have been an entire mountain of entries).

Not sure if there is anything else that I should be doing to avoid this problem.

 

[Fillip H.]

Did pruning the entries fix the problem?

 

[Rick Reeno]

Did pruning the entries fix the problem?

So far it has not happened again.

Can you explain why pruning would fix the problem or cause the issue if not done on a regular basis?

 

[Fillip H.]

If the table had millions of entries, that could be the reason. Since I do not have a large database to test this (or any other product) against, I wouldn't know exactly what indexes may need to be applied.

 

[Rick Reeno]

I believe there were between 1-2 million entries, maybe over 2. I know there were 67,000 entries alone just between Sept. and Nov. of last year and the mod has been on the site for over 2 years without pruning.

From what I gather, the issues may have been due to member profiles being crawl-bombed by bing bots. Because many of the error pages were always related to user profile pages.

 

[DragonByte Technologies]

Hello Rick Reeno,

This ticket has now been closed with the status Answered.

We hope your issue or question has been addressed to your satisfaction. If not, please feel free to re-open it by clicking this link.

If you have any further issues or questions, please feel free to start a new support ticket via the button at the top of every page.

Thank you!