Revision as of 06:22, 5 February 2008

Performance Tuning by Example

Please note that this page is being created now (5 feb 2008) and it is probably not worth reading until I've finished my first edits

Rather than a list of recipes to follow, this page takes you through a real world example.

If you have any disagreements or suggestions about performance tuning, please feel free to add them to the page, but for the convenience of anyone that is following this, mark them as suggestions - please don't just change the text.

Starting Point

There are already a bunch of tutorials out there about getting an initial configuration. At the start of this process, I expect that you will have verything setup and working to your satisfaction.

We have the following major components:

1. apache-tomcat-5.5.25
2. httpd-2.2.3-7
3. OpenCMS 7.03
4. mysql-server-5.0.22-2.1

We have used the Alkacon documentation to setup apache in front of OpenCMS using mod_proxy. We do have a slightly weird naming convention as (for historical reasons) we separate our 'public' and 'customer' sites such that the public site is totally exported while the customer site is mediated by opencms.

Thus we have something like:

/ -> mod_rewrite /export/aunz
/portal -> mod_proxy -> opencms
/export/aunz (all static pages)
/export/system
/resources

It may be easier to see what we're doing by looking at the /etc/httpd/conf.d/opencms.conf file:

Tuning Setup

The first thing to do in any tuning is to get a sense of what you want your site to do. I would imagine that scalability, performance and stability are import to you. They are to me.

We are aiming to have the site tuned for 1,000 concurrent users hitting a dynamically generated page with back-end data every 2 seconds. This doesn't sound like much, but we have a 1970s legacy system that we're integrating with (anyone want a job as a UniBasic Programmer?), so this is pretty significant load against the legacy system. Fortunately there are not that many pages that require a back-end hit, so for the other pages we want 10,000 concurrent users (still hitting a page every 2 seconds).

We're going to start by tuning the non-back-ended pages.

Before we start actual tuning there are some measurement tools that are going to be important to us.

Firstly, a naive http load. This is just users clicking on links. Great for getting a sense of the simplest type of load. The tool for generating this kind of load is provided by apache in the httpd package. If you have httpd, you probably have apache bench (or ab). For the more complicated loads we'll be using jmeter (but more on that later).

apache bench on the command line:

ab -c 60 -n1000 http://dev1.local/portal/index.html

