Erratic server load spikes

[MarcusInMd]

Our forum averages about 200 online at once. We peak at around 500 to 600 during serious weather events. Our server has load issues all the time. It will be sitting at around .5 or 1.0 with about 200 or 300 users and bam sky rocket to 40 or 50 almost crashign the server. But not really when we hit our peak number of hits. It often occurs when the number of connections is much lower. This is a dedicated dual opteron server with 4GB of ram and software raid 250GB SATA drives. PHP, HTTPD and Mysql have been optimized until the cows come home by me and a very experienced linux admin. Still didn't really help. While I cannot make a direct link to iowait this is the last thing I can think of for the load issues. Also going to a 64bit OS with the latest 2.6 kernel and an updated control panel software package. Going to add 3ware hardware RAID server (9550sx) with dual WD Raptor drives to see if this helps at all. Our present hard drives do not appear to have hardware "issues' either.

I am about at wits end because this software should be able to handle double the amount of service that we give it on a daily basis and at peak connection time.

Good luck with the extra ram etc.

Here is an example of server load when we had 220 users online at once. 45 minutes earlier we had almost 400 without a single problem.




As you can see something happens with MySQL too.

[MarcusInMd]

Should also mention that I have tried all the changes that IPB reccomends. They even logged onto our dedicated server once or twice but did nothing to solve the problem.

[Digi]

Some of your problem is probably related to IPB's cache system. They use serialize and unserialize quite often in the code :whistling: If you wanted to trace out how to remove the caching feature, you could try removing these and see how your server fares.

[MarcusInMd]

I'm not sure I understand. But I think I messed with caching along time ago because of this issue. Could you refresh my memory before I plop down 650 for new drives and a hardware raid controller. :D

BTW, I have always thought this was somekind of IPB software issue but I could never pin it down.

Mysql does report slow queries when things go haywire. Usually from the post table.

[Digi]

I just though of something else too. You said that it only happens at certain times. Have you tried assigning all of your tasks to cron jobs and spacing them out so that they do not run at the same time? If, by chance a bunch of tasks are running at the same time as a minimal spike in users, it could cause the spike that you are looking at.

[MarcusInMd]

Nope, that's not either. All of our serious cron stuff is done early morning hours. :(

What about the cache stuff you were talking about?

[Digi]

I'm not talking about non-IPB related tasks. I'm talking about that tasks that IPB itself runs. Look in the ACP under Tools & Settings => Task manager. You will see when each task is set to run (these aren't cron so they will get backed up should no one visit right at the time) and when they are set to run next. You can set these up as crons as well. I am thinking that, during the times that you are refering to, one or some of these tasks are trying to run and causing a spike in your server load.

[MarcusInMd]

Here is the graph of network traffic too.



I checked that out too. Nothing there. The odd thing is these load jumps do not happen everyday. If it was something that was happening hourly (like a cron) it should be reproducable.

[Digi]

From what you made it appear, it sounds like you are talking about occurances where a mild to heavy user load is visiting your server and then occationally things "freak out". This to me points to the tasks. They won't be noticable during low load times, but should a high user load appear and taks need to be run, I can see just what you are explaining happening.

I am going to split this topic though :)

[MarcusInMd]

Here is a typical hours worth of IPB tasks.

RSS Import Update 6th September 2006 - 02:42 PM RSS Import completed (0)

RSS Import Update 6th September 2006 - 02:12 PM RSS Import completed (0)

Hourly Clean Out 6th September 2006 - 02:07 PM Old reg_images, sessions and search results removed

RSS Import Update 6th September 2006 - 01:42 PM RSS Import completed (0)

Birthday and Events Cache 6th September 2006 - 01:28 PM Calendar recached

Announcements Update 6th September 2006 - 01:13 PM Announcements updated

RSS Import Update 6th September 2006 - 01:12 PM RSS Import completed (0)

Hourly Clean Out 6th September 2006 - 01:08 PM Old reg_images, sessions and search results removed

You can see that nothing falls into the time frame that the server spike occurs. Just after :30 after the hour.

I was kind of entertaining the idea that maybe it some strange issue with the DB. We converted over from an older version of Ikonboard I believe 2 years ago.

[MarcusInMd]

