Slashdot Mirror
Open Source Database Clusters?
grugruto asks: "A lot of open source solutions are available to scale web sites with clusters but what about databases? I can't afford an Oracle RAC license but can I have something more reliable and fault tolerant than my single Postgres box? I have seen this recent article that looks promising for open source solutions. Do anyone have experiences with clusters of MySQL , Postgres-R, C-JDBC or other solutions? How does it compare to commercial products?"
Maybe it will be of interest...
philcrissman.com.
IMHO, the biggest problem is replication; keeping them all consistent in the face of asyncronous updates. It can also reduce/eliminate the advantages of clustering if you have a significant number of updates compared to the number of quieries.
I guess the best answer depends on how dynamic your data is. If it's static, there are all sorts of easy answers. If all the updates come from a central source, or on a predictable schedule, you're almost as well off. If updates come from the great unwashed but the data can be partitioned in some way (say, geographically) you can still do it. If updates come from all over but queries can be centralized, or if your database is tiny, or if latency isn't a problem, or if you have a machine that prints money, it can still be done.
If you want to do everything for everyone everywhere, right now if not sooner, for under twenty bucks, you're screwed.
So, what are your needs?
-- MarkusQ
Check out the new replication at postgresql.org: it's master -> multiple slave replication.
Then have your slave database query the master database - and if it no longer responds, it could promote itself to master.
The replication is the easy bit - the slave promotion is the hard and gritty bit.
Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.
What *I* would probably attempt would be to setup a replication ring, and use a bigIP to make them all look like the same server. Then you get your load balancing, and scalability. I have yet to try this, but I will in the (very) near future.
Why can't all fpga/microcontroller manufacturers just release free optimizing compilers???
You should investigate eRserver. It was originally a commercial replication product for Postgres but has been open-sourced. I haven't tried it yet but it's on my to-do list.
~~~~~~~
"You are not remembered for doing what is expected of you." - Atul Chitnis
- CLUSTERING IN TUNE WITH APACHE AND MYSQL (Free registration might be required. Also see Emic Application Cluster
for MySQL
- InnoDB Hot Backup (with point in time backup)
The rest of this comment is quoted verbatim from InnoDB NewsMySQL/InnoDB-4.0.1 and Oracle 9i win the database server benchmark of PC Magazine and eWEEK. February 27, 2002 - In the benchmark eWEEK measured the performance of an e-commerce application on leading commercial databases IBM DB2, Oracle, MS SQL Server, Sybase ASE, and MySQL/InnoDB. The application server in the test was BEA WebLogic. The operating system was Windows 2000 Advanced Server running on a 4-way Hewlett-Packard Xeon server with 2 GB RAM and 24 Ultra3 SCSI hard drives.
eWEEK writes: "Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems."
The whole story. The throughput chart.
Trusted Computing FAQ | Free Dawit Isaak!
What does Slashdot do for this? I recall way back in the day there was some information about what the Slashdot tech looks like, anyone have info regarding their database setup? L
Oh come on. 99% of the time, this "failure" is due to the admin having the number of concurrent mysql sessions set lower than the number of apache sessions. Since they'll never hit that situation in their half-assed testing with one browser...
:p
And how exactly do you intend to compare the situation where MySQL saturates to the situation where apache saturates remotely, exactly? If apache falls over, you're getting no connection at all. Perhaps the database is working great, you'll never know.
Supposedly should be out by now.
Based on upvotes, Ageism is the only "-ism" Slashdotters care about and think isn't SJW
The press release of ER Server becoming open source is quite informative (karma?) as well.
Marc of PostgreSQL Inc's an incredible resource on the postgresql mailinglists too; and PostgreSQL Inc has a really cool policy that allowed them to do donate their code to the community that way:
From their release: " "DATELINE FRIDAY, DECEMBER 15, 2000 Open Source vs. Proprietary: We advocate Open Source, BSD style :) We will consider and develop
short term (up to 24 month) proprietary applications and solutions
where there is a strong business and intellectual property case to
be made. *All" proprietary developments that we are involved in
*will* become open source within two years of implementation,
without exception."
".
Also cool, they provide hosting http://www.pgsql.com/hosting/ which donates "25% of all profit from these services ... directly back into the PostgreSQL Project.
"
Ron
I'm not affiliated with them in any way, just appreciative of Marc's contributions on the mailingslists and to postgresql as well.
Availability is one of the basic issues when sizing your system. [ie, can you have it down at night for a cold backup, or does it have to be available 24x7? Can you even get a maintenance window once a month?]
As with sizing your UPS and/or generators, you need to determine what the cost to your business is for downtime.
Now, yes, you might have some issues in SLAs that spell out how much it'll cost you, if you have to refund customers's money [for service based orgs]-- or how much profit you'd lose if your customers couldn't purchase items [for sales based orgs]. But unfortunately, you have to also consider the recovery costs, the costs of damage to your reputation, etc.
If it's not worth your purchasing an Oracle or other, more expensive database, there's good odds that it's not worth the headaches of maintaining a high availability cluster with automatic failover. Instead, you can mirror the data, and keep transaction logs that you can replay.
You can have a spare system on standby, that you can keep updated on a regular basis (again, your cost of downtime, and the necessary time to recover the system will affect your choices), and when your main system should fail, you can push the most recent diffs to your standby, reconfigure the application servers to recognize the new server as the old one, and you're back in business.
It requires a bit of planning, and making sure that the necessary manual steps are well documented [so that anyone can do it, should the server outage be caused by something serious enough to take out your administrator, too], but it's easier and cheaper to build and maintain than a true cluster.
Build it, and they will come^Hplain.
TM
P.S.Cant wait for our Sun V280r shows up!
Support TBI Research: http://www.raisinhope.org
Clustering read-mostly data for performance reasons is relatively easy; for many applications, where a second or two of staleness on the replicated databases is acceptable, you can make do with a bunch of independent copies of the database, with all updates going to an authoritative database and getting replicated out from there asynchronously.
If your data can be partitioned cleanly -- that is, if you have groups of tables that are never joined with tables in other groups -- then you can perhaps get some benefit from putting different data on different servers, with no replication required. Obviously that's only worthwhile if the query load is comparable between groups.
If, on the other hand, you require ACID-compliant updates of all the replicants as a unit, you're entering difficult territory and you might have no choice but to go with a commercial solution depending on the specifics of your needs.
At just about all of the places where I've done database programming where this has come up, we ended up buying a much beefier database server (lots of processors and memory, good I/O bandwidth, redundant networking and power supplies) with disk mirroring, rather than get into the headaches of replication. A big Sun or HP server is certainly more expensive than some mid-range Dell or no-name PC, but it may end up being cheaper than the engineering time you'd spend getting anything nearly as robust and high-performance on less expensive hardware.
I've also found that very often when there's a database bottleneck that looks like it requires bigger hardware, the problem is the data model or the queries (unnecessary joins, no indexes where they're needed, poorly-thought-out normalization, etc.) or the physical layout of the data (indexes competing with data for access to the same disk, fragmentation in indexes/data, frequently-used tables spaced far apart on disk.)
If I'm dealing with Oracle, sometimes the solution is as simple as adding an optimizer hint to make the query do its joins in a sensible way. Sometimes denormalization is helpful, though you want to be careful with that. Sometimes a small amount of data caching in the application can mean a tremendous decrease in database load. And so on.
If you can tell us more about the specifics of your situation, there are lots of people here who can offer more specific advice.
It's been the largest pain in the ass I've ever had managing servers.
MySQL spanks DB2, as does postgreSQL.
Our DB2 on Linux crashed so much we spent months before we had a production ready system. We were replacing PostgreSQL and we had to rethink everything. It couldn't handle our insert load, and we were going from 4 dual 733 intel boxes to two large quad xeon boxes with 15,000 rpm disks.
We spent $100,000 on DB2 license (that with the discounted half price DB2 EEE for linux). We are now in the process of migrating to MySQL after some large benchmarks. With a few simple indexes MySQL inserts twice as fast as DB2 and selects in 0.00 seconds on any row, vs. DB2's
Throw in the support scam they pulled on us, and IBM is a joke of a company. If they weren't pushing Linux they'd annoy me more than Microsoft does. The support scam went like this. We purchased 8 CPU licenses for DB2 EEE In 8/02. In 3/03 we start recieving calls from salesmen to get our upgrade business since our 1 year support contract expires on 5/1/03. I call IBM with a serious chip on my shoulder and get the story that our anniversary date automatically defaults to any dates held by previous contracts, "it's easier that way". We had some AS/400's (talk about poor performing overpriced junk). So they wanted about $50K for "support" for another year. We declined their offer and considered suing. At $50k/year losing 4 months of support isn't acceptable to a small business.
So I am bitter at IBM. But not without reason. During our first 3 painful months deploying DB2 I opened 15 PMR trouble tickets. Of the 15 I resolved 14 while either on hold or waiting for a call back from them. ALL of the PMR's were opened with status "critical, production down". The last PMR IBM claimed to either be a bug in the Linux kernel or in DB2, they didn't know, but when I pressed, they did offer a patched version that we could "try out" on our production box to see if it worked. Throw in that clustering didn't work as advertised (not at all under moderate load), and DB2 is a pile of junk.
As the IT geek the fault landed squarely at my feet, so I did some thorough investigation and benchmarking. default config DB2 is considerably faster than both PostgreSQL and MySQL at everything but inserts. But throw in a few indexes and MySQL and PostgreSQL owns DB2's sorry excuse for a database.
I AM bitter, and this probably is flamebait. But I'm past caring about IBM and their scam operation. I'm sticking with what works, and so far NOTHING from IBM has worked.
I wasted 3 months of 7 day work weeks averaging 12 hour days on DB2 and it's so called Linux support.
end
My Linux Command of the Day site : LCOD
> The right tool for the job people
Right, and a myoptic application of the above advice would lead to a dozen different database products in a typical department. They'd all be the right tool for some job - unless you're hoping to reuse skills, reuse backup solutions (TRM for DB2, Veritas for Oracle, etc), have any hope of reliable integration, etc.
So, yeah - get the right tool for the job. But before you right that out you need to take a big step back and get a sense of what your strategic direction is, and what are all the implications of such a decision.
I know a lot of folks converting mysql to other solutions right now - because some junior guy figured it was the best solution. It might have been for the app - but it wasn't for the department. Which is like winning a battle but loosing the war.
We run a four-way data sharing cluster using DB2. Works very well us. It doesn't do peer recovery - one a instance fails, the survivours process the rollbacks and release locks. As far as I know only IMS does peer recovery.
Of course this has very little to do with OSS.
You can't embed fonts and images inside of html documents.
On linux(with my blazingly fast duron 650) a 500 page pdf I made with OpenOffice takes a few seconds to load in konqueror. I had downloaded all the indiv. web pages that made up the book(wasn't avail as one file), used cat to put them together and then waited 20 minutes for open office to load it. Mozilla took about the same time to load the same file, and konqueror was a little bit less than ten minutes. God knows how long IE would have taken, if it would have loaded at all. While were getting off topic here, Word2000 would only bring up the first web page becuase and ignored all the rest in the file. God only knows what IE would have done.
Basically, for big files PDF is the only option as far as I'm concerned. I am sorry that Microsoft and the creaters of pdf can't provide you with a decent computing experience for such basic tasks. There's only $50 billion dollars and decades of experience between the two companies, these poor guys are doing all they can.
---------- Open Source is capitalism applied to IP.
One of the telltale signs of a SQL Server installation is the frequent "deadlock" messages. I would say that if you are going to complain about transaction handling in MySQL, even the standard version that doesn't have it, you should probably complain about the transaction handling in SQL Server. If it deadlocks, and does not deadlock avoid, then it ain't an enterprise solution.
This is my sig.
I run two types of clusters, one of them is a RAC 9i on Linux. Nothing and I mean nothing has the functionality of RAC 9i. You can put a bullet through one of the nodes right in the middle of a query being returned and still get your records just like nothing ever happened. The other database I run is a postgresql on redhat advanced server and the database files are sym linked into the san (this is high availability only) . If I had to do it again I would not use postgresql because it scales for shit and I cannot under any circumstances keep it up in a 24/7 configuration. The database needs to have vaccuum run on it once a day and I have to do that manually because half the time it fails. Running a vaccum on the database while clients are connected basically locks everyone tight until it is finished.
If you cannot spend any money and wish a fast, scalable and higly available system my advice is first sapdb and or mysql and advanced server on some sort of shared scsi.
Now all of you big postgresql advocates flame away but it does not change the facts. I love the database but if you need heavy lifting it just does not cut the mustard.
Got Code?
I live in a mixed environment of a couple of commercial databases and a couple of popular no-cost ones.
The bad news is, you get what you pay for.
You'll have open source aficionados telling you how [insert brand] free db is k00l. Sadly, in reality, the few most popular such packages are simply way behind commercial systems. Some commercial systems are also way behind other commercial systems... (I mean, Windows as a db server platform? Get real & get sober, dude.)
I wish I could recommend a free or at least cheap db server. But if you care about your data so much that you are seriously going into replicated systems, the couple of most popular free packages at least aren't there yet even in basic ACID reliability.
I wish I could tell you different, and I wish the company where I work wouldn't have to pay the megabuck-class "maintenance fees" for our commercial dbs.
One main issue I see with the Emic solution is that it does not support transactions. I saw their demo at the last LinuxWorld in SF and they are just using a multicast layer to broadcast the queries to all nodes (they don't parse SQL so they can't handle transactions properly). ... WHERE date=NOW() , you will just get a different result on every node! At least, solutions like C-JDBC replaces macros such as NOW or RAND on the fly so that all databases are consistent.
Moreover, if you have queries like UPDATE
We do planned code upgrades once a week and have to kick each webserver, but the load balancer keeps the site up transparently. We probably lose a total of a few hundred incoming connections each time we do that (a total of maybe 5 seconds worth, once a week).
In the last year, I think there was once that we had to roll code back and were probably down for a few minutes, and I think one other time when we were down for an hour, I forget what exactly.
And then of course we've had network troubles occasionally, but that could be us or it could be you :)
None of that has been because of database failure (to get back ontopic sorta :) ... the MySQLs just all keep humming.
Bull pucky: From someone who with their only deployment of MySQL into a live environment went completely pear shaped, MySQL crashing several times per day. The damned thing doesn't report ANYTHING to the error log, except "I'm starting up again, and oohhh look at all that corrupt data, I hope I can do something about that!". I would never touch the database again, not with a 10 foot bargepole.
We're dropping that pile of crap faster than you can click the hyperlink on the MySQL website which says it may take up to two weeks to get any kind of support even in the case of an emergency.
We're now using MSDE for low powered embedded installations that the MySQL crowd had pushed prior to this. Who would have thought, use the Microsoft solution because the open source one doesn't cut it.
Sorry, it's been a long week of conference calls and VPNs in the middle of the night because MySQL decided to crash once again.
MySQL isn't there, and it's lost all trust from anyone who knows about databases.
dominionrd.blogspot.com - Restaurants on