silvrhand

Hey Gang,

Well unfortunately we are having some performance issues of late, it's related to one SQL query that locks the thread/post table for up to 60 seconds, since a lot of the SQL is created on the fly depending on the page it's very hard to find where the query is being executed from for me to dig through the code.

I have temporarily put a server load limit in at 8, which is pretty high. When the db locks the table the more and more users that try to browse the worse and worse it gets. Until I can find and fix the query hopefully the next few days I'm leaving this in to help try to keep the problem under control.

I know it's a pain but please work with me here, if you have any problems/suggestions/complains please feel free to PM me.

- John

C4talyst

If you guys have a dedicated server, do you run the db from a secondary hard disk? I always found that helped some with sql load issues and disk i/o, even if it's not related to this exact issue.

Mike Wazowski

iTrader: (113)

now it seems like the site is falling apart. The link at the top is cutting in and out

silvrhand

it's people doing a very common search that hits a lot of threads, that's what it is.

The DB is on a 3 disk raid5 array with a 256M caching controller. wait IO is not the problem, the search on the thread table that locks it is.

Mike Wazowski

iTrader: (113)

delete or merge the threads LOL


does it have to do with PopChargers? hahahahaha

silvrhand

How much HP will the PoPcharge get me?
What popcharger flows the best.
What stickers should I put on the server with my pop charger?

Ichigo

iTrader: (6)

Thats funny that a search probably queries the db more than someone responding to a question. Not to mention ten unecessary flame posts telling the poster to search more.

silvrhand

This is a problem for all vbulletin sites after a while, most disable searches, while some setup multiple slaves servers to handle the queries while the normal insert/select's happen on the master server.

silvrhand

Can I get some people to test searches, fulltext searches are now on, I had to index the post/thread table it took a while though..

DeeZ

im getting so many of the "server too busy" thing


please fix it soon, im going crazy over here!....lol

cessna

Seems A OK to me

silvrhand

Today?

buzzardmountain

iTrader: (17)

I've gotten several today.

DeeZ

yeah i got some today

disturbed2483

iTrader: (9)

me three

sq40

iTrader: (4)

LOTS of Server Busy messages for me today too.

silvrhand

sigh never ending job of late, well I turned back on the mysql slow log, not getting the search slowness now at least.

silvrhand

Ouch..

# Query_time: 35 Lock_time: 29 Rows_sent: 1 Rows_examined: 0
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate
) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS display
groupid,
language.phrasegroup_forumdisplay AS phrasegroup_forumdisplay,
language.phrasegroup_inlinemod AS phrasegroup_inlinemod,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep

FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.
userid) INNER JOIN language AS language ON (language.languageid = IF(user.languageid = 0,
1, user.languageid))

WHERE user.userid = 58872;

yobri

iTrader: (2)

Back up...

silvrhand

yah sorry was working with vbulletin support to check some stuff and I forgot I had shutoff the httpd server for a minute.. doh /fired!