Erratic server load spikes

[Guest]

Have you spoken to IPS / submitted a ticket about this? They might be able to help you out. IPB runs on some very large forums without the sort of issues you are experiencing.

[MarcusInMd]

For reference things that we have done to try to stop the server load spikes:

Tweaked my.cnf file numerous times.
Tweaked httpd.conf numerous times.
Installed eaccellerator.
Went from 2GB of ram to 4GB of ram.
Changed IPB code to remove LOWER from many php files.
Changed sessions to heap.
Optimized the database.


During these server load peaks we have:
Disabled searching.
Turned off every feature in the CPU/Savings section of the forum software.
Rented another server in the same data center and off loaded all of the graphics to it.
Disabled Guest viewing on our forum.
Restarted apache.
Restarted mysql.


If I can think of anything else I will update this list. I am sure I have missed a few things.

Have you spoken to IPS / submitted a ticket about this? They might be able to help you out. IPB runs on some very large forums without the sort of issues you are experiencing.

They have been of little help. I have opened about 10 tickets or so on the same problem over the past two years. They always come back with the same thing basically. You need to do a search on how to change mysql settings etc. Pretty lame if you ask me. I have really been patient with them and have spent thousands on new hardare (another grand last night on the disks and controller.) Our software subscription is up this year and I am having a very difficult time renewing it because of this and all of the hacking issues this past year.

[Guest]

Have any of your tickets about it ever made it to advanced support, so you could talk with the developers about the issue?

[MarcusInMd]

Have any of your tickets about it ever made it to advanced support, so you could talk with the developers about the issue?

I don't think so. How would I be able to tell? I know that once they said they forwarded it to someone. LEt me see if I can dig up the ticket history.

I have limited time today...getting ready to head out on a service call.

[MarcusInMd]

This was the reponse of the last service ticket I sent to them last december.

Hello,

After reviewing this ticket with a few colleagues, we would like to make the following recommendations to see if this helps address the issues you have been experiencing:

--Under the ACP->Maintenance->Forums->Edit Forums edit the settings for each forum and in the drop down list near the end of the form that says "Default date cut off for topic display" make sure it is set to "Show All". With any other setting, IPB has to produce another query that checks the cut off date, which can effect performance on large servers.

--If your crm_sessions table is not already set this way, change the table type/engine to "HEAP". In most versions of phpmyadmin, you can do this by navigating to the crm_sessions table, click Options, and then change Table Type to HEAP.

Let me know if this does not help, or does not fully resolve the issues.

Please let me know if you have any further questions or concerns. Thanks!

All of these suggestions were already implemented.

[MarcusInMd]

Implementing the new hardware RAID controller and raptor drives this upcoming week. Switching to FC4 64bit from FC2 32bit. Will try reconfiguring the my.cnf and the httpd.conf files again. Once we get busy again I will report back here with results. We are presently running on a temp server that is a P4 HT 3.2 system with 512mb of ram, single 250gb sata drive. A far cry from our production server and we have about 150 users online without issues (once I tweaked httpd.conf and my.cnf) Load is about .1 to .8. I did disable searches for now though.

[MarcusInMd]

BTW, last night i did manage to bury the temp server with load because of iowait (hitting disk for swap)

[MarcusInMd]

Well our updated server is just about complete.

I have a question for some of you more knowledgable Mysql people.

Our database has not seemed to be right since I upgraded from an older version of Mysql to 4.0. I had to repair it nightly or else these kinds of errors would start popping up.

SQL error: Incorrect key file for table 'ibf_posts'; try to repair it

SQL error code:

Date: Monday 11th of September 2006 12:43:14 PM

mySQL query error: DELETE FROM ibf_posts WHERE pid=(post number)

SQL error: Incorrect key file for table 'ibf_posts'; try to repair it

SQL error code:

Date: Tuesday 12th of September 2006 08:26:06 AM

I am wondering if this could be the cause of some of our problems with the server load.

We have also noticed that some of our older posts are start to get screwed up with text missing etc.


I was thinking of dumping most of the posts table and starting from scratch and just leave all the other tables intact. What do you all think?

