MySQL in production environments

UncleBensSauce · 7 Sep 2009 at 12:25

Hi All

I've seen quite a lot of MySQL in production projects recently, mainly for collaboration systems, webservers etc. Though nothing where it seems to be mission critical. I was wondering what peoples feelings are with regards to it - specifically to dataloss, failure rates, corruption etc.

I'm currently examining Nagios and have noted that a lot of the addons/plugins use MySQL, I'd class monitoring as a mission critial part of our IT department and i'm worried about moving to a monitoring system that depends on MySQL. All our reports, capacity planning data, system alerts etc will be in this DB and after having read so many horror stories I'm reluctant to move to it. We'd be using multiple managers for redundany so I'm sure we'll get some protection there, but the system has to be rocksolid stable for a sign-off for production.

Any thoughts/comments on this - Am I way behind the times - Does MySQL cut it in production environments and systems with a lot of data and a reasonably high rate of change?

Is postgress a better option?

Witor · 7 Sep 2009 at 13:15

As above.

Run quite a few departmental databases on a MySQL database, plus a custom-coded helpdesk. Runs very well, DB tecchies maintain it quite regularly with updates n stuff, no enterprise contract.

john_p · 7 Sep 2009 at 13:40

I have managed data-intensive MySQL environments for about 8 yrs and have never encountered corruption*

Have a sensible backup policy and some way of monitoring your Nagios server for failure, and it will be fine.

* This statement only applies to 6th Sep 2009, for the avoidance of "tempting fate" or similar

dangerstat · 7 Sep 2009 at 18:16

We've had no problems with our production MySQL databases. No corruption issues I can recall

daz · 7 Sep 2009 at 18:26

You'll want to do some kind of replication and/or clustering if the data is very important. ZFS is also worth investigating.

GhostlyPea · 7 Sep 2009 at 19:36

One of my main clients uses it in a Mission Critical environment. Its fine providing any queries you have are written by somebody who knows what they are doing. Good hardware and techies helps too.

- Pea0n

UncleBensSauce · 8 Sep 2009 at 11:46

Thanks for all the input - feel a little bit better about putting forward a system that depends on it now

You'll want to do some kind of replication and/or clustering if the data is very important. ZFS is also worth investigating.

As I understand it ZFS is only available on BSD (zfs not quite production ready) and on Solaris/OpenSolaris (Can't go this route until we know what Oracle will do)

Are there any other platforms that support zfs? I'm a big OpenSolaris fan and use it as my home OS and on my own webservers - unfortunately EU has decided to slow the Oracle deal down even more, and I can't submit a solution with products that may not be around in the same way/price as currently (anyone else expecting opensolaris/mysql support to go up an price a lot?)

Currently leaning toward a BSD on x64, failing that CentOS instead.

daz · 8 Sep 2009 at 12:01

FreeBSD MySQL performance can be about 20% better than Linux, so if you're not enticed by a Solaris option then that would be the way to go down.

Hades · 10 Sep 2009 at 00:37

I'm a big supporter of open source products. My day job is running several application support teams for a large bank. You should consider the following factors (I don't know MySQL that well so some questions may seem obvious):

* Once you start scaling very large then MySQL performance won't be as good as Oracle and DB2 for example.

* With open source you don't generally have anyone to notify you of critical patches (unless you're paying for support somewhere). So you should have someone in-house who is accountable for this.

* Does MySQL have sufficient and appropriate production strength backup/recovery utilities?

* Does MySQL have sufficient and appropriate diagnostic utilities (e.g. Statspack in oracle as just one simple example)?

* Does MySQL support replication to a failover or DR database?

* Does MySQL have sufficient and appropriate clustering capability?

* Does MySQL have sufficient and appropriate tools to reorg the data (e.g. runstats and reorgs on Oracle/DB2)?

* Do you run third party applications and how does MySQL affect the support of them? What I mean here is that most vendors will certify their product against a number of technical stacks. If you don't run against them then you may drop to best endeavours support. You want to know this before you get involved in a high severity incident.

* How does the Oracle purchase of Sun (who own MySQL) affect the future roadmap of MySQL?

You mention monitoring solutions above. what about alerting too? These are different things.

You can use ZFS on Linux too (no idea how stable it is though).

daz · 10 Sep 2009 at 09:40

I wouldn't even want to run OpenSolaris over Solaris in production let alone something as crazy as a home brew port of ZFS to Linux that runs in userspace.

bigredshark · 10 Sep 2009 at 10:35

Several massive sites run mySQL with reasonable reliability. For anything mission critical clustering is going to be necessary, backups are going to have to be good etc...

Postgres certainly isn't much better in my estimation, only upgrade worthwhile would be oracle if you're not happy with mysql.

For example I believe facebook runs on mysql (with memcached to speed things up...)

What you want for production reliability and support is RHEL running mysql or Oracle tbh...