Large forum, MySQL slow on particular query

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)
parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Large forum, MySQL slow on particular query

Post by parsim » Wed Apr 04, 2012 12:53 am

Support Request Template
What version of phpBB are you using? phpBB 3.0.10
What is your board's URL? http://forum.nationstates.net
Who do you host your board with? self (2 x Amazon AWS EC2 servers: frontend + database backend)
How did you install your board? I used the download package from phpBB.com
What is the most recent action performed on your board? Update from a previous version of phpBB3
Is registration required to reproduce this issue? Yes
Do you have any MODs installed? Yes
What version of phpBB3 did you update from? phpBB 3.0.9
What MODs do you have installed? custom
What styles do you currently have installed? modified prosilver
What language(s) is your board currently using? American English
Which database type/version are you using? MySQL 5
What is your level of experience? Comfortable with PHP and phpBB
When did your problem begin? Gradually, with growth of forum.
Please describe your problem.

Our board has 7.5 million posts and 290,000 users, with an average of 6,782 posts per day. It has a dedicated database server (an Amazon.com Large EC2 instance), but frequently exhibits very high load behavior, with MySQL taking a long time to return particular queries.

Our main problem seems to come from line 84 of viewtopic.php, which creates queries like this:

Code: Select all

$sql = 'SELECT post_id, topic_id, forum_id
    FROM ' . POSTS_TABLE . "
    WHERE topic_id = $topic_id
    " . (($auth->acl_get('m_approve', $forum_id)) ? '' : 'AND post_approved = 1') . "
    AND post_time > $topic_last_read
    AND forum_id = $forum_id
    ORDER BY post_time ASC";
This leads to mysql-slow.log entries like this:

Code: Select all

# Time: 120404 0:26:54
# User@Host: forumfe[forumfe] @ ip-10-242-49-156.ec2.internal [10.242.49.156]
# Query_time: 78.591672 Lock_time: 0.000102 Rows_sent: 1 Rows_examined: 8346
SET timestamp=1333499214;
SELECT post_id, topic_id, forum_id
    FROM phpbb_posts
    WHERE topic_id = 143108
        AND post_approved = 1
        AND post_time > 0
        AND forum_id = 4
    ORDER BY post_time ASC
 LIMIT 1;
That is, over a minute to examine a mere 8,000 rows.

I suspect this happens because MySQL is required to merge a very large index:

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 143108 AND post_approved = 1 AND post_time > 0 AND forum_id = 4 ORDER BY post_time ASC LIMIT 1;
+----+-------------+-------------+-------------+-----------------------------------------------+-------------------+---------+------+------+-----------------------------------------------------------------+
| id | select_type | table       | type        | possible_keys                                 | key               | key_len | ref  | rows | Extra                                                           |
+----+-------------+-------------+-------------+-----------------------------------------------+-------------------+---------+------+------+-----------------------------------------------------------------+
|  1 | SIMPLE      | phpbb_posts | index_merge | forum_id,topic_id,post_approved,tid_post_time | topic_id,forum_id | 3,3     | NULL |  233 | Using intersect(topic_id,forum_id); Using where; Using filesort |
+----+-------------+-------------+-------------+-----------------------------------------------+-------------------+---------+------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
It seems vaguely related to this topic, which a phpBB3 developer said was "the first time I see index_merge used for anything."

I am hesitant to follow the same style of solution and create a new index based on topic_id and forum_id, as this seems like it would be fairly enormous.

Incidentally, while investigating this, I have found that the permalink to posts, which is in "?p=xxxxxxx" style rather than "?f=x&t=y", causes 'm_approve' settings to be ignored, and the SQL query to always include "AND post_approved = 1" even if the forum has disabled post approval.
Generated by SRT Generator

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

Re: Large forum, MySQL slow on particular query

Post by Oleg » Wed Apr 04, 2012 5:16 am

I suppose I would expect the topic_id index to be used, but if you have a lot of posts per topic (thousands?) mysql may consider it insufficiently selective.

