DB locking causing cpu spike

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Anti-Spam Guide
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

DB locking causing cpu spike

Post by philipissa » Tue Apr 13, 2010 5:04 pm

Hi - I've been having an issue where my MySQL db cpu spikes about once every day or two. When this happens, the db becomes unresponsive for about 10-20 min, then clears up on its own. Looking at the db, it looks like it is trying to access locked phpBB3 tables. I was a few patches behind, so i tried upgrading to 3.0.7, but that doesn't seem to have made any difference.
I am running:

PHPBB3 version 3.0.7-PL1
PHP Built on: Linux ELsmp #1 SMP Tue Feb 2 18:35:37 EST 2010 i686
Database Version: 5.0.58
Database Collation: utf8_general_ci
PHP Version: 5.2.6
Web Server: Apache/2.0.52 (CentOS)

Any help would be appreciated. A snippet of the mysql process list is shown below.
Thanks!
philip

Code: Select all

+---------+------------------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id      | User             | Host      | db               | Command | Time | State                | Info                                                                                                 |
+---------+------------------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 1193125 | my_db_name_live  | localhost | my_db_name_live  | Query   | 494  | Sorting result       | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1193126 | my_db_name_live  | localhost | my_db_name_live  | Query   | 494  | Sorting result       | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1193130 | my_db_name_live  | localhost | my_db_name_live  | Query   | 494  | Sorting result       | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1193139 | my_db_name_live  | localhost | my_db_name_live  | Query   | 494  | Sorting result       | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1193163 | my_db_name_live  | localhost | my_db_name_live  | Query   | 495  | Sorting result       | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1193165 | my_db_name_live  | localhost | my_db_name_live  | Query   | 495  | Sorting result       | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1207308 | my_db_name_live  | localhost | my_db_name_live  | Query   | 494  | Sorting result       | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1207341 | my_db_name_live  | localhost | my_db_name_live  | Query   | 495  | Sorting result       | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1271133 | my_db_name_live  | localhost | my_db_name_live  | Query   | 67   | Locked               | SELECT t.forum_id FROM phpbb3_topics t
                                LEFT JOIN phpbb3_topics_track tt ON (tt.topic_id = t.topi |
| 1271156 | my_db_name_live  | localhost | my_db_name_live  | Query   | 67   | Locked               | SELECT t.forum_id FROM phpbb3_topics t
                                LEFT JOIN phpbb3_topics_track tt ON (tt.topic_id = t.topi |
| 1271173 | my_db_name_live  | localhost | my_db_name_live  | Query   | 67   | Locked               | SELECT t.forum_id FROM phpbb3_topics t
                                LEFT JOIN phpbb3_topics_track tt ON (tt.topic_id = t.topi |
| 1271176 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | UPDATE phpbb3_topics
                SET topic_views = topic_views + 1, topic_last_view_time = 1271179523
                WHERE  |
| 1281821 | my_db_name_live  | localhost | my_db_name_live  | Query   | 69   | Locked               | UPDATE phpbb3_topics
                SET topic_views = topic_views + 1, topic_last_view_time = 1271179522
                WHERE  |
| 1309716 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT u.*, z.friend, z.foe, p.* FROM (phpbb3_users u, phpbb3_posts p) LEFT JOIN phpbb3_zebra z ON ( |
| 1309728 | my_db_name_live  | localhost | my_db_name_live  | Query   | 260  | Copying to tmp table | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1309729 | my_db_name_live  | localhost | my_db_name_live  | Query   | 260  | Copying to tmp table | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1309730 | my_db_name_live  | localhost | my_db_name_live  | Query   | 260  | Copying to tmp table | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1309746 | my_db_name_live  | localhost | my_db_name_live  | Query   | 260  | Copying to tmp table | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1309747 | my_db_name_live  | localhost | my_db_name_live  | Query   | 260  | Copying to tmp table | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1309749 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT u.*, z.friend, z.foe, p.* FROM (phpbb3_users u, phpbb3_posts p) LEFT JOIN phpbb3_zebra z ON ( |
| 1309751 | my_db_name_live  | localhost | my_db_name_live  | Query   | 260  | Copying to tmp table | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1309753 | my_db_name_live  | localhost | my_db_name_live  | Query   | 260  | Copying to tmp table | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1309762 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT u.*, z.friend, z.foe, p.* FROM (phpbb3_users u, phpbb3_posts p) LEFT JOIN phpbb3_zebra z ON ( |
| 1309776 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT u.*, z.friend, z.foe, p.* FROM (phpbb3_users u, phpbb3_posts p) LEFT JOIN phpbb3_zebra z ON ( |
| 1311486 | my_db_name_live  | localhost | my_db_name_live  | Query   | 260  | Copying to tmp table | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1362679 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1362680 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1362682 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1368581 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT u.*, z.friend, z.foe, p.* FROM (phpbb3_users u, phpbb3_posts p) LEFT JOIN phpbb3_zebra z ON ( |
| 1373727 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id |
| 1379543 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | UPDATE phpbb3_users
                                                SET user_lastvisit = 1271179360
                                                WHERE user_id = 13446                |
| 1379555 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | UPDATE phpbb3_users
                                                SET user_lastvisit = 1271179376
                                                WHERE user_id = 13446                |
| 1435377 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | UPDATE phpbb3_users
                                                SET user_lastvisit = 1271179471
                                                WHERE user_id = 13446                |
| 1435379 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | UPDATE phpbb3_users
                                SET user_lastvisit = 1271179474
                                WHERE user_id = 13420                    |
| 1452982 | my_db_name_live  | localhost | my_db_name_live  | Query   | 68   | Locked               | UPDATE phpbb3_users
                                                SET user_lastvisit = 1271179522
                                                WHERE user_id = 13411                |
| 1452989 | my_db_name_live  | localhost | my_db_name_live  | Query   | 65   | Locked               | SELECT u.*, s.*
                                FROM phpbb3_sessions s, phpbb3_users u
                                WHERE s.session_id = '00f3a2b955fa2e0 |
| 1452996 | my_db_name_live  | localhost | my_db_name_live  | Query   | 58   | Locked               | SELECT u.*, s.*
                                        FROM phpbb3_users u



Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: DB locking causing cpu spike

Post by Oleg » Tue Apr 13, 2010 5:34 pm

1. How many posts do you have on your board?

2. What table type/engine are you using for posts table in particular, and for other tables mentioned in the processlist?

3. When the spike happens again, please run 'show full processlist' and post the complete output here, perhaps as an attachment.

4. Do you have administrative access to mysql? (i.e., can you change mysql server parameters?)

5. Please run 'show variables' and post the output here, perhaps as an attachment. There should not be any sensitive information in it but you might want to double check.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

Re: DB locking causing cpu spike

Post by philipissa » Wed Apr 14, 2010 2:50 am

Hi nn - thanks for the quick reply. Here is the info you requested:

1. there are about 49,000 posts on the board.
2. the phpbb3 tables are all MyISAM...is that what you are asking?
3. no spike yet, but will run 'show full processlist' as soon as it happens
4. I do have root privs on the system, and can adjust mysql as necessary
5. 'show variables' output is attached. just changed server name to protect the innocent.

Thanks!
Philip
Attachments
sv.txt
show variables output
(23.06 KiB) Downloaded 34 times

philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

Re: DB locking causing cpu spike

Post by philipissa » Thu Apr 15, 2010 1:45 am

Here is the output of verbose process list. Thanks again for the help!

Philip
Attachments
verbose_log.zip
(145.23 KiB) Downloaded 19 times

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: DB locking causing cpu spike

Post by Oleg » Thu Apr 15, 2010 2:01 am

Please run this query and post back the result:

Code: Select all

explain SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id,t.topic_last_poster_name, p.post_time, p.post_text, 
        	p.bbcode_uid, p.bbcode_bitfield, u.username, u.user_id
                FROM  phpbb3_forums  f,phpbb3_topics t, phpbb3_posts p,phpbb3_users u
                WHERE t.forum_id = f.forum_id
                AND t.topic_status != 1
                AND p.post_id = t.topic_last_post_id 
                AND u.user_id = p.poster_id
                ORDER BY t.topic_last_post_id DESC
 LIMIT 30
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

Re: DB locking causing cpu spike

Post by philipissa » Thu Apr 15, 2010 4:42 pm

Here you go:

Code: Select all

id      | select_type   | table | type           | possible_keys                                          | key           | key_len | ref                             | rows | Extra
1       | SIMPLE        | f     | ALL            | PRIMARY                                                | NULL          | NULL    | NULL                            | 22   | Using temporary; Using filesort
1       | SIMPLE        | t     | ref            | forum_id,forum_id_type,forum_appr_last,fid_time_moved  | forum_id_type | 3       | primarydb.f.forum_id            | 105  | Using where
1       | SIMPLE        | p     | eq_ref         | PRIMARY,poster_id                                      | PRIMARY       | 3       | primarydb.t.topic_last_post_id  | 1    |  
1       | SIMPLE        | u     | eq_ref         | PRIMARY                                                | PRIMARY       | 3       | primarydb.p.poster_id           | 1    | 

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: DB locking causing cpu spike

Post by Oleg » Fri Apr 16, 2010 3:44 pm

Try these two versions:

Code: Select all

explain SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id,t.topic_last_poster_name, p.post_time, p.post_text,
           p.bbcode_uid, p.bbcode_bitfield, u.username, u.user_id
                FROM  phpbb_topics t inner join phpbb_forums  f on t.forum_id = f.forum_id, phpbb_posts p,phpbb_users u
                WHERE 
                t.topic_status != 1
                AND p.post_id = t.topic_last_post_id
                AND u.user_id = p.poster_id
                ORDER BY t.topic_last_post_id DESC
LIMIT 30;

Code: Select all

explain SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id,t.topic_last_poster_name, p.post_time, p.post_text,
           p.bbcode_uid, p.bbcode_bitfield, u.username, u.user_id
                FROM  phpbb_topics t inner join phpbb_forums  f on t.forum_id = f.forum_id, phpbb_posts p,phpbb_users u
                WHERE 
                t.topic_status != 1
                AND p.post_id = t.topic_last_post_id
                AND u.user_id = p.poster_id
                ORDER BY t.topic_last_post_time DESC
LIMIT 30;
And also run all 3 queries (including the original one) without explain and post back how long they took.

Do you have any mods installed? I suspect this query originates in a mod.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

Re: DB locking causing cpu spike

Post by philipissa » Sat Apr 17, 2010 9:53 pm

Hi nn -

Three files are attached, each one containing the "explain" results, as well as the same query without the "explain". I only had one mod installed, simple_rss_1.0.4. However, I think most of the changes for this mod were overwritten when I did my last upgrade. I'll double-check the mod to see if it contains any queries like the ones you listed.

Thanks!
Philip
Attachments
ex1.txt
(28.45 KiB) Downloaded 33 times
ex2.txt
(28.46 KiB) Downloaded 28 times
ex3.txt
(28.47 KiB) Downloaded 34 times

philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

Re: DB locking causing cpu spike

Post by philipissa » Sat Apr 17, 2010 10:47 pm

Hi nn -

I checked the rss mod, and it does indeed have the query that you asked me about. I've attached a copy of the php file it uses. It looks pretty harmless, but I'm no expert. I've temporarily removed the rss file to prevent it from being invoked to see if it prevents the error from recurring in the next week.

Philip
Attachments
rss.php
(4.12 KiB) Downloaded 27 times

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: DB locking causing cpu spike

Post by Oleg » Sun Apr 18, 2010 8:22 am

philipissa wrote:as well as the same query without the "explain".
You included the query output, which is not needed, but did not include the time it took to run the query ("X rows in set, Y sec"). Don't bother rerunning them just yet though, all three were planned identically so results will be the same.

Interestingly my test install now plans the first two queries identically, using the second query's plan. Statistics may be involved here.

First run this query:

Code: Select all

analyze table phpbb3_posts
Then run explain on the three queries again. If anything changes from what you posted above, post results of explain, then remove explain and post just the time it takes to execute the three queries without explain.

If explain results do not change, run this query and post its results here:

Code: Select all

show create table phpbb3_posts
You must have a lot of users using the rss feeds, does this sound reasonable to you?
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

Re: DB locking causing cpu spike

Post by philipissa » Sun Apr 18, 2010 7:12 pm

Hi nn -

Oops, sorry I didn't put the times in last time. I ran it again and here is the execution time: 30 rows in set (0.58 sec). The other two queries had identical times.
Getting all the rows: 10431 rows in set (0.80 sec)

I then ran the analyze command as requested. Here is the output:
mysql> analyze table phpbb3_posts;
+------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------------+---------+----------+----------+
| primarydb.phpbb3_posts | analyze | status | OK |
+------------------------------+---------+----------+----------+
1 row in set (0.22 sec)

I then reran the explains, however the output was identical to the previous times I ran it, as well as the execution times for the queries without the explains.

Here is the output from the "show create table phpbb3_posts" command:

Code: Select all

| phpbb3_posts | CREATE TABLE `phpbb3_posts` (
  `post_id` mediumint(8) unsigned NOT NULL auto_increment,
  `topic_id` mediumint(8) unsigned NOT NULL default '0',
  `forum_id` mediumint(8) unsigned NOT NULL default '0',
  `poster_id` mediumint(8) unsigned NOT NULL default '0',
  `icon_id` mediumint(8) unsigned NOT NULL default '0',
  `poster_ip` varchar(40) collate utf8_bin NOT NULL default '',
  `post_time` int(11) unsigned NOT NULL default '0',
  `post_approved` tinyint(1) unsigned NOT NULL default '1',
  `post_reported` tinyint(1) unsigned NOT NULL default '0',
  `enable_bbcode` tinyint(1) unsigned NOT NULL default '1',
  `enable_smilies` tinyint(1) unsigned NOT NULL default '1',
  `enable_magic_url` tinyint(1) unsigned NOT NULL default '1',
  `enable_sig` tinyint(1) unsigned NOT NULL default '1',
  `post_username` varchar(255) collate utf8_bin NOT NULL default '',
  `post_subject` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `post_text` mediumtext collate utf8_bin NOT NULL,
  `post_checksum` varchar(32) collate utf8_bin NOT NULL default '',
  `post_attachment` tinyint(1) unsigned NOT NULL default '0',
  `bbcode_bitfield` varchar(255) collate utf8_bin NOT NULL default '',
  `bbcode_uid` varchar(8) collate utf8_bin NOT NULL default '',
  `post_postcount` tinyint(1) unsigned NOT NULL default '1',
  `post_edit_time` int(11) unsigned NOT NULL default '0',
  `post_edit_reason` varchar(255) collate utf8_bin NOT NULL default '',
  `post_edit_user` mediumint(8) unsigned NOT NULL default '0',
  `post_edit_count` smallint(4) unsigned NOT NULL default '0',
  `post_edit_locked` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`post_id`),
  KEY `forum_id` (`forum_id`),
  KEY `topic_id` (`topic_id`),
  KEY `poster_ip` (`poster_ip`),
  KEY `poster_id` (`poster_id`),
  KEY `post_approved` (`post_approved`),
  KEY `tid_post_time` (`topic_id`,`post_time`),
  KEY `post_username` (`post_username`)
) ENGINE=MyISAM AUTO_INCREMENT=54967 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
I wouldn't be surprised if the rss feed gets hit often, but unfortunately I don't have specifics statistics/counts at hand. I'll see if I can locate some. I think tomorrow and tuesday will be very telling as to whether removing the rss feed has fixed the problem, as that is when our traffic will be heaviest.

Thanks!
Philip

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: DB locking causing cpu spike

Post by Oleg » Mon Apr 19, 2010 9:34 am

Apologies, I referenced the wrong table. The correct table is of course topics, not posts.

Code: Select all

analyze table phpbb3_topics

Code: Select all

show create table phpbb3_topics
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

Re: DB locking causing cpu spike

Post by philipissa » Tue Apr 20, 2010 1:49 am

Hi nn -

After running the analyze on phpbb3_topics, I did get different results:

Code: Select all

+----+-------------+-------+--------+-------------------------------------------------------+---------+---------+--------------------------------------+-------+-------------------------------+
| id | select_type | table | type   | possible_keys                                         | key     | key_len | ref                                  | rows  |   Extra                       |
|  1 | SIMPLE      | t     | ALL    | forum_id,forum_id_type,forum_appr_last,fid_time_moved | NULL    | NULL    | NULL                                 | 10490 |   Using where; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY                                               | PRIMARY | 3       | primarydb.t.forum_id                 |     1 |                               |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,poster_id                                     | PRIMARY | 3       | primarydb.t.topic_last_post_id       |     1 |                               |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                                               | PRIMARY | 3       | primarydb.p.poster_id                |     1 |                               |
+----+-------------+-------+--------+-------------------------------------------------------+---------+---------+--------------------------------------+-------+-------------------------------+
Running the query also went from 0.58 seconds to 0.06 seconds.

The sql for creating the table is as follows:

Code: Select all

| phpbb3_topics | CREATE TABLE `phpbb3_topics` (
  `topic_id` mediumint(8) unsigned NOT NULL auto_increment,
  `forum_id` mediumint(8) unsigned NOT NULL default '0',
  `icon_id` mediumint(8) unsigned NOT NULL default '0',
  `topic_attachment` tinyint(1) unsigned NOT NULL default '0',
  `topic_approved` tinyint(1) unsigned NOT NULL default '1',
  `topic_reported` tinyint(1) unsigned NOT NULL default '0',
  `topic_title` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `topic_poster` mediumint(8) unsigned NOT NULL default '0',
  `topic_time` int(11) unsigned NOT NULL default '0',
  `topic_time_limit` int(11) unsigned NOT NULL default '0',
  `topic_views` mediumint(8) unsigned NOT NULL default '0',
  `topic_replies` mediumint(8) unsigned NOT NULL default '0',
  `topic_replies_real` mediumint(8) unsigned NOT NULL default '0',
  `topic_status` tinyint(3) NOT NULL default '0',
  `topic_type` tinyint(3) NOT NULL default '0',
  `topic_first_post_id` mediumint(8) unsigned NOT NULL default '0',
  `topic_first_poster_name` varchar(255) collate utf8_bin NOT NULL default '',
  `topic_first_poster_colour` varchar(6) collate utf8_bin NOT NULL default '',
  `topic_last_post_id` mediumint(8) unsigned NOT NULL default '0',
  `topic_last_poster_id` mediumint(8) unsigned NOT NULL default '0',
  `topic_last_poster_name` varchar(255) collate utf8_bin NOT NULL default '',
  `topic_last_poster_colour` varchar(6) collate utf8_bin NOT NULL default '',
  `topic_last_post_subject` varchar(255) collate utf8_bin NOT NULL default '',
  `topic_last_post_time` int(11) unsigned NOT NULL default '0',
  `topic_last_view_time` int(11) unsigned NOT NULL default '0',
  `topic_moved_id` mediumint(8) unsigned NOT NULL default '0',
  `topic_bumped` tinyint(1) unsigned NOT NULL default '0',
  `topic_bumper` mediumint(8) unsigned NOT NULL default '0',
  `poll_title` varchar(255) collate utf8_bin NOT NULL default '',
  `poll_start` int(11) unsigned NOT NULL default '0',
  `poll_length` int(11) unsigned NOT NULL default '0',
  `poll_max_options` tinyint(4) NOT NULL default '1',
  `poll_last_vote` int(11) unsigned NOT NULL default '0',
  `poll_vote_change` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`topic_id`),
  KEY `forum_id` (`forum_id`),
  KEY `forum_id_type` (`forum_id`,`topic_type`),
  KEY `last_post_time` (`topic_last_post_time`),
  KEY `topic_approved` (`topic_approved`),
  KEY `forum_appr_last` (`forum_id`,`topic_approved`,`topic_last_post_id`),
  KEY `fid_time_moved` (`forum_id`,`topic_last_post_time`,`topic_moved_id`)
) ENGINE=MyISAM AUTO_INCREMENT=14511 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
Thanks for all your help!
Philip
The

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: DB locking causing cpu spike

Post by Oleg » Tue Apr 20, 2010 9:57 am

This was the first query, right? It is still suboptimal due to ordering by post id which has no index. Try this one:

Code: Select all

explain SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id,t.topic_last_poster_name, p.post_time, p.post_text,
           p.bbcode_uid, p.bbcode_bitfield, u.username, u.user_id
                FROM  phpbb3_forums  f,phpbb3_topics t, phpbb3_posts p,phpbb3_users u
                WHERE t.forum_id = f.forum_id
                AND t.topic_status != 1
                AND p.post_id = t.topic_last_post_id
                AND u.user_id = p.poster_id
                ORDER BY t.topic_last_post_time DESC
LIMIT 30
and time without explain.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

philipissa
Registered User
Posts: 19
Joined: Fri Dec 14, 2007 5:59 pm

Re: DB locking causing cpu spike

Post by philipissa » Tue Apr 20, 2010 5:29 pm

Explain output is below

Code: Select all

mysql> explain SELECT f.forum_id,f.forum_name, f.forum_desc_options, t.topic_title, t.topic_id,t.topic_last_post_id,t.topic_last_poster_name, p.post_time, p.post_text,            p.bbcode_uid, p.bbcode_bitfield, u.username, u.user_id                 FROM  phpbb3_forums  f,phpbb3_topics t, phpbb3_posts p,phpbb3_users u                 WHERE t.forum_id = f.forum_id                 AND t.topic_status != 1                 AND p.post_id = t.topic_last_post_id                 AND u.user_id = p.poster_id                 ORDER BY t.topic_last_post_time DESC LIMIT 30;
+----+-------------+-------+--------+-------------------------------------------------------+----------------+---------+--------------------------------------+-------+-------------+
| id | select_type | table | type   | possible_keys                                         | key            | key_len | ref                                  | rows  | Extra       |
+----+-------------+-------+--------+-------------------------------------------------------+----------------+---------+--------------------------------------+-------+-------------+
|  1 | SIMPLE      | t     | index  | forum_id,forum_id_type,forum_appr_last,fid_time_moved | last_post_time | 4       | NULL                                 | 10495 | Using where |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY                                               | PRIMARY        | 3       | primarydb.t.forum_id                 |     1 |             |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,poster_id                                     | PRIMARY        | 3       | primarydb.t.topic_last_post_id       |     1 |             |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                                               | PRIMARY        | 3       | primarydb.p.poster_id                |     1 |             |
+----+-------------+-------+--------+-------------------------------------------------------+----------------+---------+--------------------------------------+-------+-------------+
4 rows in set (0.00 sec)
Execution time without the explain is 0.02 seconds.

Also, I'm changing up the rss mod to cache the results, so it only executes the rss join if the cached page is older than an hour.

Thanks,
Philip

Locked

Return to “[3.0.x] Support Forum”