[MarcusInMd]

I am starting to think this is a major part of our problem now..Just had the post table crash again.


top - 23:41:14 up 5 days, 9:40, 1 user, load average: 2.39, 1.30, 0.74
Tasks: 152 total, 1 running, 151 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.7% us, 2.0% sy, 0.0% ni, 11.1% id, 85.5% wa, 0.3% hi, 0.3% si
Mem: 514148k total, 508820k used, 5328k free, 1080k buffers
Swap: 1048568k total, 183156k used, 865412k free, 134752k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11199 mysql 15 0 197m 64m 3768 S 5.3 12.9 195:43.20 mysqld
27666 root 16 0 2020 1032 784 R 0.3 0.2 0:00.09 top
1 root 16 0 1744 464 440 S 0.0 0.1 0:02.29 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.02 ksoftirqd/0
4 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.20 migration/1
6 root 34 19 0 0 0 S 0.0 0.0 0:00.02 ksoftirqd/1
7 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/0
9 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/1
10 root 20 -5 0 0 0 S 0.0 0.0 0:00.01 khelper
11 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
14 root 10 -5 0 0 0 S 0.0 0.0 0:04.57 kblockd/0
15 root 10 -5 0 0 0 S 0.0 0.0 0:00.52 kblockd/1
16 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
110 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd

I stopped mysql before the load got out of hand.

[MarcusInMd]

I wanted to revisit this thread for any interested and to possibly help anyone else that may be having a problem such as this with your server load. I can safely say that IPB customer service does not have a clue when it comes to a problem like this and is basically worthless when help is needed of a more serious nature. I pushed the issue for over a year and always got the basically the same canned response. Because of this and the recent hackings of the software (and raising their prices on top of this is just incredible to me) nearly led us to drop IPB entirely for another package, but I am giving them one more chance this year.

Our server got a complete overhaul, I made software changes and fixed some database issues. I have not wanted to report back until I actually saw some decent member usage on our forum. The weather has been boring so there has not been a chance until recently when the big low came out of canada and dumped lake effect and snow along the northern tier of the US.

Anyway, I used a temporary server and moved everything to it all the while upgrading our system control panel (Plesk) I then took our production server offline, installed a new 3ware hardware raid controller and installed two new Western Digital Raptor Sata drives. (150GB drives) Installed then only in a RAID 1 configuration because we are limited on space inside the server. I then installed a 64 bit OS on the system, installed all upgrades and system patches etc. I then moved all of our domains back over to this server remotely (I had the production server at our offices here to do the upgrades.)

The one thing that has been bugging me is the post table database crashing on a daily basis despite doing everything I could to fix the problem. This problem seemed to start when I upgraded from a 3.X version of Mysql about a year or so ago to 4.0.X version. What I wound up doing is just exporting just this table from the database, dropping the table and then reimported the data into it. It worked fine with out any kinds of issues and only took about 1/2 an hour.

Once the production server was back online I turned off the night DB repair script I had running and decided to watch it closely and see what happened. Its been nearly a month and I am happy to report that the table has not crashed since I exported the table and reimported it into a blank table.

Our server load issues were occuring with around 200 to 300 people online in recent months and sometimes less. During the latest surge in forum activity we did not have any server load issues.

One last thing that I did do was carefully tweak MYsql and Apache settings. After much research I decided to set our table cache to a number slightly above the entire size of all of the tables on the forum (about 700mb). This has really made a drastic improvment on the servers memory usage. Our server used to dip into swap occassionaly despite the fact that we have over 4GB of ram in the system. This no longer occurs even when our traffic maxxed out nearly 300 earlier this week.

I will update this thread one last time if everything continues to go well intp the busy winter storm season. Based on our annual growth I expect to begin to have forum traffic in the area of 600 to 1000 users online at once when the first widespread threat of snow is forecasted. In the winte rmonths we generally have between 1600 and 2000 of our members visit on a daily basis.


Here are some of our more recent graphs that I am running to monitor the situation.

[hcsrob]

Okay, I found the script...

