Server Tuning

Some notes on tuning a server for MKDoc, this is based on a development server with less than 1G of RAM, a production server with a decent amount of RAM should be able to have a higher figure for ther Apache MaxClients!

mod_perl

See the Performance Tuning document from the mod_perl 1.0 User Guide.

If you install the GTop module and add this code (based on the Calculating Real Memory Usage code) to the end of mkdoc.cgi (before 1;):

use GTop;
my $proc_mem = GTop->new->proc_mem($$);
my $diff     = $proc_mem->size - $proc_mem->share;
$diff = $diff/1024;
$diff = $diff/1024;
my $size = $proc_mem->size;
$size = $size/1024;
$size = $size/1024;
my $share = $proc_mem->share;
$share = $share/1024;
$share = $share/1024;
print STDERR "Difference is $diff MB, Size is $size MB and Share is $share MB\n";

Then you will get lines like this written to the error log:

Difference is 40.4765625 MB, Size is 45.234375 MB and Share is 4.7578125 MB
Difference is 40.73828125 MB, Size is 45.49609375 MB and Share is 4.7578125 MB
Difference is 42.01171875 MB, Size is 46.76953125 MB and Share is 4.7578125 MB
Difference is 40.9453125 MB, Size is 45.6875 MB and Share is 4.7421875 MB
Difference is 41.69921875 MB, Size is 46.44140625 MB and Share is 4.7421875 MB

This basically means that each Apache process uses around 40MB of RAM and in addition there is about 5MB of shared RAM.

.
              Total RAM Dedicated to the Webserver
 MaxClients = ------------------------------------
                    MAX child's process size

So for example if you have 256M dedicated to Apache:

.
               256M
 MaxClients = ------ = 6
                40M

MySQL

See the Solving Memory Bottlenecks section of the High Performance MySQL book.

Each apache process will use one MySQL process so the MySQL max_connections should not be set to be less than the Apache MaxClients.

The MySQL you have probably came with sample config files for different situations, for example in /usr/share/mysql/*.cnf — it's probably best to pick the one best suited and use this as a basis for the MySQL main config file, /etc/my.cnf.

MySQL memory usage is set via a number of system variables, you can view the variables like this:

mysqladmin -uroot -pXXX variables

Also this command lists the settings, see tuning server parameters for more info (add --verbose for MySQL 4.1 or greater):

/usr/libexec/mysqld --help

(mysqld might also be in /usr/sbin/mysqld if you are using binaries from MySQL.com)

And you can see the number of processes like this:

mysqladmin -uroot -pXXX processlist

The mytop perl module is good for displaying mysql processes dynamically.

This is the calculation you need to do for the memory usage:

min_memory_needed = global_buffers + (thread_buffers * max_connections)

This is a simple script to get these variables:

For example:

thread_buffers
---------------------------+---------
sort_buffer_size           |  1048568
myisam_sort_buffer_size    | 67108864
read_buffer_size           |  1044480
join_buffer_size           |   131072
read_rnd_buffer_size       |   262144
---------------------------+----------
TOTAL                      | 69595128 bytes (66M)
global_buffers
---------------------------+----------
key_buffer_size            | 268435456
net_buffer_length          |     16384
---------------------------+----------
TOTAL                      | 268451840 bytes (256M)

So if the max_connections = 6 then MySQL will need:

652 = 256 + (6 x 66)

However, testing shows that the MySQL memory is actually less than this, and:

The two most important global buffers are the MyISAM key buffer (key_buffer_size)… By adding up the size of the .MYI files for the tables, you'll have a good idea how large to set the buffer.

You can get the sum on the *.MYI files like this:

# find /var/lib/mysql/ -name *.MYI | xargs du -sch

The key_buffer_size should probably be set to a value greater than the sum of these files. The machine that this document is based on has 31M of *.MYI files so key_buffer_size should be OK set to 35M or so.

The myisam_sort_buffer_size is the main thing that effects the size of each MySQL process and it is probably going to be OK set to 4M or so…

These values look like this:

thread_buffers:
+-------------------------+---------+
| myisam_sort_buffer_size | 4194304 |
| sort_buffer_size        | 1048568 |
| read_buffer_size        | 1044480 |
| join_buffer_size        |  131072 |
| read_rnd_buffer_size    |  262144 |
+-------------------------+---------+
TOTAL                       6680568 (6M)
global_buffers:
+-------------------+----------+
| key_buffer_size   | 36700160 |
| net_buffer_length |    16384 |
+-------------------+----------+
TOTAL                 36716544 (35M)

So the equation is now (6 x 6) + 35 = 71M — a lot more reasonable, this will allow the maximum number of apache processed to be increased to 10 resulting in Apache using 400M of RAM and MySQL using 95M which leaves quite a bit of spare RAM for other things.

Document Links

Performance Tuning
mod_perl Performance Tuning.
http://perl.apache.org/docs/1.0/guide/performance.html
Solving Memory Bottlenecks
Solving Memory Bottlenecks
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html#hpmysql-CHP-6-SECT-4.3
mytop perl module
mytop perl module
http://jeremy.zawodny.com/mysql/mytop/
Calculating Real Memory Usage
Calculating Real Memory Usage
http://perl.apache.org/docs/1.0/guide/performance.html#Calculating_Real_Memory_Usage
GTop module
Perl interface to libgtop.
http://search.cpan.org/search?query=GTop
System Variables
MySQL System Variables
http://dev.mysql.com/doc/mysql/en/system-variables.html
Tuning Server Parameters
Tuning MySQL Server Parameters
http://dev.mysql.com/doc/mysql/en/server-parameters.html
This document was last modified on 2005-04-21 09:05:36.
MKDoc Ltd., 31 Psalter Lane, Sheffield, S11 8YL, UK.
Copyright © 2001-2005 MKDoc Ltd.