If you are on mysql < 5.5 I would suggest upgrading to 5.5 before doing anything else as I hear they have added some optimizer improvements.

You can probably replace the index on forum_id with an index on (forum_id, topic_id). This should not result in large index growth and might be sufficient to get mysql to plan the query correctly.

If that does not work you can try (forum_id, topic_id, post_approved, post_time). This also can replace the forum_id index. This index should match the query perfectly and require at most (page size) accesses which normally should be 25.

My concern with a (forum_id, topic_id, post_approved, post_time) index is that some queries do not specify post_approved, in particular I believe moderators don't get it and if you don't have post approval enabled regular users may also not have it. You can try (forum_id, topic_id, post_time) and see if mysql would still use the index for retrieving the posts and filter them on post_approved after the fact.
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

parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Re: Large forum, MySQL slow on particular query

Post by parsim » Thu Apr 05, 2012 11:41 pm

Thank you very much!

We are running MySQL 5.1.61 on Ubuntu 10.04, so I guess it's time for an OS upgrade. I'll give this a shot and report back.

Edit: Which will probably be in a month or two, as we'll try to go direct from Ubuntu 10.04 LTS to 12.04 LTS.

parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Re: Large forum, MySQL slow on particular query

Post by parsim » Fri May 04, 2012 5:58 am

Reporting back as promised.
Oleg wrote:If you are on mysql < 5.5 I would suggest upgrading to 5.5 before doing anything else as I hear they have added some optimizer improvements.
Done! We are now running 5.5.22-0ubuntu1-log. Unfortunately, there is no noticeable improvement.
You can probably replace the index on forum_id with an index on (forum_id, topic_id). This should not result in large index growth and might be sufficient to get mysql to plan the query correctly.

If that does not work you can try (forum_id, topic_id, post_approved, post_time). This also can replace the forum_id index. This index should match the query perfectly and require at most (page size) accesses which normally should be 25.

My concern with a (forum_id, topic_id, post_approved, post_time) index is that some queries do not specify post_approved, in particular I believe moderators don't get it and if you don't have post approval enabled regular users may also not have it. You can try (forum_id, topic_id, post_time) and see if mysql would still use the index for retrieving the posts and filter them on post_approved after the fact.
I have plugged away at this and found some odd results. Replacing the 'forum_id' index with (forum_id, topic_id) did indeed seem to improve the situation, but in a surprising way: the query did not use the new index at all, but rather fell back to 'tid_post_time'.

Here's the new index:

Code: Select all

mysql> SHOW INDEXES FROM phpbb_posts;
+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| phpbb_posts |          0 | PRIMARY           |            1 | post_id       | A         |     7461329 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | topic_id          |            1 | topic_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | poster_ip         |            1 | poster_ip     | A         |      298453 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | poster_id         |            1 | poster_id     | A         |       64321 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | post_approved     |            1 | post_approved | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | tid_post_time     |            1 | topic_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | tid_post_time     |            2 | post_time     | A         |     7461329 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | post_username     |            1 | post_username | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | tid_fid           |            1 | topic_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | tid_fid           |            2 | forum_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
And here it is being ignored in favor of 'tid_post_time':

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 117104 AND forum_id = 4 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: topic_id,tid_post_time,tid_fid
          key: tid_post_time
      key_len: 3
          ref: const
         rows: 10056
        Extra: Using where
1 row in set (0.00 sec)
I then tried adding (forum_id, topic_id, post_time) index, as suggested, and found this did get used sometimes but not others, depending on the particular topic and forum id. I'm guessing this is dependent on how many matches there are for that forum_id. When it didn't get used, MySQL again used 'tid_post_time'.

Either way, it ran a lot faster, mainly because it avoided filesort, I think.

So I had the idea of reverting to phpBB's original indexes, and simply adding 'IGNORE INDEX(forum_id)'. In all the cases I tried, this radically sped up the query!

Here is a query performed first with IGNORE INDEX, then immediately again without:

Code: Select all

mysql> SELECT post_id, topic_id, forum_id FROM phpbb_posts IGNORE INDEX(forum_id) WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1;
+---------+----------+----------+
| post_id | topic_id | forum_id |
+---------+----------+----------+
| 5514210 |   110126 |       19 |
+---------+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1;
+---------+----------+----------+
| post_id | topic_id | forum_id |
+---------+----------+----------+
| 5514210 |   110126 |       19 |
+---------+----------+----------+
1 row in set (28.50 sec)
It's an instantaneous response when I use IGNORE INDEX(forum_id), compared to a 28.5 second wait without it (when MySQL does a "Using intersect(topic_id,forum_id); Using where; Using filesort", as described in my first post).

I'm a real amateur when it comes to databases, so before I start spamming "IGNORE INDEX(forum_id)" into my phpBB3 files, I wanted to run this by you and see if it made any sense.

If it's relevant, we are running on a machine under reasonably heavy disk load (mostly reads generated by MySQL) but with lots of free RAM.

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

Re: Large forum, MySQL slow on particular query

Post by Oleg » Fri May 04, 2012 7:04 pm

Did you run analyze table after changing indexes? Are you giving mysql enough memory?

Please do an explain SELECT post_id, topic_id, forum_id FROM phpbb_posts IGNORE INDEX(forum_id) WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1;.
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

User avatar
brunoais
QA Team
Posts: 378
Joined: Wed Jun 18, 2008 10:50 am

Re: Large forum, MySQL slow on particular query

Post by brunoais » Fri May 04, 2012 8:44 pm

Hum....
MySQL still only supports BTree as an index. It's a way that has a nice balance between speed (O(log n)) and space used. It's weird that it takes that long to search but the reason can be as simple as a slow disk, very busy disk or too much fragmentation Even though it does not suffer the same way as NTFS suffers (NTFS has way bigger impact), the file system used by linux can also suffer from fragmentation, actually.

parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Re: Large forum, MySQL slow on particular query

Post by parsim » Sun May 06, 2012 12:58 am

Thank you very much for the replies!
Oleg wrote:Did you run analyze table after changing indexes?
I did, yes. But to be sure, I went back and recreated the indexes and tried it again (see below).
Oleg wrote:Are you giving mysql enough memory?
Yes. I'm trying things on both a live production server and a cloned test server; they each have 4GB assigned to the Key Buffer:

Code: Select all

__ Key _________________________________________________________________
Buffer used   487.08M of   4.00G  %Used:  11.89
  Current       1.06G            %Usage:  26.42
Write hit      73.56%
Read hit       99.99%

Code: Select all

$ free -m
             total       used       free     shared    buffers     cached
Mem:          7680       7633         46          0          1       5106
-/+ buffers/cache:       2526       5153
Swap:            0          0          0
I believe the machine is disk-bound, causing a large performance penalty for queries that require MySQL to go outside the indexes:

Code: Select all

$ vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 3  3      0  45640   1248 5226356    0    0   993   214   41   39 11  8 69  6
Oleg wrote:Please do an explain SELECT post_id, topic_id, forum_id FROM phpbb_posts IGNORE INDEX(forum_id) WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1;.
Okay, here are the results, in various configurations. Note that this particular topic_id (110126) has about 50,000 posts in it; it's one of our longest.

First, as a baseline, here is our production box again, which is still using phpBB3's standard indexes. The query without IGNORE takes 20-80 seconds to complete, and its EXPLAIN looks like this:

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: index_merge
possible_keys: forum_id,topic_id,tid_post_time
          key: topic_id,forum_id
      key_len: 3,3
          ref: NULL
         rows: 1537
        Extra: Using intersect(topic_id,forum_id); Using where; Using filesort
1 row in set (0.02 sec)
Adding IGNORE INDEX(forum_id) to the mix causes it to fall back to 'tid_post_time', as described earlier, and completes very quickly (<0.1 sec):

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts IGNORE INDEX(forum_id) WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: topic_id,tid_post_time
          key: tid_post_time
      key_len: 3
          ref: const
         rows: 43040
        Extra: Using where