What you need to do is upload this file to your root forum directory ( the one with conf_global.php in it ) and then run it by going to

http://yourdomain.com/forums/index_checker210.php

.

It should make a list of all required indexes, color coded red or green. If you have any RED entries, post them up here. :)

I don't mean to hijack this post, but I tried running the index checker on my forum and came up with these entries in red:

Error : Table topics : Index last_post : Missing field 'forum_id' from multi-column index
Error : Table topics : Index last_post : Missing field 'pinned' from multi-column index
Error : Table topics : Index forum_id : Missing field 'pinned' from multi-column index
Error : Table topics : Index forum_id : Missing field 'approved' from multi-column index

edit: I added the missing indexes through phpmyadmin.
Thanks for posting this, I would have had no idea.

[njustice]

@ MarcusInMD sounds like you could have saved some serious money in hardware. :(

Care to share the entire contents of your my.cnf?

[Poseidon_merged]

I have to agree about IPS Ticket Support... It is a waste of time.

[djXpire76]

This is useful thread, I'll see what I can fine tune and post some results here... thanks! :)

[djXpire76]

Hi Marcus,

below is the my.cnf file on the server.

We've already tried all different values but the server load is still high.

We have an average of 100-200 or 250 users online...

We are self managing this server now so we can do whatever needed to speed up the forum loading, sometimes when the cpu load is high, the page can take 10s to load.

Any advise on what we can do to reduce the erratic load? thanks!
----------------------------------------------------------------------------

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
interactive_timeout=100
max_connections=100
query_cache_limit=4M
query_cache_size=64M
query_cache_type=1
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=256M
join_buffer=2M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=4M
read_rnd_buffer_size=4M
max_connect_errors=2
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log_slow_queries=/var/log/mysqld-slow.log


[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[njustice]

How much RAM do you have djxpire?

[djXpire76]

Hi everyone,

My server's load issue is now being resolved!

1st, I would like to extend a billion Thank Yous to the IPS Support Team,
they had been extremely patience and helpful throughout the whole troubleshooting process...

Ticketing System is not hopeless at all, if only you have confidence with IPS... Ticketing Rox as well!

Especially to Brandon Farber, You've Been Great! Keep It Up!

The load issue was due to Apache settings and if you refer to the following article and follow what it says,
it can be of some help when you face load issues on your server.

The article is @ http://www.ipsbeyond.com/forums/index.php?...showarticle=127

Once again, thanks to IPS Support Team! :thumbs: :thumbs:


Cheers, djXpire

[Digi]

Good to hear people talk up the IPS Support Team. Seems we only hear the complaints most times here and those that do get good support tend not to post their experiences. :)

[djXpire76]

Welll Digi, I feel that IPS really deserves a big round of applause for their dedication & hardwork...

Honestly, I really appreciate all the help given, since IPS did their part, I shuold do mine as well. :)

And most importantly, I spoke the truth. :)

[MarcusInMd]

Wanted to update (last one) some of you once again with regards to the server load issues we were having.

Our server has been running great. No slow downs, not severe server load spikes etc. We have had almost 500 members online at once too.

Here are some graphs:

The graphing software smooth things out a bit but as you can see we had no load issues during our peak times about a week or so ago. Never pushed much beyond 3 and even then she ran perfecly smooth with no slowdowns.

Edit: Sorry I uploaded the wrong files.

[MarcusInMd]

