MySQL Suitability for mission-critical data?

My experience with mysql and mssql hasn't been a ton but have been good.  My mysql has never been down and seems to be all about how many queries you are running on the page as to how fast it is.  But as far as dependability I have not had any problems.  Security seems to be good considering md5 is a one way encryption.  I am also running all my sites on a 3 server system so the chances of all three going down are very slim.  Just my two cents.

I have been using MySQL for years, but also am Oracle (OCA) certified, and support MSSQL at work.

If you are concerned about budget then MySQL can be a decent option.  However at the Enterprise level you currently seem to be at you would be better servered with SQL Server since it is still reasonable priced.

Look at the total cost of your infastructure, compaired with the cost of the Database License and Support and for MSSQL you are not talking a major expense.

Replication of MSSQL is very efficent in the current versions.  I believe Microsoft also has special licenses for warm boxes, that are meant purely for failover and do not carry a normal load.

It is not that you cannot do what you are talking about in MySQL, I would just encourage you to consider the overall costs.

If you are planning to run on Linux or some form of Unix you really need to look at PostgreSQL.  The difference between it and MySQL would be starting point and direction.  PostgreSQL didn't start out as a hobby and try to work its way into the enterprise, it started out being developed for the enterprise.

For high availability and complete "survive the fire" functionality I only recommend one pair of products, RDB coupled with a distributed OpenVMS cluster.  There is a ComputerWorld article from a while back talking about how the stock trading companies running distributed OpenVMS clusters with RDB having the primary location in the Twin Towers continued to trade until the close of business with less than a 15 minute outage even though they had physically lost the primary location and all staff there.  Every other platform combo failed that day.

High availability is all a matter of what you are willing to spend and what you are willing to do.

Most open source databases have a common Achillies heel.  They roll their own transaction management rather than participate with the transaction management which may be built into the OS kernel they are running on.

If you are reading from a message queue (like MQ Series which integrates with OS native transactions managers) and your database integrates with the same transaction manager, your transaction will be in sync.  When transaction managers are independant a system crash or some other disaster happening between the read of the message queue and the commit to the database will give you a lost persistent message situation.  Likewise, some form of interruption between the commit in the database and the commit to the message queue will leave a duplicate persistent message on the queue.

I don't know what you are using your database for, so it is tough to make a more accurate recommendation.

@mfsamuel

Thanks, I see what you are saying about mssql however I have concerns about our ability to keep a microsoft server up for months at a time, our data server for our internet processing runs mssql and despite being maintained by a professional mcp host it still seems to need a reboot every couple of weeks.

@yyyc186

Could you elaborate on why we should use postgre? The major concern is where the projects are now not where they started, surely mysql is more likely to be better going forward seeing as it is directly funded by mysql ab?

MySQL ent seems to have a lot of tools, a large community base and support direct from mysql ab. Whereas postgre only has community support, or private consultants (we have had lots of s4it consultants in the past). I know a lot of the features expected of an SQL server are more mature in Postgre, but from where we're coming from in terms of our current legacy applications, triggers, stored procs will not be used at all. Also I've heard replication in MySQL is more mature than that of Postgre.

OpenVMS is just out of the question, support and maintenence would be a face ache.

As for the rest of what you said, I can see what you're saying however the whole os / own trans management stuff somewhat over my head.

As for what we're using it for, retail and backent management at a sme. Lets say we'll have 200 client connections on 40Gb worth of data.

@mfsamuel

>>>>Thanks, I see what you are saying about mssql however I have concerns about our ability to keep a microsoft server up for months at a time, our data server for our internet processing runs mssql and despite being maintained by a professional mcp host it still seems to need a reboot every couple of weeks.

Are you running on blades which have the same Intel CPU in them that Dell and Gateway are selling in their $800 PC's even though you paid many thousands for the blade?  If so, it doesn't matter what OS you are running, that blade is going to fail.  Every blade rack is based on the design of being filled with cheap commodity components that will fail miserably.  There are supposed to be extra blades in the rack so you can load yet another image of the applications that were running while replacing the blade which cooked.

@yyyc186

>>>Could you elaborate on why we should use postgre? The major concern is where the projects are now not where they started, surely mysql is more likely to be better going forward seeing as it is directly funded by mysql ab?

If that REALLY is your major concern then you should have a massive concern over using MySQL.  That product doesn't own its own database.  InnoDB is now owned by Oracle and they will eventually squeeze the orange for juice.

