Open vishnuchalla opened 1 year ago
Why is the max and average duration so much worse? I don't understand why the graphs don't seem to correlate at all to the numbers in the table. It looks like the execution time is worse in the giant-query version.
Why is the max and average duration so much worse? I don't understand why the graphs don't seem to correlate at all to the numbers in the table. It looks like the execution time is worse in the giant-query version.
- Yes there are some queries that ran for long time (bulk ones) which are there in the top twenty list. But the average time in overall is very less when we did gaint query execution. If we look at the average times for all the inserts before and after this change it got reduced from 250 ms to 2.5 ms (In the averge query duration screenshot). The ones which are being listed in the top 20 list as time consuming are just outliers. And also if those queries in overall were running so long, we would have seen IOPS spikes in DB metrics for so long which is not happening in our case (instead it got reduced from 3000 IOPS to 2000 IOPS on an average).
Description
We have changed one of the most time taking insert query into a bulk insert query and noticed positive difference in the DB performance.
POC PR: https://github.com/quay/claircore/pull/996
DB Metrics And Observations
Before this change
Dashboard link here just in case wanted to look at other metrics as well in a sample snapshot.
After this change
Dashboard link here just in case wanted to look at other metrics as well in a sample snapshot.
If we look at the spikes after this bulk insert changes they are very narrowed and even the IOPS got decreased by 1000s and also lead to better performance on the Clair App side.(i.e reducing the IO wait time of the requests). This will ultimately help us reduce cost from both Clair DB and App perspective.
Profiling Report And Observations
Before this change. Complete report here
After this change. Complete report here
If we look at the average query execution times, there is a 100 fold difference after moving to a bulk insert strategy (i.e. 250 ms to 2.5 ms). And also the time taken for running overall queries got reduced along with the number of queries running on the DB ultimately resulting in less IOPS on DB. (This will keep DB less busy allowing more incoming requests to be served and also will help us reduce those spikes seen in both Clair App and DB)
Summary And Conclusion
OR
To have below action items on PostgreSQL.
Moving from single INSERT/UPDATE/DELETE statements to bulk INSERT/UPDATE/DELETE statement where in a single statement acts on multiple rows of the table. Supporting posts and example snippets to perform bulk operations: