FreeBSD hardware solution for a database server

[Justin Bastedo]

>From my experience you may want to take a look and kick on slow query
logging and see about fixing those slow queries, those can kill your
db server. 2nd 64bit processors, they make a huge difference.

mysql  Ver 14.12 Distrib 5.0.12-beta, for portbld-freebsd5.4 (amd64) using  4.3
Uptime:                 2 days 9 hours 55 min 40 sec

Threads: 2  Questions: 420121  Slow queries: 0  Opens: 0  Flush
tables: 16  Open tables: 53  Queries per second avg: 2.015

That server runs binary logging, and replication and handles the load
nicely, i still have tons of system resources free for load spikes.



On 9/29/05, Lanny Baron <LBaron at freebsdsystems.com> wrote:
> Uzi,
>
> What you may want to do is have an external RAID system attached with
> many smaller hard drives, and run in a RAID-10 for better performance.
> You should be using a PCI-Express RAID Controller to attach that
> external RAID.
>
> If you do the above, make sure you add one or two hot-spare drives.
> Regards,
> +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
> Lanny Baron
> http://www.FreeBSDsystems.COM
> Fine Quality High Performance Rackmount
> Servers and RAID Storage Systems
> Toll Free: 1.877.963.1900
> +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
>
>
>
> Uzi Klein wrote:
> > 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
> > _______________________________________________
> > freebsd-hardware at freebsd.org mailing list
> > http://lists.freebsd.org/mailman/listinfo/freebsd-hardware
> > To unsubscribe, send any mail to "freebsd-hardware-unsubscribe at freebsd.org"
> _______________________________________________
> freebsd-database at freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-database
> To unsubscribe, send any mail to "freebsd-database-unsubscribe at freebsd.org"
>


--
Justin Bastedo At Gmail Dot Com
--------------------------------------------------
http://www.thebastedo.com

-----BEGIN PGP SIGNATURE-----
iQA/AwUBQWxjbLk9v2UZrS+uEQI2FQCdFYEhvXMrEIqzru+mspfxLLyutH8Ani4OwJ6946W6QMkcHjPr4dAau6kq=G2aD
-----END PGP SIGNATURE-----