From owner-freebsd-database@FreeBSD.ORG Thu Sep 29 14:04:42 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 60E1416A420 for ; Thu, 29 Sep 2005 14:04:42 +0000 (GMT) (envelope-from LBaron@FreeBSDsystems.COM) Received: from mx1.freebsdsystems.com (mx1.FreeBSDsystems.COM [69.90.68.2]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9DFD243D48 for ; Thu, 29 Sep 2005 14:04:40 +0000 (GMT) (envelope-from LBaron@FreeBSDsystems.COM) Received: (qmail 79031 invoked by uid 89); 29 Sep 2005 14:04:38 -0000 Received: from unknown (HELO ?192.168.0.5?) (lbaron@freebsdsystems.com@216.235.9.82) by mx1.freebsdsystems.com with (DHE-RSA-AES256-SHA encrypted) SMTP; 29 Sep 2005 14:04:38 -0000 Message-ID: <433BF472.6030300@FreeBSDsystems.COM> Date: Thu, 29 Sep 2005 10:04:34 -0400 From: Lanny Baron Organization: FreeBSD Systems / Freedom Technologies Corporation User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050831) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Uzi Klein References: <43086205.8060307@bmby.com> <4308771D.8070502@bmby.co.il> <43087FFE.9070401@bmby.com> In-Reply-To: <43087FFE.9070401@bmby.com> 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-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 29 Sep 2005 14:04:42 -0000 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@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@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-hardware > To unsubscribe, send any mail to "freebsd-hardware-unsubscribe@freebsd.org" From owner-freebsd-database@FreeBSD.ORG Thu Sep 29 19:25:44 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 1151516A41F for ; Thu, 29 Sep 2005 19:25:44 +0000 (GMT) (envelope-from justin.bastedo@gmail.com) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.207]) by mx1.FreeBSD.org (Postfix) with ESMTP id 29D7D43D4C for ; Thu, 29 Sep 2005 19:25:43 +0000 (GMT) (envelope-from justin.bastedo@gmail.com) Received: by xproxy.gmail.com with SMTP id t4so115939wxc for ; Thu, 29 Sep 2005 12:25:42 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=uPCISHGBEQJsyHuOBL+qX0dlTEnunyD//kFCLBcBvoTzcNYpPCUGtiiKlJ9mRmk4Qo0JueXJENPDErIGjagNoiCZGyUR6z/D9aLnqk9s8+KK1n/+uHBoS2A9Ncd2Gl2nEvE3zjuz5jc5HzHEPqH4M/ojS+tfWol9ThqTMSOyNPo= Received: by 10.70.12.20 with SMTP id 20mr642869wxl; Thu, 29 Sep 2005 12:25:42 -0700 (PDT) Received: by 10.70.35.7 with HTTP; Thu, 29 Sep 2005 12:25:42 -0700 (PDT) Message-ID: <8a5255240509291225282f3bae@mail.gmail.com> Date: Thu, 29 Sep 2005 12:25:42 -0700 From: Justin Bastedo To: Lanny Baron In-Reply-To: <433BF472.6030300@FreeBSDsystems.COM> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <43086205.8060307@bmby.com> <4308771D.8070502@bmby.co.il> <43087FFE.9070401@bmby.com> <433BF472.6030300@FreeBSDsystems.COM> Cc: freebsd-database@freebsd.org, Uzi Klein , freebsd-hardware@freebsd.org Subject: Re: FreeBSD hardware solution for a database server X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list Reply-To: Justin Bastedo List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 29 Sep 2005 19:25:44 -0000 >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 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, > +=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+= =3D+=3D+=3D+=3D > Lanny Baron > http://www.FreeBSDsystems.COM > Fine Quality High Performance Rackmount > Servers and RAID Storage Systems > Toll Free: 1.877.963.1900 > +=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+=3D+= =3D+=3D+=3D+=3D > > > > 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@server.com:/usr/obj/usr/src/sys/WWW > > > > www# cat /boot/loader.conf > > kern.maxdsiz=3D"1073741824" > > kern.dfldsiz=3D"1073741824" > > kern.maxssiz=3D"1073741824" > > > > from my.cnf : > > > > # The MySQL server > > [mysqld] > > port =3D 3306 > > socket =3D /tmp/mysql.sock > > skip-locking > > key_buffer =3D 256M > > max_allowed_packet =3D 1M > > table_cache =3D 256 > > sort_buffer_size =3D 1M > > read_buffer_size =3D 1M > > read_rnd_buffer_size =3D 4M > > myisam_sort_buffer_size =3D 64M > > thread_cache =3D 8 > > query_cache_size=3D 16M > > # Try number of CPU's*2 for thread_concurrency > > thread_concurrency =3D 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=3Dyes BUILD_OPTIMIZED=3Dyes BUILD_STATIC=3Dyes > > > >> > >> 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 requiremen= ts. > > > > > > 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@freebsd.org mailing list > > http://lists.freebsd.org/mailman/listinfo/freebsd-hardware > > To unsubscribe, send any mail to "freebsd-hardware-unsubscribe@freebsd.= org" > _______________________________________________ > freebsd-database@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-database > To unsubscribe, send any mail to "freebsd-database-unsubscribe@freebsd.or= g" > -- Justin Bastedo At Gmail Dot Com -------------------------------------------------- http://www.thebastedo.com -----BEGIN PGP SIGNATURE----- iQA/AwUBQWxjbLk9v2UZrS+uEQI2FQCdFYEhvXMrEIqzru+mspfxLLyutH8Ani4OwJ6946W6QMk= cHjPr4dAau6kq=3DG2aD -----END PGP SIGNATURE-----