20

Let's say you are running a query on your database.

In development and staging, where the amount of data is small, everything looks good. Unit and integration tests are also fine and the logic is correct.

But when you get to production, where the database is much bigger, all of a sudden it turns out the query is not efficient and is running very slowly.

So you might add an index, or change the order of the parameters in the query or find a different solution.

How can you identify such a situation before getting to production?

Meaning—just like unit tests enable us to "shift left" the catch of problematic code, how can we also do it with queries? Are there known techniques or suggested frameworks for that?

Some more information:

  1. you can not clone the production database into the development/staging environment
  2. Load tests are less relevant here, as the problem is not due to load on the system (and thus on the database), but the problem is with a certain query that even one such query takes too long.
10
  • 23
    That is the entire purpose of load testing. If your production data are massive, then load testing is not optional. Jan 16 at 8:04
  • 20
    "you can not clone the prod DB into the dev/stage env." This is mostly irrelevant, your job is to find a way to produce a data set in a testing environment which allows the issue to be reproduced. Jan 16 at 9:22
  • 5
    Note that in some cases, even with an exact copy of the production environment, but idle, things may go perfectly, but it then goes awry in production because there is actual load (which may affect caches, locks, or you may have a system that goes from "just under the limit" to "just over"...). Monitoring and instrumentation help. But indeed the first step is to make sure that your queries work correctly on a similar dataset even if idle.
    – jcaron
    Jan 16 at 17:12
  • 1
    Some DB's can log queries that take longer than X. I've used that in MySQL in the past to identify problem queries for exactly this kind of scenario. Jan 16 at 18:45
  • 8
    "you can not clone the prod DB into the dev/stage env." Why not? That's literally the reason why staging environments exist: to be as close as possible to prod so that you can test the system under realistic conditions. If you can't directly clone prod due to sensitive data being in there (financial, health records, etc) you should still be able to use techniques such as anonymization or data generation to make a database that looks a lot like prod's. Why isn't this doable at your employer? Jan 17 at 17:36

6 Answers 6

33

You should have a test system using similar hardware and data sizes as your production database. Sometimes a copy of the production database with any personal/sensitive data anonymized. But it could also be completely generated data, if data is generated you need to be careful to ensure it follows similar patterns as the production database.

It may not be practical or price effective to have a totally identical system to production. But this becomes a tradeoff. The closer to a real production system, the lower the risk of bugs, but at a higher cost. Disk and memory is typically fairly cheap, so it may be feasible to use a realistic database size, but slower disks and fewer CPUs, and assume the number of queries scales linearly with better hardware. But keep in mind that this is just an assumption.

In addition to testing performance of individual queries you may want to test performance under load. So you may need some tools to generate a representative amount of queries.

This can be the staging server, or it could be named the "stress-test" system, or whatever you prefer.

1
  • 9
    Consider running the staging environment with shorter query timeouts than production if the hardware is comparable. That´s a tradeoff, too. Sometimes that could cause unwanted differences in behavior and invalidate some of the testing. Way more often, though, this will help you spot major query performance problems or approaching problems sooner. Jan 16 at 21:06
20

In my personal experience when I have queries that are slow in production, most are SELECT or read queries. When I am building out those queries and I am suspicious of their performance, usually because they have several joins, then I'll just run it manually in production (on a read slave using a read-only account) and then monitor its time and CPU usage.

If it takes more than a couple seconds, then I stop it. Or if I expect it to take a while, like a couple minutes for large aggregation reports, then I'll monitor the CPU usage and kill it if it is likely to affect production traffic. At greater cost, you could have read-slave that doesn't host production traffic.

I also like PostgreSQL's \timing and EXPLAIN and EXPLAIN ANALYZE commands where you can get actual timing information and execution plans. I guess in theory you could write a unit test that checks the execution plans and looks for patterns such as full table scans that are usually the cause of long running queries.

Another option.. Although you can't use production data (I'm guessing it is 100s of GB in size, making it impractical to generate similar quantities for a unit test), you could still generate 1000s of rows and then test the queries. If normal, fast, queries on production are 100 ms, but the same queries on your local machine or test machines test take 5 ms, then you could just require that queries take less than 5 ms.