I should also point out that if I do not repair my database nightly that the DB will eventualy become corrupted. This started I beleive when we upgraded from 2.0.4 to 2.1 (I think)

[MarcusInMd]

Here is a snippet from the slow query log during one of the episodes. Its not from the above graphs but this is from late last month.

# Time: 060827 11:14:28

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

# Query_time: 11 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:06

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

# Query_time: 12 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:07

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

# Query_time: 17 Lock_time: 0 Rows_sent: 186 Rows_examined: 782

SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time, t.forum_id

FROM ibf_sessions s

LEFT JOIN ibf_topics t ON ( t.tid=s.location_1_id)

WHERE s.location_2_type='forum' AND (s.location_2_id=15 OR t.forum_id=15)

AND s.running_time > 1156690790

AND s.in_error=0;

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

# Query_time: 18 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:08

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

# Query_time: 12 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 15 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:09

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

# Query_time: 20 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:14

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

# Query_time: 14 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:15

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

# Query_time: 15 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 13 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 23 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:17

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

# Query_time: 18 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 19 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 17 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:18

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

# Query_time: 19 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:31

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

# Query_time: 24 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:15:57

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

# Query_time: 22 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 18 Lock_time: 3 Rows_sent: 1 Rows_examined: 84061

SELECT count(*) as count FROM ibf_topics WHERE approved=1 AND state != 'link' AND forum_id IN(9,10,11,24,23,26,37,18,25,15,14,6,36,21,28,35) AND last_post > '1156673169';

# Time: 060827 11:15:59

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

# Query_time: 11 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:00

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

# Query_time: 17 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:03

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

# Query_time: 11 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 14 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:04

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

# Query_time: 13 Lock_time: 1 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 12 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:05

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

# Query_time: 17 Lock_time: 1 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 13 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:06

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

# Query_time: 14 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:12

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

# Query_time: 13 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 15 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 13 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:18

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

# Query_time: 21 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 21 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 21 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:19

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

# Query_time: 22 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 20 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 20 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 19 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 22 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 20 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 21 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 22 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:20

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

# Query_time: 23 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:24

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

# Query_time: 27 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:25

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

# Query_time: 28 Lock_time: 2 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:28

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

# Query_time: 31 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 31 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:31

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

# Query_time: 34 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:33

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

# Query_time: 35 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:35

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

# Query_time: 38 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:36

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

# Query_time: 23 Lock_time: 0 Rows_sent: 40 Rows_examined: 38920

SELECT * FROM ibf_topics t WHERE t.forum_id=6 AND t.pinned IN (0,1) and t.approved IN (0,1) ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:16:59

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

# Query_time: 11 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:00

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

# Query_time: 11 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:04

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

# Query_time: 16 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:21

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

# Query_time: 11 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:22

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

# Query_time: 14 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:23

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

# Query_time: 14 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:24

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

# Query_time: 16 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 16 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:25

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

# Query_time: 18 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:26

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

# Query_time: 18 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 16 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 18 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:27

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

# Query_time: 18 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

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

# Query_time: 20 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:28

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

# Query_time: 18 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:51

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

# Query_time: 12 Lock_time: 0 Rows_sent: 40 Rows_examined: 33998

SELECT * FROM ibf_topics t WHERE t.forum_id=15 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,40;

# Time: 060827 11:17:52

[Guest]

It looks like the majority of those queries are using the same table. Have you tried running the IPB database index checker? It could be as simple as a missing index.

That said, I wouldn't have thought that would cause 'spikes'.

Edit: Looking at it, it does seem to be reading 33,000+ database entries each time that query is run. That could be pretty costly at busy times on your board.

[Digi]

Can you run an SQL query on the task logs to see if any were running around the time you posted in your first post. If you don't find any it would definately remove the task idea.

Edit: See what happens when you take 30 minutes to reply :P Dan is definatly on to something as well :)

[MarcusInMd]

Index Checker?

Each night I run a cron job that optimizes the DB. Is this what you are referring too?

[Guest]

Nah, I'll try and dig up the script. It checks all of the database tables to ensure that the indexes are set up right. :)

[MarcusInMd]

Can you run an SQL query on the task logs to see if any were running around the time you posted in your first post. If you don't find any it would definately remove the task idea.

