Fork me on GitHub
Subscribe 6

Ticket #91 (fixed enhancement)

Pretty significant performance regressions in 1.4.X when searching

  • Created: 2010-08-02 22:17:42
  • Reported by: toofishes
  • Assigned to: Franz
  • Milestone: 1.4.6
  • Component: database
  • Priority: high

We're noticing significant performance differences in FluxBB 1.4.X vs. the 1.2.X series. I'm going to attach a few things that come to mind, and I'd be glad to chip in more, but here we go.

$ mysqladmin status
Uptime: 309733  Threads: 1  Questions: 31691959  Slow queries: 1110  Opens: 1146  Flush tables: 1  Open tables: 291  Queries per second avg: 102.320
$ mysqld --version
mysqld  Ver 5.1.47-log for unknown-linux-gnu on x86_64 (Source distribution)
$ uname -a
Linux gudrun 2.6.34-ARCH #1 SMP PREEMPT Mon Jul 5 22:12:11 CEST 2010 x86_64 Intel(R) Xeon(TM) CPU 2.80GHz GenuineIntel GNU/Linux

All FluxBB tables are InnoDB.

mysql> SELECT TABLE_SCHEMA, count(*) TABLES, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES WHERE table_schema = 'fluxbb' GROUP BY TABLE_SCHEMA;
+--------------+--------+--------+-------+-------+------------+---------+
| TABLE_SCHEMA | TABLES | rows   | DATA  | idx   | total_size | idxfrac |
+--------------+--------+--------+-------+-------+------------+---------+
| fluxbb       |     17 | 23.14M | 1.34G | 0.91G | 2.26G      |    0.68 |
+--------------+--------+--------+-------+-------+------------+---------+
1 row in set (0.84 sec)

History

Franz 2010-08-02 22:19:20

  • Description changed. (Diff)

toofishes 2010-08-02 22:30:46

  • Description changed. (Diff)

Apparently I can't attach files. How about this instead then:
http://dev.archlinux.org/~dan/mysql_queries-week.png
http://dev.archlinux.org/~dan/mysql_slo … s-week.png

The upgrade happened on the 29th during the big insert spike there. You can see from there on out, the slow queries come a lot more frequently and the overall query count gets higher (nearly 20/second).

This box does run other applications and databases as well, but nothing has changed with those in this timeframe.

Finally, a slow query log if you want to run mysqldumpslow or just look through it:
http://dev.archlinux.org/~dan/fluxbb-slow.log

Reines 2010-08-02 23:16:08

  • Milestone set to 1.4.2.
  • Owner set to Reines.
  • Priority changed from normal to high.

Thanks for the info, I'll take a look into it.

Reines 2010-08-02 23:22:05

Just to save anyone else time, the queries in question are:
post.php, line 198:

SELECT u.id, u.email, u.notify_with_post, u.language FROM users AS u INNER JOIN subscriptions AS s ON u.id=s.user_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=23 AND fp.group_id=u.group_id) LEFT JOIN online AS o ON u.id=o.user_id LEFT JOIN bans AS b ON u.username=b.username WHERE b.username IS NULL AND COALESCE(o.logged, u.last_visit)>1280633364 AND (fp.read_forum IS NULL OR fp.read_forum=1) AND s.topic_id=102056 AND u.id!=2672;

and search.php, line 180:

SELECT m.post_id, p.topic_id, t.last_post AS sort_by FROM search_words AS w INNER JOIN search_matches AS m ON m.word_id = w.id INNER JOIN posts AS p ON p.id=m.post_id INNER JOIN topics AS t ON t.id=p.topic_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=t.forum_id AND fp.group_id=3) WHERE w.word LIKE 'uuid' AND (fp.read_forum IS NULL OR fp.read_forum=1);

Franz 2010-08-03 08:09:14

By the way, the higher query count might have to do with the fact that we split up a few queries into two in viewtopic.php and viewforum.php (and other places, too) to increase performance.
These pages are probably the most requested ones in a forum application.

Reines 2010-08-03 09:13:00

  • Description changed. (Diff)

Reines 2010-08-09 12:30:34

  • Milestone changed from 1.4.2 to 1.4.3.

Reines 2010-08-23 23:10:15

  • Owner Reines removed.

toofishes 2010-08-24 01:32:23

The query at post.php, line 198 can be improved quite a bit by adding an index (it appears MySQL is too stupid to be able to use the covering index that already exists which is a shame):

mysql> explain SELECT u.id, u.email, u.notify_with_post, u.language FROM users AS u INNER JOIN subscriptions AS s ON u.id=s.user_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=23 AND fp.group_id=u.group_id) LEFT JOIN online AS o ON u.id=o.user_id LEFT JOIN bans AS b ON u.username=b.username WHERE b.username IS NULL AND COALESCE(o.logged, u.last_visit)>1280633364 AND (fp.read_forum IS NULL OR fp.read_forum=1) AND s.topic_id=102056 AND u.id!=2672;
+----+-------------+-------+--------+--------------------------+-------------------+---------+-------------------------+-------+-------------+
| id | select_type | table | type   | possible_keys            | key               | key_len | ref                     | rows  | Extra       |
+----+-------------+-------+--------+--------------------------+-------------------+---------+-------------------------+-------+-------------+
|  1 | SIMPLE      | u     | range  | PRIMARY                  | PRIMARY           | 4       | NULL                    | 22281 | Using where |
|  1 | SIMPLE      | fp    | eq_ref | PRIMARY                  | PRIMARY           | 8       | fluxbb.u.group_id,const |     1 | Using where |
|  1 | SIMPLE      | o     | ALL    | online_user_id_ident_idx | NULL              | NULL    | NULL                    |   187 | Using where |
|  1 | SIMPLE      | b     | ref    | bans_username_idx        | bans_username_idx | 78      | fluxbb.u.username       |     1 | Using where |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                  | PRIMARY           | 8       | fluxbb.u.id,const       |     1 | Using index |
+----+-------------+-------+--------+--------------------------+-------------------+---------+-------------------------+-------+-------------+
5 rows in set (0.03 sec)


mysql> ALTER TABLE online ADD INDEX online_user_id_idx (user_id);


mysql> explain SELECT u.id, u.email, u.notify_with_post, u.language FROM users AS u INNER JOIN subscriptions AS s ON u.id=s.user_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=23 AND fp.group_id=u.group_id) LEFT JOIN online AS o ON u.id=o.user_id LEFT JOIN bans AS b ON u.username=b.username WHERE b.username IS NULL AND COALESCE(o.logged, u.last_visit)>1280633364 AND (fp.read_forum IS NULL OR fp.read_forum=1) AND s.topic_id=102056 AND u.id!=2672;
+----+-------------+-------+--------+---------------------------------------------+--------------------+---------+-------------------------+-------+-------------+
| id | select_type | table | type   | possible_keys                               | key                | key_len | ref                     | rows  | Extra       |
+----+-------------+-------+--------+---------------------------------------------+--------------------+---------+-------------------------+-------+-------------+
|  1 | SIMPLE      | u     | range  | PRIMARY                                     | PRIMARY            | 4       | NULL                    | 21767 | Using where |
|  1 | SIMPLE      | fp    | eq_ref | PRIMARY                                     | PRIMARY            | 8       | fluxbb.u.group_id,const |     1 | Using where |
|  1 | SIMPLE      | o     | ref    | online_user_id_ident_idx,online_user_id_idx | online_user_id_idx | 4       | fluxbb.u.id             |     3 | Using where |
|  1 | SIMPLE      | b     | ref    | bans_username_idx                           | bans_username_idx  | 78      | fluxbb.u.username       |     1 | Using where |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                                     | PRIMARY            | 8       | fluxbb.u.id,const       |     1 | Using index |
+----+-------------+-------+--------+---------------------------------------------+--------------------+---------+-------------------------+-------+-------------+
5 rows in set (0.00 sec)

Reines 2010-10-25 11:20:20

  • Milestone changed from 1.4.3 to 1.4.4.

Reines 2011-01-26 11:19:44

  • Milestone changed from 1.4.4 to 1.4.5.

Mpok 2011-02-16 05:02:38

Any chance to see that resolved in 1.4.5 ?
wink

