3 ms·

What you're describing is the common symptom among many programmers which is their unfamiliarity with disk limitations. It's not the database's fault for not be

What you're describing is the common symptom among many programmers which is their unfamiliarity with disk limitations. It's not the database's fault for not being able to write 10M rows to the disk quickly. Disks, especially the cloud SSDs are quite slow defaulting very often to just 3000 write IOPS. On the other hand, programmers are very familiar with CPU and memory limitations. Very rarely will a programmer try to hold 10M or 1B rows in memory because they intuitively understand memory limitations which is usually around 4GB per app/process.

href 4y ago
This. I moved multiple web applications from bare-metal to the cloud, and many times had to refactor code to do hit the disk less. What worked before: Dozens of SQL queries, or loading session data from a file - for each request - it all was too slow in the cloud. Also: I find that many engineers wouldn't be able to tell you if the storage attached to their VMs was networked, or actual hardware on the hypervisor. A distinction that has big implications for throughput and latency.
doctor_eval 4y ago
Another thing I’ve seen (perhaps even been guilty of :) is assuming we don’t have to worry about our table or index structure. Inserting 10 million narrow rows of integers and enums into a database with just a Pkey index will be super fast. Inserting 10mm wide rows containing json blobs and long text strings will be bad. Inserting 10mm json blobs with a bunch of indexes will be horrible. I’ve been as guilty as anyone for thinking the database can work miracles and that I don’t have to worry about how the underlying hardware works. But it’s that lack of care that is at fault, not the database engine. It’s just doing what you told it to do.
jandrewrogers 4y ago
Writing millions of rows per second through a database on somewhat arbitrary cloud VMs is pretty normal. This isn’t an intrinsic disk limitation. IOPS doesn’t map that closely to write performance in many modern databases; an IOPS can carry a lot of logical writes. Storage bandwidth is the main limitation, which isn’t great with ubiquitous disaggregated storage in particular, but still sufficient for writing millions of rows per second without doing anything too special. Obviously this is somewhat dependent on the specific data model, but most rows are pretty small.
bob1029 4y ago
> It's not the database's fault for not being able to write 10M rows to the disk quickly. It usually is though. Being able to write 10m rows quickly is entirely a matter of how the engine is designed to work and the required durability semantics. Some designs can write 10m rows to magnetic storage in a few seconds. Others might take minutes. The most ideal designs (from a raw throughput perspective) leverage batching and can write multiple transactions per disk IO operation.
vouwfietsman 4y ago
Actually what I'm describing is exactly the opposite: because I know the limitations of the disk, if I would have to write custom code to solve my problem, I would easily be able to outperform the database. Naturally that has many other issues that still makes me choose a database, like basically all of ACID, but still it is disheartening to know that fundamentally the operation can be done an order of magnitude faster than what you're seeing, and improving the situation means understanding how the DB maps its operations to the disk, and how that produces limitations. So in fact it is the lack of knowledge of the DB, not disk, that causes the problem. Apparently at this scope, the DB is a leaky abstraction.
KronisLV 4y ago
> It's not the database's fault for not being able to write 10M rows to the disk quickly. I am primarily saying this in regards to reads, but sometimes the database management system is indeed at fault, by picking sub-optimal ways to do things. For example, I had an Oracle query that took 45 minutes to execute and I was called in to help out with it. This drastic slowdown hadn't been alleviated by reviewing what indices are in use, nor partitioning the table, however I did a bit of research and was able to optimize the execution to 3 seconds, with the NO_UNNEST optimizer hint: https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm#26730 https://docs.oracle.com/cd/B12037_01/server.101/b10752/hints... for an EXISTS check. Thankfully cases like that are somewhat rare, but I'd still generally pick boring and battle tested DBMSes for most of my use cases (maybe along the lines of PostgreSQL and MariaDB for a variety of reasons, or even SQLite for simpler cases), as opposed to newer ones. But when dealing with performance considerations, then it indeed is a good idea to look at whatever metrics are available, to see whether it's the hardware or the software that's responsible for certain characteristics. Of course, for some workloads Redis or RabbitMQ might be better suited, if available (though I've seen key-value stores and message queues implemented in SQL that worked fine, at least up to a point).
- ludston 4y ago
  If you have queries with joins or subqueries, and you have filters on multiple tables in the query, the DBMS has to make a best guess at order in which to execute the query. If you are joining, for example, two very large tables and there is a filter on each, if the first table has 10 matching rows and the second table has 1 billion, you would hope the execution plan will filter by the first table first, but the DBMS may not know how many rows match each filter unless there are appropriate column statistics, so it may execute the query in the other order. 3 seconds is often horrible performance for an sql query (although it may be "good enough" for your use-case), and implies hundreds of thousands or millions of comparisons during execution, which implies that the query is not covered well by indexes. In short, if you understand the shape of the data in the DBMS better than the database does, then you ought to provide index hints and order hints so that it can do as well as it can. (And if this particular multi-table filter is something that is run by users multiple times a minute, you should be investigating multi-table indexes, denormalising tables, or some third party external index like cdc into lucene.
ttfkam 4y ago
> Disks, especially the cloud SSDs are quite slow defaulting very often to just 3000 write IOPS. And then there's cloud solutions like AWS Aurora that blow those disk IO issues out of the water.

href 4y ago

This. I moved multiple web applications from bare-metal to the cloud, and many times had to refactor code to do hit the disk less. What worked before: Dozens of SQL queries, or loading session data from a file - for each request - it all was too slow in the cloud. Also: I find that many engineers wouldn't be able to tell you if the storage attached to their VMs was networked, or actual hardware on the hypervisor. A distinction that has big implications for throughput and latency.

doctor_eval 4y ago

Another thing I’ve seen (perhaps even been guilty of :) is assuming we don’t have to worry about our table or index structure. Inserting 10 million narrow rows of integers and enums into a database with just a Pkey index will be super fast. Inserting 10mm wide rows containing json blobs and long text strings will be bad. Inserting 10mm json blobs with a bunch of indexes will be horrible. I’ve been as guilty as anyone for thinking the database can work miracles and that I don’t have to worry about how the underlying hardware works. But it’s that lack of care that is at fault, not the database engine. It’s just doing what you told it to do.

jandrewrogers 4y ago

Writing millions of rows per second through a database on somewhat arbitrary cloud VMs is pretty normal. This isn’t an intrinsic disk limitation. IOPS doesn’t map that closely to write performance in many modern databases; an IOPS can carry a lot of logical writes. Storage bandwidth is the main limitation, which isn’t great with ubiquitous disaggregated storage in particular, but still sufficient for writing millions of rows per second without doing anything too special. Obviously this is somewhat dependent on the specific data model, but most rows are pretty small.

bob1029 4y ago

> It's not the database's fault for not being able to write 10M rows to the disk quickly. It usually is though. Being able to write 10m rows quickly is entirely a matter of how the engine is designed to work and the required durability semantics. Some designs can write 10m rows to magnetic storage in a few seconds. Others might take minutes. The most ideal designs (from a raw throughput perspective) leverage batching and can write multiple transactions per disk IO operation.

vouwfietsman 4y ago

Actually what I'm describing is exactly the opposite: because I know the limitations of the disk, if I would have to write custom code to solve my problem, I would easily be able to outperform the database. Naturally that has many other issues that still makes me choose a database, like basically all of ACID, but still it is disheartening to know that fundamentally the operation can be done an order of magnitude faster than what you're seeing, and improving the situation means understanding how the DB maps its operations to the disk, and how that produces limitations. So in fact it is the lack of knowledge of the DB, not disk, that causes the problem. Apparently at this scope, the DB is a leaky abstraction.

KronisLV 4y ago

> It's not the database's fault for not being able to write 10M rows to the disk quickly. I am primarily saying this in regards to reads, but sometimes the database management system is indeed at fault, by picking sub-optimal ways to do things. For example, I had an Oracle query that took 45 minutes to execute and I was called in to help out with it. This drastic slowdown hadn't been alleviated by reviewing what indices are in use, nor partitioning the table, however I did a bit of research and was able to optimize the execution to 3 seconds, with the NO_UNNEST optimizer hint: https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm#26730 https://docs.oracle.com/cd/B12037_01/server.101/b10752/hints... for an EXISTS check. Thankfully cases like that are somewhat rare, but I'd still generally pick boring and battle tested DBMSes for most of my use cases (maybe along the lines of PostgreSQL and MariaDB for a variety of reasons, or even SQLite for simpler cases), as opposed to newer ones. But when dealing with performance considerations, then it indeed is a good idea to look at whatever metrics are available, to see whether it's the hardware or the software that's responsible for certain characteristics. Of course, for some workloads Redis or RabbitMQ might be better suited, if available (though I've seen key-value stores and message queues implemented in SQL that worked fine, at least up to a point).

ludston 4y ago
If you have queries with joins or subqueries, and you have filters on multiple tables in the query, the DBMS has to make a best guess at order in which to execute the query. If you are joining, for example, two very large tables and there is a filter on each, if the first table has 10 matching rows and the second table has 1 billion, you would hope the execution plan will filter by the first table first, but the DBMS may not know how many rows match each filter unless there are appropriate column statistics, so it may execute the query in the other order. 3 seconds is often horrible performance for an sql query (although it may be "good enough" for your use-case), and implies hundreds of thousands or millions of comparisons during execution, which implies that the query is not covered well by indexes. In short, if you understand the shape of the data in the DBMS better than the database does, then you ought to provide index hints and order hints so that it can do as well as it can. (And if this particular multi-table filter is something that is run by users multiple times a minute, you should be investigating multi-table indexes, denormalising tables, or some third party external index like cdc into lucene.

ttfkam 4y ago

> Disks, especially the cloud SSDs are quite slow defaulting very often to just 3000 write IOPS. And then there's cloud solutions like AWS Aurora that blow those disk IO issues out of the water.