3 ms·

Can you give some refs to make these statements more real? I'm trying to understand whether an in-memory db would be faster than postgres, for instance. What'

by toomim 12y ago

Can you give some refs to make these statements more real? I'm trying to understand whether an in-memory db would be faster than postgres, for instance. What's the postgres I/O scheduler, and is it good? Are there benchmarks somewhere showing the difference? Why does the I/O scheduler make a difference for in-memory vs. disk databases? Is this a subsystem that caches the database in memory? Are you saying that, with proper caching, a disk-based database is as efficient as an in-memory db?

jandrewrogers 12y ago
PostgreSQL is a bit of a hybrid in terms of scheduling. It has its own disk cache but still goes through the kernel mechanisms. Classic database I/O schedulers are not portable so it is a bit of a challenge to implement one in big open source projects for reasons outside of technical ability. With proper cache and I/O scheduler, the same workload will fit in memory, so the only way the disk gets in the way is if the I/O scheduling does something suboptimal with respect to writes (which happens a lot with the kernel caching behavior). Modern database servers typically have more disk bandwidth than network bandwidth and therefore most write workloads should be able to go through storage at the network's wire speed, at least in theory. In practice, I/O scheduling behavior from memory to storage tends to be bursty or poorly timed. Consequently, the instantaneous I/O bandwidth requirements can exceed the effective disk bandwidth for brief periods and performance degrades. A really good database I/O scheduler, cache, and execution engine work together to basically makes sure that peak bandwidth demands to the disk subsystem are never much worse than the network wire speed. Achieving this is not trivial and requires a lot of clever dynamic resource optimization but many sophisticated database engines implement this to some degree or another.
jbergens 12y ago
I think it also depends on how you structure your database and how you use it. For some things a NOSQL design is great and most of them don't have to lock over multiple tables they might be very fast at getting structured records. They might be terrible if you try to join different records (especially if they are located on different shards) and some don't have ACID if that is a requirement. Some databases like Datomic stores information in a completely different way that may be very fast for some use cases. If you have an application or a specific use case you can try a few different databases and measure how they work. I tried a small memory database and got much better performance than SQL Server gave me while the queries were easier to write, for other things this database would not have worked at all or it might have been about as fast as a relational db. As a general rule the memory db providers seems to make the case that they are faster than normal relational databases. Take a look at VoltDb or StarCounter for example.

by toomim 12y ago

jandrewrogers 12y ago

PostgreSQL is a bit of a hybrid in terms of scheduling. It has its own disk cache but still goes through the kernel mechanisms. Classic database I/O schedulers are not portable so it is a bit of a challenge to implement one in big open source projects for reasons outside of technical ability. With proper cache and I/O scheduler, the same workload will fit in memory, so the only way the disk gets in the way is if the I/O scheduling does something suboptimal with respect to writes (which happens a lot with the kernel caching behavior). Modern database servers typically have more disk bandwidth than network bandwidth and therefore most write workloads should be able to go through storage at the network's wire speed, at least in theory. In practice, I/O scheduling behavior from memory to storage tends to be bursty or poorly timed. Consequently, the instantaneous I/O bandwidth requirements can exceed the effective disk bandwidth for brief periods and performance degrades. A really good database I/O scheduler, cache, and execution engine work together to basically makes sure that peak bandwidth demands to the disk subsystem are never much worse than the network wire speed. Achieving this is not trivial and requires a lot of clever dynamic resource optimization but many sophisticated database engines implement this to some degree or another.

jbergens 12y ago

I think it also depends on how you structure your database and how you use it. For some things a NOSQL design is great and most of them don't have to lock over multiple tables they might be very fast at getting structured records. They might be terrible if you try to join different records (especially if they are located on different shards) and some don't have ACID if that is a requirement. Some databases like Datomic stores information in a completely different way that may be very fast for some use cases. If you have an application or a specific use case you can try a few different databases and measure how they work. I tried a small memory database and got much better performance than SQL Server gave me while the queries were easier to write, for other things this database would not have worked at all or it might have been about as fast as a relational db. As a general rule the memory db providers seems to make the case that they are faster than normal relational databases. Take a look at VoltDb or StarCounter for example.