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

share|improve this question

closed as off topic by MadHatter, growse, John Gardeniers, RobM, gWaldo Jul 31 '12 at 10:19

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
To me, this looks perilously close to a shopping question, which our FAQ opposes. To maximise the chance of getting a useful answer, you may wish to read blog.stackoverflow.com/2010/11/qa-is-hard-lets-go-shopping and consider revising your question. –  MadHatter Jul 31 '12 at 8:25
2  
Does it have to be a relational engine? There are some modern btrieve implementations that are fast. –  Mark Henderson Jul 31 '12 at 8:36
    
@Mark, it doesnt have t be relational. I am open to any other solution. –  Raheel Jul 31 '12 at 8:43
    
@TomO'Connor the problem is that many servers want to dump data on one server. I have the option of only one server. –  Raheel Jul 31 '12 at 8:44
    
Well you your main goal is speed you should pay mo attention to NOSQL databases like Redis, MongoDb etc. –  Boris Ivanov Jul 31 '12 at 9:37

5 Answers 5

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.

share|improve this answer

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.

share|improve this answer
    
I am using linux ext3/4 (CentOS 5 / 6) and NTFS on windows 7 x64 as well. I have been researching on this for the past 2-3 days. And MariaDB does seem to be good. But I am not sure whether ARIA is the thing I require. I just need crash resilience and not all the ACID tests. There should be no loss of data when the server goes down. Also we do maintain UPS, good quality hardware, etc. But a crash can happen even despite these things. –  Raheel Jul 31 '12 at 9:07
    
From what you're saying here, aria looks even more appropriate. Do not use ext3 if write performance is important. Ext4 is IMHO more resillient than XFS, but probably not as fast with multiple concurrent writers. –  symcbean Jul 31 '12 at 10:17

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:

share|improve this answer

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
share|improve this answer
    
I tried batching and it does speed up. I made a single INSERT query for 10 records and results were 10 times faster. But space is still the issue. Its taking 2-2.5 times the space MyISAM is taking. –  Raheel Jul 31 '12 at 8:46
    
@Raheel - try compression. although i think innodb will always take more space. –  pQd Jul 31 '12 at 9:05
    
Yes, compression does help but its still taking more space than it should. –  Raheel Jul 31 '12 at 9:09
    
and how much space SHOULD it take? maybe you dont need indexing and dumping data to the flat file is enough? –  pQd Jul 31 '12 at 9:33
    
I dont need Indexing at all. Dumping to a FLAT file can cause data loss in case of a crash as well right ? I presume that it would be the same as MyISAM if I dump to a flat file ? –  Raheel Jul 31 '12 at 9:36

a very very very fast DB : MemSQL

share|improve this answer
    
Prevayler is in JAVA (I cannot run Java) and MemSQL has licensing issues. I am trying MariaDB and PostgreSQL to see if that helps. –  Raheel Jul 31 '12 at 8:41
    
@mnmnc: The link you posted is from 2002 ... –  Janne Pikkarainen Jul 31 '12 at 8:44
    
:) You are correct. let me fix it.. –  mnmnc Jul 31 '12 at 8:47

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