FreeBSD MySQL Performance Tunning suggestions???

[Joerg Bruehe]

Hi all !


VeeJay wrote:
> [[...]]
> 
> At my job, I am going to build a Web Server with
> 
>  1. FreeBSD 7.0-RELEASE amd64
>  2. Apache 2.2.8
>  3. PHP 4.4.8 (or may be PHP5, what do you suggest?)
> 
> Server's hardware configuration is as follow:
> 
>  2  x  Quad Core Xeon E5450 3.0GHz,2x6MB,1333FSB
>  16GB (8x2GB Dual Rank DIMMs) 667MHz FBD
>  6  x  450GB SAS 15k 3.5" HD Hot Plug
>  PERC 6/i, Integrated Controller Card x6 backplane
>  PE2950 III C5 MSS R10 Add-in PERC 5/i / 6/i 1 S
>  TCP/IP Offload Engine 2P
>  Broadcom TCP/IP Offload Engine functionality (TOE) Not Enabled
> 
> For FreeBSD 7.0-RELEASE amd64 Which MySQL 5.0 would be used ?
>  1. FreeBSD 7.x (x86_64)
>   or
>  2. FreeBSD 6.x (x86)

Your machine has 16 GB of RAM.

If you ever want to use really large caches in the MySQL server process, 
you need to use a 64 bit binary, called "x86_64" in the MySQL package 
file names.

> 
> I have done some googling and made these configuration files for Apache and
> MySQL?
> 
> Apache:
> httpd.conf-----------------------------------------------------start------------
> [[... file snipped ...]]
> 
> 
> MySQL:
> my.cnf------------------------------------------------------------start--------
> [[... file snipped ...]]
> 
> 
> Is there something you can tune?

You cannot tune without knowing which bottleneck you should widen, and 
how much resources still are available.
IMNSHO, you need to start with some configuration, put load onto it (as 
representative as possible), then watch the system's behavior (take 
measurements !), and only then determine which part you want to improve.

Example: Database caches are good to reduce disk I/O and so to increase 
performance, but you will not increase caches if your system is already 
paging heavily (= your RAM is too small).

There is no need to change any parameter unless
a) its current setting restricts your performance, and you have
    sufficient resources to raise the limit,   or
b) its current setting allocates more resources than needed here,
    which could be used better at some other place.

You need some initial run and measurement to check that.


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg at mysql.com
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028