Reines 2011-03-11 09:22:41

  • Milestone changed from 1.4.5 to 1.4.6.

Franz 2011-07-23 22:22:43

I am not an expert on these things. Would adding the index be enough to fix this, @toofishes? If so, on which columns?

Franz 2011-07-23 22:45:45

  • Owner set to Franz.

toofishes 2011-07-28 13:35:47

It definitely helped in our case. I would definitely suggest adding such an index. The reason the normal covering index principles don't work here is due to the fact that indexes on memory-based tables are created as hash indexes by default, so there is no B-tree traversal.

In the more general "does this completely fix search" category, no, it does not. We have a very large forum (40K users, 120K topics, 940K posts), so the search tables are far and away the cause of nearly all lookup problems for a few reasons:

1. Shouldn't be doing search in a database anyway, but this seems to be being addressed in issue #421 (search engine modules).
2. The search tables are not scaling. We have 730K search words, but 26 million search matches.
3. Search words are heavily overloaded. Our top ten words have counts ranging from 80K - 120K matches *each*.
4. When searching, the full results are sent back to the PHP code (30K plus rows at times), only to be stored back in the database in the search cache. There should be two things fixed here- get the results straight into the DB with no round trip, and have a way of limiting the maximum number of search results if one wishes to do so.

Franz 2011-07-28 14:01:37

Weird, it looks like such an index once existed. Any idea, anybody?

toofishes 2011-07-28 14:16:27

It looks like that is due to someone making the mistake that the two-part index on user (user_id_ident_index, user_id and ident columns) would work. As I noted in my last comment, it does not (at least in MySQL; other databases will likely not create a HASH index by default) due to the index being of HASH type vs BTREE.

http://dev.mysql.com/doc/refman/5.5/en/ … -hash.html

The solution is either to add the other index as I suggested (which will work really fast and well for equality joins, which is all you do), or explicitly create the two-column index as a BTREE index. I'd suggest just adding the additional index.

Franz 2011-07-28 14:18:01

Which means the other one can be deleted again?

toofishes 2011-07-28 14:19:52

It depends. You will need to look for all queries that involve the online table and see what they join on.

However, because the two column index is unique, it is likely there to enforce uniqueness meaning it shouldn't be removed even if unused by queries.

Franz 2011-07-28 14:27:45

Commit 352e465 to fluxbb fluxbb-1.4

#91: Add an index to the online table (user_id column) to speed up a query in post.php.

Franz 2011-07-28 14:29:52

There we are. This should fix the problem (or, at least, improve performance) in post.php.

Is there a similar fix we could apply to search.php? As long as we don't revamp the whole search system, bigger changes would be ok, too.

toofishes 2011-07-30 05:05:24

Re: adding that index back in- I would probably do that only for MySQL, unless other database engines are also doing funny things with the online table. Or is it just our database that has this set up using Engine=MEMORY? In which case it shouldn't be needed anywhere and this is just something in our setup that differs from the now default, and the index won't be needed for anyone else.

Other thoughts, including that other query. It looks like the shit performance comes down to a few things. MySQL has a terrible optimizer, and looking at the slow query log today, every time we get a slow hit on that query (the only slow one in there), it is because it is returning 50K+ rows. More importantly, running one of these through EXPLAIN shows that MySQL thinks only about 12 rows will be returned, which is obviously way off. I'm not sure there is much you can fix with this.

Something else of note is the non-uniqueness of some unique fields- namely id in search_words.

$ tail /var/lib/mysql/gudrun-slow.log
# Time: 110730  0:32:00
# User@Host: fluxbb[fluxbb] @ localhost []
# Query_time: 8.111481  Lock_time: 0.000206 Rows_sent: 74483  Rows_examined: 226858
SET timestamp=1312000320;
SELECT m.post_id, p.topic_id, t.last_post AS sort_by FROM search_words AS w INNER JOIN search_matches AS m ON m.word_id = w.id INNER JOIN posts AS p ON p.id=m.post_id INNER JOIN topics AS t ON t.id=p.topic_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=t.forum_id AND fp.group_id=4) WHERE w.word LIKE 'system' AND (fp.read_forum IS NULL OR fp.read_forum=1);