Looks like the log has been purged. :(

[Digi]

That's not good >_< Be sure to keep an eye on that in the future when these erratic loads occur and let me know the outcome.

[MarcusInMd]

Nah, I'll try and dig up the script. It checks all of the database tables to ensure that the indexes are set up right. :)

That would be most helpful.

I am replacing this server with a temporary server Friday morning and bringing it home with me to clean the drives off, install the new controller and new raptor drives and basically rebuild the disk from scratch. We were hacked a couple of times this year and I want to clean it off and make sure that everything is locked down.

I have not ordered the disk hardware yet, if I can find a definative answer that is not hardware related it would be awesome. I just don't believe it is but I am at wits end. LOL My staff is hammering me to do something when ever a big weather event occurs. I personally want to get it fixed as well.

That's not good >_< Be sure to keep an eye on that in the future when these erratic loads occur and let me know the outcome.

I had it look back 2000 posts and my oldest log entries are from Sept 3rd. But its basically a repeat of what I posted above every hour.

I had written a script that goes out and gets some weather stuff and adds it to the DB each hour and I even disabled that thinking perhaps it was causing an issue but it did not fix the problem at hand.

[Guest]

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. :)

[MarcusInMd]

Bummer. :(

This script will check for the required MySQL database indexes in IPB 2.1.x

Script output:

Table admin_permission_keys - Index perm_main OK!

Table admin_permission_keys - Index perm_child OK!

Table attachments - Index attach_pid OK!

Table attachments - Index attach_msg OK!

Table attachments - Index attach_post_key OK!

Table attachments - Index attach_mid_size OK!

Table cal_events - Index daterange OK!

Table cal_events - Index approved OK!

Table dnames_change - Index dname_member_id OK!

Table dnames_change - Index date_id OK!

Table email_logs - Index from_member_id OK!

Table email_logs - Index email_date OK!

Table member_extra - Index id OK!

Table members_converge - Index converge_email OK!

Table members - Index id OK!

Table members - Index name OK!

Table members - Index mgroup OK!

Table members - Index bday_day OK!

Table members - Index bday_month OK!

Table members - Index members_display_name OK!

Table message_text - Index msg_date OK!

Table message_text - Index msg_sent_to_count OK!

Table message_text - Index msg_deleted_count OK!

Table message_topics - Index mt_from_id OK!

Table message_topics - Index mt_owner_id OK!

Table message_text - Index msg_sent_to_count OK!

Table message_text - Index msg_deleted_count OK!

Table moderators - Index forum_id OK!

Table moderators - Index group_id OK!

Table moderators - Index member_id OK!

Table posts - Index topic_id OK!

Table posts - Index author_id OK!

Table posts - Index post_date OK!

Table sessions - Index location1 OK!

Table sessions - Index location2 OK!

Table sessions - Index location3 OK!

Table skin_templates_cache - Index template_set_id OK!

Table skin_templates_cache - Index template_group_name OK!

Table templates_diff_import - Index diff_func_group OK!

Table templates_diff_import - Index diff_func_name OK!

Table template_diff_changes - Index diff_change_func_group OK!

Table template_diff_changes - Index diff_change_type OK!

Table topic_markers - Index marker_forum_id OK!

Table topic_markers - Index marker_member_id OK!

Table topics - Index topic_firstpost OK!

Table topics - Index last_post OK!

Table topics - Index forum_id OK!

It's never anything easy for us.

[Guest]

Ah well... At least that is ruled out now. ;)

[MarcusInMd]

Yeah, I guess so. I think I have tried just about everything. I wish I had a recent iowait output during that last even but I was not around when the problem started and was unable to manually check out the disk iowait to see if the problem was related.

Could an outside unfluence cause this problem? For instance someone hitting the DB with some kind of server load testing program or something?

I did some of my own testing of the above and the results were revealing with limited time that I had to work with it. and if so, I guess I would have to look at some kind of brute force prevention on the IPB database etc.

[MarcusInMd]

Does the fact that his only happens ocassionaly rule out server configuration? I mean I have tweaked everything but maybe I am missing something?

[MarcusInMd]

I went ahead and ordered my new controller and drives for our server. If this does not work I might have to start looking at other software packages to get acceptable performance.