Date: Sun, 21 Aug 2005 15:22:06 +0200 From: Uzi Klein <uzi@bmby.com> To: Uzi Klein <uzi@bmby.com> Cc: freebsd-database@freebsd.org, freebsd-hardware@freebsd.org Subject: Re: FreeBSD hardware solution for a database server Message-ID: <43087FFE.9070401@bmby.com> In-Reply-To: <4308771D.8070502@bmby.co.il> References: <43086205.8060307@bmby.com> <4308771D.8070502@bmby.co.il>
next in thread | previous in thread | raw e-mail | index | archive | help
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@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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?43087FFE.9070401>