Timeline for How would you identify slow queries before reaching production?
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 21, 2024 at 4:50 | comment | added | GrandmasterB | @MagnieMozios I'd like to applaud the common sense 'just run it' advice. Also making multiple read-only copies of the db works wonders when reports are running huge and complex SQL queries. Most db's support such replication. | |
| Jan 19, 2024 at 10:42 | comment | added | Matthieu M. | @MagnieMozios: I remember having to optimize a number of SQL queries years ago. It was a massive pain, with the DB changing the order of joins, etc... under my feet. Worse was that the table was partitioned, so it regularly picked an execution plan on a near-empty partition, and then performance would be terrible as the partition would fill up. In the end, I just took the matter in my own hands and hinted each query to death to enforce the use of the one execution plan that scaled well. And since then I've wished for a lower-level query language. | |
| Jan 17, 2024 at 18:08 | comment | added | Magnie Mozios | @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, 2024 at 17:58 | comment | added | davidbak | What is a "read slave"? An read-only live clone database instance or something like that? | |
| Jan 17, 2024 at 16:44 | comment | added | Peter Cordes | 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, 2024 at 16:39 | comment | added | Magnie Mozios | 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, 2024 at 16:34 | comment | added | Magnie Mozios | 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, 2024 at 14:47 | comment | added | Peter Cordes | And I guess I'm wondering how much value there is in checking for tight timings on a very small DB; has that caught problems for you in the past? Like forgetting to create an index for a field or something? | |
| Jan 17, 2024 at 14:47 | comment | added | Peter Cordes | 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. | |
| S Jan 16, 2024 at 17:23 | review | First answers | |||
| Jan 17, 2024 at 0:32 | |||||
| S Jan 16, 2024 at 17:23 | history | answered | Magnie Mozios | CC BY-SA 4.0 |