PG 12 slow selects from pg_settings
Dear PostgreSQL community,
we have noticed a severe decrease in performance reading
pg_catalog.pg_settings table in PostgreSQL 12 on MS Windows 10 machines
compared to earlier versions.
```
explain (analyze, buffers, timing)
SELECT * from pg_catalog.pg_settings where name =
'standard_conforming_strings';
```
On *PostgreSQL 12.5, compiled by Visual C++ build 1914, 64-bit:*
Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5
width=485) (actual time=343.350..343.356 rows=1 loops=1)
Filter: (name = 'standard_conforming_strings'::text)
Rows Removed by Filter: 313
Planning Time: 0.079 ms
Execution Time: 343.397 ms
Compare to* PostgreSQL 11.13, compiled by Visual C++ build 1914, 64-bit*:
Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5
width=485) (actual time=0.723..0.728 rows=1 loops=1)
Filter: (name = 'standard_conforming_strings'::text)
Rows Removed by Filter: 289
Planning Time: 0.125 ms
Execution Time: 0.796 ms
This is standard installation, the changed parameters are:
```
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
```
client_encoding UTF8 client
DateStyle ISO, YMD client
default_text_search_config pg_catalog.simple session
default_transaction_isolation read committed session
dynamic_shared_memory_type windows configuration file
extra_float_digits 3 session
lc_messages Lithuanian_Lithuania.1257 configuration file
lc_monetary Lithuanian_Lithuania.1257 configuration file
lc_numeric Lithuanian_Lithuania.1257 configuration file
lc_time Lithuanian_Lithuania.1257 configuration file
listen_addresses * configuration file
log_destination stderr configuration file
log_file_mode 0640 configuration file
log_timezone Europe/Helsinki configuration file
logging_collector on configuration file
max_connections 100 configuration file
max_stack_depth 2MB environment variable
max_wal_size 1GB configuration file
min_wal_size 80MB configuration file
port 5444 configuration file
search_path "$user", public session
shared_buffers 128MB configuration file
TimeZone Europe/Helsinki client
The slowing down is observed on *MS Windows 10 machines only*. We have pg12
on linux (PostgreSQL 12.6 (Debian 12.6-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit ) that doesn't show any
decrease in performance.
I've testet different versions and it seems the problem appeared on PG12,
earlier versions up to PG11 work ok. PG13 also suffers from low reading
speed of pg_settings.
The behaviour is reproduced on 3 different machines (2 virtual and one
physical, different hardware).
What is the cause of this? How to fix the issue?
Regards,
Julius Tuskenis
Re: PG 12 slow selects from pg_settings
Em sex., 8 de out. de 2021 às 04:01, Julius Tuskenis < [email protected]> escreveu: > Dear PostgreSQL community, > > we have noticed a severe decrease in performance reading > pg_catalog.pg_settings table in PostgreSQL 12 on MS Windows 10 machines > compared to earlier versions. > > ``` > explain (analyze, buffers, timing) > SELECT * from pg_catalog.pg_settings where name = > 'standard_conforming_strings'; > ``` > > On *PostgreSQL 12.5, compiled by Visual C++ build 1914, 64-bit:* > Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5 > width=485) (actual time=343.350..343.356 rows=1 loops=1) > Filter: (name = 'standard_conforming_strings'::text) > Rows Removed by Filter: 313 > Planning Time: 0.079 ms > Execution Time: 343.397 ms > You can try 12.8 which is available now, there is a dll related fix that can make some improvement. regards, Ranier Vilela
Re: PG 12 slow selects from pg_settings
Thank you, Ranier, v12.8 has improved the performance PostgreSQL 12.8, compiled by Visual C++ build 1914, 64-bit: ``` Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5 width=485) (actual time=7.122..7.128 rows=1 loops=1) Filter: (name = 'standard_conforming_strings'::text) Rows Removed by Filter: 313 Planning Time: 0.083 ms Execution Time: 7.204 ms ``` Would you please direct me to the change log or some bug report to read in detail what was causing the problem and how it was fixed? Regards, Julius Tuskenis 2021-10-08, pn, 14:01 Ranier Vilela rašė: > Em sex., 8 de out. de 2021 às 04:01, Julius Tuskenis < > [email protected]> escreveu: > >> Dear PostgreSQL community, >> >> we have noticed a severe decrease in performance reading >> pg_catalog.pg_settings table in PostgreSQL 12 on MS Windows 10 machines >> compared to earlier versions. >> >> ``` >> explain (analyze, buffers, timing) >> SELECT * from pg_catalog.pg_settings where name = >> 'standard_conforming_strings'; >> ``` >> >> On *PostgreSQL 12.5, compiled by Visual C++ build 1914, 64-bit:* >> Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5 >> width=485) (actual time=343.350..343.356 rows=1 loops=1) >> Filter: (name = 'standard_conforming_strings'::text) >> Rows Removed by Filter: 313 >> Planning Time: 0.079 ms >> Execution Time: 343.397 ms >> > You can try 12.8 which is available now, there is a dll related fix that > can make some improvement. > > regards, > Ranier Vilela >
Re: PG 12 slow selects from pg_settings
Em sex., 8 de out. de 2021 às 09:06, Julius Tuskenis < [email protected]> escreveu: > Thank you, Ranier, > > v12.8 has improved the performance > > PostgreSQL 12.8, compiled by Visual C++ build 1914, 64-bit: > ``` > Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5 > width=485) (actual time=7.122..7.128 rows=1 loops=1) > Filter: (name = 'standard_conforming_strings'::text) > Rows Removed by Filter: 313 > Planning Time: 0.083 ms > Execution Time: 7.204 ms > ``` > > Would you please direct me to the change log or some bug report to read in > detail what was causing the problem and how it was fixed? > The history is long, but if you want to read. https://www.postgresql.org/message-id/flat/7ff352d4-4879-5181-eb89-8a2046f928e6%40dunslane.net regards, Ranier Vilela
Re: PG 12 slow selects from pg_settings
> The history is long, but if you want to read. > https://www.postgresql.org/message-id/flat/7ff352d4-4879-5181-eb89-8a2046f928e6%40dunslane.net Thank you, Ranier. It's amazing how much effort and work that issue caused! Thank You and all involved! Regards, Julius Tuskenis 2021-10-08, pn, 15:50 Ranier Vilela rašė: > Em sex., 8 de out. de 2021 às 09:06, Julius Tuskenis < > [email protected]> escreveu: > >> Thank you, Ranier, >> >> v12.8 has improved the performance >> >> PostgreSQL 12.8, compiled by Visual C++ build 1914, 64-bit: >> ``` >> Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5 >> width=485) (actual time=7.122..7.128 rows=1 loops=1) >> Filter: (name = 'standard_conforming_strings'::text) >> Rows Removed by Filter: 313 >> Planning Time: 0.083 ms >> Execution Time: 7.204 ms >> ``` >> >> Would you please direct me to the change log or some bug report to read >> in detail what was causing the problem and how it was fixed? >> > The history is long, but if you want to read. > > https://www.postgresql.org/message-id/flat/7ff352d4-4879-5181-eb89-8a2046f928e6%40dunslane.net > > regards, > Ranier Vilela >
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On Thu, Oct 7, 2021 at 10:22:12PM -0700, Jeremy Schneider wrote: > > On Oct 7, 2021, at 19:38, Bruce Momjian wrote: > > Our wait events reported in pg_stat_activity are really only a first > > step --- I always felt it needed an external tool to efficiently > > collect and report those wait events. I don't think the server log > > is the right place to collect them. > > What would you think about adding hooks to the functions I mentioned, > if someone wrote an open source extension that could do things with > the wait event start/stop times in a preload library? (I am adding Alexander Korotkov to this email since he worked on wait events.) The original goal was to implement wait event reporting in a way that could always be enabled, and that was successful. I thought trying to do anything more than that in the server by default would add unacceptable overhead. So the big question is how do we build on the wait events we already have? Do we create an external tool, do it internally in the database, or a mix? Is additional wait event detail needed and that can be optionally enabled? It would be good to see what other tools are using wait events to get an idea of what use-cases there are for Postgres. > But we could use parameters too, that’s another gap. For example > - which buffer, object, etc for buffer_content? Which filenode and > block for an IO? Which relation OID for a SQL lock? Then you can find > which table, whether the hot block is a root or leaf of a btree, > etc. This can be done by extending the wait infra to accept two or > three arbitrary “informational” parameters, maybe just numeric for > efficiency, or maybe string, and each individual wait event can decide > what to do with them. We’d want to pass that info out over the hooks > too. This is another reason to support wait event tracing in the DB - > sometimes it might be difficult to get all the relevant context with a > kernel probe on an external tool. I think a larger question is what value will such information have for Postgres users? > Sent from my TI-83 I was an SR-52 guy in my teens. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On Thu, Oct 7, 2021 at 11:35:16PM -0400, Mladen Gogala wrote: > > On 10/7/21 22:15, Jeremy Schneider wrote: > There is an extension which does wait event sampling: > > https://github.com/postgrespro/pg_wait_sampling > > It's one of the Postgres Pro extensions, I like it a lot. Postgres Pro is > getting very popular on the Azure cloud. It's essentially Microsoft response > to Aurora. Also EnterpriseDB has the event interface and the views analogous > to Oracle: edb$session_wait_history, edb$session_waits and edb$system_waits > views are implementing the event interface in Edb. You can look them up in > the documentation, the documentation is available on the web. The foundation > is already laid, what is needed are the finishing touches, like the detailed > event documentation. I am currently engaged in a pilot porting project, Ah, this is exactly what I wanted to know --- what people are using the event waits for. Can you tell if these are done all externally, or if they need internal database changes? > I agree with you about the logging capacity. Postgres is very loquacious > when it comes to logging. I love that feature because pgBadger reports are > even better than the AWR reports. Oracle is very loquacious and verbose too. Nice, I had not heard that before. > As for the "tracing vs. sampling" debate, Oracle has both. > V$ACTIVE_SESSION_HISTORY is a sampling view. Sampling views are more > practical, especially when there are pooled connections. Personally, I would > prefer sampling. Yes, slide 101 here: https://momjian.us/main/writings/pgsql/administration.pdf#page=101 shows the Postgres monitoring options for reporting and alterting/aggegation. Yes, both are needed for wait event, and right now we really don't have either for wait events --- just the raw information. However, I also need to ask how the wait event information, whether tracing or sampling, can be useful for Postgres because that will drive the solution. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
Bruce Momjian schrieb am 08.10.2021 um 17:21: However, I also need to ask how the wait event information, whether tracing or sampling, can be useful for Postgres because that will drive the solution. I guess everyone will use that information in a different way. We typically use the AWR reports as a post-mortem analysis tool if something goes wrong in our application (=customer specific projects) E.g. if there was a slowdown "last monday" or "saving something took minutes yesterday morning", then we usually request an AWR report from the time span in question. Quite frequently this already reveals the culprit. If not, we ask them to poke in more detail into v$session_history. So in our case it's not really used for active monitoring, but for finding the root cause after the fact. I don't know how representative this usage is though. Thomas
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On Fri, Oct 8, 2021 at 05:28:37PM +0200, Thomas Kellerer wrote: > Bruce Momjian schrieb am 08.10.2021 um 17:21: > > However, I also need to ask how the wait event information, whether > > tracing or sampling, can be useful for Postgres because that will drive > > the solution. > > I guess everyone will use that information in a different way. > > We typically use the AWR reports as a post-mortem analysis tool if > something goes wrong in our application (=customer specific projects) > > E.g. if there was a slowdown "last monday" or "saving something took minutes > yesterday morning", > then we usually request an AWR report from the time span in question. Quite > frequently > this already reveals the culprit. If not, we ask them to poke in more detail > into v$session_history. > > So in our case it's not really used for active monitoring, but for > finding the root cause after the fact. > > I don't know how representative this usage is though. OK, that's a good usecase, and something that certainly would apply to Postgres. Don't you often need more than just wait events to find the cause, like system memory usage, total I/O, etc? -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On Fri, Oct 8, 2021 at 11:40 PM Bruce Momjian wrote: > > On Fri, Oct 8, 2021 at 05:28:37PM +0200, Thomas Kellerer wrote: > > > > We typically use the AWR reports as a post-mortem analysis tool if > > something goes wrong in our application (=customer specific projects) > > > > E.g. if there was a slowdown "last monday" or "saving something took > > minutes yesterday morning", > > then we usually request an AWR report from the time span in question. Quite > > frequently > > this already reveals the culprit. If not, we ask them to poke in more > > detail into v$session_history. > > > > So in our case it's not really used for active monitoring, but for > > finding the root cause after the fact. > > > > I don't know how representative this usage is though. > > OK, that's a good usecase, and something that certainly would apply to > Postgres. Don't you often need more than just wait events to find the > cause, like system memory usage, total I/O, etc? You usually need a variety of metrics to be able to find what is actually causing $random_incident, so the more you can aggregate in your performance tool the better. Wait events are an important piece of that puzzle. As a quick example for wait events, I recently had to diagnose some performance issue, which turned out to be some process reaching the 64 subtransactions with the well known consequences. I had pg_wait_sampling aggregated metrics available so it was really easy to know that the slowdown was due to that. Knowing what application exactly reached those 64 subtransactions is another story.
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
Bruce Momjian schrieb am 08.10.2021 um 17:40: I guess everyone will use that information in a different way. We typically use the AWR reports as a post-mortem analysis tool if something goes wrong in our application (=customer specific projects) E.g. if there was a slowdown "last monday" or "saving something took minutes yesterday morning", then we usually request an AWR report from the time span in question. Quite frequently this already reveals the culprit. If not, we ask them to poke in more detail into v$session_history. So in our case it's not really used for active monitoring, but for finding the root cause after the fact. I don't know how representative this usage is though. OK, that's a good usecase, and something that certainly would apply to Postgres. Don't you often need more than just wait events to find the cause, like system memory usage, total I/O, etc? Yes, the AWR report contains that information as well. e.g. sorts that spilled to disk, shared memory at the start and end, top 10 statements sorted by total time, individual time, I/O, number of executions, segments (tables) that received the highest I/O (read and write) and so on. It's really huge.
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On 10/8/21 11:21, Bruce Momjian wrote: Ah, this is exactly what I wanted to know --- what people are using the event waits for. Can you tell if these are done all externally, or if they need internal database changes? Well, the methodology goes like this: we get the slow queries from pgBadger report and then run explain (analyze, timing, buffers) on the query. If we still cannot figure out how to improve things, we check the events and see what the query is waiting for. After that we may add an index, partition the table, change index structure or do something like that. Unrelated to this discussion, I discovered Bloom extension. Bloom indexes are phenomenally useful. I apologize for the digression. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
