UBB.Developers Forums UBB.threads 7 Development How Do I? Speed problems

Hi,

Lately I have been getting more and more complaints from my users that my board is very slow, and unfortunately they are right.

The problem is that we can't find out what is causing this. We are running on a dual Netra 300 MHz with 1Gb of memory (dedicated server) and the board isn't that big (~30-35 users at any given time, ~170K posts). I would think that the machine should be able to cope, but according to my partner it is running at 0% idle time and both harddisk are busy contiuously as well.

Obviously the easiest solution would be to throw more hardware at this, but hardware is not cheap. Besides we only upgraded to this server (donated by our members, btw) 6 months ago.

For the moment we have switched off full tracking, but both I and the members much prefer having this switched on.

One possible solution we are thinking about is moving all posts older than 3 months to a read-only archive. Unfortunately since we would want this to be in a different database, we have not figured out how to do this.

Suggestions would be more than welcome.

Sanuk!

PS Oh, we are running 6.1.1 btw

You could also set the default postlist to 10 posts which will save alot of queries to the database versus say a 20 post limit.

Did switching off full tracking make the response time acceptible?

Hi,

Thanks for the suggestion, but it already is set to 10.

Sanuk!

Have you isolated it to be a board thing? IE - if you close the boards for a few minutes, does the server calm down?

What are the most accessed files? Do you have logs running for that? If you can find out what's being used the most, you can work on reducing the load that the particular script does to the server.

A suggestion would be to turn off zlib compression if it is running. It improves speed by compressing files -- the problem is that in high-traffic sites this means that the processor is doing a lot of extra work compressing stuff.

Hope that helps a bit.

Hi,

"Have you isolated it to be a board thing?"
Not really, but since this is the only thing running on the server (AFAIK) it seems highly unlikely that it is something else, right?

"What are the most accessed files?"
From the logs:

The stylesheets I am assuming are irrelevant.

"A suggestion would be to turn off zlib compression if it is running."
It isn't.

Anyway, thanks for the suggestions.

Sanuk!

Hi,

One of my board members suggested upgrading the MySQL to version 4.0.12 and switching on the caching. However he also mentioned that this is still very new and that perhaps it could conflict with UBBT.

Does anybody here know?

Sanuk!

In the case of showflat being the highest hit page (215 000 - is that in one month? Quite the traffic )...

-in theme settings, you could turn sown the default number of shown posts from where it is now. (variable amount of speed recovered)
-not show pictures with people's posts (a little speed recovered)
-turn off/remove ratings (a little speed recovered)
-if installed with php, turn on zlib as an experiment...it will reduce the size of the info being sent, but increase processor usage... it has the potential to make it much slower, or much faster, or balance out to do nothing (in which case it might be best with it off)

anyone else got any ideas?

Hi,

"In the case of showflat being the highest hit page (215 000 - is that in one month? Quite the traffic )..."
About 22 days, I think. Traffic is decent yes

"-in theme settings, you could turn sown the default number of shown posts from where it is now."
Already down to 10.

"-not show pictures with people's posts (a little speed recovered)"
Hmmm, I think this would hardly be worth the bother since those come from other servers.

"-turn off/remove ratings (a little speed recovered)"
Removed already.

"-if installed with php, turn on zlib as an experiment...it will reduce the size of the info being sent, but increase processor usage... it has the potential to make it much slower, or much faster, or balance out to do nothing (in which case it might be best with it off)"
I'd rather not try this since our server is already at 0% idle time

Anyway, thanks for the suggestions.

Sanuk!

Hi,

(sorry missed this one)

"Did switching off full tracking make the response time acceptible?"
Yes, it made a rather large difference. It is annoying to check the board without it though

Sanuk!

[]ksanuk said:
"-not show pictures with people's posts (a little speed recovered)"
Hmmm, I think this would hardly be worth the bother since those come from other servers.
[/]

They may come from other servers, but the URL is grabbed from your database. It is just a tiny recovery though.

Hi,

Okay, point taken. Still I think this would not be noticable.
Our biggest problem seems to be too many posts (I think my partner said that the post table was something like 1Gb?), which is why I would like to move a chunk of them to an archive.

Sanuk!

[]One of my board members suggested upgrading the MySQL to version 4.0.12 [/]

We use MySQL 4.0x for several months now. It runs stable and the performance improvement is quiet impressive.
Caching will not work, cause the posts table gets changed all the time.

[]Astaran said:
Caching will not work, cause the posts table gets changed all the time. [/]

It may not work on the posts table, but it will help on a lot of other tables, and if you run any other php/mysql scripts on your site it would help with them as well.

Turning off full new post tracking on the front page is inevitable once you reach a certain popularity (unless you have wads of cash to throw into hardware).

[]"Did switching off full tracking make the response time acceptible?"
Yes, it made a rather large difference. It is annoying to check the board without it though [/]

I've also heard that upgrading to MySQL 4 can help, but don't have personal experience with this.

