6 ms·

Always test your data operations on data volumes (and user load) that mirror what you expect to see in production. This is important across the entirety of you

by tom_b 15y ago

Always test your data operations on data volumes (and user load) that mirror what you expect to see in production. This is important across the entirety of your dev stack.

mickeyp 15y ago
Indeed. If you are using Oracle you can use its Database Replay to replay the workload with the same characteristics as the production database on your test or dev environment. Pretty nifty stuff, actually.
InclinedPlane 15y ago
One of the biggest problems in database operations is that there are many potential query plans for a given query, but finding the optimal one can be difficult. More so, which query plan is optimal varies depending on table size. If you let the DB pick its own query plan all the time you can end up with inconsistent performance, often by an order of magnitude or more. However, if you turn on query plan stability (every query after the first one will have the same plan) then you can end up with a state where the query plan doesn't change as the data change, which can lead to serious performance issues as a table's size grows (if your table starts out small, the DB may choose a full table scan, which is very fast, but will bog down extremely when the table grows to thousands or millions of row). In short, performance is a hard problem, and if anything the tools for analyzing and optimizing performance for DB operations are well behind the state of the art for compiled code, for example.
- gaius 15y ago
  Oracle is pretty smart about this, if a new plan is slower than the previous one, it seamlessly flips back.
  - politician 15y ago
    JIT compilers have been doing hotspot recompilation for a while now. Is there a reason why databases haven't adopted the same techniques? Is there some barrier to caching multiple query plans optimized for different load scenarios?
    - gaius 15y ago
      What do you mean by "load scenario"? The only thing that should affect the plan is the characteristics of the underlying data (e.g. do we expect to return so many rows that we should full-scan anyway, or can we use an index).
protomyth 15y ago
In some cases, this really isn't possible. Many companies will not spring for the storage (hundreds of terabytes+) or computing power to test with an adquete data set. In some cases, using a copy of the production database, is prohibited due to privacy concerns. I know of one company that was running IBM DB2 EEE that had a 40 node production environment with almost a petabyte of data. I also hear that the USPS database is rather big.
einhverfr 15y ago
And for the record, this was found in staging phase. In other words, we run it through sanity tests in a development environment, it gets rolled out to staging, tested there, and then production. Staging is usually a copy of production a few weeks old at most.

by tom_b 15y ago

Always test your data operations on data volumes (and user load) that mirror what you expect to see in production. This is important across the entirety of your dev stack.

mickeyp 15y ago

Indeed. If you are using Oracle you can use its Database Replay to replay the workload with the same characteristics as the production database on your test or dev environment. Pretty nifty stuff, actually.

InclinedPlane 15y ago

One of the biggest problems in database operations is that there are many potential query plans for a given query, but finding the optimal one can be difficult. More so, which query plan is optimal varies depending on table size. If you let the DB pick its own query plan all the time you can end up with inconsistent performance, often by an order of magnitude or more. However, if you turn on query plan stability (every query after the first one will have the same plan) then you can end up with a state where the query plan doesn't change as the data change, which can lead to serious performance issues as a table's size grows (if your table starts out small, the DB may choose a full table scan, which is very fast, but will bog down extremely when the table grows to thousands or millions of row). In short, performance is a hard problem, and if anything the tools for analyzing and optimizing performance for DB operations are well behind the state of the art for compiled code, for example.

gaius 15y ago
Oracle is pretty smart about this, if a new plan is slower than the previous one, it seamlessly flips back.
- politician 15y ago
  JIT compilers have been doing hotspot recompilation for a while now. Is there a reason why databases haven't adopted the same techniques? Is there some barrier to caching multiple query plans optimized for different load scenarios?
  - gaius 15y ago
    What do you mean by "load scenario"? The only thing that should affect the plan is the characteristics of the underlying data (e.g. do we expect to return so many rows that we should full-scan anyway, or can we use an index).

protomyth 15y ago

In some cases, this really isn't possible. Many companies will not spring for the storage (hundreds of terabytes+) or computing power to test with an adquete data set. In some cases, using a copy of the production database, is prohibited due to privacy concerns. I know of one company that was running IBM DB2 EEE that had a 40 node production environment with almost a petabyte of data. I also hear that the USPS database is rather big.

einhverfr 15y ago

And for the record, this was found in staging phase. In other words, we run it through sanity tests in a development environment, it gets rolled out to staging, tested there, and then production. Staging is usually a copy of production a few weeks old at most.