1 row in set (0.00 sec)
Now here is the test box, on which I've added the indexes you suggested. I had previously deleted forum_id, so I recreated that again first:

Code: Select all

mysql> CREATE INDEX forum_id ON phpbb_posts (forum_id);
Query OK, 7461329 rows affected (7 min 56.02 sec)
Records: 7461329  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE phpbb_posts;
+-------------------+---------+----------+-----------------------------+
| Table             | Op      | Msg_type | Msg_text                    |
+-------------------+---------+----------+-----------------------------+
| forum.phpbb_posts | analyze | status   | Table is already up to date |
+-------------------+---------+----------+-----------------------------+
1 row in set (0.02 sec)

mysql> SHOW INDEXES FROM phpbb_posts;
+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| phpbb_posts |          0 | PRIMARY           |            1 | post_id       | A         |     7461329 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | topic_id          |            1 | topic_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | poster_ip         |            1 | poster_ip     | A         |      298453 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | poster_id         |            1 | poster_id     | A         |       64321 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | post_approved     |            1 | post_approved | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | tid_post_time     |            1 | topic_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | tid_post_time     |            2 | post_time     | A         |     7461329 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | post_username     |            1 | post_username | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | tid_fid           |            1 | topic_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | tid_fid           |            2 | forum_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | fid_tid_post_time |            1 | forum_id      | A         |          23 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | fid_tid_post_time |            2 | topic_id      | A         |      152272 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | fid_tid_post_time |            3 | post_time     | A         |     7461329 |     NULL | NULL   |      | BTREE      |         |               |
| phpbb_posts |          1 | forum_id          |            1 | forum_id      | A         |          23 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 rows in set (0.00 sec)
... and then ran an EXPLAIN for the standard query (not using IGNORE INDEX yet), just to confirm that it's the same as the production box, and that the new indexes are not used:

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: index_merge
possible_keys: topic_id,tid_post_time,tid_fid,fid_tid_post_time,forum_id
          key: topic_id,forum_id
      key_len: 3,3
          ref: NULL
         rows: 1592
        Extra: Using intersect(topic_id,forum_id); Using where; Using filesort
1 row in set (0.00 sec)
Now to actually answer your question, here is an EXPLAIN using IGNORE INDEX(forum_id):

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts IGNORE INDEX(forum_id) WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: topic_id,tid_post_time,tid_fid,fid_tid_post_time
          key: fid_tid_post_time
      key_len: 6
          ref: const,const
         rows: 28381
        Extra: Using where
1 row in set (0.00 sec)
In this case, it is indeed using one of the new indexes you suggested. It also completes near-instantly.

Out of interest, I added index that to the IGNORE list. MySQL then dropped back to 'tid_post_time':

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts IGNORE INDEX(forum_id,fid_tid_post_time) WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: topic_id,tid_post_time,tid_fid
          key: tid_post_time
      key_len: 3
          ref: const
         rows: 45391
        Extra: Using where
1 row in set (0.00 sec)
This output is also what I get sometimes if I use different numbers for topic_id and forum_id. For example, here it is with a topic_id of 178109:

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 178109 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: topic_id,tid_post_time,tid_fid,fid_tid_post_time,forum_id
          key: tid_post_time
      key_len: 3
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)
Furthermore, I noticed that for some values of topic_id, it will use 'tid_fid' (plus a filsort), the other new index you suggested:

Code: Select all

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 17367 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: topic_id,tid_post_time,tid_fid,fid_tid_post_time,forum_id
          key: tid_fid
      key_len: 6
          ref: const,const
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.06 sec)
I haven't been able to figure out any pattern to this; I thought perhaps it was based on length of topic, but this didn't seem to hold during a brief experiment:
  • tid_post_time used in threads with: 5 posts (178109), 15 posts (178067)
  • tid_fid: 5,000 posts (17367)
  • fid_tid_post_time: 50,000 posts (110126), 100 posts (175393), 100 posts (143891), 3,000 posts (173504), 5 posts (175175)