I spoke to soon. Everything was running fantastic until about three week ago when we started getting erratic load jumps again. :( Trying to track down the root of the problem this time.



Here are some slow queries that are showing up in our servers log.

Any ideas on getting these slow queries beaten down?

# Time: 070112 23:59:25

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 4 Rows_examined: 4

SELECT * FROM ibf_topic_markers WHERE marker_member_id=1541;

# Time: 070112 23:59:40

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 3 Rows_examined: 9

SELECT p.*,

m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title,m.hide_email, m.warn_level, m.warn_lastwarn,

me.msnname,me.aim_name,me.icq_number,me.signature, me.website,me.yahoo,me.location, me.avatar_location, me.avatar_type, me.avatar_size, m.members_display_name

FROM ibf_posts p

LEFT JOIN ibf_members m ON (p.author_id=m.id)

LEFT JOIN ibf_member_extra me ON (me.id=m.id)

WHERE p.pid IN(2206,2235,2237) ORDER BY pid asc;

# Time: 070113 0:00:05

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SET insert_id=1633907;

INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic_i

,queued,post_htmlstate,post_key,post_parent) VALUES(817,1,1,'216.164.18.11',1168664402,0,'<!--quoteo(post=1633879:date=Jan 12 2007, 11:55 PM:name=weathafella)--><div class=\'quotetop\'>QUOTE(weathafella @ Jan 12 2007, 11:55 PM) [post="1633879"]<{POST_SNAPBACK}>[/post]</div><div class=\'quotemain\'><!--quotec--><br />Folks....this setup depicted while far out is an unbelievable signal for extreme cold delivery...YIKES!<br /><img src=\"http://models.easternuswx.com/gfs/GFS_00_opNH_H50S_372.gif\" border=\"0\" alt=\"IPB Image\" /><br /><!--QuoteEnd--></div><!--QuoteEEnd--><br /><br />but do we ever get a negative nao? a bit harder to get any good precip without it, not impossible, but difficult. i like cold are but it is frustrating to not see snow','chrisNJ',120478,0,0,'6d41a8aff2a7cb9e1569913f5bd45fd3',1633879);

# Time: 070113 0:00:51

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SET insert_id=89984;

INSERT INTO ibf_message_text (msg_date,msg_post,msg_cc_users,msg_sent_to_count,msg_post_key,msg_author_id,msg

ip_address) VALUES(1168664449,'

<br /><br />so? when did you think you ever had the chance?','',2,'a7b2b79c2278fd8a917a161a1ed4b8a8',1391,'24.91.145.34');

# Time: 070113 0:01:12

# User@Host: board_user[board_user] @ localhost []

# Query_time: 9 Lock_time: 0 Rows_sent: 6420 Rows_examined: 17422

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM ibf_posts p

LEFT JOIN ibf_topics t ON ( p.topic_id=t.tid )

WHERE t.forum_id IN (15,31,36)

AND p.queued=0

AND p.author_id IN ('3797');

# Time: 070113 0:01:19

# User@Host: board_user[board_user] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent: 175 Rows_examined: 11979

SELECT pid FROM ibf_posts WHERE topic_id IN(119859,119295,119043,116820,116740,116454,116030,116022,116015,115359,115343,

14577,113798,111403,110409,109869,109858,109139,108424,108382,107743,106611,1060

2,105746,105656,105375,105279,105014,104983,104842,104342,104026,103864,103685,1

3069,102497,101353,100062,99910,99883,99723,99636,98712,98576,98015,97480,97423,

7395,97291,97020,95769,93673,93459,92056,91888,91883,91209,90926,90817,90546,904

6,90389,90378,90167,88322,88309,87696,87609,87360,87329,87228,87213,86986,86727,

6659,86540,86531,86374,86046,85897,85783,85694,85617,85595,85015,84580,83601,835

9,83333,82593,82188,81380,81271,81265,81105,80828,80569,80499,80417,80382,80085,

9975,79916,79781,79325,78995,78516,78464,78188,78020,77744,77597,77238,77132,760

5,75341,73750,73735,73723,73652,73651,73257,73074,73069,72668,72624,72262,72057,

2005,71837,71737,71622,71323,71199,71132,70380,70245,70002,69576,69368,68576,681

0,67546,66310,65946,65938,65566,65362,64270,64051,63763,61934,61916,61670,61034,

0894,60490,60402,60214,60180,60080,60059,59594,59521,59093,58975,58905,57155,569

7,56829,56820,56818,56218,56210,56091) AND new_topic=1;

# Time: 070113 0:01:32

# User@Host: board_user[board_user] @ localhost []

# Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 22324

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=1718;

# Time: 070113 0:01:37

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 20 Rows_examined: 60

SELECT p.*,

m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title,m.hide_email, m.warn_level, m.warn_lastwarn,

me.msnname,me.aim_name,me.icq_number,me.signature, me.website,me.yahoo,me.location, me.avatar_location, me.avatar_type, me.avatar_size, m.members_display_name

FROM ibf_posts p

LEFT JOIN ibf_members m ON (p.author_id=m.id)

LEFT JOIN ibf_member_extra me ON (me.id=m.id)

WHERE p.pid IN(1529449,1529457,1529463,1529466,1529471,1529476,1529480,1529495,1529508,15295

6,1529532,1529533,1529543,1529556,1529564,1529567,1529568,1529649,1529689,152971

) ORDER BY pid asc;

[bfarber]

What a lot of people fail to realize is that load issues like the ones being reported here are NOT at all easy to track down and diagnose. ;) Especially when they're not reproducable.

