Fast Database to store 1 Billion records [closed]

Question

I am writing a code to store data and want to store 1 Billion to 10 Billion records or even more. Most importantto this is speed (nearly 10000 - 20000 / records per second), SPACE usage and CRASH proofing. Now I have been playing with MySQLs MyISAM and it works pretty FAST and matches the speed required for us. MyISAM also has optimal SPACE usage and the database file in the MySQL data folder was nearly equal to the amount of data I inserted e.g. for 100000 records of 30 charaters each, it took only 3 MB. But the only problem is that it is not ACID and can cause data loss on crashes.

So I wanted to know of any alternatives (I dont even mind changing the Database software to anything else) which can help us to store so much data and store them FAST.

I have already tried MySQL INNODB and its inserts are very slow. I could only achieve 800 - 1000 / records per second. Also the amount of space to store data in INNODB is pretty high. It takes nearly 8 MB to store the same 100000 records of 30 chars each.

I also tried MongoDB as an alternative, but it again requires TOO much space to store the same data.

Even SQLITE3 DB is VERY VERY SLOW to write 100000 records. I tried this with PHP in a for loop. It also missed some 20 records and gave errors of database unavailable.

So I wanted to know of any open source Database software which can meet my requirements. I heard that MariaDBs ARIA is ACID compliant. Is it true ? Or is PostgreSQL also a good fast, option with optimal space utilization.

I am open to any solution which is FAST, utilizes optimal space and also is CRASH proof. Looking forward to replies.

=== UPDATE on MariaDB Aria === Its even bad than InnoDB when it comes to Insert speed and Space. It took 1.5 times the amount of time than InnoDB to insert the same data and nearly 2.5 times space used by MyISAM.

Answer 1

This is not the kind of thing anyone can 100% 'nail' in 5 minutes but if you want to achieve that level of performance and functionality consistently then I strongly suspect you're going to have to spend money.

If you have budget for this then I'd look to combine Oracle's 'TimesTen' engine, possibly with either enterprise SSD or PCIe Flash (such as FusionIO) and if your going that far them I'd recommend going with Intel's Xeon E7-xxxx series as they have fantastic RRAS capabilities to keep your server up and running as long as possible in the event of failures.

Answer 2

If getting more features and beter performance was simply a matter of using different software, then everyone would be doing it (at least the few people still wotking in IT ;)

If you need ACID support - then why try mongoDB?

It's quite possible to get the throughput you want with innodb (but myisam is faster) - provided you spend enough on hardware and tune the system properly. But that your first constraint is to minimise space usage, this rather implies that you don't have the budget to go and buy a SSD RAID set.

can cause data loss on crashes

I've seen data losses on crashes in Oracle and Sybase - but yes, in theory, these should be more resillient. I would usually spend some time, effort and money on avoiding the crash in the first place (dedicated server, using reasonable quality hardware, UPS, stable software).

Even SQLITE3 DB is VERY VERY SLOW

SQLite is particularly sensitive to the underlying filesystem (but this will affect all databases). That you don't mention what filesystem (or even which Operating System!) this is running on makes me think you've not spent a lot of time looking at tuning the installation.

If it were me I'd have a look at MariaDB - it's a fork of mysql and includes an improved ISAM engine (Aria) which has some performance benefits and supports transactions. (running on linux, using xfs with nobarriers and the deadline scheduler).

But you'll need to look at your hardware to really get it flying.

Answer 3

From my experience InnoDB (if properly configured and used) is only a bit slower than MyISAM (certainly not an order of magnitude). A couple of suggestions come to my mind to improve performance:

• You should be using the latest versions of MySQL InnoDB storage engine.
• You should be using a large buffer pool size (~80% available RAM) and a large log file size.
• You should be using transactions and batch inserts to get a high throughput. Batch size is an important performance factor (not too small but not too large either).
• You should be using AUTOINCREMENT id as PRIMARY KEY. Any indexes will lower your insert speed, especially UNIQUE.
• You should be using the lowest transaction isolation level that works for you.
• Tune innodb_flush_method for your system.
• If you don't require full ACID guarantees, look into changing the value for innodb_flush_logs_at_trx_commit
• Use proper hardware, tune your OS/storage/file system

You will find good pointers here:

• http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb.html
• http://www.oracle.com/partners/en/knowledge-zone/mysql-5-5-innodb-myisam-522945.pdf
• http://stackoverflow.com/questions/2194914/why-is-mysql-innodb-so-much-slower-at-full-table-scans-than-myisam

Answer 4

there are plenty of parameters you can use to tweak innodb. myisam for that amount of the data is unsuitable - eg it requires repair after a crash.

take a look at mysqltuner or percona's sql configuration wizard; throw a lot of ram and fast io-subsystem on it and innodb can give you very decent performance.

innodb allows on the fly compression of the data. it'll not make things faster but can save you some storage space.

regardless of selected db engine:

• try batching your inserts, if possible have a queuing system between data producer and the database
• think about sharding your data
• think about parallelising your insert process. it'll not help you with myisam but can give you better performance with innodb on mysql and other database servers

Answer 5

a very very very fast DB : MemSQL