>>>>MySQL ent seems to have a lot of tools, a large community base and support direct from mysql ab. Whereas postgre only has community support, or private consultants (we have had lots of s4it consultants in the past). I know a lot of the features expected of an SQL server are more mature in Postgre, but from where we're coming from in terms of our current legacy applications, triggers, stored procs will not be used at all. Also I've heard replication in MySQL is more mature than that of Postgre.

Data replication will eventually be found as a chapter in "The Poor Man's James Bond" between the chapter which teaches you how to booby trap a door by hinge pinning a sledge to the ceiling and how to build a grenade out of electrical tape and nails.

Why is data replication implemented?  Because both the database and the OS are not fault tolerant.  Others will try to give you many reasons such as keeping a read only reference copy to balance loads, but lack of fault tolerance is the ugly truth behind it.

Replication isn't as mature in PostgreSQL because it started on business class OSes which allowed the database storage areas to be stripe-set across multiple disk farms at multiple locations.  You had to physically lose two locations simultaneously in order to have an outage.  The replication in PostgreSQL is less advanced as you say because the only intended use for it was to create read only reference tables to balance load.

>>>>OpenVMS is just out of the question, support and maintenence would be a face ache.

Then, you need to come to the realization your application is NOT mission critical.  There will be outages, and you need a current BRP (Business Recovery Plan) on everyone's desk.  Mission critical applications require an infrastructure which will survive the fire.  Those infrastructures do not create BRP documents because they have to physically lose a minimum of two locations simultaneously.  Lose of a single physical location is automatically handled by the infrastructure.

>>>>As for the rest of what you said, I can see what you're saying however the whole os / own trans management stuff somewhat over my head.

It's over a lot of people's heads.  I've been trying to cover it in the book series I've been writing.

>>>>As for what we're using it for, retail and backent management at a sme. Lets say we'll have 200 client connections on 40Gb worth of data.

How many physical locations are you going to distribute your database over?  Just one?  You will have outages and at the worst possible time.  A cheap SAN card will create a SAN-storm and you will lose access to your disk farm.  The power will go out for longer than your UPS can maintain or you will find out the computers are on UPS but not the network equipment.  Your data center will be fed by only one communications trunk line and Joe with his back hoe 3 states away will have a bad day.

Once developed, core business applications have a minimum 7 year settling time.  They remain in place for at least 7 years beyond that.  Where the tools you are looking at will be 14 years from now is irrelevant.  Your application will be using the tools for that long.  Somewhere around year 12 you will look at changing over to a new architecture.

Those who understand this fundamental truth make good decisions.

>>>>>>>>>>>>>>>>Are you running on blades which have the same Intel CPU in them that Dell and Gateway are selling in their $800 PC's even though you paid many thousands for the blade?  If so, it doesn't matter what OS you are running, that blade is going to fail.  Every blade rack is based on the design of being filled with cheap commodity components that will fail miserably.  There are supposed to be extra blades in the rack so you can load yet another image of the applications that were running while replacing the blade which cooked.

-- Nope it's not on a blade, we only have blades for application servers and disposeable suff.

>>>>>>>>>>>>>If that REALLY is your major concern then you should have a massive concern over using MySQL.  That product doesn't own its own database.  InnoDB is now owned by Oracle and they will eventually squeeze the orange for juice.

-- You do have a point there, however they say on their site they have arranged a multi-year agreement - but as you say for how long.

>>>>>>>>>>>>>>>>> Data replication will eventually be found as a chapter in "The Poor Man's James Bond" between the chapter which teaches you how to booby trap a door by hinge pinning a sledge to the ceiling and how to build a grenade out of electrical tape and nails.

-- Well, money isn't "no object" in our case unfortunately, we are not a bank, we are a medum sized company - it may have to be a poor mans james bond job, that's why I'm here asking for some advice.

>>>>>>>>>>>> Why is data replication implemented?  Because both the database and the OS are not fault tolerant.  Others will try to give you many reasons such as keeping a read only reference copy to balance loads, but lack of fault tolerance is the ugly truth behind it.

Replication isn't as mature in PostgreSQL because it started on business class OSes which allowed the database storage areas to be stripe-set across multiple disk farms at multiple locations.  You had to physically lose two locations simultaneously in order to have an outage.  The replication in PostgreSQL is less advanced as you say because the only intended use for it was to create read only reference tables to balance load.

-- Er? Then why was it implimented in Postgre at all? You've just given me the same excuse in that paragraph as the one you told me not to accept in the one above.

>>>>>>>>>>>> Then, you need to come to the realization your application is NOT mission critical.  There will be outages, and you need a current BRP (Business Recovery Plan) on everyone's desk.  Mission critical applications require an infrastructure which will survive the fire.  Those infrastructures do not create BRP documents because they have to physically lose a minimum of two locations simultaneously.  Lose of a single physical location is automatically handled by the infrastructure.

