FreeBSD hardware solution for a database server

[Uzi Klein]

Stuart Cianos wrote:
> Hi Uzi -
> 
> That is a decent configuration for a variety of tasks. What type of 
> speed issues are you seeing: is it limited to a couple of queries? How 
> many transactions are you running in a given time period? Have you 
> optimized the indexes on your tables for your particular tasks and/or 
> operations?

mysql> \s
--------------
mysql  Ver 14.7 Distrib 4.1.13, for portbld-freebsd5.4 (i386) using  4.3

Connection id:          16931
Current database:       *******
Current user:           *******
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.12-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 3 days 2 hours 30 min 38 sec

Threads: 22  Questions: 1070775  Slow queries: 356  Opens: 64745  Flush 
tables: 1  Open tables: 256  Queries per second avg: 3.992
--------------

> 
> If you copy your configuration file and post it to the list (make sure 
> you remove any sensitive info like usernames or passwords, if you store 
> that type of thing in there) we might be able to help you a bit more.

Server is a Proliant DL380 G4 (dual Xeon 3.2, 2 GB ram)

www# uname -v
FreeBSD 5.4-RELEASE-p6 #4: Mon Aug  1 17:26:05 UTC 2005 
mook at server.com:/usr/obj/usr/src/sys/WWW

www# cat /boot/loader.conf
kern.maxdsiz="1073741824"
kern.dfldsiz="1073741824"
kern.maxssiz="1073741824"

from my.cnf :

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8


> If you haven't tuned your config file for your particular configuration, 
> then this can also result in performance not being up to par. Ensure 
> that your kernel is compiled for SMP capability and that your MySQL is 
> compiled with optimization ON for maximum throughput. While the 
> optimization doesn't make a huge difference in the short run, millions 
> of transactions later a couple of miliseconds here and miliseconds there 
> add up to real time.

Kernel is compiled with SMP support

MySQL compiled with:
  WITH_PROC_SCOPE_PTH=yes BUILD_OPTIMIZED=yes BUILD_STATIC=yes

> 
> RAID 0/1 is ideal, although RAID 5 is very sufficient for most all 
> purposes in this case. If we were running Oracle or Sybase, then 
> different RAID configurations suit different storage requirements, i.e. 
> RAID 5 for the table data storage and RAID 0/1 for the transaction logs. 
> There reasons for this get fairly technical, but if you are interested 
> in the reasons behind this you can google the topic. MySQL doesn't have 
> such demanding performance tuning requirements.

That what my original question meant to be:
What are the minimum/recommended system requirements (*hardware* wise)
for a heavy loaded database server.

Thanks, Uzi