RE: minimizing pg_stat_statements performance overhead
От | Raymond Martin |
---|---|
Тема | RE: minimizing pg_stat_statements performance overhead |
Дата | |
Msg-id | BN8PR21MB1217BCB209C027F3B42E09A6B1580@BN8PR21MB1217.namprd21.prod.outlook.com обсуждение исходный текст |
Ответы |
RE: minimizing pg_stat_statements performance overhead
(legrand legrand <legrand_legrand@hotmail.com>)
RE: minimizing pg_stat_statements performance overhead (Fabien COELHO <coelho@cri.ensmp.fr>) |
Список | pgsql-hackers |
Hi Fabien, Thank you for your time. Apologies for not being more specific about my testing methodology. > > PGSS not loaded: 0.18ms > > This means 0.0018 ms latency per transaction, which seems rather fast, on my laptop I have typically 0.0XX ms... This actually means 0.18 milliseconds. I agree that this is a bit high, so I instead created an Ubuntu VM to get resultsthat would align with yours. > I could not reproduce these results on my ubuntu laptop. Could you be more precise about the test? Did you use pgbench?Did it run in parallel? What options were used? What is the test script? I did not use pgbench. It is important to call pg_stat_statements_reset before every query. This simulates a user that isperforming distinct and non-repeated queries on their database. If you use prepared statements or the same set of querieseach time, you would remove the contention on the pgss query text file. I re-tested this on an Ubuntu machine with 4cores and 14GB ram. I did not run it in parallel. I used a python script thatimplements the follow logic: - select pg_stat_statements_reset() -- this is important because we are making pgss treat the 'select 1' like a new querywhich it has not cached into pgss_hash. - time 'select 1' Repeat 100 times for each configuration. Here are my Ubuntu results: pgss unloaded Mean: 0.076 Standard Deviation: 0.038 pgss.track=none Mean: 0.099 Standard Deviation: 0.040 pgss.track=top Mean: 0.098 Standard Deviation: 0.107 pgss.track=none + patch Mean: 0.078 Standard Deviation: 0.042 The results are less noticeable, but I still see about a 20% performance improvement here. > There I have an impact of 10% in these ideal testing conditions wrt latency where the DB does basically nothing, thus whichwould not warrant to disable pg_stat_statements given the great service this extension brings to performance analysis. I agree that pg_stat_statements should not be disabled based on these performance results. > Note that this does not mean that the patch should not be applied, it looks like an oversight, but really I do not havethe performance degradation you are suggesting. I appreciate your input and I want to come up with a canonical test that makes this contention more obvious. Unfortunately, it is difficult because the criteria that causes this slow down (large query sizes and distinct non-repeatedqueries) are difficult to reproduce with pgbench. I would be open to any suggestions here. So even though the performance gains in this specific scenario are not as great, do you still think it would make sense tosubmit a patch like this? -- Raymond Martin ramarti@microsoft.com Azure Database for PostgreSQL
В списке pgsql-hackers по дате отправления:
Следующее
От: legrand legrandДата:
Сообщение: RE: minimizing pg_stat_statements performance overhead