From owner-freebsd-hardware@FreeBSD.ORG Sun Aug 21 12:23:24 2005 Return-Path: X-Original-To: freebsd-hardware@freebsd.org Delivered-To: freebsd-hardware@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 7915E16A41F; Sun, 21 Aug 2005 12:23:24 +0000 (GMT) (envelope-from uzi@bmby.com) Received: from dev.bmby.co.il (l192-114-46-204.broadband.actcom.net.il [192.114.46.204]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6B8C243D46; Sun, 21 Aug 2005 12:23:23 +0000 (GMT) (envelope-from uzi@bmby.com) Received: from [192.168.0.2] ([192.168.0.2]) by dev.bmby.co.il (8.12.9/8.12.9) with ESMTP id j7LCNRPS018782; Sun, 21 Aug 2005 15:23:27 +0300 Message-ID: <43087FFE.9070401@bmby.com> Date: Sun, 21 Aug 2005 15:22:06 +0200 From: Uzi Klein Organization: B.M.B.Y Software Systems Ltd. User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Uzi Klein References: <43086205.8060307@bmby.com> <4308771D.8070502@bmby.co.il> In-Reply-To: <4308771D.8070502@bmby.co.il> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cc: freebsd-database@freebsd.org, freebsd-hardware@freebsd.org Subject: Re: FreeBSD hardware solution for a database server X-BeenThere: freebsd-hardware@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: General discussion of FreeBSD hardware List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 21 Aug 2005 12:23:24 -0000 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