Knowing What's Causing Server Load Rise

[Sonic98]

The people at the host don't seem to know much about running one.

As you know from the other thread I have the myriad package from this host

http://www.ubiquityservers.com/vps.php

othersockbuf

is the resource that is usually the culprit, though there have been a small few numbers of time

kmemsize & numtcpsock seem to be at issue, but it's almost always

othersockbuf

in the black zone

I use the Member's Online Today mod, and it's often around 100-120. I have it set you show online for the last 15 minutes. Sometimes I set it to 10. Usually that shows users in the 30s

[.CMANNS]

cant always trust your host..

[Trouble]

| 18864 | my_database | localhost | my_database | Query   | 232  | Sending data | SELECT p.pid, p.queued, t.approved, t.forum_id

								FROM ibf_posts p

								 LEFT JOIN ibf_topic |

| 18879 | my_database | localhost | my_database | Query   | 223  | Sending data | SELECT p.pid, p.queued, t.approved, t.forum_id

								FROM ibf_posts p

								 LEFT JOIN ibf_topic |

| 18902 | my_database | localhost | my_database | Query   | 213  | Sending data | SELECT p.pid, p.queued, t.approved, t.forum_id

								FROM ibf_posts p

								 LEFT JOIN ibf_topic |

The root problem seems to stem from these queries which from the log, take a couple of minutes to send the query results from the mySQL server to the client (off which sits the PHP application). This suggests the results from these queries are huge in size and the large list of locked queries occurs as the table can't be changed while the data is being sent to the client. grep -R "SELECT p.pid, p.queued, t.approved, t.forum_id" * over the IPB source files yields two similar queries in sources/lib/search_mysql_(man|ftext).php:

			$posts_query = "SELECT p.pid, p.queued, t.approved, t.forum_id

													FROM ".SQL_PREFIX."posts p

													 LEFT JOIN ".SQL_PREFIX."topics t on (t.tid=p.topic_id)

													WHERE {$topic_filter}{$posts_datecut}  t.forum_id IN ($forums)

													 $posts_name AND ($post_like)";

I'd guess people are searching for too common words and the large results being returned set is causing things to break. You could try disabling the search function to see if the problem stops, which would confirm this is the problem.

[Sonic98]

cant always trust your host..

Obviously. BEcause they keep saying there is no way to improve performance with the current package

[.CMANNS]

Obviously. BEcause they keep saying there is no way to improve performance with the current package

Your site will only perform as well as their hardware.

[SecondSight_merged]

Thanks for your ideas and clues. I will try them tonight and see how my server is doing. :)

[SecondSight_merged]

I've not made the changes yet. I'm waiting for tomorrow... But I have another question : what do you thing of this SQL Processes ?

Thank you ! :)

[.CMANNS]

I don't understand whats wrrong with it.

[SecondSight_merged]

I wondered if I could be concerned with the number of sleeping processes wich are hanging...
Anyway, the server restarted on it's own... :(

[.CMANNS]

I wondered if I could be concerned with the number of sleeping processes wich are hanging...

Anyway, the server restarted on it's own... :(

ouch.

I could take a look at it if you have root.

[SecondSight_merged]

Thank you ! I didn't have the time to come here since I had a lot of work.

Apache has been rebuilt by the support staff, but this didn't help. So, the problem is momentarily solved with the installation of a php accelerator.

[.CMANNS]

In both threads of yours I haven't seen a "top" list of whats using all your resources.

SSH and type

top

thx

[SecondSight_merged]

Up to now, my server has been doing well. I'll do that as soon as the problem occurs again. :)

Thank you ! :)

[jmueller]

I changed sessions, members, posts and topics to innodb. Gave innodb 500MB of cache to work with and tada.

hmmm :g: when i try to change the tables mentioned to inno i get the following errors:

ibf_posts #1121 - Column 'post_date' is used with UNIQUE or INDEX but is not defined as NOT NULL 

ibf_members #1121 - Column 'bday_day' is used with UNIQUE or INDEX but is not defined as NOT NULL

any idea? did you get those too?

[.CMANNS]

jmueller, I'd suggest not changing to innodb, also make ibf_sessions into heap innodb is pointless for sessions, I've tryed both.

