UBB.Developers Forums Community Development Programming MySQL and Server Configuration Spring Cleaning

www.UtterAccess.com came to be from the site www.AThree.com, which originally used a web bbs forum, then threads 5.x.x.

Running 6.1.1, we now have >23,000 users, 210,000 posts and are noticing erratic performance with no definite conclusion as to why. I've optimized the Db and have begun a search to reduce 'weight' anywhere possible, feeling the performance issue may be being caused by the quantity of records.

I ran this query on the database via phpmyadmin:


which returns >900 orphaned (useless) records in the w3t_Last table. They probably exist due to the conversions.

I'm fairly savvy with databases, but not so with mySQL. Other than sitting here and knocking out the records 1 by 1, does anyone know what the query would be to toss these in one shot?

Any other performance enhancements anyone can think of would be appreciated. We were using mySQL 4 but down graded to 3 to see if it would help (no. )

Thanks all,

Gord

> Other than sitting here and knocking out the records 1 by 1,
> does anyone know what the query would be to toss these in
> one shot
Write a little script doing this ;-)
MySQL does currently not support join or subselects in delete-statements.
But if you hold your result and kick the records out using the primary key it should work.

> We were using mySQL 4 but down graded to 3 to see if it
> would help
Of course MySQL 3 is much slower than MySQL 4!
What says your SHOW STATUS and what are the values in your
my.cnf?

So long...

Indy

Well, Not such a big difference with mySQL 3 and 4 speed - at least in my case. This was only applied as it was advised on the WHM CPanel news in the event any mySQL issues were being encountered.

Here's a solution that I've applied and I haven't heard any complaints yet...

Run a delete query to remove all w3t_Last records older than say, 1 year. First off, if you have thousands of posts as I do, a member who comes back after 1 year of absence ends up with such a horrific number of un-reads, they'll probably just hit the 'mark all read' anyway.

This tossed around 26,000 records from the bloated table and immediately improved the speed. I might even move that up to 6 months to see the reaction.

Ultimately, re-running my original query returned 1 record. That I could handle with a manual delete.

Thank you,

Gord

Wow. I had about 12000 entries from before this year which I removed and it was a significant speed boost to my board. Thanks for that tip!

Anytime - my pleasure.

I've also just noticed that the records included are showing User versus all forums?! This is not logical. Should be User versus the forums they're entitled to see. With 40+ forums X 23,000 members ... well, that's a stack of records to read so frequently.

Rick? Perhaps a little query work could set this so only the Users viewable forums are logged? Let's see... I have about 5 forums which are privilege only, so that's 5 x say, 22,500 which could be removed ...yep. Helluva savings for space and query time.

Opinions anyone?

Thanks again,

Gord

There is a variable called $Viewable in the query which limits the query to only the forums which the user is allowed to see. It is used on all forum queries to make sure that they won't see any forums that they are not supposed to.

Well, I went even further yesterday and reduced the 'sweep' to anything older than 3 months (another 8,000 or so). Still no complaints and certainly not as dramatic a feeling as the first one, but satisfying none the less. No noticible change in speed to my eyes.

Perhaps this should be written in as a Cron task along the lines of Expire threads and subscriptions?

Also, back using mySQL 4 with thanks to www.Bravidio.com for undo-ing and re-doing ...trying what ever it takes to make things better. I'll report in if there's any noticible increase or decrease.

Gord

Why does w3t_Last keep more than 1 entry per forum?

and what is the query you ran to remove those older than say six months?

I use the doexpire cron job I think you can set forums up to expire threads after so much time already?

It shouldn't have more than one entry FOR EACH USER per forum.

If so - see the "anyone running 6.4" in the announcement forum here. If there were duplicates, then your indexes probably aren't right.

This table keeps track of that last time each user has visited a forum.

So if you have 20 forums, you can conceivable have 20 entries for each user.

>This table keeps track of that last time each user has visited a forum.

ic....just what would happen if one would empty it completely?

Everyone would have every post unread -

so that's OK to do - just tell everyone to use the mark all read feature and set everything read to start fresh.