Date: Thu, 29 Sep 2005 12:25:42 -0700 From: Justin Bastedo <justin.bastedo@gmail.com> To: Lanny Baron <LBaron@freebsdsystems.com> Cc: freebsd-database@freebsd.org, Uzi Klein <uzi@bmby.com>, freebsd-hardware@freebsd.org Subject: Re: FreeBSD hardware solution for a database server Message-ID: <8a5255240509291225282f3bae@mail.gmail.com> In-Reply-To: <433BF472.6030300@FreeBSDsystems.COM> References: <43086205.8060307@bmby.com> <4308771D.8070502@bmby.co.il> <43087FFE.9070401@bmby.com> <433BF472.6030300@FreeBSDsystems.COM>
next in thread | previous in thread | raw e-mail | index | archive | help
>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@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, > +=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-----
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?8a5255240509291225282f3bae>