Stress Testing CakePHP with Pylot

Submitted by Derick Ng on 6 November 2009 - 4:21am
CakePHP/Pylot

Using Pylot to Load Test CakePHP

Since we like to use the CakePHP framework here at A51 we figured it would be great if we could find or develop a tool to load test our sites as we developed them to weed out any issues that would be caused in a high load production environment. After some research, we found a small open source application called Pylot (http://www.pylot.org) which is made for testing performance and scalability of web services. Pylot generates concurrent load (HTTP Requests), verifies server responses, and produces reports with metrics. Tests suites are executed and monitored from a GUI or shell/console.

I decided to use CentOS 5.4 (stable) for my testing and started with a clean install of the OS with just the base packages and no window manager. Pylot requires a couple dependencies for different actions you may want it to perform. First is Python, and since Pylot requires Python 2.5+ I needed to compile Python from source as CentOS repository only had 2.4.3. The next dependencies were NumPy (http://sourceforge.net/projects/numpy)  as well as MatPlotlib (http://sourceforge.net/projects/matplotlib) which are both used for generating graphs.

Now that my system was ready to run Pylot I needed to setup a test case (ie. a list of what and how I wanted a site to be tested). Pylot sets up tests cases with simple XML files. In it’s simplest form, a test case would look like this:


  
    http://www.foo.com/bar

To run the test we run the run.py script in the base directory of the Pylot source. Pylot will run a designated amount of clients for a designated amount of time on the URLs listed in the test case XML document and then generate a results directory with a nice HTML file to display the findings.

As great as the data that comes of out Pylot is, it wasn’t good enough to point out to us what exactly what slowing the site down, it would just tell you what pages where slower then others. What we needed was a way to generate MySQL performance data from Pylot and combine the two data collections together to see where the site was slow under n amount of users and what MySQL queries were causing that.

It finally dawned on me to just use the debug feature in CakePHP, which generates a query log  per page load. For Example,

(default) 12 queries took 19 ms
Nr Query Affected Rows Took (ms)
1 DESCRIBE `contents` 10 2
2 DESCRIBE `layouts` 6 6 2
3 DESCRIBE `contents_blocks` 7 7 2
4 DESCRIBE `blocks` 6 6 2
5 DESCRIBE `media` 8 8 2
6 SELECT `Content`.`id`, `Content`.`title`, `Content`.`sub_head`, `Content`.`intro`, `Content`.`body`, `Content`.`published`, `Content`.`path`, `Layout`.`name`, `Layout`.`id` FROM `contents` AS `Content` LEFT JOIN `layouts` AS `Layout` ON (`Layout`.`published` = 1 AND `Content`.`layout_id` = `Layout`.`id`) WHERE `Content`.`published` = 1 AND `Content`.`path` = ‘/’ LIMIT 1 1 1 1
7 SELECT `ContentsBlock`.`position`, `Blocks`.`method`, `ContentsBlock`.`cache_time` FROM `contents_blocks` AS `ContentsBlock` LEFT JOIN `blocks` AS `Blocks` ON (`ContentsBlock`.`blocks_id` = `Blocks`.`id`) WHERE `ContentsBlock`.`published` = 1 AND `Blocks`.`published` = 1 AND `ContentsBlock`.`content_id` = 1 ORDER BY `ContentsBlock`.`position` ASC 3 3 1
8 SELECT `Block`.`method` FROM `blocks` AS `Block` WHERE 1 = 1 3 3 1
9 DESCRIBE `news_items` 9 9 2
10 SELECT `NewsItem`.`id`, `NewsItem`.`title`, `NewsItem`.`news_date` FROM `news_items` AS `NewsItem` WHERE `NewsItem`.`published` = 1 ORDER BY `NewsItem`.`news_date` DESC LIMIT 3 2 2 1
11 DESCRIBE `menus` 10 10 2
12 SELECT `Menu`.`id`, `Menu`.`parent_id`, `Menu`.`name`, `Menu`.`path` FROM `menus` AS `Menu` WHERE `Menu`.`published` = 1 ORDER BY `Menu`.`parent_id` ASC, `Menu`.`placement` ASC 6 6 1

Since Pylot downloads the page render to a log file all I needed to do was write a script to parse the log files right after the testing finished and log all the query times. I decided to write my script in PHP as it would allow me to easily build a web interface for future tests by co-workers. With some string manipulation we can parse out the time per query which allows us to find the fastest and slowest query per page load as well as the overall query time per page load. This information coupled with the fact that Pylot is gathering these tests results based of a defined user base over a set period of time means we can see if certain queries perform differently when experiencing a higher load.

foobar

Simple web interface for testing. Image shows a test running 100 agents concurrently over a 1 minute test.

Pylot Web Frontend Results

Pylot Web Frontend Results. Displays the slowest and fastest results of the test. Log files are broken down into each agents tests.

foo

Final MySQL query results showing total queries & query time on the server.

Our initial find was with regard to the DESCRIBE queries that were being run on page loads. For some reason our pages were spending more then half their query time on these DESCRIBE queries. After some research on the topic we found that we weren’t alone being a little confused about these queries. Many people simply said that turning the debug off was stopping the DESCRIBE queries but we seemed to prove otherwise with the use of MySQL slow query logging.

We found that the only way to actually get rid of the DESCRIBE queries was to have a cache setting.  Therefore the DESCRIBE queries are dependant on the cache setting. If the cache is disabled, we will get DESCRIBE queries which allow the CakePHP framework to understand the database layout. If cache is turned on, the DESCRIBE queries will fire once and cache the result, meaning the subsequent users will not trigger them.

NOTE: Global cache setting in app/config/core.php (Configure::write(‘Cache.disable’, true);)

The more I use this system the more I seem to like it. We now have free reign to test our applications in our office with as many concurrent users as our servers can handle. We have found some interesting issues so far – relating from MySQL configuration to networking configuration to  fan speed and temperature control.

It goes to show that production level testing in a development environment can really pay off.