-- Well if thats the case, lets use the term "mission critical" loosely. If our main building burns down then we aren't going to give a flying duck that we lost the last however many few transactions not on the offsite slave, as long as we can bring that slave back up and still have 99.99% of our data and keep on going with business.

>>>>>>>> How many physical locations are you going to distribute your database over?  Just one?
You will have outages and at the worst possible time.  A cheap SAN card will create a SAN-storm and you will lose access to your disk farm.  The power will go out for longer than your UPS can maintain or you will find out the computers are on UPS but not the network equipment.  Your data center will be fed by only one communications trunk line and Joe with his back hoe 3 states away will have a bad day.

-- We have two sites, the main site has a massive ups and a very large generator that starts up as soon as the ups detects that it's taken over. The other smaller site has a ups. Whatever, comms and servers will be up -it's already saved us once. It's insane how cheap a massive generator and ups system is compared to software licencing.

I Appreciate everything you've said, but I think we need to get this in perspective, and so far you've shown that you know a lot but you've not helped me really.

We don't have the ability to stop world war one happening, and if the power goes ourt everywhere forever then we cant do anything can we. If we loose a few transactions in the worst ever case that'd be fantastic.

@yyyc186

You seem very passionate about this. May I ask what you do for a living and what sort of organization ?

>>>>You seem very passionate about this. May I ask what you do for a living and what sort of organization ?

I'm a software consultant who has spent nearly 20 years in this field.  Much of my work has been true mission critical.  It offends me morally, ethically, and professionally when both management and marketing types toss around "mission critical" to make themselves sound important.  As to background, I've done stock exchange trading floor systems, stuff that requires security clearance, stuff for suppliers to people that require security clearance, and for a time was in regular chat mode with a company that does control software for nuclear power plants.  A single lost message in the stock exchange application could be anywhere from $100 to $350million and basically you were liable for the entire transaction once it hit the wire coming to you.  Thankfully, I never actually wrote code for the power plants.  I can live with a few lives depending on my software, but not entire metropolises.

>>>>-- Er? Then why was it implimented in Postgre at all? You've just given me the same excuse in that paragraph as the one you told me not to accept in the one above.

Replication got implemented in MySQL for the sole reason the product has absolutely no fault tolerance and cannot use the fault tolerance built into the surrounding architecture.

Replication was implemented in PostgreSQL and RDB because system managers wanted to do load balancing by creating read only tables for users that were up to date.  These databases already used the surrounding fault tolerance of the architecture.

For an idea just how bad Oracle RAC 10 is take a look at this:
http://www.eweek.com/article2/0,1895,1429814,00.asp

>>>>I Appreciate everything you've said, but I think we need to get this in perspective, and so far you've shown that you know a lot but you've not helped me really.

I haven't helped you because you have already made the choice to use MySQL and are simply looking for someone here to confirm that decision.

Mission critical applications choose their architecture, you cannot call an application mission critical, then choose the architecture that Gartner is paid to market this week simply because it is in vogue.

Does your development environment mirror your production?  Is your development environment spread across both locations, but optically isolated from production, not sharing the same disk farm?  

If so, conduct an actual test.  Install MySQL, PostgreSQL, and any other database you are considering.  (Read The Fine Manual when installing.  Configure the database products for how THEY tell you to implement fault tolerance, not how you want to implement it.)

Write a simple application which will accept input from the outside world in the same manner your production system currently does.  Write a standalone application which can generate transactions in a throttled( 300-3000 per second) manner which have unique ID's.  Boot up one of the test applications.  Start your transaction generator.  After you see things being written to the database, kick the plug out of the wall for the primary and mark the ID where you did it.  Power your primary back up without shutting your secondary down.  When it comes up, is your database hosed/corrupted  (requires some kind of hand massaging or worse, is completely inaccessible).  If so,  the product has already failed.  Count/make note of how many transactions are missing.  Start the test application again on the primary and ensure it still writes to the database.  See if some of the missing transactions came in (MQ series or some other product with persistent message queuing would be a reason some would show up)

Conduct this test with each and every database you are interested in.

If one of your database products is corrupted when you reboot, it cannot be considered.  This typically happens when databases cache their internal pointers for performance.  Depending upon how much of the internal data was cached awaiting write, you can lose your entire database (i.e. Orbitz)

If all of your database products seem to work, run the test twice more with the ones you like.  Calculate the average number of lost transactions across all 3 test runs for each product.  That's how many customers you will honk off that will never come back.