[jmueller]

jmueller, I'd suggest not changing to innodb, also make ibf_sessions into heap innodb is pointless for sessions, I've tryed both.

i didn't change it to inno, stayed with myisam. i changed the sessions table to heap though.

i read some stuff and from what i understand it is okay to set sessions to heap but nothing else. heap tables stores the information in RAM and so when the server crashes or shuts down the data stored in a heap table is lost. for sessions that's absolutely okay, i guess. i think i will never have 500 sessions or more active at once so it should be no performance problem to use heap tables.
do you have any experience with heap tables, cmanns? you only have ibf_sessions set to heap and nothing else? i'm just curious. what is the performance limit for a heap table - 500 rows? 1000 rows? 10.000 rows? any ideas?
innodb is a performance boost on posts and member tables, i guess. even though i'm satisfied with the performance of my boards i would like to get some input. did you change those tables to inno and notice a difference? if so - minor or major?
i know, i know ... too many questions so early in the morning :lol:

[.CMANNS]

I've changed tables to differnt types before, for shoutbox I set its data table to heap and got great performance w/over 4 shouts a second at times. I dunno the performance limit of heap, I do know if your board is 'big' you need to raise the max heap table size before it turns back into myisam and written to the disk.

[jmueller]

I've changed tables to differnt types before, for shoutbox I set its data table to heap and got great performance w/over 4 shouts a second at times. I dunno the performance limit of heap, I do know if your board is 'big' you need to raise the max heap table size before it turns back into myisam and written to the disk.

are you talking about the d22 shoutbox? because that one uses 'auto_increment' on the s_id and thus changing the table to heap doesn't work. heap does not support that attribute. how did you do that? :blink:

[.CMANNS]

It wasn't deans

[bfarber]

If you store a shoutbox table in HEAP all shouts would be lost when mysql restarts. You couldn't do that for a standard shoutbox script, unless you didn't care about retaining the data.

innodb is not a performance boost on topics or posts tables. People misunderstand what innodb does and doesn't do.

First, what it DOES do:
Pros: row-level locking (instead of locking the entire table to fetch a row, it only locks the one row and leaves the rest of the table accessible - this CAN be a big performance boost in the right circumstances)

Cons: it uses up to as much as 3x the disk space, so if you have limited disk space on your account, be careful with this

What it doesn't do:
Cons: Support fulltext searching


Now, if you switch over to a search engine like sphinx and use innodb on the posts and topics table you COULD get a noticeable performance difference. But please keep in mind for all of this, you're only going to be able to notice the difference and do a lot of the stuff needed if you have a dedicated server. ;)

Some good mysql optimizing advice:
http://www.databasejournal.com/features/my...cle.php/3367871
http://www.databasejournal.com/features/my...10897_3110171_2
http://www.databasejournal.com/features/my...10897_3110171_2

Some apache advice:
http://www.howtoforge.com/configuring_apac...mum_performance


You also have to know what is going on with the server at the same time. Does your database backup run during your peak hours for example? If so, that can be your problem alone - reschedule the cron. Or do you have a bunch of things running that you don't use? Do you have 10,000 apache modules loaded that are never used? What about php modules - how many of those are loaded that are not used?

Simply running less stuff on your server can make all the difference.

[jmueller]

thanks for that post bfarber. i'll have a look at the resources when i have a free minute. heap for the sessions sure helps a lot. i'm anxious to try zends' caching engine which i'll do in a couple of weeks. should improve performance even more.

[.CMANNS]

Simply running less stuff on your server can make all the difference.

or tuning it correctly

Simply reading the phpmyadmin status page can help you tune it perfectly

[rct2·com]

Hello !

I'm back. I finally managed to read the httpd.conf file. I have these values :

KeepAlive On MaxKeepAliveRequests 2000 KeepAliveTimeout 10 MinSpareServers 10 MaxSpareServers 20 StartServers 15 MaxClients 2000 MaxRequestsPerChild 0

Timeout  10

Your first line of defence against this paging should be that MaxClients setting. Use 'top' to see a typical memory allocation for httpd process, then divide that into the amount of physical memory you have. If you take the answer and use that as your MaxClients setting, it should almost completely remove paging.