Those are things I have personally done, but the number of new DB queries my team added were just a few a week and I had a good enough grasp of the database that I could catch potentially bad queries in code reviews before code was deployed. If a bad query was deployed, we had a pretty solid revert process.

If this is something you do constantly and absolutely need an automated process for, then the things I would try (theoretical at this point) is creating some static analysis tests that check queries for:

  • where clauses on foreign keys
  • where clauses on indexes
  • execution plans for full table scans

If you are doing tons of UPDATEs, INSERTs, and/or DELETEs.. then that is a little more complicated and I don't have as much experience in that. The little experience I do have are in situations where the processes take 20 minutes.

9
  • 2
    The 100 ms production / 5 ms local testing advice sort of assumes that different queries will generally scale the same way with database size. As the rest of your answer correctly points out, that might not be the case. I guess it's maybe still useful, and being fast on a small database is probably necessary for it to be fast on a large database, but it's no guarantee of being fast enough in production, so you still need to do the other things. Just wanted to emphasize that point. Jan 17 at 14:47
  • 2
    You are right that the tight timings can be inaccurate. Though, I have caught slow queries by comparing timings. I have run queries that take 50ms locally and seconds on production (when manually run) which was unusually slow. Then once I figured out the correct index it took 5ms locally and then on production the fixed query took 15ms or so. It is just another tool since, as others have mentioned, you won't really know until it is in production. Jan 17 at 16:34
  • 7
    The worst situations I've had are when the execution plans that PostgreSQL comes up with are different locally and in production. Which is due to different statistics on the tables. And then having different execution plans between the first (cold) run and the second (warm) run due to caching and other optimizations. Which points out the "non-deterministic" issues with DBs that @Steve mentioned in his answer. DB optimizations are awesome for workloads, terrible for troubleshooting. Jan 17 at 16:39
  • 3
    Ok cool, so a tight time limit locally doesn't guarantee safety in production, but it can catch some problems early. Sounds like a good idea, especially if it sometimes saves you from going down a dead-end design road that requires a query that can't be made fast enough in production. Jan 17 at 16:44
  • 2
    @davidbak Correct! Just like you are more likely to read a book from a library than to make changes to it, you would create multiple read-only copies so more people can read the book. Nobody likes being in a waitlist. The same principle applies with databases and applications. You create more copies so more people can access the data. Plus, like a library, you don't want people making changes to a copy (hence "read only"). If you make a change, you go to the single "source of truth" copy that you modify and then distribute those changes from there. Jan 17 at 18:08
11

How can you identify such a situation before getting to prod?

I would argue that isn't really possible to do safely, reliably and economically. Instead, you should try to minimize impact of query changes, quickly detect performance regressions and build a software engineering process that allows you to quickly fix issues and regressions.

Use feature flagging to limit new features or changes to just small sample of users. Possibly even to known beta users. You could slowly increase amount of users who have access to the feature, monitor for performance issues and automatically roll back if problems show up.

Modern telemetry systems make it trivial to implement performance monitoring and to identify which part of the code has performance issues.

And having solid automated deployment pipeline, with reliable automated regression test suite should make it possible to quickly deploy fixes to queries or schemas. This would reduce the need to catch issues before they hit production. And with canary releases and good monitoring should reduce need for up-front performance testing.

1
  • 1
    Could you give me some pointers to "modern telemetry systems [that] make it trivial to implement performance monitoring"? Thanks!
    – Pablo H
    Jan 18 at 13:30
5

In fact the querying behaviour of mainstream database engines is practically non-deterministic (from the perspective of a developer), so there is never a test that can decisively identify or anticipate all problematic operation.

The reason the behaviour is non-deterministic is to allow the database engine to automatically adapt to the workload it is applied to from time to time.

The alternative to this automatic adaptation would not only be a lot of additional detailed up-front work which the developer would have to perform, but potentially massive rework every time a change occurred (including not just changes in what we think of as the code or configuration, but even mere changes in data volumes, which can tip the balance of certain algorithms or require different approaches to storage).

It's also very difficult to predict, define, or emulate the exact pattern of concurrent loads that a database engine will experience in production use, and therefore extremely difficult to reproduce in testing how a database engine will react to it.

