Greetings,
I am in the process of implementing a fairly large mysql server for
an even larger company, and naturally i want to use FreeBSD. The
hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k
rpm disks in a RAID5 setup. I'm not exactly informed as to the specific
workload yet, however i know the database will have several million rows
and be larger than 10GB.
So, first of all, am i crazy for choosing fbsd+mysql for this rather
than something like Solaris + Oracle? :) Secondly, i am just looking for
some suggestions, opinions, success/failure story's that may help me
out. Is anyone out there using FreeBSD for something of this size? I am
hoping that everything will work out well, and the client will be happy.
This would generate some good PR for FreeBSD, as it is a very large
international company and it would be the first FreeBSD server (that i
know of) of this type there.
Thanks, any input will be appreciated.
Mike Jakubik <mikej@rogers.com> wrote:> > I am in the process of implementing a fairly large mysql server for > an even larger company, and naturally i want to use FreeBSD. The > hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k > rpm disks in a RAID5 setup.Generally speaking, RAID 5 is known for lousy performance in database loads. Consider using RAID 10.> So, first of all, am i crazy for choosing fbsd+mysql for this rather > than something like Solaris + Oracle? :)Well, you should be using FreeBSD+PostgreSQL, but that's just my religion.> Secondly, i am just looking for > some suggestions, opinions, success/failure story's that may help me > out. Is anyone out there using FreeBSD for something of this size? I am > hoping that everything will work out well, and the client will be happy. > This would generate some good PR for FreeBSD, as it is a very large > international company and it would be the first FreeBSD server (that i > know of) of this type there.Yes, this is being done. I would suggest surfing the Postgresql performance mailing list archives a bit. There are often discussions of huge databases there: http://archives.postgresql.org/pgsql-performance/ -- Bill Moran The presence of stale files in this directory can cause the dreaded unpredictable results, and therefore it is highly recommended that you delete them.
On Oct 23, 2006, at 4:01 PM, Mike Jakubik wrote:> I am in the process of implementing a fairly large mysql server > for an even larger company, and naturally i want to use FreeBSD. > The hardware will be an HP DL385, 2 x dual-core Opterons, 16GB > RAM, 7 x 15k rpm disks in a RAID5 setup. I'm not exactly informed > as to the specific workload yet, however i know the database will > have several million rows and be larger than 10GB. > > So, first of all, am i crazy for choosing fbsd+mysql for this > rather than something like Solaris + Oracle? :)Moderately...it kinda depends on the budget available. I regard Solaris + Oracle as one of the most reliable combinations for moderate to extreme load, for a system that might well be in operation for five to ten years. If I was going to do FreeBSD, I might look into Postgres instead of MySQL; well, I might look into something else than MySQL under many circumstances. I've gotten some pretty good use out of OpenBase, for another choice. As for the disk configuration, using RAID-5 is one of the worst possible choices for a database; using multiple RAID-1 mirrors or a RAID-10 config would probably do a lot better in terms of performance and reliability. -- -Chuck
On Tue, 24 Oct 2006 01:01:38 +0200, Mike Jakubik <mikej@rogers.com> wrote:> Greetings, > > I am in the process of implementing a fairly large mysql server for > an even larger company, and naturally i want to use FreeBSD. The > hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k > rpm disks in a RAID5 setup. I'm not exactly informed as to the specific > workload yet, however i know the database will have several million rows > and be larger than 10GB. > > So, first of all, am i crazy for choosing fbsd+mysql for this rather > than something like Solaris + Oracle? :) Secondly, i am just looking for > some suggestions, opinions, success/failure story's that may help me > out. Is anyone out there using FreeBSD for something of this size? I am > hoping that everything will work out well, and the client will be happy. > This would generate some good PR for FreeBSD, as it is a very large > international company and it would be the first FreeBSD server (that i > know of) of this type there. > > Thanks, any input will be appreciated.I'm running MySQL 5 on Linux at my work (4 disk RAID 10/32G RAM/4xsingle-core). It has a DB of > 100 GB and much more than millions of rows and the preformance is very good with quite a lot of users via the webserver. But I do not have any comparisons with Oracle or other systems. Unforunately we don't run FreeBSD at work except for my workstation. The size of the db is not the problem. The load is more important. Are there a lot of sequential queries or simultanious? Do you do a lot of locking/selects/updates/insert? Are there thousands of tables or just 1? I do recommend a 64 bit OS if your hardware supports it, because it makes allocating memory for MySQL a lot easier. Ronald. -- Ronald Klop Amsterdam, The Netherlands
> I am in the process of implementing a fairly large mysql server for > an even larger company, and naturally i want to use FreeBSD. The > hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k > rpm disks in a RAID5 setup. I'm not exactly informed as to the specific > workload yet, however i know the database will have several million rows > and be larger than 10GB. > > So, first of all, am i crazy for choosing fbsd+mysql for this rather > than something like Solaris + Oracle? :) Secondly, i am just looking for > some suggestions, opinions, success/failure story's that may help me > out. Is anyone out there using FreeBSD for something of this size? I am > hoping that everything will work out well, and the client will be happy. > This would generate some good PR for FreeBSD, as it is a very large > international company and it would be the first FreeBSD server (that i > know of) of this type there.I'm managing a 28 GB postgresql (7.4.9) database running on FreeBSD 6.0 (release). The server is a quad-core opteron with 8 GB ram. The database has many smaller tables and one large with 47+ million entries and the activity is mainly inserts. The most important settings I tweaked was: shared_buffers = 32768 vacuum_mem = 262144 max_fsm_pages = 1250000 max_fsm_relations = 1000 effective_cache_size = 65536 random_page_cost = 2 These settings are for pg 7.4. If you go for postgresql you want 8.1. If you go for FreeBSD remembere to change these settings in the kernel: options SHMMAXPGS=393216 options SEMMNI=240 options SEMMNS=1440 options SEMUME=240 options SEMMNU=720 The command 'ipcs -ma' on FreeBSD will tell you SEGSZ (size in bytes) of the shared memory postgres is using. Our's is 299573248 bytes and you can adjust shared_buffers according to this. I found the information at http://www.varlena.com/GeneralBits/Tidbits/perf.html. regards Claus
[ Replying offlist as pretty much all my points have already been said by others, so I'm just trying to help reinforce what others have said ] On Mon, 2006-10-23 at 19:01 -0400, Mike Jakubik wrote:> I am in the process of implementing a fairly large mysql server for > an even larger company, and naturally i want to use FreeBSD. The > hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k > rpm disks in a RAID5 setup. I'm not exactly informed as to the specific > workload yet, however i know the database will have several million rows > and be larger than 10GB.Go for RAID10 rather than RAID5 - in my testing with an LSI LSI MegaRAID SCSI 320-2 RAID card, this gave roughly 4-5x speed benefit over RAID5, on the FreeBSD-based database mentioned below. You'd also want to use a 64 bit operating system on that server, otherwise it will perform far worse than expected.> So, first of all, am i crazy for choosing fbsd+mysql for this rather > than something like Solaris + Oracle? :)I would suggest either FreeBSD/PostgreSQL or Solaris/Oracle. I have several databases in excess of 30 gig on each and both perform well once tuned. The largest PostgreSQL database I set up tops 100G and grows relatively steadily. I've been very impressed with PostgreSQL, and may well be replacing some of my Oracle licenses with it in the future. You don't say what data you will be storing, but this may also affect the decision. Do you need to easily do full-text searches? Are you storing spacial/geographic (GIS) data? Think about backing up the data too - can the database be taken down for cold backups or must it be 100% available? And can you see a need for multiple servers in the future for redundancy? The answers to these questions may influence your choice more than anything else. It is probably worth your time testing the three options - I believe Oracle and Solaris can both be downloaded freely for evaluation purposes. Write a few small scripts to basically hammer the database with selects, inserts and deletes, and see which performs best. Pull the power on the server a few times and see which recovers best. I suspect you may go off MySQL. After having being burned several times with MySQL (data loss, unexplained slowdowns and general lack of scalability with growing database sizes), I must be honest and say I haven't tried it for a while, though I also have no desire to as PostgreSQL has never let me down yet. Summary: FreeBSD is a great platform for this, but I would seriously reconsider RAID5 and MySQL. No matter which option you choose, be prepared to put considerable effort into tuning the OS and database. Hope that helps, Gavin
Mike Jakubik wrote:
> I am in the process of implementing a fairly large mysql server for
> an even larger company, and naturally i want to use FreeBSD. The
> hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k
> rpm disks in a RAID5 setup. I'm not exactly informed as to the
specific
> workload yet, however i know the database will have several million rows
> and be larger than 10GB.
As others have noted, RAID5 is worst-case for databases.
For both reliability and performance I recommend RAID1 or
RAID10.
> So, first of all, am i crazy for choosing fbsd+mysql for this rather
> than something like Solaris + Oracle? :)
My recommendation is FreeBSD + PostgreSQL. I've worked
quite a lot with various Mysql and PostgreSQL databases
in the past, and the latter outperforms other combinations
in general. Also it is my impression that PostgreSQL is
much more reliable and resistant against evil things like
crashes (power failure or whatever), thanks to its WAL
data storage which is similar to a journaled file system
(transaction-aware, of course).
A few years ago (2002) I fed the German phone book into a
PostgreSQL database running on a Pentium-III 800 MHz with
256 MB RAM (FreeBSD 4-stable). I was just curious how well
it would cpe with that. Importing the 35.6 million rows
and creating an index took 40 Minutes (I think I didn't
even use the optimized COPY instruction, so it could have
been even faster). Select commands on the table were
processed surprisingly fast, but I didn't really hammer
on it because the machine was running a production Apache
at the same time. :-)
Best regards
Oliver
--
Oliver Fromme, secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing
Dienstleistungen mit Schwerpunkt FreeBSD: http://www.secnetix.de/bsd
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.
"If you aim the gun at your foot and pull the trigger, it's
UNIX's job to ensure reliable delivery of the bullet to
where you aimed the gun (in this case, Mr. Foot)."
-- Terry Lambert, FreeBSD-hackers mailing list.
On Mon, Oct 23, 2006, Mike Jakubik wrote:> I am in the process of implementing a fairly large mysql server for > an even larger company, and naturally i want to use FreeBSD. The > hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k > rpm disks in a RAID5 setup. I'm not exactly informed as to the specific > workload yet, however i know the database will have several million rows > and be larger than 10GB.Again, don't use RAID 5 for databases. Distribute your data over a bunch of table spaces on RAID 1/10 volumes. Furthermore ensure that your external storge can cope with high concurrent disk I/O. Speaking of HP we had very poor results with the cheaper storage boxes like MSA1000/MSA1500. It seems that their I/O processors can saturated very easily under concurrent I/O. If you stick to HP, choose an EVA for performance reasons. Later you can easily improve speed by adding additional disks. If you have big tables you may want to use one of the newer PostgreSQL features, Partitioning and Constraint Exclusion. See [1] for details. Disabling setproctitle() gains additional performance points, too. [1] http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION -cs
On Oct 23, 2006, at 7:01 PM, Mike Jakubik wrote:> So, first of all, am i crazy for choosing fbsd+mysql for this > rather than something like Solaris + Oracle? :) Secondly, i am just > looking for some suggestions, opinions, success/failure story's > that may help me out. Is anyone out there using FreeBSD for > something of this size? I am hoping that everythingFirst thing, choosing mysql for anything truly relational is a bit crazy... but your choice of FreeBSD is very sound, and your hardware seems reasonable, except RAID5 is generally not the best choice for a heavily written-to database. You can't really compare mysql to oracle at all, and mentioning them in the same breath sounds funny to me... Take a good look at postgres. In the end, it really depends on your workload and how much truly relational qork you're expecting the DB to do. If all you're using the DB for is a file store, you might as well use mysql, but then you have to worry about all of your data integrity in your application. Personally, I prefer to put that burden on the DB engine. The size of your DB is not all that large. There are people running terabyte DB's under postgres. Our big DB is around 60Gb with hundreds of millions of rows spread across dozens of tables which are regularly joined with each other for reports. It is pounded on 24x7 with lots and lots of inserts, updates, and selects going on all the time.
On Monday 23 October 2006 6:01 pm, Mike Jakubik wrote:> I'm not exactly informed as to the specific workload yet, however i know > the database will have several million rows and be larger than 10GB.No offense, but that's a pretty small database. Also, IMHO the crazy part is using MySQL over PostgreSQL. It's hardly any faster, and you have to do a *lot* of client-side work to emulate PostgreSQL's built-in functions. Unless you use the much slower InnoDB that's now owned by Oracle, in which case MySQL gets most of those features (but is no faster than PostgreSQL would be). -- Kirk Strauser -------------- next part -------------- A non-text attachment was scrubbed... Name: not available Type: application/pgp-signature Size: 155 bytes Desc: not available Url : http://lists.freebsd.org/pipermail/freebsd-stable/attachments/20061025/719ff540/attachment.pgp
On Mon, Oct 23, 2006 at 07:01:38PM -0400, Mike Jakubik wrote:> Greetings, > > I am in the process of implementing a fairly large mysql server for > an even larger company, and naturally i want to use FreeBSD. The > hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k > rpm disks in a RAID5 setup. I'm not exactly informed as to the specific > workload yet, however i know the database will have several million rows > and be larger than 10GB. > > So, first of all, am i crazy for choosing fbsd+mysql for this rather > than something like Solaris + Oracle? :) Secondly, i am just looking for > some suggestions, opinions, success/failure story's that may help me > out. Is anyone out there using FreeBSD for something of this size? I am > hoping that everything will work out well, and the client will be happy. > This would generate some good PR for FreeBSD, as it is a very large > international company and it would be the first FreeBSD server (that i > know of) of this type there. > > Thanks, any input will be appreciated.I ran a database of this general size on FreeBSD + Postgresql in 1998! Definitely not crazy to do this, but I'd run Raid10 instead of 5, as performance will be much better. I would not use mysql for this - but that's my personal preference coming through here. I believe Postgresql has far better data integrity in a corporate-style environment. -- -- Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist http://www.denninger.net My home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://genesis3.blogspot.com Musings Of A Sentient Mind