Re: Running large DB's on FreeBSD
On Nov 10, 2006, at 6:35 PM, Nguyen Tam Chinh wrote: For the pg configuration, I use this on a 4Gb box: max_connections = 100 shared_buffers = 7 # min 16 or max_connections*2, 8KB each work_mem = 262144 # min 64, size in KB Thank you very much. And how did you set the semaphore's parameters? Do you have any trick or experience? I just think it's just weird to inceremently increase ipc.shm* and ipc.sem* to get the right values. The documentation of PostGreSQL gives us some examples but without explanation how they found those values. The SEM parameters are the bare minimum. Pg uses a small number of semaphores, so unless you have a bazillion connections allowed, just use these settings: kern.ipc.semmsl=512 kern.ipc.semmap=256 kern.ipc.semmni=32 kern.ipc.semmns=512 Now, for the SHM usage, it is just arithmetic. You now how many buffers you're asking for, you know how big they are, and you just need to add some for overhead and you've got your number. If you want Pg to compute it for you, just read the error log when you fire up Pg with a small shm setting in the OS.
Re: Running large DB's on FreeBSD
On Oct 24, 2006, at 7:27 AM, Vivek Khera wrote: I believe the front-end application is MySQL dependent, but what is so much better about PostgreSQL? I understand that it has some more advanced features, but if they are not used, then what is the advantage? (I really like the InnooDB storage in MySQL) So after billions and billions of inserts/updates/deletes, how do you reclaim all that lost space in innodb? dump + reload is what I hear. If you've configured it properly you can just do optimize table foo. also, do you value your data? ie, if you insert data which cannot be stored should the DB silently alter it or should it throw back an error for your application to decide what to do? guess which DB does which... Not MySQL if you configure/ask it not to. - ask -- http://www.askbjoernhansen.com/ ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Tue, 24 Oct 2006, Vivek Khera wrote: On Oct 24, 2006, at 12:27 PM, Nguyen Tam Chinh wrote: 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. Could you share with us your servers' hardware specifics and configuration (tuning) of PostgreSQL? This would help many in making decision. My current favorites are the SunFire X4100 from Sun with an Adaptec 2230SLP dual channel U320 RAID card and a 14+ disk array. These are incredibly stable. The disk arrays I have right now are from Dell, and I would not recommend them. I don't think they're totally U320 compliant as some drives occasionally come up at U160 speed. The Adaptec card is the *only* dual channel U320 SCSI card availble in low-profile size; unfortunately LSI doesn't make a low-profile version of the 320-2X card... I have one box with 4Gb which is good for our smaller databases, and one which we are upgrading from 4Gb to 8Gb next week due to the high load it has. I use 1 disk from each SCSI channel to make a mirrored RAID volume for boot + OS + postgres transaction log, and the remaining disks in RAID10 with the disks on each mirror pair coming from opposite SCSI channels. For the pg configuration, I use this on a 4Gb box: max_connections = 100 shared_buffers = 7 # min 16 or max_connections*2, 8KB each work_mem = 262144 # min 64, size in KB maintenance_work_mem = 524288 # min 1024, size in KB max_fsm_pages = 180 # min max_fsm_relations*16, 6 bytes each vacuum_cost_delay = 25 # 0-1000 milliseconds checkpoint_segments = 256 checkpoint_timeout = 900 effective_cache_size = 27462# `sysctl -n vfs.hibufspace` / 8192 (BLKSZ) random_page_cost = 2 log_min_error_statement = error Thank you very much. And how did you set the semaphore's parameters? Do you have any trick or experience? I just think it's just weird to inceremently increase ipc.shm* and ipc.sem* to get the right values. The documentation of PostGreSQL gives us some examples but without explanation how they found those values. - With best regards, |The Power to Serve Nguyen Tam Chinh| http://www.FreeBSD.org Loc: sp.cs.msu.su | ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
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 ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://genesis3.blogspot.comMusings Of A Sentient Mind ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
RE: Running large DB's on FreeBSD
-Original Message- From: [EMAIL PROTECTED] [mailto:owner-freebsd- [EMAIL PROTECTED] On Behalf Of Jim C. Nasby Sent: Friday, October 27, 2006 12:44 AM To: David Magda Cc: Mike Jakubik; [EMAIL PROTECTED] Subject: Re: Running large DB's on FreeBSD On Mon, Oct 23, 2006 at 08:15:04PM -0400, David Magda wrote: As for Postgres on FreeBSD, FlighAware seems to be using it some some decent amount of data: . Receiving the data and processing it puts them about 6 minutes behind real time . Generating one map can be done in about 160 milliseconds of CPU time . Capable of generating several million maps a day . About 1 TB of stored data . Approximately 40 million position updates on air craft per day http://joseph.randomnetworks.com/archives/2006/05/12/flightaware- freebsd-and-postgresql/ And that's on a dual opteron with 12G of memory and a run of the mill RAID10 (for the database that is). Yes.. but how many disks (size/type/rpm?) are in that RAID 10? I'm guessing it's an external enclosure... Also, I know 10k rpm vs 15 doesn't make much of a difference for sequential, but random IO seems to be significantly improved. Granted, it's not as dramatic as adding more spindles... I think the other point that may be relevant is the active section of the data that you're accessing, and how good your design is in terms of being able to access that directly. You could have a 1TB database, but only have a portion that is frequently accessed/updated. In that case, you might just need lots of storage, which is fairly inexpensive these days. Also, your money might be better spent on more RAM- if you can fit most of the active data in memory, that will also have a positive impact on performance. As pointed out, 10GB isn't really that much, especially when you can buy relatively inexpensive servers with 8 or 16 GB of ram. Fitting over half your db in memory is quit a luxury. - Bucky ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Fri, Oct 27, 2006 at 10:06:30AM -0400, Bucky Jordan wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:owner-freebsd- [EMAIL PROTECTED] On Behalf Of Jim C. Nasby Sent: Friday, October 27, 2006 12:44 AM To: David Magda Cc: Mike Jakubik; [EMAIL PROTECTED] Subject: Re: Running large DB's on FreeBSD On Mon, Oct 23, 2006 at 08:15:04PM -0400, David Magda wrote: As for Postgres on FreeBSD, FlighAware seems to be using it some some decent amount of data: . Receiving the data and processing it puts them about 6 minutes behind real time . Generating one map can be done in about 160 milliseconds of CPU time . Capable of generating several million maps a day . About 1 TB of stored data . Approximately 40 million position updates on air craft per day http://joseph.randomnetworks.com/archives/2006/05/12/flightaware- freebsd-and-postgresql/ And that's on a dual opteron with 12G of memory and a run of the mill RAID10 (for the database that is). Yes.. but how many disks (size/type/rpm?) are in that RAID 10? I'm guessing it's an external enclosure... Also, I know 10k rpm vs 15 doesn't make much of a difference for sequential, but random IO seems to be significantly improved. Granted, it's not as dramatic as adding more spindles... IIRC it's a 6 drive array of SATA. Nothing all that fancy. I think the other point that may be relevant is the active section of the data that you're accessing, and how good your design is in terms of being able to access that directly. You could have a 1TB database, but only have a portion that is frequently accessed/updated. In that case, you might just need lots of storage, which is fairly inexpensive these days. Also, your money might be better spent on more RAM- if you can fit most of the active data in memory, that will also have a positive impact on performance. As pointed out, 10GB isn't really that much, especially when you can buy relatively inexpensive servers with 8 or 16 GB of ram. Fitting over half your db in memory is quit a luxury. Well, what's most important is your system architecture. If you have a poor design to start with, you'll never get good performance out of it. -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Mon, Oct 23, 2006 at 08:15:04PM -0400, David Magda wrote: As for Postgres on FreeBSD, FlighAware seems to be using it some some decent amount of data: . Receiving the data and processing it puts them about 6 minutes behind real time . Generating one map can be done in about 160 milliseconds of CPU time . Capable of generating several million maps a day . About 1 TB of stored data . Approximately 40 million position updates on air craft per day http://joseph.randomnetworks.com/archives/2006/05/12/flightaware- freebsd-and-postgresql/ And that's on a dual opteron with 12G of memory and a run of the mill RAID10 (for the database that is). -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Tue, 24 Oct 2006, Patrick M. Hausen wrote: Hello! On Mon, Oct 23, 2006 at 04:10:19PM -0700, Chuck Swiger wrote: 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. I second that. And I just want to add: you can easily and reliably run this configuration in software by the means of gmirror and gstripe. Saves you some $$ that you would have spent on a hardware RAID controller. And it's neither slower nor less reliable. Actually, one with a battery backed cached can be faster, especially for databases. PostgreSQL (and any database worth using) will fsync its WAL at the end of every transaction. Without a battery backed cache this will involve at least one write to disk (and, I believe, usually at least one disk rotation, so your disk rotation speed can limit your transaction rate). With a battery backed cache the write will go straight to the cache and there's no need to wait for the disk. It's also easier to get away with putting your WAL and data on one RAID 10 array. Without a battery backed cache having a separate RAID 1 array for WAL can help, because the WAL is written sequentially and having random access mixed in makes that perform badly. A 10GB database is very small, though, so IO bandwidth, seek times and the number of spindles available for reading data may not matter very much. You may also want to worry about being able to switch the array from one computer to another following a server hardware failure. Some of the fancier setups (like SANs) may even let you do this remotely (if rather expensively, and not necessary with high IO bandwidth). ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
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 pgpM59fWmS0aK.pgp Description: PGP signature
RE: Running large DB's on FreeBSD
You may find this article interesting: http://tweakers.net/reviews/638/4 The whole MySQL vs. PostgreSQL discussion can be very lively and interesting, however I sort of doubt the bsd-stable list is the place. You might want to ask specific questions on the relevant MySQL and PostgreSQL discussion lists to assist you with your decision. I've been a long time user of Postgres, and having to use MySQL on a recent project just reaffirmed that preference, but that's just my 2 cents. - Bucky -Original Message- From: [EMAIL PROTECTED] [mailto:owner-freebsd- [EMAIL PROTECTED] On Behalf Of Kirk Strauser Sent: Wednesday, October 25, 2006 2:00 PM To: freebsd-stable@freebsd.org Subject: Re: Running large DB's on FreeBSD 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 ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Oct 24, 2006, at 1:09, Bill Moran wrote: Well, you should be using FreeBSD+PostgreSQL, but that's just my religion. Is it religion when it just makes more sense? But I digress. There are numerous reasons to prefer PostgreSQL over MySQL, a few of which are: - It scales well to multiple CPUs (almost linear, provided your connections are under sufficient load). I've seen benchmarks like this from a 16 CPU Altix (SGI). - It can do complex queries, and it does them well (I've seen it outperform MySQL regularly - especially where MySQL couldn't perform the query directly). - Data integrity is very important to the PostgreSQL community, so it doesn't ignore errors or truncate your data or things like that (MySQL does). - It has a great community; the people on the mailing lists are very knowledgeable and helpful. You'll usually have a solution for a problem within a day. - AFAIK, the key developers run FreeBSD. One thing; there are a lot of PostgreSQL vs. MySQL comparisons, but they usually fail to tune both databases properly or test with workloads that have been optimized for MySQL. For further questions you really should ask around at the postgresql mailing lists. Regards, -- Alban Hertroys It's not a bug! It's a six-legged feature! !DSPAM:363,453dbe3b7241041496339! ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
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 = 125 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 ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Tue, 2006-Oct-24 02:21:06 +0200, Ronald Klop wrote: On Oct 23, 2006, at 4:44 PM, Mike Jakubik wrote: advanced features, but if they are not used, then what is the advantage? (I really like the InnooDB storage in MySQL) One nice thing about MySQL is the plethora of backends - you can pick the backend to suit the type of data and access methods. Example: writing 1 bit on 1 disk needs to read some info from all disks to recalculate the parity. So this doesn't scale very well. Any sane RAID-5 implementation will regenerate the parity by new_parity = old_parity XOR old_data XOR new_data Though this still turns a single write into 2 reads and 2 writes. Basically: Don't use RAID-5. -- Peter Jeremy pgpWeb9QHY05E.pgp Description: PGP signature
Re: Running large DB's on FreeBSD
[ 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 ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
Hello! On Mon, Oct 23, 2006 at 04:10:19PM -0700, Chuck Swiger wrote: 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. I second that. And I just want to add: you can easily and reliably run this configuration in software by the means of gmirror and gstripe. Saves you some $$ that you would have spent on a hardware RAID controller. And it's neither slower nor less reliable. For anything besides RAID 5, 6 or similar (and you don't want RAID 5 for a transaction heavy system) there is no need for dedicated hardware anymore. Just get a decent SCSI system with a hot plug backplane, of course. HTH, Patrick -- punkt.de GmbH Internet - Dienstleistungen - Beratung Vorholzstr. 25Tel. 0721 9109 -0 Fax: -100 76137 Karlsruhe http://punkt.de ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
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. ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Oct 23, 2006, at 7:44 PM, Mike Jakubik wrote: I believe the front-end application is MySQL dependent, but what is so much better about PostgreSQL? I understand that it has some more advanced features, but if they are not used, then what is the advantage? (I really like the InnooDB storage in MySQL) So after billions and billions of inserts/updates/deletes, how do you reclaim all that lost space in innodb? dump + reload is what I hear. also, do you value your data? ie, if you insert data which cannot be stored should the DB silently alter it or should it throw back an error for your application to decide what to do? guess which DB does which... we can go on forever on this tangent. i'd recommend finding the gotchas pages for both mysql and postgres and decide which is the lesser of evils for your app and go with it. for me, mysql has never won the argument :-)
Re: Running large DB's on FreeBSD
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 ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
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 everything First 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.
Re: Running large DB's on FreeBSD
On Tue, 24 Oct 2006, Vivek Khera wrote: 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 everything 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. Could you share with us your servers' hardware specifics and configuration (tuning) of PostgreSQL? This would help many in making decision. - With best regards, |The Power to Serve Nguyen Tam Chinh| http://www.FreeBSD.org Loc: sp.cs.msu.su | ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Oct 24, 2006, at 12:27 PM, Nguyen Tam Chinh wrote: 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. Could you share with us your servers' hardware specifics and configuration (tuning) of PostgreSQL? This would help many in making decision. My current favorites are the SunFire X4100 from Sun with an Adaptec 2230SLP dual channel U320 RAID card and a 14+ disk array. These are incredibly stable. The disk arrays I have right now are from Dell, and I would not recommend them. I don't think they're totally U320 compliant as some drives occasionally come up at U160 speed. The Adaptec card is the *only* dual channel U320 SCSI card availble in low-profile size; unfortunately LSI doesn't make a low-profile version of the 320-2X card... I have one box with 4Gb which is good for our smaller databases, and one which we are upgrading from 4Gb to 8Gb next week due to the high load it has. I use 1 disk from each SCSI channel to make a mirrored RAID volume for boot + OS + postgres transaction log, and the remaining disks in RAID10 with the disks on each mirror pair coming from opposite SCSI channels. For the pg configuration, I use this on a 4Gb box: max_connections = 100 shared_buffers = 7 # min 16 or max_connections*2, 8KB each work_mem = 262144 # min 64, size in KB maintenance_work_mem = 524288 # min 1024, size in KB max_fsm_pages = 180 # min max_fsm_relations*16, 6 bytes each vacuum_cost_delay = 25 # 0-1000 milliseconds checkpoint_segments = 256 checkpoint_timeout = 900 effective_cache_size = 27462# `sysctl -n vfs.hibufspace` / 8192 (BLKSZ) random_page_cost = 2 log_min_error_statement = error Everything else is default. We run vacuum manually rather than using autovacuum for historical reasons. With upcoming 8.2 release I plan to experiment with higher shared_buffers settings. But for best help, pose your load details and hardware details and query info to the pgsql-performance list. Very smart folk there to help.
Running large DB's on FreeBSD
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. ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
Mike Jakubik [EMAIL PROTECTED] 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. ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
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 ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Tue, 24 Oct 2006 01:01:38 +0200, Mike Jakubik [EMAIL PROTECTED] 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 ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
Chuck Swiger wrote: On Oct 23, 2006, at 4:01 PM, Mike Jakubik wrote: 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. I believe the front-end application is MySQL dependent, but what is so much better about PostgreSQL? I understand that it has some more advanced features, but if they are not used, then what is the advantage? (I really like the InnooDB storage in MySQL) 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. Is RAID5 really that bad when a lot of fast disks are used and the controller has a decent cache with a BBWC? Thanks for the feedback guys. ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
Ronald Klop wrote: 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. Well, i guess i can always fall back on Linux if there are any major problems on FreeBSD. 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. Thats the information i do not have yet. My guess is mostly selects and possibly more than one table. I also believe they will make use of stored procedures. I will have to use AMD64 anyways, as it will be using 16GB of ram. ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Oct 23, 2006, at 4:44 PM, Mike Jakubik wrote: 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. I believe the front-end application is MySQL dependent, but what is so much better about PostgreSQL? I understand that it has some more advanced features, but if they are not used, then what is the advantage? (I really like the InnooDB storage in MySQL) I'm not sure whether avoiding deadlocks and using row-level locking by default qualifies as advanced features, but unless you use InnoDB with MySQL, you don't get that from MySQL. Postgres has been around for a lot longer, and isn't as volatile as MySQL seems to be; also, it avoids some of the needless timer overhead that MySQL seems to enjoy, and the less-accurate-but-much-quicker gettimeofday() under Linux helps MySQL on that platform versus FreeBSD. 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. Is RAID5 really that bad when a lot of fast disks are used and the controller has a decent cache with a BBWC? Thanks for the feedback guys. Yes, RAID-5 really can be that bad, unless your database is read-only or read-mostly. Lots of small writes will perform badly under RAID-5, even with a battery-backed write-cache in write-back mode... -- -Chuck ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Oct 23, 2006, at 19:10, Chuck Swiger wrote: 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. FWIW, Solaris 10 Update 3 (6/06) comes with Postres on the DVDs and you can get official support from Sun if that's important. Solaris/ x86 does run on HP hardware (with support available), but I don't the exact HCL offhand. As for Postgres on FreeBSD, FlighAware seems to be using it some some decent amount of data: . Receiving the data and processing it puts them about 6 minutes behind real time . Generating one map can be done in about 160 milliseconds of CPU time . Capable of generating several million maps a day . About 1 TB of stored data . Approximately 40 million position updates on air craft per day http://joseph.randomnetworks.com/archives/2006/05/12/flightaware- freebsd-and-postgresql/ ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Tue, 24 Oct 2006 02:00:22 +0200, Chuck Swiger [EMAIL PROTECTED] wrote: On Oct 23, 2006, at 4:44 PM, Mike Jakubik wrote: 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. I believe the front-end application is MySQL dependent, but what is so much better about PostgreSQL? I understand that it has some more advanced features, but if they are not used, then what is the advantage? (I really like the InnooDB storage in MySQL) I'm not sure whether avoiding deadlocks and using row-level locking by default qualifies as advanced features, but unless you use InnoDB with MySQL, you don't get that from MySQL. Postgres has been around for a lot longer, and isn't as volatile as MySQL seems to be; also, it avoids some of the needless timer overhead that MySQL seems to enjoy, and the less-accurate-but-much-quicker gettimeofday() under Linux helps MySQL on that platform versus FreeBSD. 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. Is RAID5 really that bad when a lot of fast disks are used and the controller has a decent cache with a BBWC? Thanks for the feedback guys. Yes, RAID-5 really can be that bad, unless your database is read-only or read-mostly. Lots of small writes will perform badly under RAID-5, even with a battery-backed write-cache in write-back mode... Example: writing 1 bit on 1 disk needs to read some info from all disks to recalculate the parity. So this doesn't scale very well. -- Ronald Klop Amsterdam, The Netherlands ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
On Mon, 23 Oct 2006, Bill Moran wrote: Mike Jakubik [EMAIL PROTECTED] 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. This is not another holy war, we also have a dual Xeon 3.6, 2xSCSI RAID0, 8GB of RAM and a database of 40Gb. But after using mysql 5.0 with all performance tricks I found in google and FreeBSD Wiki page, we decided to change to PostgreSQL and now are happy with it. PS. RAID5 is a bad choice for large DB. I'd recommended a RAID0 with standalone backup server. - With best regards, |The Power to Serve Nguyen Tam Chinh| http://www.FreeBSD.org Loc: sp.cs.msu.su | ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Running large DB's on FreeBSD
From Mike Jakubik [EMAIL PROTECTED], Mon, Oct 23, 2006 at 07:44:45PM -0400: Chuck Swiger wrote: On Oct 23, 2006, at 4:01 PM, Mike Jakubik wrote: 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. I believe the front-end application is MySQL dependent, but what is so much better about PostgreSQL? I understand that it has some more advanced features, but if they are not used, then what is the advantage? (I really like the InnooDB storage in MySQL) The argument for pgsql over mysql given freebsd is based on freebsd being fairly competitive for process-based concurrency whereas the more refined 1:1 thread implementation in linux often gives mysql an edge on that platform. pgsql vs. mysql performance comparisons are usually run on Linux and do not necessarily predict performance under freebsd for the testload. ** Find a testload similar in structure to your workload before deciding; make sure that the testload uses hardware with similar bottlenecks to your production setup ** ___ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to [EMAIL PROTECTED]