Marcus, given the history you have with your posts table corrupting, there was obviously an issue with it in the past. What you may want to do when the server's not so busy is drop the indexes on the post table from your mysql command line, and then re-add them. For all we know there could be an index that isn't proper, and that's part of the problem.

The queries you posted really shouldn't be running slow I'd think. What version of IPB? If it's 2.2, I have some improvements for resource usage already ready for the next release I can implement if possible. Additionally, 2.2 can natively support eaccelerator (you said you had installed) which can help performance too.

[MarcusInMd]

What a lot of people fail to realize is that load issues like the ones being reported here are NOT at all easy to track down and diagnose. ;) Especially when they're not reproducable.

Marcus, given the history you have with your posts table corrupting, there was obviously an issue with it in the past. What you may want to do when the server's not so busy is drop the indexes on the post table from your mysql command line, and then re-add them. For all we know there could be an index that isn't proper, and that's part of the problem.

The queries you posted really shouldn't be running slow I'd think. What version of IPB? If it's 2.2, I have some improvements for resource usage already ready for the next release I can implement if possible. Additionally, 2.2 can natively support eaccelerator (you said you had installed) which can help performance too.

Thanks for the response. Your not kidding about trying to track down these issues. I have my configuration files optimized as best as they possible can be. We are still running 2.1.7 but will be son be switching to 2.2.1 when our new skins are completed.

Last night I did mysqldump of the database and did a "check". All clear. No errors. Then I ran an optimize wihout issue.

How should I drop the indexes and re-add them? When I put the server back online in sept, and since we have so much ram on the system, I made the key buffer size the size of our post table. The other night I increased it again in the hopes that it would iprove performace. No go. With regards to eaccellerator. I have not reinstalled it since I upgraded the server.

Any more ideas to the above suggestions?

[MarcusInMd]

Even though mysql prompt check did not find any issues whe I was looking through the database tables with phpmysqladmin I found this in the ibf_topics table:

Warning More than one INDEX key was created for column `topic_firstpost`

Warning More than one INDEX key was created for column `forum_id`

Could this be causing a problem? If so I will go through and look at all of the tables manually and see if I can find any more.

Here are some more slow-queries. Anything stand out?

# Time: 070113 9:47:26

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 2415

SELECT COUNT(DISTINCT(p.topic_id)) as max FROM ibf_topics t

LEFT JOIN ibf_posts p ON (p.topic_id=t.tid)

WHERE t.forum_id=15 AND p.author_id=4709 AND p.new_topic=0

and t.approved=1;

# Time: 070113 10:12:51

# User@Host: board_user[board_user] @ localhost []

# Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 18410

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=331;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 18410

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=331;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 18410

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=331;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 18410

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=331;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 18410

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=331;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 18410

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=331;

# Time: 070113 10:23:18

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 5124

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=422;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 5124

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=422;

# Time: 070113 10:28:57

# User@Host: board_user[board_user] @ localhost []

# Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 3642

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=3991;

# Time: 070113 10:35:58

# User@Host: board_user[board_user] @ localhost []

# Query_time: 6 Lock_time: 0 Rows_sent: 3363 Rows_examined: 6726

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM ibf_posts p

LEFT JOIN ibf_topics t ON ( p.topic_id=t.tid )

