The goal of our benchmark is simulating the load that Tweakers.net (excluding the forum) exerts on the database under normal circumstances. The product version – the one that just assisted the author in producing the current page of this review – runs a MySQL 4.0 installation with close to two hundred tables, which vary substantially in size (ranging from a few kilobytes to several gigabytes) and activity. The database runs on a dedicated system and gets its instructions from a load-balanced cluster of web servers. This separation of data and web apps is based on a classic 'two-tier' pattern.
The web servers are supplied with PHP 4.4 and use the standard MySQL libraries. Depending on which page is requested, the web servers fire different queries of widely varying complexity at the database. Some queries are directed at a single table while others access four or more. The
WHERE clauses tend to be quite short: in most cases only filtering according to a particular key takes place (such as the number of a news item or product) . In a few cases additional operations like sorting or page numbering are performed.
The benchmark purposely ignores regular maintenance, in part to reduce the duration of the test but also to minimise the number of write operations (
INSERT), which in turn minimizes the influence of the storage system on the performance. The active part of the database encompasses only a few gigabytes, allowing systems with 4GB to 8GB to take the entire working set into memory, so that even with 2GB there is not much disk activity. This puts the emphasis of operation on the processors and memory. A further difference with the production environment is that in the tests, whole series of requests are handled in a single batch, while normally, every page requires a new connection with the database to be initiated. Cutting out that step allows us to load the database more severely and in a more controlled fashion.
The test database is a backup of the production database that gets loaded in MySQL 4.1.20 and 5.0.20a. It is imported in a CVS version of PostgreSQL 8.2, which, insofar as we have been able to determine, is fully stable. The latter has had a couple of its indexes replaced to allow better performance. Additionally, a few data structures were altered. Other than the server that is being grilled at present, the lab setup consists of three machines: two Appro web servers (dual Xeon 2.4GHz with 1GB of memory) to generate requests, and a third machine to process the results. Gigabit ethernet ties the machines together.
In our test, a visitor is represented by a series of pageviews that are handled by Apache 2.2 and PHP 4.4. On average, a series of requests totals 115, with variations due to varying probabilities of responding to a news item or entering a price in the Pricewatch section of this site. To be precise, every 'visitor' requests the following pages, which are chosen at random unless stated otherwise.
|1||Dynamic frontpage (subscriber's function)|
|18||Most recent news items|
|7||Random news items|
|13||Overview of Pricewatch categories|
|14||Pricewatch price tabels|
|5% probability||Enter a new price|
|2.5% probability||Post reaction to the most recent news item|
Although this may not be entirely natural, we believe it to be an acceptable approximation of reality, with enough variation and load to make the database sweat. The requests are put in a random order to get an unpredictable pattern fired at the database. To prevent the web servers from becoming a bottleneck, as little as possible is done with the responses. The final result is that the number of page views that gets executed in a time span of exactly ten minutes (measured with Apache-bench). Although not every page is the same, even the slowest of runs request over ten thousand of them, which means we can feel quite safe concerning the comparability of the results from a statistical point of view.
A complete session involves runs with varying numbers of simultaneous visitors, ranging from one to a hundred. Each web server simulates half the number of visitors. The database machine fires the starting shot, so that it can register the start and end times itself. We are considering doing this with a separate control system in the future, but at the moment it does not appear to have a significant influence on the result if we leave it to the database machine. Each session is kicked off by a warming-up round with 25 simultaneous visitors, to get memory and cache filled up. This ensures that the first run is not at a disadvantage. After every run, the database is cleaned up by removing the newly entered prices and reactions (a 'vacuum' command in the case of PostgreSQL). After that, the machine gets a thirty second break to prepare for the next attack.