0

First, I'm asking this question because I'm studying to be a DBA. Recently I learned a lot about database locking, and then some questions arouse in my mind.

Suppose I'm a DBA, I'm in charge of a huge database from a online sales website. Some users reported that the site is slow, and my boss asks me to test the most used queries to see if they are running smooth or not. If I get locking right, if I just run an EXPLAIN ANALYZE on our tb_products I would probably lock it and it can have a negative impact on sales being processed at that time (i.e. some users could not buy their products because I'm "testing" queries).

My question is: how can I properly tests queries in a production database? In my (probably naive) way of thinking, what I need to do is to dump the database and its configurations, create a "sample" database with that and do the tests there. But them, if I'm testing locally on my machine, even with the same configs, numbers will differ, as we're testing on another hardware that's not the server hardware, right? I can't depict how can this tests be done when I think of it. It is probably a silly question, but any help to clarify that would be appreciated.

Improve this question
7
  • Please tag which database system and version.
    – J.D.
    Mar 10, 2022 at 1:13
  • PostgreSQL 13, but my question is about general concepts, not about a specifc database system.
    – André Carvalho
    Mar 10, 2022 at 12:54
  • You're probably better off changing one of your tags to it since tags are used for filtering, and someone filtering on the PostgreSQL tag may miss your question if you don't tag it accordingly.
    – J.D.
    Mar 10, 2022 at 12:55
  • I see. But in fact I just wanna know in general how can I test queries in a production database. My expectation is that, with this general answer, I can get clues about what I should read to get the specifics for Postgres. I think many DBAs here can explain to me how they approach these problem when they face it, like saying "oh, when I need to do this, I use THIS tool or I do THIS stuff". And them I can read about THIS in Postgres.
    – André Carvalho
    Mar 10, 2022 at 12:59
  • 1
    "if I just run an EXPLAIN ANALYZE on our tb_products I would probably lock it" - no. A plain SELECT does not lock a table. In Postgres, readers never block writers and writers never block readers.
    – user1822
    Mar 10, 2022 at 13:51

1 Answer 1

Reset to default
0

My expectation is that you should mimic the real life usage of the production online sales website on HTTP protocol level using a suitable load testing tool which supports HTTP (and maybe other protocols which might be used)

Given you run a stress test like starting with 1 user and gradually increase the load the store website will be generating more and more concurrent queries so you would be able to inspect the slow query log or deadlocks

With regards to where to run the test, it's not a good idea to run the test against a scaled-down environment so if you cannot use the production server for tests (i.e. during night or weekend) you should restore the database backup against the server having the same hardware resources and the database engine configuration as the production one so it would be an exact replica.

Improve this answer

Your Answer

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

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