WHERE t.forum_id IN (9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35)

AND p.queued=0

AND p.author_id IN ('1569');

# Time: 070113 11:00:10

# User@Host: board_user[board_user] @ localhost []

# Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 6156

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,33,6,31,36,21,28,39,35) AND p.author_id=807;

# Time: 070113 11:01:22

# User@Host: board_user[board_user] @ localhost []

# Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 12685

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=1592;

# Time: 070113 11:12:52

# User@Host: board_user[board_user] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 14855

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,13,8,18,25,34,38,40,15,14,33,6,31,36,21,28,39,35) AND p.author_id=2720;

# Time: 070113 11:23:52

# User@Host: board_user[board_user] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 12272

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=90;

# Time: 070113 11:31:40

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 1832

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=1136;

# Time: 070113 11:46:15

# User@Host: board_user[board_user] @ localhost []

# Query_time: 10 Lock_time: 0 Rows_sent: 1 Rows_examined: 25951

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,13,8,18,25,34,38,40,15,14,33,6,31,36,21,28,39,35) AND p.author_id=1837;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 25951

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,13,8,18,25,34,38,40,15,14,33,6,31,36,21,28,39,35) AND p.author_id=1837;

# Time: 070113 11:47:59

# User@Host: board_user[board_user] @ localhost []

# Query_time: 5 Lock_time: 0 Rows_sent: 3948 Rows_examined: 9966

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM ibf_posts p

LEFT JOIN ibf_topics t ON ( p.topic_id=t.tid )

WHERE t.forum_id IN (15)

AND p.queued=0

AND MATCH(post) AGAINST ('+chuck' IN BOOLEAN MODE);

# Time: 070113 11:50:23

# User@Host: board_user[board_user] @ localhost []

# Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 12066

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=4236;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 12066

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=4236;

# Time: 070113 11:59:50

# User@Host: board_user[board_user] @ localhost []

# Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 12685

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=1592;

# Time: 070113 12:10:36

# User@Host: board_user[board_user] @ localhost []

# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SET insert_id=1634812;

INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic_i

,queued,post_htmlstate,post_key,post_parent) VALUES(1837,1,1,'68.49.26.72',1168708234,'','SPECI KDFW 131639Z 31014KT 1/4SM R17C/2000VP6000FT TS FZRA FG BKN006 OVC011CB 00/M01 A3022 RMK AO2 SFC VIS 1/2 TSB39RAE39FZRAB39 OCNL LTGIC OHD TS OHD P0028<br /><br />SPECI KDFW 131649Z 32010KT 1 1/2SM R17C/2400VP6000FT TS -FZRA BR BKN006 OVC011CB 00/M01 A3022 RMK AO2 TWR VIS 2 TSB39RAE39FZRAB39 TS OHD-NE MOV NE P0053<br /><br />METAR KDFW 131653Z 31012KT 2SM TS FZRA BR BKN006 OVC013CB 00/M01 A3022 RMK AO2 TSB39RAE39FZRAB39 SLP237 TS OHD-NE MOV NE P0054 T00001006<br /><br />SPECI KDFW 131700Z COR 33010KT 2SM TS -FZRA BR BKN006 BKN009 OVC013CB 00/M01 A3022 RMK AO2 SFC VIS 2 1/2 TS NE MOV NE P0002 $<br /><br />==<br /><br />Forecast for Dallas:<br /><br />Today<br />Thunderstorms and freezing rain. Much colder. Ice accumulation of up to one quarter of an inch. Highs in the lower 30s. North winds 10 to 15 mph. Chance of precipitation near 100 percent. <br /><br />Tonight<br />A chance of thunderstorms and freezing rain in the evening...then thunderstorms and freezing rain after midnight. Ice accumulation of one quarter to one half of an inch. Lows in the lower 30s. North winds around 10 mph. Chance of precipitation near 100 percent. <br /><br />Sunday<br />Thunderstorms and freezing rain. Ice accumulation of one quarter to one half of an inch. Highs in the lower 30s. North winds 10 to 15 mph. Chance of precipitation near 100 percent. <br /><br />Sunday Night<br />Freezing rain and sleet likely. Breezy. Ice accumulation of less than one quarter of an inch. Lows in the mid 20s. North winds 10 to 15 mph increasing to 15 to 25 mph after midnight. Chance of precipitation 60 percent. Wind chill readings 3 to 13. <br /><br />==<br /><br />Statement as of 5:13 AM CST on January 13, 2007<br /><br />... Ice Storm Warning in effect until 6 am CST Monday...<br />... Freezing Rain Advisory no longer in effect...<br /><br />The National Weather Service in Fort Worth has upgraded the<br />Freezing Rain Advisory to an Ice Storm Warning... which is in<br />effect until 6 am CST Monday.<br /><br />Arctic air will continue to slowly spread southward across North<br />Texas today and tonight. Temperatures will reach freezing by early<br />afternoon along and northwest of a Bonham... to Rockwall... to Cedar<br />Hill... to Glen Rose... to Lampasas line. As temperatures reach or<br />fall below freezing... rain will begin to freeze on contact with<br />objects on the surface. This will form a glaze of ice... first on<br />elevated surfaces such as trees... power lines and bridges... and<br />eventually on surface roads. Accumulations of one-quarter to one<br />inch of ice are possible. The heaviest freezing rain is expected<br />late tonight and Sunday.<br /><br />Significant ice accumulations on trees and power lines may cause<br />them to break and fall. Stay indoors and prepare for power<br />outages. Stay away from downed power lines as they may still be<br />live.<br /><br />Roads... bridges... and overpasses in the warning area will become<br />slick and hazardous. Exercise extreme caution if travel is<br />absolutely necessary. Keep an extra blanket... flashlight...<br />food... and water in your vehicle in case you become stranded.','Ian',120503,0,0,'7cae7949b88f57d1020ccf1eabfd1f3c',0);

