Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on a project that does real-time monitoring of data being transmitted, and logs if there are any of the various type of errors, etc. The CRUD operations are real-time. We initially planned on using PostgreSQL, but the problem we are facing is that PostgreSQL is not fast enough to handle real-time CRUD operations even after tweaking it a little; same goes for MySQL and other biggies. SQLite performs a lot faster than them, but is soon almost dead when the database size reaches a few hundred MBs. Another constraint is that the monitoring is to be done over network.

Is there any database that can handle such fast operations? Or should I opt for a NoSQL database?

EDIT (Regarding design):

The design is normalized to the maximum extent it was possible. The stored data is almost mutually independent, so there are very few joins. Also, I said "a few hundred MBs" just as a reference. The actual databases that we work upon are multiple GBs in size. Lots and lots of data is inserted every second and retrieved also.

Talking about PostgreSQL, it takes 5-7 times the time taken by SQLite in the tests that I ran on my data.

EDIT (Regarding speed):

I'll like to mention a worst-case scenario that can happen.

Suppose that the main application is being used at 10 instances (or PCs). They all interact with a cental DBMS and insert data into it. Now, every single app will have many threads doing some operations on the data that is being transmitted in real-time. The app reports if there is erraneous data in the streaming or not. And since the data is analyzed at packet level, lots of errors can happen in a sec. Based on some very basic calculations, the worst case may require an insertion rate of ~3k rows per second per instance, with each row having some 8-10 associated columns. I tested such a test on my machine(4GB ram, QuadCore), and SQLite is able to do this in ~1 sec over network. I had tweaked PostgreSQL a little, and it did that same in ~5 sec (I'd admit that I did not optimize it a lot, as I am not a pro in the DBMS field). But the bottleneck comes as the db size grows bigger in SQLite; the insertions seemingly go on almost fine, but the reads take a lot of time. I had tested it with a DB size of 3gb myself.

Our major concern is the insertions, which, in worst case will be ~3k per app instance and in average case ~500-1k insertions per instance.

share|improve this question
2  
"choosing a really fast database" -- From the Stackoverflow 'database' tag wiki: The term 'database' should not be confused with Database Management System (DBMS). A database is an organized collection of data. A DBMS is the system software used to create and manage databases and provide users and applications with access to the database. A database is to a DBMS as a document is to a Word Processor. –  onedaywhen Apr 30 '12 at 8:31
    
What is the data flow like & what response times are needed? Just wondering why it needs to be "really fast". –  FreshPhilOfSO Apr 30 '12 at 9:06
    
What is "lots of data inserted every second"? What hardware? –  gbn Apr 30 '12 at 9:18
2  
Why not show us e.g. the explain plan and the PostgreSQL configuration? If you left all configuration properties at the default settings, there is a lot to gain by optimizing it. –  a_horse_with_no_name Apr 30 '12 at 9:20
    
Take a look at This question. Would this type of approach work for you? –  ConcernedOfTunbridgeWells Apr 30 '12 at 10:26
show 1 more comment

4 Answers

If you can't scale a major RDBMS then your database design (includes indexing, queries and the like) or hardware is wrong. The choice of platform is almost irrelevant.

It is that simple. Especially when you mention "few hundred megabytes" which implies low volumes (I mean a few dozen writes per second)

share|improve this answer
    
Edited the question. –  c0da Apr 30 '12 at 8:48
    
+1 - This data volume is not exceptional at all. There are many many many many applications/sites that have a higher concurrent write throughput –  JNK Apr 30 '12 at 15:48
add comment

If you really want fast transactions in your database, make sure that you handle the database correctly. For example, when talking about Oracle it is easy to have it handle 30.000 tps. With a few subtle tweaks it can be brought down to only a few thousand transactions per second.

For a very nice demo look at OLTP Performance - The Trouble with Parsing It all comes down to prevent extra work, re-use connections as often as possible, prepare statements and bind variables. Do this and your database can perform and scale in an optimal way, assuming that your storage can handle the load.

share|improve this answer
add comment

You might want to consider buffering your writes and doing periodic bulk loads. This will be much more efficient on the inserts. If your system users can live with reporting or analysing data that's a few seconds old this will be the best way to load the data by far.

share|improve this answer
add comment

If all DBMS are too slow, that indicate the problem is elsewhere. That could be a hardware problem, have you checked the cpu/ram/disk IO during your tests? Maybe a network issue / limitation.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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