Best database for inserting records

tags:

database

views:

194

answers:

+2 Q:

Best database for inserting records

What would be the best DB for Inserting records at a very high rate.

The DB will have only one table and the Application is very simple. Insert a row into the DB and commit it but the insertion rate will be very high.

Targetting about 5000 Row Insert per second.

Any of the very expensive DB's like Oracle\SQLServer are out of option.

Also what are the technologies for taking a DB Backup and will it be possible to create one DB from the older backed up DB's ?

I can't use InMemory capabilities of any DB's as I can't afford a crash of the Application. I need to commit the row as soon as I recieve it.

To get high throughput you will need to batch inserts into a big transaction. I really doubt you could find any db that allows you to round trip 5000 times a second from your client.

Sqlite can handle tons of inserts (25K per second in a tran) provided stuff is not too multithreaded and that stuff is batched.

Also, if structure correctly I see no reason why mysql or postgres would not support 5000 rows per second (provided the rows are not too fat). Both MySQL and Postgres are a lot more forgiving to having a larger amount of transactions.

Sam Saffron 2009-06-18 10:56:58

I can't use the InMemory thing of SQLite

Geek 2009-06-18 11:02:10

you don't have to ... those benchmarks are for data written to disk.

Sam Saffron 2009-06-18 11:06:40

I have used SQLite but Information retrieval in SQLite is way too slow once the DB hits 10 million rows. Do you think MySQL would be better ?

Geek 2009-06-18 11:12:18

Yes MySQL scales better and handles multi threading better, just make sure you read up about innodb before starting any mysql work.

Sam Saffron 2009-06-18 11:16:31

-1. OP states "I need to commit the row as soon as I receive it." That's a transaction per row. SQLite performance will be terrible.

finnw 2009-06-18 11:24:08

@finnw, I seriously doubt you can handle 5000 distinct transactions from a client per second, the round trips will kill you.

Sam Saffron 2009-06-18 11:26:13

@finnw - I agree but the Transaction thing will be applicable for all the DB's. Why do you think any other DB would do better.

Geek 2009-06-18 11:27:03

@Geek, theoretically you could have multiple threads inserting against mysql ... nonetheless batching is key to getting high throughput

Sam Saffron 2009-06-18 11:29:24

@Sam - I think we are getting closer to the problem. If I don't commit the row in SQLite, will I be able to get the rows if the System crashes. I know in Oracle you can get back uncommited rows. Will it be possible in SQLite ?

Geek 2009-06-18 11:29:25

@Geek, no way to get back uncommitted data with either sqlite or mysql to the best of my knowledge, I don't know about postgres or firebird, but doubt it.

Sam Saffron 2009-06-18 11:31:08

@Sam - Thanks mate. Will continue to research :-)

Geek 2009-06-18 11:32:20

Why are you against batching stuff?

Sam Saffron 2009-06-18 11:32:59

@Sam, there is no network round trip with SQLite. It's the synchronous disk writes that will kill it. SQLite is affected by this more than some DBMSes (I don't fully understand why, but it is.)

finnw 2009-06-18 11:34:41

@Sam - How will I maintain the Bulk update, I can't afford a DataLoss so can't write things to a File. A poster here tells me that it might be possible to recover data from File too if the System crashes.

Geek 2009-06-18 11:41:08

+1 A:

Postgres provides WAL (Write Ahead Log) which essentially does inserts into RAM until the buffer is full or the system has time to breath. You combine a large WAL cache with a UPS (for safety) and you have very efficient insert performance.

SpliFF 2009-06-18 11:01:02

If you can't do SQLite, I'd take a look at Firebird SQL if I were you.

Galwegian 2009-06-18 11:05:52

How about MySQL ?

Geek 2009-06-18 11:07:02

firebird is way better at multithreading (it allow you to read and write data to the db at the same time as opposed to having a global lock)

Sam Saffron 2009-06-18 11:07:42

@Geek - My experience is that Firebird will outperform MySQL on a properly designed network setup, as well as generating less network traffic. If it's a local setup then you may be better off with MySQL. Good luck anyway.

Galwegian 2009-06-18 11:13:12

Thanks Gal. I would definetely consifer Firebird. I am currently working with SQLite but it is way too slow for reading once the DB reaches a few million rows. Reading the DB however is a rare operation. Please explain "properly designed network setup". ?

Geek 2009-06-18 11:16:51

@Geek is your sqlite table indexed properly?

Sam Saffron 2009-06-18 11:22:38

+2 A:

If your main goal is to insert a lot of data in a little time, perhaps the filesystem is all you need.

Why not write the data in a file, optionally in a DB-friendly format (csv, xml, ...) ? That way you can probably achieve 10 times your performance goal without too much trouble. And most OSs are robust enough nowadays to prevent data loss on application failures.

Edit: As said below, jounaling file systems are pretty much designed so that data is not lost in case of software (or even hardware in case of raid-arrays) failures. ZFS has a good reputation.

Nicolas 2009-06-18 11:34:24

Ok, I could use a File and then do a Bulk Insert. Are you sure that I can recover a File in case of Crash on Linux ?

Geek 2009-06-18 11:42:59

zfs or any of the journaled filesystems handle system crashes pretty gracefully

Sam Saffron 2009-06-18 11:47:14

ansaurus

tags:

views:

answers:

Best database for inserting records

related questions