Please, don't play fast and loose with the term "mission critical".

As to your uptime requirement, follow this link to read a note about the Amsterdam Police Department and 10 contiguous years of uptime.  It's not the longest, (some sites are over 17 years contiguous uptime) but it is interesting.

http://groups.google.com/group/comp.os.vms/browse_thread/thread/d7fb3d0b269b68ac/ccd0304ff611335d?lnk=gst&q=longest+uptime+years#ccd0304ff611335d

>>>>>>>>>>>> I'm a software consultant who has spent nearly 20 years in this field.

-- Sorry to ask that, I just wanted to make sure I wasn't speaking to a university proffessor or something that had studied the subject to death but never actually done any real serious work.

>>>>>>>>>>>> Replication got implemented in MySQL for the sole reason the product has absolutely no fault tolerance and cannot use the fault tolerance built into the surrounding architecture.

-- This is the kind of info I was really after, would you be kind enough to explain the "fault tollerence" part to me a bit, especially pertaining to PostgreSQL and MySQL? I promise I'll then stop bugging you and do research.

>>>>>>>>>>>> I haven't helped you because you have already made the choice to use MySQL and are simply looking for someone here to confirm that decision.

-- Definately not the case! I was after somebody to say what you are to me, and tell us all the reasons as to why we should not, before I go and make the decision to use it. In that respect you have really helped - you've just left me a bit confused after reading MySQLs marketing, that's all.

>>>>>>>>>>>> Does your development environment mirror your production?  Is your development environment spread across both locations, but optically isolated from production, not sharing the same disk farm?  

If so, conduct an actual test.  Install MySQL, PostgreSQL, and any other database you are considering.  (Read The Fine Manual when installing.  Configure the database products for how THEY tell you to implement fault tolerance, not how you want to implement it.)

-- Yes and I intend to do these tests thoroughly, you have confirmed here that the test's I was going to do are in the right direction.

>>>>>>>>>>>> Please, don't play fast and loose with the term "mission critical".

-- Lets call our database "essential for business opperation" then, not make ourselvs sound too important in that respect. The data is very important and we can't loose it, but if we loose a few transactions we'll be red faced, we don't want that but nobody is going to die. If one location goes down we need to be able to get back up on either the same system or a slave / standby asap. However we need to have good backups and a BRP.

Do you know much about replication and postgres as to the most reliable options, I've looked into it and there's lots of options.

-- This is the kind of info I was really after, would you be kind enough to explain the "fault tollerence" part to me a bit, especially pertaining to PostgreSQL and MySQL? I promise I'll then stop bugging you and do research.

"Fault tolerance" -- the ability to lose part of the architecture executing the transaction, recover the transaction and successfully process it without any human intervention.

In order to achieve the above, you need a distributed transaction manager embedded in the kernel of every node in your cluster.  Since it has been proven what Unix and Linux were calling clusters were no where near what an actual cluster is, they now tend to call what they do grids.  Grids operate on "the swarm" principle.  (Many will die, but some will survive.)  Clusters are designed around the "Survive the Fire" principal.  (A soldier is expendable, the mission is not.)

I haven't done much with PostgreSQL.  I was looking at joining with a few others to port the current release to OpenVMS.  The original implementation ran on VAX/VMS and made full use of DECdtm (distributed transaction manager).  It also made use of the transaction managers on the IBM platforms it was running on.