(don't forget to change your derver name)

This will make 60 concurrent connections and request the page http://dev1.local/portal/index.html up to a maximum of 10000 pages.

This gives you a baseline. Save it.

My Baseline is:

SServer Software:        Apache-Coyote/1.1
Server Hostname:        dev1.local
Server Port:            80

Document Path:          /portal/index.html
Document Length:        5986 bytes

Concurrency Level:      10
Time taken for tests:   95.435580 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      6260000 bytes
HTML transferred:       5986000 bytes
Requests per second:    10.48 [#/sec] (mean)
Time per request:       954.356 [ms] (mean)
Time per request:       95.436 [ms] (mean, across all concurrent requests)
Transfer rate:          64.05 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       3
Processing:    96  952 2084.1    676   36504
Waiting:       95  951 2084.1    675   36503
Total:         96  952 2084.1    676   36504

Percentage of the requests served within a certain time (ms)
  50%    676
  66%    846
  75%    931
  80%    996
  90%   1360
  95%   1884
  98%   3008
  99%   8735
 100%  36504 (longest request)

Tuning MySQL

OK, so now that all of that is out of the way let's start looking at the back of the stack. Now I should point out here that I am not an expert in MySQL. However there are loads of them out there and lots of them write webpages. Yipee! Thanks lazyweb.

For background take a look at [1] and [2].

There are also useful tools such as:

[3] [4]

So for our purposes I'm just going to show the changes I made to my.cnf:

[mysqld]
datadir=/u01/mysql
socket=/u01/mysql/mysql.sock
user=mysql

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

skip-locking
table_cache = 256

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

# enable the slow query log, default 10 seconds
log-slow-queries
# log queries taking longer than 5 seconds
long_query_time = 5
# log queries that don't use indexes even if they take less than long_query_time
log-queries-not-using-indexes

# Add an in memory cache so that we can keep the content in memory
query_cache_size = 32M

# Increase packet size limit due to OpenCms requirements
max_allowed_packet=32M

# Setup some reasonable defaults

set-variable=max_connections=300
set-variable=wait_timeout=500
max_connect_errors = 100

# Start a thread cache

thread_cache = 30

# Start Replication Config Settings
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# End Replication Config Settings
     
[mysql.server]
user=mysql
basedir=/u01/mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
user=mysql
socket=/u01/mysql/mysql.sock

Feel free to ignore the replication section - as we're running a backup hot standby server as well. Oh and you probably have different paths - I keep my data on a separate file system called /u01.

More interesting is the section for loggin slow queries - keep an eye on these for later tuning work.

You can see the section:

; Setup some reasonable defaults

set-variable=max_connections=300
set-variable=wait_timeout=500
max_connect_errors = 100

Max connections stops mysql from going crazy and eating all the resources on the machine. For our expected load of 1000 concurrent users, 300 connections will probably be sufficient - but we'll see later.

OpenCMS connects to MySQL using the apache commons code dbcp. This seems to take a while to connect even while running locally, so we're upped the wait_timeout to 500. This is half a second and should be fine. If you start seeing errors in your catalina logs complaining about " Failed to write locks to database " then this is an area to look at.

Max connect errors is a bit of a magical incantation. While you would expect that an error either happens or it doesn't we actually allow a larger than standard number of errors to occur before we kill a connection. This is to allow a bit more time for mysql to recover from transient issues than the default of max_connect_errors = 10

After MySQL Tuning: ab -c 60 -n10000 http://dev1.local/portal/index.html

Server Software:        Apache-Coyote/1.1
Server Hostname:        www.nutrimetics.com.au
Server Port:            80

Document Path:          /portal/index.html
Document Length:        5956 bytes

Concurrency Level:      100
Time taken for tests:   78.264082 seconds
Complete requests:      10000
Failed requests:        0
Write errors:           0
Total transferred:      61650000 bytes
HTML transferred:       59560000 bytes
Requests per second:    127.77 [#/sec] (mean)
Time per request:       782.641 [ms] (mean)
Time per request:       7.826 [ms] (mean, across all concurrent requests)
Transfer rate:          769.25 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.7      0      13
Processing:    24  779 4366.8     57   37687
Waiting:       24  779 4366.8     56   37686
Total:         24  779 4366.8     57   37687

Percentage of the requests served within a certain time (ms)
  50%     57
  66%     72
  75%     90
  80%    110
  90%    359
  95%    492
  98%  17514
  99%  32836
 100%  37687 (longest request)

After tomcat tuning

Server Software:        Apache-Coyote/1.1
Server Hostname:        www.nutrimetics.com.au
Server Port:            80

Document Path:          /portal/index.html
Document Length:        6005 bytes

Concurrency Level:      200
Time taken for tests:   92.178489 seconds
Complete requests:      10000
Failed requests:        0
Write errors:           0
Total transferred:      62140000 bytes
HTML transferred:       60050000 bytes
Requests per second:    108.49 [#/sec] (mean)
Time per request:       1843.570 [ms] (mean)
Time per request:       9.218 [ms] (mean, across all concurrent requests)
Transfer rate:          658.32 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    6 169.5      0    8997
Processing:    16 1828 9496.1     64   92026
Waiting:       16 1828 9496.0     64   92024
Total:         16 1834 9505.2     64   92039

Percentage of the requests served within a certain time (ms)
  50%     64
  66%     83
  75%    105
  80%    130
  90%    533
  95%   2363
  98%  40927
  99%  61524
 100%  92039 (longest request)