Optimize the database

peet · Post by **peet** » Tue Apr 05, 2011 7:49 pm

Depends from hour to hour for. Sometimes posting is annoying slow, sometimes "normal". Most be the SQL game I suppose.

Broodwich · Post by **Broodwich** » Tue Apr 05, 2011 8:21 pm

yeah the past week or so its been intermittent for me as well

fuzzylunkin1 · Post by **fuzzylunkin1** » Wed Apr 06, 2011 7:28 am

Most forum SQL code is utter @#(! and this is probably no exception.

Post by **Tigereye** » Wed Apr 06, 2011 5:47 pm

The SQL game went offline for a month for a complete rewrite to introduce computational limits. It's back online with only 2 users (the author and me) and I haven't played it for a month. It exists purely as a proof of concept and is not actually played anymore.

Plus, that SQL game used a completely different database engine. It worked on Postgres while our forum DB uses MySQL.

I was planning on upgrading the forum software sometime this month during my surgery recovery. When this happens there will be a big database purge and clean first so that much less needs to be migrated over.

But as others have said in this thread... I don't seem to see the problems you describe. Yes sometimes it may take 1 or 2 seconds for a page to load instead of feeling "instant" but never have I waited 2 *minutes* for any reply from this server.

I just ran a TOP command on the saucetel server and of its 8 cores only 3 of them were loaded at 60%. The other 5 were idle. Plus there are gigabytes of RAM free - the pagefile isn't being touched.

On top of this, new RAM is about to be purchased for our forum server and it will be installed in about a month.

If I find myself awake really late one night I'll look into running some DB maintenance after taking the forum offline, but I don't want to take the forum offline during the day.

--TE

Post by **FreeBeer** » Wed Apr 06, 2011 10:11 pm

FWIW, TE, I have seen pretty big lags. Never two minutes, but 15-20 seconds has happened. Response is sporadic - sometimes instantaneous, sometimes 1-3 seconds, sometimes longer. And it seems to go in batches... ie once I start seeing "lag", I'll continue to see it for many minutes. Response can be particularly bad when sending a post. (and I have a good ping time to TO fwiw)

fuzzylunkin1 · Post by **fuzzylunkin1** » Wed Apr 06, 2011 11:17 pm

Ever narrow it down to a certain time of day/week?

Post by **FreeBeer** » Wed Apr 06, 2011 11:31 pm

Nothing specific. I'm generally on this forum throughout the day (mostly mornings and evenings, but mid-day on weekends, too). It can happen at any time.

Mastametz · Post by **Mastametz** » Wed Apr 06, 2011 11:54 pm

Yeah, what others said. It's really random. Sometimes it loads instantly and sometimes I feel like I'm on tin-can-on-a-string internet.

Post by **Tigereye** » Thu Apr 07, 2011 3:12 am

I'll look into setting up some automated stats of CPU, RAM and bandwidth of the server to see if there are any spikes that would explain it.

Thanks for bringing it to my attention. I'll let you know if I find anything.
For all I know all we need is a bit of database tuneup as Bard mentioned.

--TE

Mastametz · Post by **Mastametz** » Thu Apr 07, 2011 3:41 am

It literally took me an entire minute to access the forum from the home page, just now.