$ mysqldumpslow /var/lib/mysql/gudrun-slow.log
Reading mysql slow query log from /var/lib/mysql/gudrun-slow.log
Count: 46  Time=10.43s (479s)  Lock=0.00s (0s)  Rows=63602.8 (2925728), fluxbb[fluxbb]@localhost
  SELECT m.post_id, p.topic_id, t.last_post AS sort_by FROM search_words AS w INNER JOIN search_matches AS m ON m.word_id = w.id INNER JOIN posts AS p ON p.id=m.post_id INNER JOIN topics AS t ON t.id=p.topic_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=t.forum_id AND fp.group_id=N) WHERE w.word LIKE 'S' AND (fp.read_forum IS NULL OR fp.read_forum=N)

mysql> EXPLAIN SELECT m.post_id, p.topic_id, t.last_post AS sort_by FROM search_words AS w INNER JOIN search_matches AS m ON m.word_id = w.id INNER JOIN posts AS p ON p.id=m.post_id INNER JOIN topics AS t ON t.id=p.topic_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=t.forum_id AND fp.group_id=4) WHERE w.word LIKE 'system' AND (fp.read_forum IS NULL OR fp.read_forum=1);
+----+-------------+-------+--------+-------------------------------------------------------+----------------------------+---------+-------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                                         | key                        | key_len | ref                     | rows | Extra       |
+----+-------------+-------+--------+-------------------------------------------------------+----------------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | w     | range  | PRIMARY,search_words_id_idx                           | PRIMARY                    | 62      | NULL                    |    1 | Using where |
|  1 | SIMPLE      | m     | ref    | search_matches_post_id_idx,search_matches_word_id_idx | search_matches_word_id_idx | 4       | fluxbb.w.id             |   12 |             |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,posts_topic_id_idx                            | PRIMARY                    | 4       | fluxbb.m.post_id        |    1 |             |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                                               | PRIMARY                    | 4       | fluxbb.p.topic_id       |    1 |             |
|  1 | SIMPLE      | fp    | eq_ref | PRIMARY                                               | PRIMARY                    | 8       | const,fluxbb.t.forum_id |    1 | Using where |
+----+-------------+-------+--------+-------------------------------------------------------+----------------------------+---------+-------------------------+------+-------------+
5 rows in set (0.00 sec)

Pierre 2011-07-31 14:11:22

I just had a look at the install script. The online table is created as HEAP unless you choose InnoDB in which case InnoDB is also used here.

We migrated to InnoDB later so we probably missed to change the enigne of that table.

What was the rationale behind this decision?

Franz 2011-07-31 20:34:25

Here's what Smartys told me on Twitter: https://twitter.com/NealPoole/status/97699418672873472

So that probably means we can change the engine again, can we?

Franz 2011-07-31 21:05:45

Ok, I've got two commits prepared for this.

One changes the engine of the online table to MyISAM for normal MySQL(i) or to InnoDB for those who use that.

I am not sure whether we would still need the extra index in that case - should it be removed again or still stay?

@toofishes and @pierre: Could you test these two variants, please?

Franz 2011-08-02 12:52:30

Commit 083e721 to fluxbb fluxbb-1.4

#91: Change online table engine to MyISAM for MySQL.

Franz 2011-08-02 12:54:13

There we go. This should do the necessary. Please test this!

The remaining question is whether the index on online.user_id can be removed again (if I understood everything correctly, it should be removed).

Franz 2011-08-03 07:56:31

Commit fd88efb to fluxbb fluxbb-1.4

Revert commit 352e465. #91.

Franz 2011-08-03 08:08:28

Commit af36a9e to fluxbb fluxbb-1.4

Also bump database revision number in core.

Related to #91, #465.

Franz 2011-08-03 08:16:22

Commit c7ed3fd to fluxbb fluxbb-1.4

Add latest database changes to install.php, too.

Related to #91, #465.

Franz 2011-08-03 08:17:55

  • Component changed from search to database.
  • Status changed from open to fixed.

I believe this should be fixed now.

Franz 2011-08-03 09:46:05

  • Type changed from bug to enhancement.

Pierre 2011-08-03 09:52:53

I have converted our online DB to innodb and removed the index on user_id. Let's see how it does.