I'm looking at setting up a DB for a very high volume non-profit situation that also would benefit from high speed updates and searches.

Each transaction record will be relatively small (basic customer information plus 10-12 extra fields) but must be updated/modified literally thousands of times via different updates, reports and screens. (But I don't need records of each update except in recovery.)

Is MYSQL best for this? What file system would be most efficient?




Virtually any database server can meet your needs, whether it is MySQL, Microsoft SQL Server, Oracle, etc. Performance with all the rdbms' vary a lot based on the following criteria

  • I/O Performance of the disk (get good, fast, RAID'd disks over high throughput connections)
  • Performance and Amount of RAM (good and fast RAM, and lots of it)
  • File System Overhead (use a modern fs designed for databases. Ext4 works fine, but there's OCFS2 that is Oracle's file system, built for databases, or even Oracle's RAW storage where there isn't even a file system in the OS-sense... the database manages the disk entirely.)
  • Properly Indexed table structure (quick access to data depends upon your data being indexed correctly, where "correct" means most efficient for all your needs.)
  • Properly Optimized Queries... usually goes hand-in-hand with the correct indexes; the database can try to figure out the best optimization for your query, but it can only do so with correct indexes and statistics. Better yet, fine tune your queries so that the database doesn't have to do a lot of optimization on its own, and you'll be faster and happier for it.
  • I/O Performance of the network - if you are transferring a lot of data in or out, network performance will matter (otherwise the database has to wait when sending data across the wire).

A few things that really don't matter very much (as far as performance):

  • Your operating system. Windows, Mac OSX, Linux, and anything else that's modern will do just fine as the operating system layer. Most of your work is done in the database anyway, with occasional tasks needing done in the OS. Pick an OS that will simplify your life (and not complicate it), but modern databases are performant on most OSes.
  • Your database engine. Yes, there are critical differences between MySQL and Oracle and MS SQL, etc. Does it really matter? If there is a particular feature you must have, then by all means, go for it, but ultimately, all will be sufficient with the correctly configured hardware and tables.

Really, pick a database, an OS, a file system, and build your app. With good hardware and good table design (with indexes and optimization), things should go swimmingly.

All that said, here's what I'd pick (only because I'm most familiar with this type of configuration):

  • RHEL / OEL / Ubuntu (OS)
  • Oracle Standard One (or Standard, or EE, depending on your needs) (DB)
  • Ext4 Journaled File System (FS)
  • 2 2xCore Processors (>2.5GHz)
  • 8 GiB Memory (something around 1066 or faster MHz)
  • 1 TiB HDD (SATA, with >=7200rpms)
  • At least gigabit ethernet

Swap out the OS, File system, and DB as desired. Your machine should scream and be just fine for a good number of databases, and is probably a bit overkill. Best thing, though, is that you can always add memory and disk space and upgrade your processors if you need more. If necessary, move to a new box, or add boxes (if your database supports it). (Even better: run your environment as a Virtual Machine/Cluster. Then moving to another physical machine is child's play.)

  • 1
    I'd use Postgres rather than Oracle on a non-Windows platform. It's free of all licensing costs as you scale up. – gbn Jul 3 '11 at 17:18
  • 1
    As I said above, switch DBs as desired. I use Oracle because I know Oracle, and I don't know Postgres. I will use MySQL on occasion, but it is often a bit too twitchy for my tastes. Further, if performance isn't absolutely critical and data size remains < 11GiB, the soon-to-be released 11gR2 XE edition is 100% free of licensing costs. But it will only use 1GiB/RAM and 1 Processor, which is why I didn't suggest it above. – Kerri Shotts Jul 5 '11 at 4:37
  • Thank you for your help. Now I’m wondering if I should consider using a cloud with a key value store like Google’s AppEngine Datastore/Big Table or Microsoft Window’s Azure, That would allow for plenty of room to grow Any opinions on clouds/the best service. Any reasons not to go with one? Sorry this is becoming more open-ended and chatty Thanks. G – anon Jul 6 '11 at 3:12

There isn't enough information..

When you say "thousands of times", is that per week or per second?

If per second, then the stack (OS, DB) you use doesn't matter. It's all about good IO for the logs. eg SSDs. And design+code quality.

If per week, then consider your maximum size. <10GB? Then look at SQL Server Express for Windows. Or Postgres for non-Windows.

And "non-profit" means "on the cheap". In which case, you really have to go Linux+Postgres unless you'd then incur training or support overhead because you're a Windows shop already. But "on the cheap" hits the "pick any 2 of fast, cheap and good" limit (good is size limit here).

I would stay away from MySQL though: IMO is has too many quirks compared to the other popular systems (SQL Server, Oracle, Postgres, even DB2 and Sybase)

  • +1 but SSDs are good for scattered io, not logs which are written sequentially. – Jack Douglas Jul 4 '11 at 8:01
  • SSDs are also very prone to failure; many will die within a year or two if there is heavy read/write cycles. For something that is changing a lot, the more reliable magnetic media is going to be best. Regardless, good backups are going to be critical. – Kerri Shotts Jul 5 '11 at 4:35
  • Sorry to be so slow in responding. I’ve been out of town; My understanding is that MYSQL is free to nonprofits. Is that incorrect? I suspect the DB will exceed 11GB at some point, which makes me think I should consider using a cloud with a key value store like Google’s AppEngine Datastore/Big Table or Microsoft Window’s Azure With something like that, how would you archive data ( that has to be saved for the IRS but is no longer used on a daily basis)? How would you delete it when the IRS requirements expired? Could you just delete a whole year? Thanks. G – anon Jul 6 '11 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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