1

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.

Improve this question
5
  • do you have budge for sql licensing ?
    – ajreal
    Sep 12, 2011 at 19:13
  • 5
    Any reason why PostgreSQL is not on your list?
    – user20202
    Sep 12, 2011 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, 2011 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, 2011 at 19:31
  • I've updated my answer slightly.
    – mrdenny
    Sep 12, 2011 at 20:30

4 Answers 4

Reset to default
1

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.

Improve this answer
1

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).

Improve this answer
1

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.

Improve this answer
0

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.

Improve this answer
2
  • 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, 2011 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, 2011 at 21:12

Not the answer you're looking for? Browse other questions tagged .