These are all in the same forum (19). Possibly the number of topics in the forum makes a difference to index selection as well! I'm not sure.
brunoais wrote:It's weird that it takes that long to search but the reason can be as simple as a slow disk, very busy disk or too much fragmentation
We certainly have a very busy disk. However, the load all seems to be coming from MySQL. 'iotop' reveals MySQL threads frequently reading up to 2-10MB/s.
Last edited by parsim on Sun May 06, 2012 1:11 am, edited 1 time in total.

parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Re: Large forum, MySQL slow on particular query

Post by parsim » Sun May 06, 2012 1:06 am

I should mention, I did actually go ahead and add 'IGNORE INDEX(forum_id)' to the query starting at line 83 in viewtopic.php on our production box, and that query has vanished from our mysql-slow.log. Disk activity is still quite high, but very noticeably lower than before.

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

Re: Large forum, MySQL slow on particular query

Post by Oleg » Wed May 09, 2012 4:54 am

It sounds like mysql is misplanning that query for some reason. Given an index on both forum_id and topic_id I cannot imagine a situation where it would be advantageous to do an "index merge" over the separate forum_id and topic_id indexes instead of using the index covering both columns.
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

User avatar
brunoais
QA Team
Posts: 378
Joined: Wed Jun 18, 2008 10:50 am

Re: Large forum, MySQL slow on particular query

Post by brunoais » Wed May 09, 2012 10:55 am

Oleg wrote:It sounds like mysql is misplanning that query for some reason. Given an index on both forum_id and topic_id I cannot imagine a situation where it would be advantageous to do an "index merge" over the separate forum_id and topic_id indexes instead of using the index covering both columns.
Should we inform MySQL devleopers about the problem?

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

Re: Large forum, MySQL slow on particular query

Post by Oleg » Wed May 09, 2012 2:50 pm

Feel free.
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

parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Re: Large forum, MySQL slow on particular query

Post by parsim » Thu May 10, 2012 5:54 am

Thanks for the reply!

I've submitted a MySQL bug. Hope that's the right place.

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

Re: Large forum, MySQL slow on particular query

Post by Oleg » Thu May 10, 2012 6:21 am

I think you can also force the use of a particular index, if so I would be curious what the plan/runtime are when forcing the use of an index including both forum id and topic id.
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

parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Re: Large forum, MySQL slow on particular query

Post by parsim » Thu May 10, 2012 6:31 am

Sorry to be such a noob, but what is a good way to benchmark that? I know I can avoid the key buffer with SQL_NO_CACHE, but I think my slower queries occur because MySQL relies on the filesystem cache for data. My forum database size is 6.9GB, and the server has 7.5GB of memory; add in the key buffer for indexes, and it can't keep the whole thing in RAM at once. So when it needs to scan big tables, it does a lot of reading.

I find that even with SQL_NO_CACHE, when I run a query, it may be very quick or very slow, depending (I think) on whether the server's filesystem happens to already have that part of the file in its own cache. And there's no way for MySQL to tell the filesystem to stop caching something.

Short of rebooting the server every time I want to benchmark a query, I can't think how to overcome this.

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

Re: Large forum, MySQL slow on particular query

Post by Oleg » Thu May 10, 2012 8:41 am

The plan should stay the same even if its execution time varies.

Your database should not be keeping the entire dataset in memory. Indexes should hopefully be kept in memory, and for actual rows the database should be hitting the disk.

If your indexes are getting swapped out of memory you might need to tune something or make the indexes smaller or cut some out. If your database does too much disk i/o that would be a different issue.

Naturally these are mostly general considerations.
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

Locked

Return to “[3.0.x] Support Forum”

Who is online

Users browsing this forum: thecoalman and 34 guests

cron