Test with large loads

Some modules need to be tested with large loads. Here are some situations where your module might slow down a large or busy site.

• You add SQL.
• You clear the cache.

The cache_clear_all function makes Drupal rebuild variables, menus and other items. Cache is used for anonymous users and is switched off for users when they log in. If you clear the whole cache frequently then your Web site slows down to rebuild cached information for people browsing the site and the extra overhead slows down the site for everyone.

You can be selective when clearing the cache and that will reduce the rebuilding. If you delete a specific cache entry then MySQL will find the single entry quickly, delete just the one entry, and only the one entry will be rebuilt. When you use a wildcard to select a range of entries then MySQL may have to scan the whole cache table index to find the right entries and rebuilding all those entries will take time.

There is at least once cache entry per node which means a site with a lot of nodes will suffer a greater slowdown than a site with a few nodes. If your test system has only a few nodes then you may never see a slowdown. You need to test with a site that has lots of nodes.

SQL can have the equivalent of a wildcard. The cache_clear_all wildcard translates to an SQL " where like" combination and the 'like" allows wildcards. Those wildcards and some other SQL scan a whole table instead of finding an individual row. Scanning a table is far slower than finding specific entries using an index.

http://drupal.org/node/35909 is an example of a discussion of a site slowdown caused by SQL. Read the discussion to get an idea of when a site is big enough to slow down and the type of symptoms you might see.

The 35909 problem was caused by a database table scan and the scan was replaced with a faster index lookup. The change to the SQL in a module was implemented at http://drupal.org/node/36429.

If your SQL runs once in an administration page then your SQL will not slow down a Web site unless you are scanning the whole of a very large frequently used site. If your SQL runs once per page view then your SQL will slow down your site even if the SQL is just slightly inefficient.

Think about an administration page that finds all nodes containing the text 'how-to' and changes the text to 'how to'. The SQL has to read all the text in every node. There are 40000 nodes in drupal.org. A search of drupal.org for 'how-to' will take several seconds and slow down the whole site for the whole search. If there are 100 people using the search and they each search 10 times per hour then drupal.org will slow down 1000 times per hour. You will never see the slow response on a test site with a few nodes and one user.

Now think about SQL that selects a specific row from one table but does it every page view. Your current Web site might retrieve 5 rows per page view. You add one more row. That is a total of 6 rows which is a 20 percent increase in database activity. If your module reads 10 rows instead of one, then your site jumps from 5 rows per page view to 15 rows per page view. Again you will not see the slowdown in a test site but you will see it in a site the size of Drupal.

Test your module with a large site. If you do not have a large site then offer the code for review by other Drupal developers and for testing at large sites. If you are reading rows from a table then you can search for the table name to see if anyone else has comments about the use of the table.

Look at the indexes used for table access. If you access table xyz using "where abc = '123'" then the database software will look for an index on column abc. If there is no index then the database software reads through every row of table xyz.

Indexes slow down database updates, do nothing to database reads and speed up searches. Adding an index to column abc will slightly slow down updates of the table and will speed up those cases where you search for a value in abc. The index will help if there are few updates and lots of searches. If there are many updates and searches are rare then an index might slow down the site more than you save on the occasional search.

Joins and compound where clauses are worth looking at. If you search a table with 100 entries then the search is so fast that you may not notice it. If you join two tables, each with 100 entries, and then search the result, you can be searching a result set containing 10000 rows. When you join three tables of 100 rows each then you could be searching 1000000 rows.

Memory is a limited resource that does not show up in a test system but becomes significant in a Web server with many active users. If your changes to code mean that you have to increase PHP's default memory size setting then you may slow down your Web site.

CPU power is also a limited resource and can be chewed up by frequent use of CPU intensive operations including string searches and regular expressions. If you add several CPU intensive operations to every page view then you will slow down the site by a noticeable amount. Drupal's cache mechanism may help you avoid performing the operations for every page view.

You can imagine how quickly you can drag a Web site to a complete halt with just a slight change to SQL or a big change to code. If your module adds SQL to every page view, every node view, or creates a database scan then you need to test with large numbers of rows in your tables and many users. If you module clears the cache or does something else to chew up resources, the result will show up in larger sites. If your module contains a potential slow down and you cannot test on a large site then ask other developers about ways to perform the task at maximum efficiency.