help!!! ------- 8 sql statements overload my db!!

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
mouse.com
Registered User
Posts: 1
Joined: Mon Sep 08, 2008 7:16 pm

help!!! ------- 8 sql statements overload my db!!

Post by mouse.com » Mon Sep 08, 2008 7:25 pm

my account has been suspended the ISP for overloading their db. almost all the posts are done by spamer machines.

my question: is there any solution for it? can i change the sql statement or commnet out some of them?

1.Time: 22% (6994 sec)
Amount: 16% (308 queries)
Rows Examined/Sent: 31480013 / 13500
Avg. Query Exec/Lock Time: 22 / 1 sec.
Used databases: [mydb]
Query example: use mydb; SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time FROM phpbb_topics t, phpbb_users u, phpbb_posts p, phpbb_posts p2, phpbb_users u2 WHERE t.forum_id = 1 AND t.topic_poster = u.user_id AND p.post_id = t.topic_first_post_id AND p2.post_id = t.topic_last_post_id AND u2.user_id = p2.poster_id AND t.topic_type <> 2 ORDER BY t.topic_type DESC, t.topic_last_post_id DESC LIMIT 0, 50;

2.Time: 10% (3141 sec)
Amount: 13% (246 queries)
Rows Examined/Sent: 45927193 / 246
Avg. Query Exec/Lock Time: 12 / 4 sec.
Used databases: [mydb]
Query example: use mydb; SELECT MAX(post_time) AS last_post_time FROM phpbb_posts WHERE poster_ip = '4403874e';

3.Time: 11% (3463 sec)
Amount: 10% (193 queries)
Rows Examined/Sent: 198380 / 1496
Avg. Query Exec/Lock Time: 17 / 2 sec.
Used databases: [mydb]
Query example: use mydb; SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid FROM phpbb_posts p, phpbb_users u, phpbb_posts_text pt WHERE p.topic_id = 132618 AND pt.post_id = p.post_id AND u.user_id = p.poster_id ORDER BY p.post_time ASC LIMIT 165, 15;

4.Time: 5% (1677 sec)
Amount: 7% (138 queries)
Avg. Query Exec/Lock Time: 12 / 3 sec.
Used databases: [mydb]
Query example: use mydb; INSERT INTO phpbb_sessions (session_id, session_user_id, session_start, session_time, session_ip, session_page, session_logged_in, session_admin) VALUES ('ef89fae1ea1456d949b9006905c75e40', -1, 1220779307, 1220779307, '4809e7aa', -1, 0, 0);

5.Time: 4% (1347 sec)
Amount: 5% (95 queries)
Avg. Query Exec/Lock Time: 14 / 5 sec.
Used databases: [mydb]
Query example: use mydb; UPDATE phpbb_sessions SET session_user_id = -1, session_start = 1220781934, session_time = 1220781934, session_page = -9, session_logged_in = 0, session_admin = 0 WHERE session_id = '' AND session_ip = 'cfd267ca';

6.Time: 3% (1125 sec)
Amount: 4% (80 queries)
Rows Examined/Sent: 0 / 80
Avg. Query Exec/Lock Time: 14 / 5 sec.
Used databases: [mydb]
Query example: use mydb; SELECT * FROM phpbb_forums WHERE forum_id = 8;

7.Time: 3% (956 sec)
Amount: 3% (74 queries)
Rows Examined/Sent: 112646 / 11
Avg. Query Exec/Lock Time: 12 / 4 sec.
Used databases: [mydb]
Query example: use mydb; SELECT username FROM phpbb_users WHERE LOWER(username) = 'nonamurkss';

8.Time: 3% (1046 sec)
Amount: 3% (74 queries)
Rows Examined/Sent: 0 / 66
Avg. Query Exec/Lock Time: 14 / 6 sec.
Used databases: [mydb]
Query example: use mydb; SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '41a2674f6f28e39cd5e413e6f0764b18' AND u.user_id = s.session_user_id;

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: help!!! ------- 8 sql statements overload my db!!

Post by espicom » Mon Sep 08, 2008 7:56 pm

What they've sent you is confusing, at best - those queries should not present significant load to a well-configured database server. And the "rows sent" column is really off... A query that is supposed to return at most 50 rows returned 13500?!?

Check out the top of this board for how to deal with stopping spam; there's a long topic, the first message of which details a number of MODs that have proven quite effective against spammers.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Locked

Return to “2.0.x Support Forum”