Test performance/scalability of d6.d.o

I certainly intend to look at the slow query log before this goes live and to look at the queries generated by any views. Are there other areas that need attention?

I have some memcache integration patches to help with performance. One is waiting to be merged (providing an external pagecache, allowing anonymous pages to be served without hitting the database), another I'm cleaning up so it can be applied cleanly the way our source control system works (a pagecache lock that prevents cache stampedes) -- I hope to have it ready later today or tomorrow. I also need to roll a new memcache module release, as code for a minimum cache lifetime has landed there.

Beyond that, I'm aware of the need for a patch to the menu system, per this comment. I had hoped to work on that already, perhaps later today or tomorrow.

Note that we did already merge all performance patches that are currently part of Drupal.org -- David and I were working on a clean way to maintain these patches which has been accomplished.

I don't know how much effort it would take, but it seems to me that the association could buy time on a cluster at AWS. We could set up three web-heads and 2 db servers and then have a third server load test. This may be more trouble than it's worth, but it seems a viable option worth exploring.

Hi,

One thing that's been impressed onto me as I've been working to improve the performance of a site we're building for a client is that the order of items in the 'WHERE' clause is important for determining the index that mysql will use - I had gotten into a habit of doing node queries with 'WHERE status = 1 and type = 'article'', for instance, when 'WHERE type = 'article' and status = 1' will perform better because the type index is more specific.

Related to that, views puts items in the WHERE clause of it's queries in the same order that they're in the filter display - pushing the 'Node: published' filter down and other more specific filters up generally helps.

@jcfialia That is incorrect.

mysql> EXPLAIN SELECT * FROM node WHERE status = 1 AND type = "project_issue";
+----+-------------+-------+------+----------------------------------------------------------------+-----------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys                                                  | key       | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+----------------------------------------------------------------+-----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | node  | ref  | node_type,node_status_type,tracker_global,node_status_type_uid | node_type | 32      | const | 181414 | Using where | 
+----+-------------+-------+------+----------------------------------------------------------------+-----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM node WHERE type = "project_issue" AND status = 1;
+----+-------------+-------+------+----------------------------------------------------------------+-----------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys                                                  | key       | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+----------------------------------------------------------------+-----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | node  | ref  | node_type,node_status_type,tracker_global,node_status_type_uid | node_type | 32      | const | 181414 | Using where | 
+----+-------------+-------+------+----------------------------------------------------------------+-----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

What David told me in Cambridge, MA was that the D5 patches were ported so it is really up to (1) how slower is D6 in itself (2) how slower are the new project stuff, which was changed. Apart that, the search seems to have been improved in speed, even now that we use an external test search server.

I' have been wondering for the D6 menu system whether we need to stop trying to cache the whole router in cache_menu - this is a very big blob to read/write.

We try to pull it out of the cache (and/or set it) here: http://api.drupal.org/api/function/menu_router_build/6

The only place we are using this blob is: http://api.drupal.org/api/function/menu_link_save

And basically we could rewrite this function: http://api.drupal.org/api/function/_menu_find_router_path/6

to do a query if $menu was not passed in. Really having the $menu in memory is typically only beneficial when we are rebuilding the navigation links and hence are calling menu_link save many times in a single page load.

We will use the SUN machine to do some performance tests and collect slow queries and improperly indexed tables.

Gerhard completed extensive testing of the site, and suggested several improvements:

#371475: Reduce left joins for issue views Assigned to: dww
#372168: Slow query log from updated version of drupal.org
#371521: Missing index in menu_links table
#371458: Missing index menu_router tab_root
#371439: Mixed primary key on profile_values does not work
#371452: Missing index on visibility in profile_fields
#371543: Missing index for status on blocks
#215080: Performance: change {system}.type: alter table system modify column type VARCHAR(32);