Drupal.org D7 performance testing

This one is definitely a @nnewton task, along with help from Fabian Franz.

I started updating the issue summary to a list of host sports we should be sure to test, including merging in #1549410: Performance test taxonomy listing pages.

I've done some testing comparing the Drupal 6 stage site VM to the Drupal 7 upgrade one. Both perform about the same when faced with the top 5000 URLs from Drupal.org. Stabilizing at 40 simulated users and 5 requests per second with a couple percent exceeding a 4 second cut off. Of those slow requests, some do take over 30 seconds on either version of the site. Given the limitations of the staging VMs, I don't think the slow requests in a ramp test like this is a good gauge of which URLs actually took the longest for the pages to generate but just any requests that happened while a slow page was being built.

Next is going to be some more focused testing for the slow loading pages from the ramp testing. For these tests I'm going to limit testing to one page at a time and compare the load times across versions of the site, which should point out any slow loading pages specific to the upgrade better than the ramp testing.

What's your definition of "load time"? Time to download a HTML response from the server? Time to load the entire page, including CSS/JS/…?

We're mainly concerned with HTML response. The front-end isn't changing a whole lot with the D7 upgrade, there will be approximately the same amount of CSS/JS as on D6. I think there are even a few less images, thanks to more-modern things like CSS rounded corners and gradients.

The big change is Drupal 7's field storage. We already have a few pages on Drupal.org that are hot spots for the DB. We want to make sure they are not made worse, and there are no new ones.

Ok, thanks for the clarification!

Hi Chizu,

When was this test run? I'd like to pull the slow logs.

-N

I don't have dates for the previous ramp testing, since I tossed most of the data in favor of better versions of the tests. I reran a bunch of it this evening though.

10-24 - UTC times
2:05 - 2:20 : git7site cache cleared ramp test top 5000 URLs (stabilized at 4 users)
2:20 - 2:30 : git7site ramp test top 5000 URLs (stabilized at 36 users)
2:50 - 3:40 : git7site linear test of top 1000 URLs from production with cache cleared
3:50 - 4:55 : staging linear test of top 1000 URLs from production with cache cleared
5:45 - 5:50 : git7site ramp test top 5000 URLs (stabilized at 40 users)
6:50 - 7:00 : git7site retest 100 slowest URLs (10 users)

A user is defined for these tests as one request every 1 to 10 seconds in a ratio of URLs matching the logs. The top n URLs are number of requests per day from one sample day. The linear tests just run one request at a time in an attempt to isolate page loads from each other.

Are any of these authed tests?

-N

Those tests were run without any authed users. Finished up some new tests better covering the projects/issues and forums parts of the site and reran the top 5000 URLs ramp test with authenticated users.

10/28 UTC times for the slow query logs
22:30 - 22:40 Authed issues tests
22:40 - 22:50 Authed forum tests
22:55 - 00:00 Authed user ramp testing

Weighted the results by average page loads per day and the highest impact slow page is the forum index (/forum) by a rather large margin. Attached is the slow query log from that comparing the staging and git7site.

Both of these queries can be simplified greatly by using the forum_index table.

Before:

+----+-------------+-------+--------+----------------------------------------------------------------------+------------------+---------+-------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                        | key              | key_len | ref               | rows   | Extra                                        |
+----+-------------+-------+--------+----------------------------------------------------------------------+------------------+---------+-------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | n     | ref    | PRIMARY,vid,node_status_type,nid,tracker_global,node_status_type_uid | node_status_type | 4       | const             | 553235 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY                                                              | PRIMARY          | 4       | git7staging.n.vid |      1 |                                              |
|  1 | SIMPLE      | ncs   | eq_ref | PRIMARY                                                              | PRIMARY          | 4       | git7staging.n.nid |      1 |                                              |
+----+-------------+-------+--------+----------------------------------------------------------------------+------------------+---------+-------------------+--------+----------------------------------------------+

