FreeBSD hardware solution for a database server
justin.bastedo at gmail.com
Thu Sep 29 12:25:44 PDT 2005
>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:
> 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.
> Lanny Baron
> 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
> To unsubscribe, send any mail to "freebsd-database-unsubscribe at freebsd.org"
Justin Bastedo At Gmail Dot Com
-----BEGIN PGP SIGNATURE-----
-----END PGP SIGNATURE-----
More information about the freebsd-hardware