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.