Running large DB's on FreeBSD

[Claus Guttesen]

>     I am in the process of implementing a fairly large mysql server for
> an even larger company, and naturally i want to use FreeBSD. The
> hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM,  7 x 15k
> rpm disks in a RAID5 setup. I'm not exactly informed as to the specific
> workload yet, however i know the database will have several million rows
> and be larger than 10GB.
>
>     So, first of all, am i crazy for choosing fbsd+mysql for this rather
> than something like Solaris + Oracle? :) Secondly, i am just looking for
> some suggestions, opinions, success/failure story's that may help me
> out. Is anyone out there using FreeBSD for something of this size? I am
> hoping that everything will work out well, and the client will be happy.
> This would generate some good PR for FreeBSD, as it is a very large
> international company and it would be the first FreeBSD server (that i
> know of) of this type there.

I'm managing a 28 GB postgresql (7.4.9) database running on FreeBSD
6.0 (release). The server is a quad-core opteron with 8 GB ram. The
database has many smaller tables and one large with 47+ million
entries and the activity is mainly inserts.

The most important settings I tweaked was:

shared_buffers = 32768
vacuum_mem = 262144
max_fsm_pages = 1250000
max_fsm_relations = 1000
effective_cache_size = 65536
random_page_cost = 2

These settings are for pg 7.4. If you go for postgresql you want 8.1.
If you go for FreeBSD remembere to change these settings in the
kernel:

options         SHMMAXPGS=393216
options         SEMMNI=240
options         SEMMNS=1440
options         SEMUME=240
options         SEMMNU=720

The command 'ipcs -ma' on FreeBSD will tell you SEGSZ (size in bytes)
of the shared memory postgres is using. Our's is 299573248 bytes and
you can adjust shared_buffers according to this. I found the
information at http://www.varlena.com/GeneralBits/Tidbits/perf.html.

regards
Claus