The timing of everything is often crucial, and brute-forcing an analysis and testing of all possible theoretical conflicts would be both computationally intensive (probably impossibly so) and would not be very informative about what matters.

It's impossible for a developer to predict exactly what the timings of everything will be in production use under every possible current and future circumstance, so even if you had a brute-force analysis of every possible problem, you wouldn't know which problems would actually apply to the real world use in production.

In reality, the design of established database engines incorporates a lot of accumulated experience in real production use, and refinement of the technology from that experience.

The problems themselves are considered fairly essential. That is, they don't arise from poor tooling or inexpert design choices (at least not always), but arise from the very nature of business information systems and from the circumstances they have to cope with.

The need for concurrent working on mutable shared data is really the root of all the difficulty.

5
  • 8
    This is a bunch of facts, but what would you actually suggest the OP does to resolve their issue? Jan 16 at 10:35
  • 3
    @PhilipKendall, I don't suggest any resolution to their issue in their terms, but rather that the OP accept that databases are incapable of being thoroughly covered by tests, and instead accept that they need both expert development who are capable of heuristically avoiding problems, and continued oversight to react to problems as they are discovered in use.
    – Steve
    Jan 16 at 11:04
  • 1
    All true, but the most common performance problems I see post-go live are a) Missing indexes, b) bad queries "fixed" with DISTINCT, implicit conversions between varchar, nvarchar and numerics. All the queries work but with a realistic data set and monitoring you can catch them pre-production regardless of query plan. Their very nature means they will be crap. Jan 18 at 3:46
  • 1
    It's true that these problems are hard, but that's not a reason not to try. Developing a performance test suite and making continual improvements to it based on experience can at least mean you transition from always dealing with issues reactively to sometimes being able to be proactive.
    – James_pic
    Jan 18 at 11:56
  • @James_pic, my counsel in this OPs context (where there are already extensive tests covering the usual matters) is to plan to react, and plan for the need for reaction time. I don't think you've taken me seriously when I say many problems are practically impossible to test for, not just hard.
    – Steve
    Jan 18 at 15:44
2

Most databases are smart at planning queries and will use statistical information from the actual data to do so.

Therefore, if you use a test database with generated dummy data, it is absolutely essential that the size and statistical distribution of this dummy data be a good representation of real world data.

If you test for a small dataset that is not representative of your real data... you will get query plans optimized for your test dataset, which can completely miss the mark. Ironically, the smarter the database, the worse it gets, because query plans can be wildly different depending on data size and statistics.

Example 1: Geographic search

If you generate test data that with random uniform distribution, it is not representative. Searching for points within an area of a certain size will always give more or less the same number of results. In the real world, there are very dense cities and vast empty areas. A better statistical distribution modeling this would allow you to catch performance problems like using a bounding box that is too large on an area that is too densely populated with results.

Example 2: Forum

Likewise if test data is generated with an uniform distribution, with all topics having more or less the same number of posts, you will miss the typical runaway topic with thousands of pages. If it is paginated with a naive "ORDER BY timestamp OFFSET..." it has to scan the whole tens of thousands of posts and skip them in order to display the last one. This also kills your cache. So the most frequently displayed page (the last one) is the slowest, and the topic in question is the one with highest traffic, which is a recipe for bad performance.

In this case it could have been caught earlier by thinking a little bit more: the developer's efforts should be focused on making the most frequently displayed pages (first and last) the fastest, and it's okay if page 500 in a 1000 pages topic is slow to display because no-one will ever display it. In this real world case, the solution was to switch the ORDER BY from ASC to DESC depending on the page number, and tweak OFFSET/LIMIT values accordingly. So for the last page, it would scan the (topic_id, timestamp) index starting from the end, resulting in "OFFSET 0" for the last page, no rows skipped, no cache pollution, and much higher performance.

It's important to add some outliers in the test data, if you have groups or categories there should be one with a much larger size than the others.

If you have TEXT or binary fields allowing near-unlimited length, then these should not all contain "Hello, world" but instead a representative sample of text lengths. Especially if you want to test fulltext search.

The test server should not be overpowered. In fact, if you test with a single user, having an overpowered test server can be misleading, because in real use, server resources are shared between all users. So if it has too much RAM and SSDs, everything's going to be too fast!