Is your MySQL configuration file tuned optimally for your server? The MySQL distribution includes three or four sample config files, designed for different situations. Then you can customize it further yourself.

I don't know enough about MySQL to help with this, but others might be able to help. Also, I think that at mysql.com, you can pay them for a custom tuning.

I can't say anything about tables apart from the ubbthreads ones, but cache rate for the ubbthreads tables is rather bad.

I tested it on my live board, i got:

| Qcache_queries_in_cache | 823
| Qcache_inserts | 22941
| Qcache_hits | 26280

So it doesn't have much effect.

If you have a large post table, the search option is normally the bottleneck.
If users submit queries that fullfill several table scans, the server load increased dramatically and all others queries are locked.

You can do some mysql and change the search routines, but that depends on the individual server configuration.

Hi,

"Is your MySQL configuration file tuned optimally for your server?"
Knowing my partner I think it likely is. His MySQL skills are quite decent (much better than mine ).

Perhaps a combination is in order, more RAM, upgrade MySQL and remove a bunch of old posts.

Sanuk!

How long had your mysql server been up when those stats were taken?

You are correct about the search being the bottleneck, it's the same with most bulletin board software. I've recently written a search modification to disallow certain words that are very common on our board as well as requiring that the search term is at least 5 characters.

Maybe one of you MySQL 4 users can see this post at community. Someone is having trouble. But it must be them.

Ksnnuk - I think Lisa_P once "archived" old posts to a different table to speed things up... I know her board is huge. I think she's on vacation now... but she might have some insight into how that was done.

That stats are from my developing board that hasn't much traffic so.
Here are some stats from my production board.
Uptime witch cache ~ 3h

select statements total 138.337
Qcache queries in cache 2935
Qcache inserts 137368
Qcache hits 153421


So it has very little effect. It has no effect on the main tables that cause the heavy load (posts, messages, users).
The small tables that have static content like categories, addressbook and smilies can be cached, but that should have much effect, cause this tables are normaly very small.

Hi,

"You are correct about the search being the bottleneck, "
Hmmm, had not realized that. I think search is used quite a bit on the board.

"I've recently written a search modification to disallow certain words that are very common on our board as well as requiring that the search term is at least 5 characters."
Would you mind sharing the code for this? Might be useful on my board as well.

Sanuk!

Hi,

"I think Lisa_P once "archived" old posts to a different table to speed things up... I know her board is huge. I think she's on vacation now... but she might have some insight into how that was done."
Thanks. I might PM her.

Sanuk!

Hi,
How long has it been since the tables were optimized? I'm sure that you have done this but I just thought I would throw it out there since you didn't mention it.

Hi,

"How long has it been since the tables were optimized? "
To be honest, I have no idea. My partner handles all of the server stuff.
I'll ask him. Thanks.

Sanuk!

I optomized my Users table last night (red line for overhead) and then this morning it was back to the same number in the red line.

Not sure that optomizing has any long term effects based on my little experiment.

[]extremebikini said:
I optomized my Users table last night (red line for overhead) and then this morning it was back to the same number in the red line.

Not sure that optomizing has any long term effects based on my little experiment. [/]

Hmm , I would have thought optimizing tables would have had more of an affect on larger boards. Given the time it would take to search tables of that size you would think(or at least I would) that the more effecient the tables=less wasted time=increase in performance.

I guess the proof is in the pudding though.

Something sounds odd about a forum with 170k posts taking up 1 gb of space... are you allowing some huge file attachments? 100 - 120 mb sounds more like it.

Hi,

No attachments at all, but we do get quite a bit of large posts. Could be wrong on the 1Gb as well though. I seem to recall my partner mentioning this number, but could remember incorrectly.

Sanuk!

[]ksanuk said:
We are running on a dual Netra 300 MHz with 1Gb of memory (dedicated server) and the board isn't that big (~30-35 users at any given time, ~170K posts).

AllenAyres said:
Something sounds odd about a forum with 170k posts taking up 1 gb of space... are you allowing some huge file attachments? 100 - 120 mb sounds more like it. [/]

The way I read it the 1Gb of memory is referring to the amount of RAM the server has not how much space the board is taking up.

If I'm reading it correct...then how much space is the board taking up? How big are your database tables?

Hi,

It seems that I was mistaken. The database is 175Mb and not 1Gb as I first thought.

Some other info my partner send me (which means nothing to me ):

Mysql usage:
mysql Ver 11.15 Distrib 3.23.42, for sun-solaris2.8 (sparc)
Connection id: 202168
Current database:
Current user: []root@localhost[/]
Uptime: 7 days 23 hours 21 min 54 sec

Threads: 2 Questions: 2369584 Slow queries: 289 Opens: 83
Flush tables: 1 Open tables: 76 Queries per second avg: 3.440

Sanuk!

My modifications work with MySQL 4.x only.
I recommend to update your MySQL anywise. The speed improvement is significant.

Hi,

"I recommend to update your MySQL anywise. The speed improvement is significant."
Yeah, my partner is already testing the new version of MySQL.

Sanuk!