Replace with:
SELECT fi.tid, count(fi.nid) topic_count, sum(fi.comment_count) comment_count FROM forum_index fi GROUP BY fi.tid ORDER BY NULL;

+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------+
|  1 | SIMPLE      | fi    | ALL  | NULL          | NULL | NULL    | NULL | 308037 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------+

Before:

+----+-------------+-------+--------+---------------------------------------------------------------------------------------+---------+---------+----------------------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                         | key     | key_len | ref                              | rows   | Extra                                        |
+----+-------------+-------+--------+---------------------------------------------------------------------------------------+---------+---------+----------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | f     | ref    | PRIMARY,tid                                                                           | tid     | 4       | const                            | 261390 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | n     | ref    | PRIMARY,vid,uid,node_status_type,nid,tracker_user,tracker_global,node_status_type_uid | vid     | 5       | git7staging.f.vid                |      1 | Using where                                  |
|  1 | SIMPLE      | ncs   | eq_ref | PRIMARY,last_comment_uid                                                              | PRIMARY | 4       | git7staging.n.nid                |      1 |                                              |
|  1 | SIMPLE      | u1    | eq_ref | PRIMARY                                                                               | PRIMARY | 4       | git7staging.n.uid                |      1 | Using where; Using index                     |
|  1 | SIMPLE      | u2    | eq_ref | PRIMARY                                                                               | PRIMARY | 4       | git7staging.ncs.last_comment_uid |      1 | Using where                                  |
+----+-------------+-------+--------+---------------------------------------------------------------------------------------+---------+---------+----------------------------------+--------+----------------------------------------------+

Replace with:
SELECT ncs.last_comment_timestamp AS last_comment_timestamp, ncs.last_comment_uid AS last_comment_uid, CASE ncs.last_comment_uid WHEN 0 THEN ncs.last_comment_name ELSE u.name END AS last_comment_name FROM forum_index fi INNER JOIN node_comment_statistics ncs ON fi.nid = ncs.nid INNER JOIN users u ON ncs.last_comment_uid = u.uid WHERE fi.tid = 22 ORDER BY fi.last_comment_timestamp DESC LIMIT 1 OFFSET 0;

+----+-------------+-------+--------+--------------------------+------------------------+---------+----------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys            | key                    | key_len | ref                              | rows | Extra       |
+----+-------------+-------+--------+--------------------------+------------------------+---------+----------------------------------+------+-------------+
|  1 | SIMPLE      | fi    | index  | forum_topics             | last_comment_timestamp | 4       | NULL                             |    1 | Using where |
|  1 | SIMPLE      | ncs   | eq_ref | PRIMARY,last_comment_uid | PRIMARY                | 4       | git7staging.fi.nid               |    1 |             |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                  | PRIMARY                | 4       | git7staging.ncs.last_comment_uid |    1 | Using where |
+----+-------------+-------+--------+--------------------------+------------------------+---------+----------------------------------+------+-------------+

I found #145353: Forums performance improvements and am committing my patch to BZR, and deploying.

We'll keep looking for performance improvements, but this is now not a blocker as far as I know.

Hey drumm,

This is from forum subtopic correct?

SELECT f.tid AS tid, COUNT(n.nid) AS topic_count, SUM(ncs.comment_count) AS comment_count
FROM
node n
INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid
INNER JOIN forum f ON n.vid = f.vid
WHERE (n.status = '1')
GROUP BY tid

Nevermind, its both (as is the re-written version).

-N

#14 comes from the main /forum page before #145353: Forums performance improvements was applied. It is now

SELECT f.tid AS tid, COUNT(f.nid) AS topic_count, SUM(f.comment_count) AS comment_count FROM node n INNER JOIN forum_index f ON n.nid = f.nid GROUP BY tid ORDER BY NULL ASC

Start quantifying problem areas.

Closing since D7 upgrade is live. We have separate issues for existing performance troubles.