Ipb2.2.1 Causes Frequent Extremely High Server Load

[lkcnnet]

It appears a IPB2.2.1 board is causing frequent extremely high server load on my server.

This is a dedicated server with 4GB RAM. The IPB2.2.1 board is the busiest application, with a large mysql database (about 1.8GB). There are several other websites/applications but less busy and with much smaller database.

Recently the server is experiencing frequent (from once an hour to once several hours) extremely (load average >100) high server load. The server load often increased quickly from 1.0-2.0 to >100, while the number of tasks (processes I think) increased from about 200 to >500. All these happened within about one minute.

When I had chance to check mysql processes during the load spike, I saw many mysql processes related to IPB2.2.1 board are in Locked or Sending data state, forming a very long queue. Here are a couple of samples (I only quote first several lines). lkcnnet_bbs is the user and database of this IPB2.2.1 board.

I wonder if anyone could kindly offer some suggestion? Thanks.

+--------+------------------+-----------+--------------------+---------+------+--------------------+----------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+------------------+-----------+--------------------+---------+------+--------------------+----------------------------+

| 149911 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 222 | Sending data | SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (t.tid=p.topic_id AN |

| 149912 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 223 | Sending data | SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (t.tid=p.topic_id AN |

| 149984 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 221 | Sending data | SELECT count(*) as count

FROM ibf_posts p

LEFT JOIN ibf_topics t ON (t.tid=p.topic_id AN |

| 150993 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 92 | Sending data | SELECT p.*, pp.*,

m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m |

| 151017 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 85 | Sorting result | SELECT p.*, pp.*,

m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m |

| 151055 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 77 | Sorting result | SELECT p.*, pp.*,

m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m |

| 151322 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 10 | Locked | UPDATE ibf_sessions SET member_name='',member_id=0,member_group=2,login_type=0,running_time=11773598 |

| 151325 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 8 | Locked | SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time

|

| 151329 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 9 | Locked | INSERT INTO ibf_sessions (id,member_name,member_id,member_group,login_type,running_time,ip_address,b |

| 151331 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 9 | Locked | SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time

|

| 151337 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 14 | Sending data | SELECT p.*,m.members_display_name, m.mgroup FROM ibf_posts p LEFT JOIN ibf_members m ON ( m.id=p.au |

| 151332 | lkcnnet_bbs | localhost | lkcnnet_bbs | Sleep | 1 | | |

| 151349 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 9 | Locked | SELECT id, member_id, member_name, login_type, running_time, member_group FROM ibf_sessions WHERE ru |

| 151351 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 4 | Locked | SELECT p.*, pp.*,

m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m |

| 151352 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 2 | Locked | INSERT INTO ibf_sessions (id,member_name,member_id,member_group,login_type,running_time,ip_address,b |

| 151353 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 9 | Locked | INSERT INTO ibf_sessions (id,member_name,member_id,member_group,login_type,running_time,ip_address,b |

| 151354 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 7 | Locked | INSERT INTO ibf_sessions (id,member_name,member_id,member_group,login_type,running_time,ip_address,b |

| 151355 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 22 | Sending data | SELECT p.*,m.members_display_name, m.mgroup FROM ibf_posts p LEFT JOIN ibf_members m ON ( m.id=p.au |

| 151356 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 8 | Locked | INSERT INTO ibf_sessions (id,member_name,member_id,member_group,login_type,running_time,ip_address,b |

| 151357 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 5 | Locked | DELETE FROM ibf_sessions WHERE id='Yahoo! Slurp=202160179160_session' |

|

Another example:

+--------+----------------------+-----------+--------------+---------+------+----------------+------------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+----------------------+-----------+--------------+---------+------+----------------+------------------------------------+

| 141122 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 173 | Sending data | SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time, t |

| 141221 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 173 | Sending data | SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time, t |

| 141222 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 173 | Sending data | SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time, t |

| 141225 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 173 | Sending data | SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time, t |

| 141229 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 170 | Sending data | SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time, t |

| 141233 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 162 | Locked | INSERT INTO ibf_sessions (id,member_name,member_id,member_group,login_type,running_time,ip_address,b |

| 141232 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 161 | Locked | INSERT INTO ibf_sessions (id,member_name,member_id,member_group,login_type,running_time,ip_address,b |

| 141235 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 161 | Locked | UPDATE ibf_sessions SET member_name='Phil',member_id=117,member_group=13,login_type=0,running_time=1 |

| 141237 | lkcnnet_bbs | localhost | lkcnnet_bbs | Query | 162 | Sorting result | SELECT tid, title, posts, starter_id as member_id, starter_name as member_name, start_date as post_d |

[.CMANNS]

Alter table ibf_sessions to be in heap.

[lkcnnet]

Thanks .CMANNS, could you explain more?

I asked the Support and was told they had made changes to my database (don't know which tables). I'm now waiting to see the effects.

[.CMANNS]

alter table ibf_sessions type = heap; I think

[lkcnnet]

Thanks. It has been done by the support.

However it has improved the situation but not solved it. There are still load spikes which seems coming from nowhere, when the traffic is not particularly higher than usual.

My server has dual Xeon 2.80Ghz and 4GB memory. The IPB2.2.1 database is about 1.8GB, with 1.1M posts, 126K topics, usually 300-600 'users in the last 15 minutes', 500-1000 posts a day. My host starts to suggest me to add a new server for mysql only. But..is my current server not able to handle with forum of this size and traffic?

[.CMANNS]

It all depends on the my.cnf and apache settings. Apache uses alot of memory, I could see that 4gB being eaten away. Otherwise your server is as fast as my cluster running about 4x sites your size.

[TCWT]

your server should easily handle thath load. Apache is a resource hogger. :(

[.CMANNS]

Switching from apache always helps.