There is no "native" distributed transaction manager on Linux or most Unix flavors.  (True/64 had a version of DECdtm at one point, but I don't know if it still does since HP is trying to push everyone into that worthless HP/UX  platform.)

With that said, there are some transaction managers (not embedded in the kernel) which try to mimic what DECdtm could do.  They don't really mimic it.  When a system reboots there is always some manual tasks which have to occur to recover transactions.

Perhaps what has went over your head is that distributed transaction managers don't require more than one node to be distributed.  They manage a transaction across distributed data sources.  DECdtm manages transactions involving data from MQ Series, ACMS, RMS Indexed files, and RDB databases.  One message coming in via MQ Series can be kept as a single transaction until it completes through each of these sources.  Two phase commit is a topic a lot of people cannot wrap their minds around, but you cannot have a distributed transaction manager without it.  The transaction needs to be committed at each data source, then the transaction itself can be committed.

Fault tolerance isn't just a hardware issue.  Tandem tried to solve it with mostly just hardware.  While they did manage to make computer systems you could empty the clip of a 45 into and they would still keep running, they missed the other side of it.  Long before we had disk arrays and SANs OpenVMS had the ability to do volume shadowing and drive stripe setting at the OS level.  Every disk (logical or physical) connected to any node could be mounted across the entire cluster and made part of a bound volume, shadow set, or stripe set.  A disk array or a SAN can handle all of this for you, but only within its architecture which is usually just one cabinet.  If each location has its own disk array or SAN, those can be mounted cluster wide and the OS can make the logical/virtual volumes they present part of a bound volume, shadow set, or stripe set.  Disk arrays and SANs are set up to handle single spindle failures.  Clusters are setup to handle single location failures.

Each OpenVMS cluster appears to the outside world as a single computer, no matter how many nodes it contains across however many locations.  They implement both cluster aliasing and IP aliasing.  When the cluster is properly configured, it will serve the same IP address no matter which nodes actually boot.  This means if the node currently acting as the entry point suddenly ceases to exist, the new cluster which forms will continue serving that same IP address.  You have to read a few manuals to get that portion configured, but it's absolutely amazing once done.  (This is one of the reasons the companies running OpenVMS in the Twin Towers were able to keep trading throughout the day even though the Tower location was gone.)

An OS level stripe set with a configured disk to use for recovery will automatically reconstruct the contents of the stripe set once it realizes a spindle is missing, just like the stripe sets do in a disk array or SAN.

When you use MQ series and its persistent messaging, you place the queue files on a cluster wide stripe set and you cannot use any message which has been received in a queue.  There is a little bit of setup work to do getting the MQ Queue manager to start up automatically on which ever cluster node is the leader of the quorum, but it is a one time effort just like the IP aliasing.

There are distributed transaction managers sold by third parties for Unix and Linux platforms, but you have to read the fine print VERY carefully.  None of them are great, some aren't even good.  Most will only handle messages in queues.  You need to look at the documentation put out by the community for whichever open source database you choose and see which distributed transaction managers they support.  To my knowledge there is no open source distributed transaction manager for either Linux or Unix.  The OS kernel doesn't implement the necessary components to build one.  You need logicals, ACLs, and a common user authorization file.  Then you need media services which respect ALL of those components.  (ACL = Access Control List)

SANs are the new Achilles Heel for data centers.  IT continually repeats the mistakes of the past.  Many years ago NIC cards cost over $1200 for the quality built name brand ones.  Then came a slew of $200 el-cheapos.  Some of those el-cheapos would be polite and simply die.  Most would die spasticly setting off a "network storm" which locked all users out of the network.  Then we built network sniffers to find out which el-cheapo card was causing the problem.  Eventually quality became consistent and NIC interfaces are now built into the motherboard on most computers.

SANs are going through the same evolutionary period.  El-cheapo SAN cards are dying spastically setting off SAN storms locking all systems out of the SAN.  There is no SAN sniffer right now so you power everything off for a while and it all seems to work again when you bring it back up.  After a few hours or a few days, the same thing happens again.  The only way to identify the offending card is to shut things down one at a time over a very prolonged outage.  All kinds of things get corrupted when your shutdown procedure cannot close open files on the SAN.  It's a nasty situation which hopefully you've avoided.  There will be at least 5 more years of pain before the bottom feeders are weeded out of the supply chain.

>>>>Do you know much about replication and postgres as to the most reliable options, I've looked into it and there's lots of options.

First look into the doc to find out what transaction managers it supports on your platform.  Once you have that in place, replication is only for reference tables.  Since you aren't running a clustered OS, each location you have needs to be directly connected to each disk array you have, not accessing it through another node.  You will only be protected from a single spindle failure and a single node outage in this manner, but it is the best you can do.  You will lose all non-persistent messages which have not been processed on the primary node.  Without IP aliasing you will also lose all incoming messages headed to the old IP address.  This message loss cannot be measured accurately.

If you cannot directly connect both machines to the disk arrays at each location, you need to use disk arrays which can directly connect to each other and can perform volume shadowing on their own.  This frees each node from having to perform the additional rights.  You will need to manually break the shadow set when your primary node crashes so the secondary node can treat the secondary disk array as the primary.  Some disk arrays may allow you to "flip" between primary and shadow.  I haven't looked into it lately, but there was a lot of talk about that 10 years ago so somebody probably implemented it.  If you do this you have to shadow EVERY volume which is part of your open source database and configure your database not to cache writes, let the array do that for you.

>>>>you've just left me a bit confused after reading MySQLs marketing, that's all.

Have you ever found a vendor that prints marketing saying "Our stuff works some of the time for some of the people"?

I must say yyyc186 was the eye opener here, thanks for the info