# Time: 070113 12:14:59

# User@Host: board_user[board_user] @ localhost []

# Query_time: 3 Lock_time: 0 Rows_sent: 10 Rows_examined: 896

SELECT p.*, t.*, t.posts as topic_posts, t.title as topic_title, m.*, me.*

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid)

LEFT JOIN ibf_members m ON (m.id=p.author_id)

LEFT JOIN ibf_member_extra me ON (me.id=p.author_id)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=641

ORDER BY post_date DESC

LIMIT 0,10;

# Time: 070113 12:16:51

# User@Host: board_user[board_user] @ localhost []

# Query_time: 8 Lock_time: 0 Rows_sent: 4062 Rows_examined: 8637

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM ibf_posts p

LEFT JOIN ibf_topics t ON ( p.topic_id=t.tid )

WHERE t.forum_id IN (9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35)

AND p.queued=0

AND MATCH(post) AGAINST ('+dave murray' IN BOOLEAN MODE);

# Time: 070113 12:19:11

# User@Host: board_user[board_user] @ localhost []

# Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 12659

SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid AND t.approved=1)

WHERE p.queued=0 AND t.forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35) AND p.author_id=350;

Should the table ibf_topicview have an index? thrugh phphadmin I see:

Warning No index defined!

On this table.

[MarcusInMd]

It looks like these queries had to go through our entire database the funny thing is it did not adversly affect the servers load.

# Time: 070113 20:02:44

# User@Host: board_user[board_user] @ localhost []

# Query_time: 17 Lock_time: 0 Rows_sent: 1 Rows_examined: 1473528

SELECT COUNT(*) as posts

FROM ibf_posts p USE INDEX (topic_id)

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid)

WHERE t.forum_id IN(0,9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35)

AND p.queued=0 AND p.post_date > 1168730288;

# User@Host: board_user[board_user] @ localhost []

# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 1473528

SELECT COUNT(*) as posts

FROM ibf_posts p USE INDEX (topic_id)

LEFT JOIN ibf_topics t ON (p.topic_id=t.tid)

WHERE t.forum_id IN(0,9,10,11,24,23,26,37,18,25,15,14,6,31,36,21,28,39,35)

AND p.queued=0 AND p.post_date > 1168730288;