Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am in charge of deciding which database to use for a big government project with:

  • 100 Million records (estimated in one big table)
  • Availability and data reliability is paramount.
  • Performance not as important as availability and reliability.
  • Table structure pretty flat with some 20-30 tables.
  • SQL queries more or less straightforward with no complex joins.
  • Cost reduction is an important factor.
  • Number of users in the range 100-200 (at the same time).
  • must chose between clustering (2 servers) or server redundancy (two everything in one box)
  • All servers will be equipped with RAID/SAS hard drives and multi-core dual CPUs
  • Can hire appropriate DB admin according to RDBMS choice

Is mySQL mature enough to handle this kind of configuration?

Please help.

share|improve this question

migrated from stackoverflow.com Sep 12 '11 at 19:21

This question came from our site for professional and enthusiast programmers.

closed as not constructive by Shane Madden, Holocryptic, Scott Pack, Iain, RobM Sep 12 '11 at 20:29

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

    
do you have budge for sql licensing ? –  ajreal Sep 12 '11 at 19:13
4  
Any reason why PostgreSQL is not on your list? –  user20202 Sep 12 '11 at 19:20
1  
You might want to have a database admin work on the project. It sounds a little like you're not familiar with the options for the sysadmin side, is that a fair statement? –  Bart Silverstrim Sep 12 '11 at 19:29
    
If you want availability and reliability paramount, you're probably talking two servers geographically separated to prevent a fire or other disaster from wiping out access. Which entails a lot of other work for keeping the accessibility up...are you talking about your users being geographically spread out, or in one or two offices? What kind of budget do you have? etc. –  Bart Silverstrim Sep 12 '11 at 19:31
    
I've updated my answer slightly. –  mrdenny Sep 12 '11 at 20:30
add comment

4 Answers

up vote 1 down vote accepted

You may have "prior art" on the licensing front which can make your decision a bit clearer. As an example, I have many government clients who have enterprise-wide licenses for ORACLE, so standing up a new server is a non-issue, outside of hardware considerations. Check with your client for existing enterprise solutions.

If this is to be a generic solution then you may want to consider any of the mapping layer solution sets which would allow you to have flexibility on the backend deployment, such as using ODBC, JDBC or JMS as appropriate to connect from the next downstream layer to the DB, with the settings for the connection fed from a configuration file. This way you can avoid a degree of lock in. Of course, if this solution is single customer specific and you never anticipate selling to anyone else then you can avoid such architectural considerations....unless they have multiple database vendors in house and they want the flexibility to execute on any back end.

share|improve this answer
add comment

I guess all the mentioned databases are mature enough. If your scenarios are that simple, I would choose the right system by the development platform you plan to use.

For example, when developing in .NET, MS SQL Server 2008 gives you many nice integration features and it's simply much easier to integrate this database than for example MySQL. Oracle is a generic choice, but the price is much higher. On Windows server, I would prefer MS SQL 2008, on Linux MySQL or Oracle. But I know all systems are implemented on both platforms.

All these system are similarly reliable and are used in many 24/7 applications (MySQL is no exception).

share|improve this answer
add comment

One big deciding factor is what database management and development experience to you have in house? If you are currently an Oracle only shop and you have to hire a MySQL DBA to manage the MySQL database then that'll cost a lot more in the long run than a new Oracle license will.

What's "100 Mega records (estimated)" mean? Is that supposed to be 100 Million? All three listed can handle the amount of data, provided that you give the server enough CPU and memory to handle the workload.

One thing that needs to be figured out is the RPO and RTO for the database. That will help you design the solution that you need.

share|improve this answer
add comment

Your requirements are vague, but I would choose MySQL Enterprise Edition on x64 (12 core, 48 GB RAM) Red Hat Linux, running in Master/Slave redundancy mode.

You wanted generic advice, this is a powerful system for a cheap price. 2 servers will cost 10K each, software will cost about 7K per box for one year support (5K MySQL EE, 2K RHEL). You're now supported by Oracle on MySQL. You could also swap in Oracle Enterprise Linux for the same price.

Nothing you've said indicates that you need more than that. Master/Slave MySQL is a well known configuration, much easier than clustering, and it'll be back up within 15 minutes after a hardware failure on your primary. Like Bart said, geographic isolation would really be helpful, you typically can't do that on a cluster, but you can with MySQL replication.

share|improve this answer
    
On x86 really? Why not x64? 15 minutes of down time is a long time for a lot of applications to be down. With Clustering (or Mirroring in a SQL Server setup) you'll usually be back up in seconds (only minutes for VERY LARGE database applications). –  mrdenny Sep 12 '11 at 20:29
    
It's true that clustering has much lower MTTR, but it is also a lot more complicated/expensive. I've used Oracle RAC successfuly, it works, but it's much harder. I've seen that many people think they need 5 nines until they see the real price in terms of expertise and licenses; suddenly 3 nines is plenty. Thanks for the x86 comment, I've corrected my reply. Certainly you want ample memory for your DB. –  Ranzo Taylor Sep 12 '11 at 21:12
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.