If you work on a website, it really helps to have a "developer mode" which displays all the queries done for the page at the bottom, along with timings. The way I implemented it was to allow users with admin rights to impersonate a normal user, so they would see the website exactly as seen by users, plus the query log and other performance information at the bottom of the page.

7
  • 1
    Is this generated using ChatGPT?
    – Dominique
    Jan 18 at 14:20
  • 1
    No it is not generated lol
    – bobflux
    Jan 18 at 14:50
  • @Dominique: One contraindication is posts before December 2022 (on other sites). Perfect spelling and grammar is not impossible for humans (though extremely rare). Jan 18 at 23:43
  • @Dominique what makes you think it was AI generated btw?
    – bobflux
    Jan 19 at 1:03
  • @bobflux: I thought that based on the structure of the answer: first a very readable statement, then some elaboration, and afterwards an even more saying example. I have seen quite some ChatGPT answers, having that same structure. I must admit that I have never seen an A.I. generated answer, containing two examples, so that should have been a warning this answer is not A.I. generated.
    – Dominique
    Jan 19 at 7:09
1

I had a very similar situation a few times in my career. The worst was being told "we don't need to import old data into the new system" until two weeks before we went live, despite me asking for nearly a year. Our home page on that app went from loading in less than a second to taking a minute and forty-five seconds once we had 400k+ base records into the system.

Here's my checklist of what I look for as I build out a project:

  • How many tables are being joined together in a query?
  • Do we have any subqueries in our SELECT statements, especially within columns?
  • When using Entity Framework, or any other ORM, are we including children and collections of children in the queries?
  • Do we have any triggers on the tables when we perform INSERTs or UPDATEs?
  1. In the example I mentioned earlier, the home page was displaying a table that gathered data from FOURTEEN different tables in the database. All of the JOINs were on Primary Key columns, and there weren't inefficient columns being used for those connections.

Their DBA suggested we start throwing a ton of Indexes at the tables. However, remember that having too many Indexes can also slow the tables down because of the complexity of the page structures (especially in SQL Server).

By applying all of their DBA's suggested Indexes, the query time went from 1:45 down to 1:35. Hardly the savings he expected. Instead of throwing indexes at multiple tables, you should take a look at creating a VIEW. What these do is allow the Database Engine to keep the Execution Plan in memory for far longer than the minor optimizations it performs when being sent the same command string repeatedly. Using a VIEW for that homepage to only grab the columns we wanted, and having the full execution plan cached dropped the load time of that homepage back to a single (full) second.

  1. This approach would also apply if you have a complex or subqueried column in a SELECT.

  2. If you happen to notice you're including multiple children, or collections of children, in your queries through an ORM you'll notice just how convoluted the SELECT statements it generates can be. This can cause massive duplication and explode the amount of data being sent back and forth.

In these cases, you'll be far better off to make a separate query for the children using the distinct IDs you get back from those parent objects. Then manually assign the children before returning out of your Data Access layer. It's a bit more work to do it this way, but if you're noticing lots of slowdown, you'll be saving time overall doing the logic yourself instead of letting the ORM handle it for you.

  1. For the most part, you shouldn't need triggers on your tables. Especially if they wind up cascading to other tables that have their own triggers, you're going to drastically increase the time required to write data. If you have other code sending the data to your database, try and do that yourself before saving your data. Granted, this isn't always possible.
3
  • 4
    "the last piece of advice I would recommend would be to use NO LOCK on your SELECT queries ... you won't be preventing other reads or writes while you get what you need" - absolute codswallop and utterly dangerous advice. Not only do reads not typically block other reads, and not only does so-called NOLOCK still involve some locking (including so-called "latching"), but there is almost no case where reading data at that isolation level is desirable.
    – Steve
    Jan 18 at 4:31
  • @Steve Removed that section.
    – krillgar
    Jan 18 at 12:58
  • SQL Server doesn't cache execution plans for views at all. It caches execution plans for statements (which may or may not reference a view) - i.e. if you have a view CREATE VIEW V1 AS SELECT C1, C2 FROM T then there is no meaningful distinction in plan caching behaviour between SELECT C1, C2 FROM T and SELECT * FROM V1 Jan 18 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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