Re: Foreign table performance issue / PostgreSQK vs. ORACLE
Hi Ingolf, > On 29. Jan 2021, at 13:56, Markhof, Ingolf > wrote: > > Hi! > > I am struggling with the slow performance when running queries referring to > foreign tables. – Yes, I know… - Please read the whole story! Done and it rings a bell or two. > The set-up basically is a production database and a reporting database. As > names indicate, the production database is used for production, the reporting > database is for analysis. On the reporting database, the only way to access > product data is via foreign tables that link to the related production tables. > > Now, while some queries on the reporting service run fine, some don't even > return any data after hours. > > However, the same set-up worked fine in Oracle before. Reporting wasn't > always fast, but it delivered results in acceptable time. A query executed on > the Oracle reporting server returns data in e.g. 30 seconds. But running the > query translated to PostgreSQL on the PostgreSQL DB does not deliver a single > row after hours (!) of run time. > > So, I wonder: Is there a fundamental difference between Oracle database links > and foreign tables in PostgreSQL that could explain the different run times? > Could there be some tuning option in PostgreSQL to make queries via foreign > tables faster (e.g. I heard about option fetch_size)? You did not explicitly mention it, but I assume you are using postgres_fdw to connect from reporting (R) to production (P). Thomas and Tom already mentioned incomplete/non-existing/non-applicable filter pushdowns. I want to add another probable root cause to the list explaining the behavior you experience. The postgres_fdw uses a CURSOR on P to execute the query. While this guarantees transaction safety, it also prohibits parallelism (PostgreSQL server-side cursors enforce a sequential plan). As a result, depending on the size of tables, indexes, and filters pushed down (or not), this probably results in slow-running queries. IMO, the worst-case scenario is that a sequential table scan without any filtering, and a single worker runs on the target. Of course, you can try to optimize schemas on P and queries on R, enabling more filter pushdown and eventually a faster execution. However, I believe this does not work with your entire workload, i.e. there will always be performance gaps. The parallelism issue is theoretically fixable by utilizing partitions on P. R then connects to P with multiple postgres_fdw-backed child tables. However, this will only work with a patch to postgres_fdw to implement "IsForeignScanParallelSafe" (see [1] for a possible implementation). Without this method, there will be no parallelism again. Without, the partitions scan occurs sequentially, not showing a performance gain. I want to mention there are proprietary options available (re-)enabling PostgreSQL parallelism with cursors. Such an extension can potentially fix your performance issue. However, I have not tried it so far with a setup similar to yours. Cheers, Sebastian [1]: https://github.com/swarm64/parallel-postgres-fdw-patch -- Sebastian Dressler, Solution Architect, Swarm64 AS +49 30 994 0496 72 | sebast...@swarm64.com
Re: JIT on Windows with Postgres 12.1
On Wed, Feb 12, 2020 at 12:05:11PM -0800, Andres Freund wrote: > Yes, LLVM would work on windows. I'd not even be surprised if one could > make it work on windows already when using a mingw based build, instead > of msvc. In my mingw environment, postgresql build with option "--with-llvm" failed with the follwing message. Is it difficult to make PostgreSQL JIT work on windows using mingw? --Message1 /mingw64/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I../../../../src/include -I./src/include/port/win32 "-I../../../../src/include/port/win32" -DWIN32_STACK_RLIMIT=4194304 -DBUILDING_DLL -flto=thin -emit-llvm -c -o latch.bc latch.c latch.c:680:19: error: static_assert expression is not an integral constant expression StaticAssertStmt(WSA_INVALID_EVENT == NULL, ""); ~^~ -- --Message2(When commenting latch.c:680 out) llvmjit_inline.cpp:32:10: fatal error: sys/mman.h: No such file or directory 32 | #include | ^~~~ compilation terminated. -- --Environment PostgreSQL:postgresql13.0 Windows:Windows10 MSYS2 packages:mingw-w64-x86_64-gcc base-devel mingw-w64-x86_64-llvm(llvm11 installed) mingw-w64-x86_64-clang(clang11 installed) -- -- Yuuki Fujii
Re: Foreign table performance issue / PostgreSQK vs. ORACLE
> > > Could there be some tuning option in PostgreSQL to make queries via > foreign tables faster (e.g. I heard about option fetch_size)? > fetch_size can make a difference, but it won't change a query that takes hours into a query that takes seconds. The default is likely too low, though. Have you analyzed the foreign table or set use_remote_estimate? With no statistics, postgres may just be pulling the entire remote table. As others have mentioned, it would be useful to see your query. There may be obvious issues, such as functions in the predicate, but without the query, we can only guess. >
Occasional lengthy locking causing stalling on commit
G'day all, We have a postgres 11.9 instance serving 7-8k queries/sec. In general it's humming along with no issues (p50 patency of ~1ms; p95 ~30ms). Lately though, we've seen occasional locking that causes all commits to the main database to be briefly blocked and the APIs that it backs to stall. This is what I know so far: * It's happened at varying times of day over the last 3 weeks. Around 10 significant cases (20-30 second stalls) and more shorter ones. * Each time queries are blocked behind an AccessExclusiveLock on the database (class 1262) that is itself blocked. * There's no change in DB throughput at the time. * No schema changes or any other queries are being made that would involve an AccessExclusiveLock. * The blocked queries are all commit statements; our metrics show dozens of traces each time that look perfectly normal except for a 20 second commit somewhere within them. * We make heavy use of listen/notify via que with ~80 notify events per second across 16 listen channels. (https://github.com/que-rb/que) Based on all that and the logs below, the locks in question appear to be held while inserting NOTIFY messages on commit: https://www.postgresql.org/message-id/3598.1363354686%40sss.pgh.pa.us If that's the case, the real question is why a small number of those commits are taking so much longer than expected. Any advice on how to proceed from here much appreciated. Cheers Ben Hoskings --- The observed outage here started at ~00:35:22 and ended when the blocked locks were acquired. 2021-01-27 00:35:15.264 UTC [1554652]: [21-1] db=main,user=runtime LOG: process 1554652 still waiting for ShareLock on transaction 3399854732 after 1000.091 ms 2021-01-27 00:35:15.264 UTC [1554652]: [22-1] db=main,user=runtime DETAIL: Process holding the lock: 1542461. Wait queue: 1554652, 1542154, 1542496. 2021-01-27 00:35:15.264 UTC [1554652]: [23-1] db=main,user=runtime CONTEXT: while rechecking updated tuple (#,#) in relation "devices" 2021-01-27 00:35:15.264 UTC [1554652]: [24-1] db=main,user=runtime STATEMENT: UPDATE "devices" SET "last_successful_request_at" = '2021-01-27 00:35:14.203471', "updated_at" = '2021-01-27 00:35:14.204966' WHERE "devices"."id" = # 2021-01-27 00:35:15.265 UTC [1542154]: [82-1] db=main,user=runtime LOG: process 1542154 still waiting for ShareLock on transaction 3399854732 after 1000.098 ms 2021-01-27 00:35:15.265 UTC [1542154]: [83-1] db=main,user=runtime DETAIL: Process holding the lock: 1542461. Wait queue: 1554652, 1542154, 1542496. 2021-01-27 00:35:15.265 UTC [1542154]: [84-1] db=main,user=runtime CONTEXT: while rechecking updated tuple (#,#) in relation "devices" 2021-01-27 00:35:15.265 UTC [1542154]: [85-1] db=main,user=runtime STATEMENT: UPDATE "devices" SET "last_successful_request_at" = '2021-01-27 00:35:14.226635', "updated_at" = '2021-01-27 00:35:14.227932' WHERE "devices"."id" = # 2021-01-27 00:35:15.267 UTC [1542521]: [93-1] db=main,user=runtime LOG: process 1542521 still waiting for ShareLock on transaction 3399854728 after 1000.067 ms 2021-01-27 00:35:15.267 UTC [1542521]: [94-1] db=main,user=runtime DETAIL: Process holding the lock: 1547170. Wait queue: 1542514, 1542521. 2021-01-27 00:35:15.267 UTC [1542521]: [95-1] db=main,user=runtime CONTEXT: while updating tuple (#,#) in relation "devices" 2021-01-27 00:35:15.267 UTC [1542521]: [96-1] db=main,user=runtime STATEMENT: UPDATE "devices" SET "last_successful_request_at" = '2021-01-27 00:35:14.257563', "updated_at" = '2021-01-27 00:35:14.258934' WHERE "devices"."id" = # 2021-01-27 00:35:15.278 UTC [1479762]: [60-1] db=main,user=runtime LOG: process 1479762 still waiting for AccessExclusiveLock on object 0 of class 1262 of database 0 after 1000.129 ms 2021-01-27 00:35:15.278 UTC [1479762]: [61-1] db=main,user=runtime DETAIL: Process holding the lock: 1518905. Wait queue: 1517976, 1518853, 1445675, 1518975, 1477989, 1518976, 1517975, 3018947, 1468183, 1477990, 1532581, 1474376, 1558501, 1477991, 1473039, 1468182, 1558509, 1477988, 1558516, 3019502, 1518852, 1473037, 1473038, 1547149, 1518909, 1479762, 1479765. 2021-01-27 00:35:15.290 UTC [1479765]: [51-1] db=main,user=runtime LOG: process 1479765 still waiting for AccessExclusiveLock on object 0 of class 1262 of database 0 after 1000.110 ms 2021-01-27 00:35:15.290 UTC [1479765]: [52-1] db=main,user=runtime DETAIL: Process holding the lock: 1518905. Wait queue: 1517976, 1518853, 1445675, 1518975, 1477989, 1518976, 1517975, 3018947, 1468183, 1477990, 1532581, 1474376, 1558501, 1477991, 1473039, 1468182, 1558509, 1477988, 1558516, 3019502, 1518852, 1473037, 1473038, 1547149, 1518909, 1479762, 1479765. 2021-01-27 00:35:15.290 UTC [1479765]: [53-1] db=main,user=runtime STATEMENT: COMMIT 2021-01-27 00:35:15.293 UTC [1542496]: [130-1] db=main,user=runtime LOG: process 1542496 still waiting for ShareLock on transaction 3399854732 after 1000.123 ms 2021-01-27 00:35:15.293 UTC [1542496]: [131-1] db=main,user=runtime DETAIL: Process holding the lock: 1542461. Wait
SV: Npgsql and the Connection Service File
> > >-Oprindelig meddelelse- >Fra: Laurenz Albe >Sendt: 29. januar 2021 20:14 >Til: Niels Jespersen ; pgsql-general@lists.postgresql.org >Emne: Re: Npgsql and the Connection Service File > >On Fri, 2021-01-29 at 14:13 +, Niels Jespersen wrote: >> Is there a way to get Npgsql understand the Connection Service File >> (https://www.postgresql.org/docs/13/libpq-pgservice.html). ? >> >> The purpose is to abstract physical details such as hostname, portnumber >> away from the application. > >No, the service file is part of libpq, and Npgsql doesn't use that. > Yes. Thank you for clarifying. It would be nice if Npgsql (and jdbc and others) emulated the libpq behaviour. Because in my mind, abstracting hostname, portnumber and databasename away is a really useful feature. How do others manage this? Regards Niels Jespersen >Yours, >Laurenz Albe >-- >Cybertec | https://www.cybertec-postgresql.com >
Re: Occasional lengthy locking causing stalling on commit
Ben Hoskings writes: > We have a postgres 11.9 instance serving 7-8k queries/sec. In general > it's humming along with no issues (p50 patency of ~1ms; p95 ~30ms). > Lately though, we've seen occasional locking that causes all commits > to the main database to be briefly blocked and the APIs that it backs > to stall. > ... > * We make heavy use of listen/notify via que with ~80 notify events > per second across 16 listen channels. (https://github.com/que-rb/que) Possibly you'd benefit from updating to v13, which has the listen/notify performance improvements Martijn referred to in the other thread. It's also possible that the hangup is unrelated to that, being somewhere later in commit processing than where the notify traffic gets dumped out. If you could identify which process has the "database 0" lock during one of these delays, and capture a stack trace from it, that would be super-informative. Not sure about a good way to do that though. Maybe you could automate tailing the log for "DETAIL: Process holding the lock: nnn." and gdb'ing that process. regards, tom lane
Offlne Slony Installation
I want to use slony replication tool offline with edb or postgresql. I have been using Windows 10 operating system. For this, I have downloaded enterprise database 13.1 version. After installation I select slony download from stackbuilder plus. Then I copied these 2 files to offline windows 10 machine. I install both of them. After that, i try to run a simple slonik script to setup master. while executing "init cluster" command i get file not found error, c:servershare/slony1_base.2.8.sql. Do you have an idea what is going on?Best Regards,
Edb Jdbc Ssl Connection
I want to establish simple jdbc ssl connection using edb as 11 and java 8 u271 with postgresql jdbc driver 42.2.9 (I have also tried these versions: 42.2.18 and 42.2.8).I followed the instructions from the following link.https://www.enterprisedb.com/postgres-tutorials/how-enable-ssl-authentication-edb-postgres-advanced-serverI tested ssl connection successfully using "psql". But I cannot establish a jdbc connection. I got "FATAL: connection requires a valid client certificate" error. After a little googling, I want to use a different url than specified here just to consume all the possibilities. After modifiying the url with "sslcert=./test/client.crt sslkey=./test/client.key sslrootcert=./test/server.crt" I got "SSLSocket duplex close failed". Could you please help me with this problem? Thanks for you in advance.
