Re: [EXTERNAL] Need help with performance tuning pg12 on linux
Em qua., 27 de dez. de 2023 às 14:11, Wilson, Maria Louise (LARC-E301)[RSES] escreveu: > Thanks for the reply!! Having some issues due to nulls…. Any other > thoughts? > > > > i=# ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id); > > ERROR: column "granule_uuid" contains null values > Well, uuid is a bad datatype for primary keys. If possible in the long run, consider replacing them with bigint. Can you try a index: CREATE INDEX granule_file_file_id_key ON granule_file USING btree(file_id); Although granule_file has an index as a foreign key, it seems to me that it is not being considered. My 2cents. Best regards, Ranier Vilela
Re: Weird behavior of INSERT QUERY
Em dom., 4 de jun. de 2023 às 11:49, Satalabaha Postgres < satalabaha.postg...@gmail.com> escreveu: > > > > On Sun, 4 Jun 2023 at 19:46, Ranier Vilela wrote: > >> Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres < >> satalabaha.postg...@gmail.com> escreveu: >> >>> Hi Listers, >>> >>> DB : postgres 14. >>> >>> We are experiencing weird performance issue of one simple insert >>> statement taking several minutes to insert data. The application calls >>> insert statement via stored procedure show mentioned below. >>> >>> The select query in the insert returns about 499 rows. However, this >>> insert statement when executed from application user i.e. schema1_u takes >>> close to 8 minutes. When the same insert statement gets executed as >>> postgres user it takes less than 280 ms. Both the executions use the same >>> execution plan with only difference that when schema1_u executes the SQL, >>> we observe "Trigger for constraint fk_con_tablea: time=426499.314 >>> calls=499" taking more time. Both the parent and child tables are not big >>> in size. There is no table bloat etc for both of these tables. Below are >>> the details. >>> Is there any way we can identify why as postgres user the insert >>> statement works fine and why not with application user schema1_u? >>> >>> Stored Procedure: >>> >>> >>> CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double >>> precision, parcreatedby text) >>> RETURNS void >>> LANGUAGE plpgsql >>> AS $function$ >>> BEGIN >>> insert into table_a >>> ( >>> ROWVERSION, >>> CREATED, >>> ISDELETED, >>> ISIGNORED, >>> IMPORTEDACCOUNTCODE, >>> IMPORTEDUNITCODE, >>> BEGINNINGBALANCE, >>> ENDINGBALANCE, >>> CREATEDBY, >>> FILEID >>> ) >>> select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY >>> HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'), >>> to_timestamp(To_char(clock_timestamp() at time zone >>> 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'), >>> false, >>> false, >>> IMPORTEDACCOUNTCODE, >>> IMPORTEDUNITCODE, >>> BEGINNINGBALANCE, >>> ENDINGBALANCE, >>> parCreatedBy, >>> FILEID >>> from STAGING_table_a >>> where FILEID = parFileId; >>> >>> END; >>> $function$ >>> ; >>> >> Can you show what type is FILEID? >> >> Can there be type mismatch? >> >> > regards, >> Ranier Vilela >> > > Thanks Ranier. Please find the below. > > \d+ schema1.table_a > Table "schema1.table_a" >Column| Type | Collation | > Nullable | Default | Storage | Stats target | Description > > -++---+--+-+--+--+- > id | numeric(20,0) | | not > null | | main | | > rowversion | timestamp(4) without time zone | | not > null | | plain| | > created | timestamp(4) without time zone | | not > null | | plain| | > isdeleted | boolean| | not > null | | plain| | > lastupdated | timestamp(4) without time zone | | >| | plain| | > isignored | boolean| | not > null | | plain| | > importedaccountcode | character varying(255) | | >| | extended | | > importedunitcode| character varying(255) | | >| | extended | | > beginningbalance| numeric(19,5) | | >| | main | | > endingbalance | numeric(19,5) | | >| | main | | > createdbyid
Re: Weird behavior of INSERT QUERY
Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres < satalabaha.postg...@gmail.com> escreveu: > Hi Listers, > > DB : postgres 14. > > We are experiencing weird performance issue of one simple insert statement > taking several minutes to insert data. The application calls insert > statement via stored procedure show mentioned below. > > The select query in the insert returns about 499 rows. However, this > insert statement when executed from application user i.e. schema1_u takes > close to 8 minutes. When the same insert statement gets executed as > postgres user it takes less than 280 ms. Both the executions use the same > execution plan with only difference that when schema1_u executes the SQL, > we observe "Trigger for constraint fk_con_tablea: time=426499.314 > calls=499" taking more time. Both the parent and child tables are not big > in size. There is no table bloat etc for both of these tables. Below are > the details. > Is there any way we can identify why as postgres user the insert statement > works fine and why not with application user schema1_u? > > Stored Procedure: > > > CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double > precision, parcreatedby text) > RETURNS void > LANGUAGE plpgsql > AS $function$ > BEGIN > insert into table_a > ( > ROWVERSION, > CREATED, > ISDELETED, > ISIGNORED, > IMPORTEDACCOUNTCODE, > IMPORTEDUNITCODE, > BEGINNINGBALANCE, > ENDINGBALANCE, > CREATEDBY, > FILEID > ) > select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY > HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'), > to_timestamp(To_char(clock_timestamp() at time zone > 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'), > false, > false, > IMPORTEDACCOUNTCODE, > IMPORTEDUNITCODE, > BEGINNINGBALANCE, > ENDINGBALANCE, > parCreatedBy, > FILEID > from STAGING_table_a > where FILEID = parFileId; > > END; > $function$ > ; > Can you show what type is FILEID? Can there be type mismatch? regards, Ranier Vilela
Re: How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server
Em qua., 31 de mai. de 2023 às 09:40, Sergio Rus escreveu: > As you can see, server B has 2 CPUs and is using NUMA on Linux. And the > CPU clock is slower on server B than server A. Maybe any of those are > causing that latency? > > Any suggestions or ideas where to look? I'd really appreciate your help. > If this is cpu bound, linux perf can show the difference. https://wiki.postgresql.org/wiki/Profiling_with_perf regards, Ranier Vilela
Re: PostgreSQL performance on ARM i.MX6
Em ter., 23 de mai. de 2023 às 08:43, Druckenmueller, Marc < marc.druckenmuel...@philips.com> escreveu: > Hi there, > > > > I am investigating possible throughput with PostgreSQL 14.4 on an ARM > i.MX6 Quad CPU (NXP sabre board). > > Testing with a simple python script (running on the same CPU), I get ~1000 > request/s. > Can you share kernel and python detalis? (version, etc). regards, Ranier Vilela
Re: Connection forcibly closed remote server error.
Em qua., 15 de fev. de 2023 às 15:55, aditya desai escreveu: > Hi Jeff, > Apologies. Here is how the message actually looks like. > > could not receive data from client: An existing connection was forcibly > closed by the remote host. > It looks like a Microsoft tool message. Perhaps .NET https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-exist-connection-closed I think that Postgres has nothing to do with the problem. regards, Ranier Vilela >
Re: Getting an index scan to be a parallel index scan
Em qua., 1 de fev. de 2023 às 02:39, Alex Kaiser escreveu: > Hello, > > I'm trying to get the following query to use a plan with parallelism, but > I haven't been successful and would like some advice. > > The schema and table that I'm using is this: > > CREATE TABLE testing( >id INT, >info INT, >data_one TEXT, >data_two TEXT, >primary key(id, info) > ); > > INSERT INTO testing(id, info, data_one, data_two) > SELECT idx, idx, md5(random()::text), md5(random()::text) > FROM generate_series(1,1000) idx; > > Then the query that I'm trying to run is this (I'll include the full query > at the very end of the email because it is long: > > select * from testing where id in (1608377,5449811, ... <1000 random ids> > ,4654284,3558460); > > Essentially I have a list of 1000 ids and I would like the rows for all of > those ids. > > This seems like it would be pretty easy to parallelize, if you have X > threads then you would split the list of IDs into 1000/X sub lists and give > one to each thread to go find the rows for ids in the given list. Even > when I use the following configs I don't get a query plan that actually > uses any parallelism: > > psql (15.1 (Debian 15.1-1.pgdg110+1)) > Type "help" for help. > > postgres=# show max_parallel_workers; > max_parallel_workers > -- > 8 > (1 row) > > postgres=# set max_parallel_workers_per_gather = 8; > SET > postgres=# set parallel_setup_cost = 0; > SET > postgres=# set parallel_tuple_cost = 0; > SET > postgres=# set force_parallel_mode = on; > SET > postgres=# explain select * from testing where id in (1608377,5449811, ... > ... ,4654284,3558460); > Can you try: select * from testing where id any = (values(1608377),(5449811),(5334677) ... ... ,(4654284),(3558460)); Or alternately you can use EXTEND STATISTICS to improve Postgres planner choice. regards, Ranier Vilela
Re: Fwd: temp_file_limit?
Em seg., 19 de dez. de 2022 às 16:29, Frits Jalvingh escreveu: > Ok, just to make sure that I understand correctly: > The parallel hash implementation needs to resize its table because of a > mismatch in expected tuple count. I do expect this to be true: Postgres > often grossly underestimates the expected row counts in our queries. > This is not fully implemented yet: removing the "old "files is not yet > done, so every time the table resizes it creates a new set of files and the > old ones remain. > I assume that the "used file size" only includes the "current" set of > files, and that the old ones are not counted towards that amount? That > would explain why it overallocates, of course. > It is not necessary what is happening. Could you try manually deleting (rm) these files, using the postgres user? It's an ugly and dirty test, but it could indicate that files are really being left behind, without being deleted by Postgres. Alternatively, you could compile a version with CHECK_WRITE_VS_EXTEND set, and try to fetch as much information from the logs as possible, as has been indicated by others here. > By itself I now know what to do: I just need to disable all parallelism ( > •̀ᴗ•́ )و ̑̑ > > I usually do that anyway because it makes queries die randomly. This is > just another reason. > > I restarted that query with max_parallel_workers_per_gather=0, and this > does not seem to use tempspace at all. It was not exactly fast, it took 82 > minutes of a single process running at 100% cpu. > https://explain.depesz.com/s/HedE > Anyway, see the hint page (https://explain.depesz.com/s/HedE#hints), maybe it will be useful. regards, Ranier Vilela
Re: Fwd: temp_file_limit?
Em seg., 19 de dez. de 2022 às 11:45, Frits Jalvingh escreveu: > Hi Ranier, thanks for your help. > > I do not have more disks lying around, and I fear that if it does not > complete with 1.3TB of disk space it might not be that likely that adding > 750GB would work... > Postgres version: the original (prd) issue was on 10.x. I also tested it > on 14.x with the same issue. I then upgraded my machine to 15.1 to make > sure to report on the latest version, and all information mentioned in this > thread is from that version. > You can run with a Postgres debug compiled version? https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD Maybe, some light appears. regards, Ranier Vilela >
Re: Fwd: temp_file_limit?
Em seg., 19 de dez. de 2022 às 06:47, Frits Jalvingh escreveu: > > The test is running on Ubuntu 22.04.1, x86_64, the disk is an NVMe 2TB > WD850X with ext4 as a file system. > It's probably not a production environment. Any chance of adding another 2TB NVMe, just for the temp files? To see if Postgres can finish the queries and provide more information? What exactly is the version of Postgres (14.???) are you using it? regards, Ranier Vilela
Re: Select on partitioned table is very slow
Em qui., 25 de ago. de 2022 às 10:43, Jose Osinde escreveu: > > Dear Depesz, Laurenz, > > Thanks very much for the fast responses. They are actually correct and > saved me a lot of time. I couldn't test the cast from the Java test but > this is something I can deal with later on (most probably updating the > column types to text in the database side instead). But what I could do was > reproduce the same problem in the psql console using the cast in the other > way. This sentence: > > explain analyze select logical_identifier, version_id, lastproduct > FROM test_product_ui_partition.product_ui pui > WHERE > pui.mission_id='urn:esa:psa:context:investigation:mission.em16'::citext > AND > pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t00-20220729t235959'::citext; > The query in explain.txt attached, it seems not the same. explain analyze select logical_identifier, version_id, lastproduct FROM test_product_ui_partition.product_ui pui WHERE pui.mission_id='urn:esa:psa:context:investigation:mission.em16'::text AND pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t00-20220729t235959'::text; ::text? regards, Ranier Vilela
Re: Strange behavior of limit clause in complex query
= "Extent1"."id_status"
> AND EXISTS (
> SELECT 1 AS "C1"
> FROM (
> SELECT "Extent3"."TagId" FROM
> "dbo"."ngx_tag_content" AS "Extent3"
> WHERE "Extent1"."id" =
> "Extent3"."ContentId"
> ) AS "Project1"
> WHERE EXISTS (
> SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable1"
> WHERE "Project1"."TagId" = 337139)
> )
> AND ("Extent2"."id_path" IN (27495,27554,27555)
> AND NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable2" WHERE TRUE = FALSE)
> )
> ) AS "Project5" *ORDER BY "Project5"."C3" DESC LIMIT 6*;
>
I think that LIMIT is confusing the planner.
Forcing a path that in the end is not faster.
Can you try something similar to this?
WITH q AS (
SELECT "Project5".id
FROM (
SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
FROM "dbo"."ng_content" AS "Extent1"
INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id" =
"Extent2"."id_content"
WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
timestamp)
AND 2 = "Extent1"."id_status"
AND EXISTS (
SELECT 1 AS "C1"
FROM (
SELECT "Extent3"."TagId" FROM
"dbo"."ngx_tag_content" AS "Extent3"
WHERE "Extent1"."id" = "Extent3"."ContentId"
) AS "Project1"
WHERE EXISTS (
SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
"SingleRowTable1"
WHERE "Project1"."TagId" = 337139)
)
AND ("Extent2"."id_path" IN (27495,27554,27555) AND
NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS "SingleRowTable2"
WHERE TRUE = FALSE))
))
SELECT * FROM q ORDER BY q.C3 DESC LIMIT 6;
Probably, using CTE, the plan you want.
regards,
Ranier Vilela
Re: postgres backend process hang on " D " state
Em dom., 29 de mai. de 2022 às 10:20, James Pang (chaolpan) < chaol...@cisco.com> escreveu: > Hi, > >We have a performance test on Postgresql 13.4 on RHEL8.4 , > Hard to say with this info, but how is this " test", why not use the 13.7, with all bugs fixes related? regards, Ranier Vilela >
Re: DB connection issue suggestions
Em qua., 11 de mai. de 2022 às 04:18, Sudhir Guna escreveu: > Hi MichaelDBA, > > Thank you for reviewing. > > I had validated the show max_connections and its 1000. > I think that you are wasting resources with this configuration. Try enabling Connection Pool at Pentaho configuration. And set the *Pool Size* (Maximum) to 100 for Pentaho and 100 for Postgres (max_connections). Under Advanced Options (DataSource Windows) enable Connection Pool. Probably Pentaho is trying to use more connections than Postgres allows. regards, Ranier Vilela
Re: DB connection issue suggestions
Em ter., 10 de mai. de 2022 às 14:49, Sudhir Guna escreveu: > Dear All, > > We have recently upgraded Postgresql 9.4 standalone server to Postgresql > 11.2 with High Availability (2 servers : Master and Standby). > > While trying to test using ETL applications and reports, we observe that > the ETL jobs fails with below error, > > 2022/05/06 16:27:36 - Error occurred while trying to connect to the > database > 2022/05/06 16:27:36 - Error connecting to database: (using class > org.postgresql.Driver) > 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already > > We have increased the max_connections = 1000 in postgresql.conf file. > > It worked ok for a day and later we get the same error message. > > Please help to advise on any additional settings required. The prior > Postgresql 9.4 had the default max_connections = 100 and the applications > worked fine. > I guess that ETL is pentaho? You can try to use the latest JDBC driver (42.3.5) . regards, Ranier Vilela
Re: significant jump in sql statement timing for on server vs a remote connection
Em qua., 20 de abr. de 2022 às 12:16, Sbob escreveu: > > On 4/19/22 22:17, Jeff Janes wrote: > > On Tue, Apr 19, 2022 at 5:00 PM Sbob wrote: > >> >> However if we move the file to another server in the same network and >> run with a psql -h then it runs for more than 10min. > > > What is the ping time? Packet loss? You can't take for granted that the > network is good and fast just because they are on the same LAN. > > Cheers, > > Jeff > > > Here is the ping stats: > > --- db-primary ping statistics --- > 4 packets transmitted, 4 received, 0% packet loss, time 3000ms > 3000 ms? Are sure that haven't packet loss? regards, Ranier Vilela
Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <
mku...@peabodyenergy.com> escreveu:
> Hi Team,
>
>
>
> We are running the below query in PostgreSQL and its taking approx. 8 to 9
> sec to run the query.
>
>
>
> Query – 1
>
>
>
> Select * from
>
> (
>
> Select payment_sid_c,
>
> lms_app.translate_payment_status(payment_sid_c) AS paymentstatus
>
> from
>
> lms_app.lms_payment_check_request
>
> group by payment_sid_c) a
>
> where paymentstatus in ('PAID', 'MANUALLYPAID')
>
>
>
>
>
> The explain plan and other details are placed at below link for more
> information. We have checked the indexes on column but in the explain plan
> it is showing as Seq Scan which we have to find out.
>
>
>
>
>
> *https://explain.depesz.com/s/Jsiw#stats
> <https://explain.depesz.com/s/Jsiw#stats>*
>
>
>
>
>
> This query is using a function translate_payment_status on column
> payment_sid_c whose script is attached in this mail
>
>
>
> Could please anyone help or suggest how to improve the query performance.
>
You can try create a partial index that help this filter:
Filter: ((lms_app.translate_payment_status(payment_sid_c))::text = ANY
('{PAID,MANUALLYPAID}'::text[]))
See at:
https://www.postgresql.org/docs/current/indexes-partial.html
regards,
Ranier Vilela
Re: Optimal configuration for server
Em seg., 7 de mar. de 2022 às 18:10, Luiz Felipph escreveu: > Hi Tomas, > > Thank you for your reply! > > Thomas, > >> You need to monitor shared buffers cache hit rate (from pg_stat_database >> view) - if that's low, increase shared buffers. Then monitor and tune >> slow queries - if a slow query benefits from higher work_mem values, do >> increase that value. It's nonsense to just increase the parameters to >> consume more memory. > > > Makes perfect sense! The system is a OLTP and unfortunately has some > issues about how big the single lines are(too many colunms). In some cases > I have to bring to app 150k lines(in some not so rare cases, 200k ~300k) to > process in a single transaction, then update and insert new rows. It's > works fine, except when eventually start to outOfMemory or Connection has > been closed forcing us to restart the application cluster. Finally I'll > have access to a performance environment to see how is configured(they > promised me a production mirror) and then get back to you to provide more > detailed information. > > Thanks for you time! > > Ranier, > >> Are you using nested connections? > > > What do you mean with "nested connections"? If you are talking about > nested transactions, then yes, and I'm aware of subtransaction problem but > I think this is not the case right now (we had, removed multiple points, > some other points we delivered to God's hands(joking), but know I don't see > this issue) > I mean "nested", even. Two or more connections opened by app. If this is case, is need processing the second connection first, before the first connection. Just a guess. regards, Ranier Vilela
Re: Optimal configuration for server
Em seg., 7 de mar. de 2022 às 14:18, Luiz Felipph escreveu: > Greatings Ranieri, > > Server logs I need ask to someone to get it > > Redhat EL 7 > > Postgres 12 > > Humm.. I will find out were I should put keep Alive setting > Are you using nested connections? regards, Ranier Vilela >
Re: Optimal configuration for server
Em seg., 7 de mar. de 2022 às 08:54, Luiz Felipph escreveu: > Hi everybody! > > I have a big application running on premise. One of my main database > servers has the following configuration: > > 72 CPUs(2 chips, 18 physical cores per chip, 2 threads) Xeon Gold 6240 > 1TB of ram or 786GB (5 servers at all) > A huge storage( I don't know for sure what kind is, but is very powerful) > > A consulting company recommended the following configuration for theses > main servers(let me know if something important was left behind): > > maxx_connections = 2000 > shared_buffers = 32GB > temp_buffers = 1024 > max_prepared_transactions = 3000 > work_men = 32MB > effective_io_concurrency = 200 > max_worker_processes = 24 > checkpoint_timeout = 15min > max_wal_size = 64GB > min_wall_size = 2GB > effective_cache_size = 96GB > (...) > > I Think this is too low memory setting for de size of server... The number > of connections, I'm still measuring to reduce this value( I think it's too > high for the needs of application, but untill hit a value too high to > justfy any memory issue, I think is not a problem) > > My current problem: > > under heavyload, i'm getting "connection closed" on the application > level(java-jdbc, jboss ds) > Server logs? What OS (version) What Postgres version. Keep-alive may not be configured at the client side? regards, Ranier Vilela >
Re: OOM killer while pg_restore
Em qui., 3 de mar. de 2022 às 15:32, Marc Rechté escreveu: > Le 03/03/2022 à 16:31, Tom Lane a écrit : > > =?UTF-8?Q?Marc_Recht=c3=a9?= writes: > >> We have a pg_restore which fails due to RAM over-consumption of the > >> corresponding PG backend, which ends-up with OOM killer. > >> The table has one PK, one index, and 3 FK constraints, active while > >> restoring. > >> The dump contains over 200M rows for that table and is in custom format, > >> which corresponds to 37 GB of total relation size in the original DB. > > The FKs would result in queueing row trigger events, which would occupy > > some memory. But those should only need ~12 bytes per FK per row, > > which works out to less than 10GB for this number of rows, so it may > > be that you've hit something else that we would consider a leak. > > > > Does memory consumption hold steady if you drop the FK constraints? > > > > If not, as others have noted, we'd need more info to investigate > > this. The leak is probably independent of the specific data in > > the table, so maybe you could make a small self-contained example > > using a script to generate dummy data. > > > > regards, tom lane > > > > > Actually the number of rows is 232735712. > > Accordingly the RAM consumption would be x12 x3 = 7.8 GiB. > > This is close to the 8,1g I reported earlier (actually it was closer to > 7.8 GB, due to GiB vs. GB confusion). > > So there is no memory leak. > > It took 16 hours on my box to reach that RAM consumption, and then the > COPY failed when checking the first FK (as the referenced table was empty). > > I dropped the FK, index, and 3 FK constraints and started over the > pg_restore: > > 11 minutes to load the table (I did not have time to note RAM consumption) > > I then created the PK and index: > > 24 minutes > > For FK, I don't know because the referenced table are empty (but I'll be > able to test next week, if deemed necessary). > > 16 hours vs. 35 minutes to reach the same state. > Maybe it's out of reach, but one way to help Postgres developers fix this is to provide Flame Graphs [1] based on these slow operations. For confidentiality and privacy reasons, the data is out of reach. My 2c here. regards, Ranier Vilela [1] https://www.brendangregg.com/flamegraphs.html
Re: An I/O error occurred while sending to the backend (PG 13.4)
Em qui., 3 de mar. de 2022 às 15:19, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu: > I am also starting to feel that the issue being on the database’s side is > less and less likely. There is something happening in between, or possibly > on the client. > > > > Ranier, the only reason I was focusing on this at the PG level is that > this issue started to show up several months ago shortly after I updated to > PG13 from PG11. Had run PG11 for 2 years without ever seeing that issue at > all. The ETL itself hasn’t changed either, except for upgrading the JDBC > driver… But I did revert back to an older JDBC driver and the issue still > did occur eventually. > > > > Of course, other things could have changed in the client’s IT > infrastructure that I am not aware of, so I am pushing that angle as well > more aggressively now. I am also pushing for WireShark to monitor the > network more closely. Stay tuned! > > > > Thank you so much all for your support but at this time, I think the ball > is in my camp and working out with it on some plan. > You are welcome. regards, Ranier Vilela
Re: An I/O error occurred while sending to the backend (PG 13.4)
Em qui., 3 de mar. de 2022 às 13:46, Justin Pryzby escreveu: > On Thu, Mar 03, 2022 at 01:33:08PM -0300, Ranier Vilela wrote: > > Sorry, but this is much more on the client side. > > The client is reporting the problem, as is the server. > Are you read the server log? " 2022-03-03 01:04:40 EST [21228] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. 2022-03-03 01:04:40 EST [21228] LOG: unexpected EOF on client connection with an open transaction" > > Following the logs, it is understood that the client is dropping the > > connection. > > The logs show that the client's connection *was* dropped. > And on the server, the same. > No, the log server shows that the client dropped the connection. > > > So most likely the error could be from Pentaho or JDBC. > > > > > https://www.geeksforgeeks.org/java-net-socketexception-in-java-with-examples/ > > " This *SocketException* occurs on the server-side when the client closed > > the socket connection before the response could be returned over the > > socket." > > > > I suggest moving this thread to the Pentaho or JDBC support. > > We don't know the source of the problem. Yeah, but it is much more likely to be on the client. > I still doubt it's in postgres, Everything indicates not. but I > don't think it's helpful to blame the client, just because the client > reported > the problem. If the server were to disconnect abruptly, I'd expect the > client > to report that, too. > > Laurent would just have to start the conversation over (and probably > collect > the same diagnostic information anyway). The client projects could blame > postgres with as much rationale as there is for us to blame the client. > > Please don't add confusion here. I just suggested, this is not an order. regards, Ranier Vilela
Re: An I/O error occurred while sending to the backend (PG 13.4)
ent.java:134) > 2022/03/03 01:04:56 - Upsert2.0 -at > org.pentaho.di.core.database.Database.insertRow(Database.java:1288) > 2022/03/03 01:04:56 - Upsert2.0 -... 6 more > 2022/03/03 01:04:56 - Upsert2.0 - Caused by: java.net.SocketException: > Connection reset > 2022/03/03 01:04:56 - Upsert2.0 -at > java.net.SocketInputStream.read(SocketInputStream.java:209) > 2022/03/03 01:04:56 - Upsert2.0 -at > java.net.SocketInputStream.read(SocketInputStream.java:141) > 2022/03/03 01:04:56 - Upsert2.0 -at > org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161) > 2022/03/03 01:04:56 - Upsert2.0 -at > org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128) > 2022/03/03 01:04:56 - Upsert2.0 -at > org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113) > 2022/03/03 01:04:56 - Upsert2.0 -at > org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73) > 2022/03/03 01:04:56 - Upsert2.0 -at > org.postgresql.core.PGStream.receiveChar(PGStream.java:453) > 2022/03/03 01:04:56 - Upsert2.0 -at > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2119) > 2022/03/03 01:04:56 - Upsert2.0 -at > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) > 2022/03/03 01:04:56 - Upsert2.0 -... 11 more > > On the DB: > > 2022-03-03 01:04:40 EST [21228] LOG: could not receive data from client: > An existing connection was forcibly closed by the remote host. > 2022-03-03 01:04:40 EST [21228] LOG: unexpected EOF on client connection > with an open transaction > Sorry, but this is much more on the client side. Following the logs, it is understood that the client is dropping the connection. So most likely the error could be from Pentaho or JDBC. https://www.geeksforgeeks.org/java-net-socketexception-in-java-with-examples/ " This *SocketException* occurs on the server-side when the client closed the socket connection before the response could be returned over the socket." I suggest moving this thread to the Pentaho or JDBC support. regards, Ranier Vilela
Re: OOM killer while pg_restore
Em qui., 3 de mar. de 2022 às 09:19, Marc Rechté escreveu: > Em qui., 3 de mar. de 2022 às 05:59, Marc Rechté > escreveu: > > > > Hello, > > > > We have a pg_restore which fails due to RAM over-consumption of > > the corresponding PG backend, which ends-up with OOM killer. > > > > The table has one PK, one index, and 3 FK constraints, active > > while restoring. > > The dump contains over 200M rows for that table and is in custom > > format, which corresponds to 37 GB of total relation size in the > > original DB. > > > > While importing, one can see the RSS + swap increasing linearly > > for the backend (executing the COPY) > > > > On my machine (quite old PC), it failed after 16 hours, while the > > disk usage was reaching 26 GB and memory usage was 9.1g (RSS+swap) > > > > If we do the same test, suppressing firstly the 5 constraints on > > the table, the restore takes less than 15 minutes ! > > > > This was tested on both PG 14.2 and PG 13.6 (linux 64-bit machines). > > > > It there a memory leak or that is normal that a bacend process may > > exhaust the RAM to such an extent ? > > > > Hi Marc, > > Can you post the server logs? > > > > regards, > > Ranier Vilela > > Will it help ? > Show some direction. > 2022-02-25 12:01:29.306 GMT [1468:24] user=,db=,app=,client= LOG: > server process (PID 358995) was terminated by signal 9: Killed > 2022-02-25 12:01:29.306 GMT [1468:25] user=,db=,app=,client= DETAIL: > Failed process was running: COPY simulations_ecarts_relatifs_saison > (idpoint, annee, saison, idreferentiel, ecartreltav, ecartreltnav, > ecartreltxav, ecartreltrav, ecartreltxq90, ecartreltxq10, ecartreltnq10, > ecartreltnq90, ecartreltxnd, ecartreltnnd, ecartreltnht, ecartreltxhwd, > ecartreltncwd, ecartreltnfd, ecartreltxfd, ecartrelsd, ecartreltr, > ecartrelhdd, ecartrelcdd, ecartrelpav, ecartrelpint, ecartrelrr, > ecartrelpfl90, ecartrelrr1mm, ecartrelpxcwd, ecartrelpn20mm, > ecartrelpxcdd, ecartrelhusav, ecartreltx35, ecartrelpq90, ecartrelpq99, > ecartrelrr99, ecartrelffav, ecartrelff3, ecartrelffq98, ecartrelff98) > FROM stdin; > COPY leak? regards, Ranier Vilela
Re: OOM killer while pg_restore
Em qui., 3 de mar. de 2022 às 05:59, Marc Rechté escreveu: > Hello, > > We have a pg_restore which fails due to RAM over-consumption of the > corresponding PG backend, which ends-up with OOM killer. > > The table has one PK, one index, and 3 FK constraints, active while > restoring. > The dump contains over 200M rows for that table and is in custom format, > which corresponds to 37 GB of total relation size in the original DB. > > While importing, one can see the RSS + swap increasing linearly for the > backend (executing the COPY) > > On my machine (quite old PC), it failed after 16 hours, while the disk > usage was reaching 26 GB and memory usage was 9.1g (RSS+swap) > > If we do the same test, suppressing firstly the 5 constraints on the > table, the restore takes less than 15 minutes ! > > This was tested on both PG 14.2 and PG 13.6 (linux 64-bit machines). > > It there a memory leak or that is normal that a bacend process may exhaust > the RAM to such an extent ? > Hi Marc, Can you post the server logs? regards, Ranier Vilela
Re: An I/O error occurred while sending to the backend (PG 13.4)
Em seg., 28 de fev. de 2022 às 13:50, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu: > > > >From: Ranier Vilela > >Sent: Thursday, February 24, 2022 08:46 > >To: Justin Pryzby > >Cc: l...@laurent-hasson.com; pgsql-performa...@postgresql.org > >Subject: Re: An I/O error occurred while sending to the backend (PG 13.4) > > > >Em qui., 24 de fev. de 2022 às 09:59, Justin Pryzby pry...@telsasoft.com> escreveu: > >On Thu, Feb 24, 2022 at 08:50:45AM -0300, Ranier Vilela wrote: > >> I can't understand why you are still using 13.4? > >> [1] There is a long discussion about the issue with 13.4, the project > was > >> made to fix a DLL bottleneck. > >> > >> Why you not use 13.6? > > > >That other problem (and its fix) were in the windows build environment, > and not > >an issue in some postgres version. > >Yeah, correct. > >But I think that it was very clear in the other thread that version 13.4, > >on Windows, may have a slowdown, because of the DLL problem. > >So it would be better to use the latest available version > >that has this specific fix and many others. > > > >regards, > >Ranier Vilela > > > OK, absolutely. I was thinking about even moving to 14. I know migrations > within a release are painless, but my experience with upgrading across > releases has also been quite good (short of bugs that were found of > course). Any opinion on 14.2? > Of course, 14.2 would be better than 13.6, but I think that there are chances that this specific problem is not beneficial. And both 13.6 and 14.2 still suffer from a Windows version specific issue [1]. A solution has been proposed which has not yet been accepted. But in general terms, you will benefit from adopting 14.2 for sure. regards, Ranie Vilela [1] https://www.postgresql.org/message-id/CAEudQAovOEM0haC4NbWZaYGW4ESmAE1j6_yr93tS8Xo8i7%2B54A%40mail.gmail.com
Re: An I/O error occurred while sending to the backend (PG 13.4)
Em qui., 24 de fev. de 2022 às 09:59, Justin Pryzby escreveu: > On Thu, Feb 24, 2022 at 08:50:45AM -0300, Ranier Vilela wrote: > > I can't understand why you are still using 13.4? > > [1] There is a long discussion about the issue with 13.4, the project was > > made to fix a DLL bottleneck. > > > > Why you not use 13.6? > > That other problem (and its fix) were in the windows build environment, > and not > an issue in some postgres version. Yeah, correct. But I think that it was very clear in the other thread that version 13.4, on Windows, may have a slowdown, because of the DLL problem. So it would be better to use the latest available version that has this specific fix and many others. regards, Ranier Vilela
Re: An I/O error occurred while sending to the backend (PG 13.4)
Em qua., 23 de fev. de 2022 às 21:47, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu: > > >> -Original Message- >> From: l...@laurent-hasson.com >> Sent: Saturday, December 4, 2021 14:18 >> To: Justin Pryzby >> Cc: pgsql-performa...@postgresql.org >> Subject: RE: An I/O error occurred while sending to the backend (PG > 13.4) >> >> >> > -Original Message- >> > From: Justin Pryzby >> > Sent: Saturday, December 4, 2021 12:59 >> > To: l...@laurent-hasson.com >> > Cc: pgsql-performa...@postgresql.org >> > Subject: Re: An I/O error occurred while sending to the > backend (PG >> > 13.4) >> > >> > On Sat, Dec 04, 2021 at 05:32:10PM +, > l...@laurent-hasson.com >> > wrote: >> > > I have a data warehouse with a fairly complex ETL process > that has >> > been running for years now across PG 9.6, 11.2 and now 13.4 > for the >> > past couple of months. I have been getting the error "An I/O > error >> > occurred while sending to the backend" quite often under load > in 13.4 >> > which I never used to get on 11.2. I have applied some tricks, >> particularly >> > with the socketTimeout JDBC configuration. >> > > >> > > So my first question is whether anyone has any idea why this > is >> > happening? My hardware and general PG configuration have not >> > changed between 11.2 and 13.4 and I NEVER experienced this on > 11.2 >> in >> > about 2y of production. >> > > >> > > Second, I have one stored procedure that takes a very long > time to >> run >> > (40mn more or less), so obviously, I'd need to set > socketTimeout to >> > something like 1h in order to call it and not timeout. That > doesn't seem >> > reasonable? >> > >> > Is the DB server local or remote (TCP/IP) to the client? >> > >> > Could you collect the corresponding postgres query logs when > this >> > happens ? >> > >> > It'd be nice to see a network trace for this too. Using > tcpdump or >> > wireshark. >> > Preferably from the client side. >> > >> > FWIW, I suspect the JDBC socketTimeout is a bad workaround. >> > >> > -- >> > Justin >> >> It's a remote server, but all on a local network. Network > performance is I >> am sure not the issue. Also, the system is on Windows Server. What > are you >> expecting to see out of a tcpdump? I'll try to get PG logs on the > failing query. >> >> Thank you, >> Laurent. >> >> >> >> > > Hello Justin, > > It has been ages! The issue has been happening a bit more often recently, > as much as once every 10 days or so. As a reminder, the set up is Postgres > 13.4 on Windows Server with 16cores and 64GB memory. I can't understand why you are still using 13.4? [1] There is a long discussion about the issue with 13.4, the project was made to fix a DLL bottleneck. Why you not use 13.6? regards, Ranier Vilela [1] https://www.postgresql.org/message-id/MN2PR15MB2560BBB3EC911D973C2FE3F885A89%40MN2PR15MB2560.namprd15.prod.outlook.com
Re: PG 12 slow selects from pg_settings
Em sex., 8 de out. de 2021 às 09:06, Julius Tuskenis < julius.tuske...@gmail.com> 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
Em sex., 8 de out. de 2021 às 04:01, Julius Tuskenis < julius.tuske...@gmail.com> 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: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
Em ter., 5 de out. de 2021 às 01:04, Mladen Gogala escreveu: > As for Jeff Holt, I believe that a person of his stature needs to be > taken seriously and not lectured "how are things done in Postgres > community". I am rather confused by the thinly veiled hostility toward > Oracle. In my opinion, Postgres community should be rather welcoming to > Oracle people like Frits Hoogland, Frank Pachot or Jeff Holt. > I think that you're a little mistaken, the hostility of the "gurus" is not exactly against Oracle guys, but rather towards anyone who is not a "committer". Just follow the pgsql-hackers list, and you'll see that newbies are very unwelcome, whether they're really newbies like me, or they're really teachers. regards, Ranier Vilela
Re: Problem with indices from 10 to 13
Em ter., 28 de set. de 2021 às 12:40, Daniel Diniz <
dan...@flashcourier.com.br> escreveu:
> Hello I migrated from postgres 10 to 13 and I noticed that there was a big
> increase in a querie that I use, I did explain in 10 and 13 and the
> difference is absurd, the indices and data are the same in 2. I've
> re-created and re-indexed but I don't know what changed from 10 to 13 which
> made the performance so bad, I don't know if it needs some extra parameter
> in some conf on 13.
>
> Postgres 13
>
> "QUERY PLAN"
> "Limit (cost=1.13..26855.48 rows=30 width=137) (actual
> time=10886.585..429803.463 rows=4 loops=1)"
> " -> Nested Loop (cost=1.13..19531164.71 rows=21819 width=137) (actual
> time=10886.584..429803.457 rows=4 loops=1)"
> "Join Filter: (h.ult_eve_id = ev.evento_id)"
> "Rows Removed by Join Filter: 252"
> "-> Nested Loop (cost=1.13..19457514.32 rows=21819 width=62)
> (actual time=10886.326..429803.027 rows=4 loops=1)"
> " -> Nested Loop (cost=0.85..19450780.70 rows=21819
> width=55) (actual time=10886.259..429802.908 rows=4 loops=1)"
> "-> Index Scan Backward using hawbs_pkey on hawbs h
> (cost=0.57..19444209.67 rows=21819 width=46) (actual
> time=10886.119..429802.676 rows=4 loops=1)"
> " Filter: ((tipo_hawb_id = ANY
> ('{1,10,3}'::integer[])) AND ((nome_des)::text ~~*
> convert_from('\x255354455048414e592053544f4557204c45414e44524f25'::bytea,
> 'LATIN1'::name)))"
> " Rows Removed by Filter: 239188096"
>
Index Scan Backward looks suspicious to me.
239,188,096 rows removed by filter it's a lot of work.
Do you, run analyze?
regards,
Ranier Vilela
Re: Query executed during pg_dump leads to excessive memory usage
> Index Scan using pg_authid_oid_index on pg_authid > (cost=0.28..2.29 rows=1 width=64) (actual time=0.002..0.002 rows=1 > loops=1026902) >Index Cond: (oid = t.typowner) >SubPlan 4 > -> Index Scan using pg_class_oid_index on pg_class (cost=0.43..2.45 > rows=1 width=1) (actual time=0.003..0.003 rows=1 loops=671368) >Index Cond: (oid = t.typrelid) >SubPlan 5 > -> Index Scan using pg_type_oid_index on pg_type te > (cost=0.42..2.44 rows=1 width=4) (actual time=0.020..0.020 rows=1 > loops=355428) >Index Cond: (oid = t.typelem) > Planning Time: 0.535 ms > Execution Time: 774011.175 ms > (35 rows) > > The high number of rows in pg_class result from more than ~550 schemata, > each containing more than 600 tables. It's part of a multi tenant setup > where each tenant lives in its own schema. > > I began to move schemata to another database cluster to reduce the number > of rows in pg_class but I'm having a hard time doing so as a call to > pg_dump might result in a database restart. > > Is there anything I can do to improve that situation? > Can you try: 1. Limit resource usage by Postgres, with cgroups configuration. 2. pg_dump compression: man pgsql -Z 3. Run vacuum and reindex before? regards, Ranier Vilela
Re: PostgreSql 9.4 Database connection failure
Em sex., 10 de set. de 2021 às 06:26, Lionel Napoleon < lionelnapol...@hotmail.com> escreveu: > Hi , > Hi, please when you post, choose to post to all. I was able to rectify most of the problems since they were from my > application side bugs. > Good to know. However the following entry keeps coming in the postgresql log file: > > 2021-09-10 12:06:33 IST LOG: could not receive data from client: No > connection could be made because the target machine actively refused it. > Some connection (socket) from server, can't receive data from client. Client stuck? > The application seems to be working despite of this log.My question is > ..do I need to be worried of the above message > I think yes, your client still has some bug, but you can solve this during development. regards, Ranier Vilela
Re: PostgreSql 9.4 Database connection failure
Em qui., 9 de set. de 2021 às 04:46, Lionel Napoleon < lionelnapol...@hotmail.com> escreveu: > Hi, > I have an issue with my PostgreSql 9.4 version database. Almost > every week I get the following error > > Error: #2147500037 > Could not connect to the server; > Could not connect to remote socket immedaitely > Source: Microsoft OLE DB Provider for ODBC Drivers > SQL State: 08001 > > My application and database are on the same system. The communicating port > being 5432. > initially I thought it was the permissible connection limit issue. So I > raised the max_connections > parameter to 300.But still the problem exists. > > I think that question will be better answered at: https://www.postgresql.org/list/pgsql-general/ However, it seems to me that this is a bug with Microsoft ODBC. regards, Ranier Vilela
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Em qui., 2 de set. de 2021 às 12:22, Andrew Dunstan escreveu: > > On 8/31/21 1:55 PM, Andrew Dunstan wrote: > > On 8/31/21 11:37 AM, Julien Rouhaud wrote: > >> On Tue, Aug 31, 2021 at 10:51 PM l...@laurent-hasson.com > >> wrote: > >>> OK... I thought that track had been abandoned as per Julien's last > message. Anyways, I'll be patient! > >>> > >> I just happened to have both standard installer and locally compiled > >> versions available, so I could confirm that I reproduced the problem > >> at least with the standard installer. Note that my message also said > >> " if default build on windows has NLS included". After looking a bit > >> more into the Windows build system, I confirm that NLS isn't included > >> by default so this is not the problem, as Andrew said. > >> > >> After installing gettext and a few other dependencies, adapting > >> config.pl I wish I could also confirm being able to reproduce the > >> problem on my build, but apparently I'm missing something as I can't > >> get any modification in config.pl have any effect. I'm not gonna > >> waste more time on that since Andrew is already in the middle of the > >> investigation. > > > > > > The culprit turns out to be the precise version of libiconv/libintl > > used. There is a slight difference between the versions used in the > > 11.13 installer and the 13.4 installer. We need to dig into performance > > more (e.g. why does the test take much longer on an NLS enabled build > > even when we are using 'initdb --no-locale'?) But I'm pretty confident > > now that this is the issue. I've started talks with our installer guys > > about fixing it. > > > > > > > Here are a couple of pictures of profiles made with a tool called > sleepy. The bad profile is from release 13.4 built with the latest > gettext, built with vcpkg. The good profile is the same build but using > the intl-8.dll copied from the release 11.13 installer. The good run > takes about a minute. The bad run takes about 30 minutes. > > > I'm not exactly sure what the profiles tell us. > Bug in the libintl? libintl doesn't cache untranslated strings https://savannah.gnu.org/bugs/?58006 regards, Ranier Vilela
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Em dom., 29 de ago. de 2021 às 21:29, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu: > > > From: Ranier Vilela > Sent: Sunday, August 29, 2021 14:20 > To: l...@laurent-hasson.com > Cc: Tom Lane ; Andrew Dunstan ; > Justin Pryzby ; pgsql-performa...@postgresql.org > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and > 13.4 > > Em dom., 29 de ago. de 2021 às 13:03, mailto:l...@laurent-hasson.com > <mailto:l...@laurent-hasson.com> escreveu: > >Sure, there's no question that message translation will have *some* cost. > >But on my machine it is an incremental tens-of-percent kind of cost, > >and that is the result you're getting as well. So it's not very clear > >where these factor-of-several-hundred differences are coming from. > >A hypothesis that has not yet come up, may be some defect in the code > generation, > >by the previous msvc compiler used, because in all my tests I always use > the latest version, > >which has several corrections in the code generation part. > > > > > Hello all, > > I don't think this reproduces the issue I experience. I saw a difference > of around 500x! What you see is 5x, which according to Tom would be > expected for an execution path involving exceptions. And NLS should have an > impact as well since more work happens. From the numbers you published, I > see 10-15% change which again would be expected? > Yes, It seems to me that is expected for NLS usage. > > > I cannot think of anything that would be specific to me with regards to > this scenario given that I have tried it in quite a few environments from > plain stock installs. Until one of you is able to reproduce this, you may > be chasing other issues. > I think I'm unable to reproduce the issue, because I didn't use any plain > stock installs. > Postgres env tests here, is a fresh build with the latest msvc. > I have no intention of repeating the issue, with something exactly the > same as your environment, > but with a very different environment. > > Can you show the version of Postgres, at your Windows 10 env, who got this > result? > Planning Time: 0.171 ms > Execution Time: 88031.585 ms > > regards, > Ranier Vilela > > > > > --- > Hello Ranier, > > All my tests were on latest 13.4 install I downloaded from the main site. > > SELECT version(); > PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit > > > As per the following: > > > I think I'm unable to reproduce the issue, because I didn't use any > plain stock installs. > > Postgres env tests here, is a fresh build with the latest msvc. > > I have no intention of repeating the issue, with something exactly the > same as your environment, > > but with a very different environment. > > I am not sure I understand. Are you saying the standard installs may be > faulty? Not exactly. A stock install from the stock installer on a windows machine should take > 10mn top. If it doesn't reproduce the issue out of the box, then at least I > have a confirmation that there may be something weird that I am somehow > repeating across all the installs I have performed??? > Most likely it's something in your environment, along with your client. All I can say is that it is unreproducible with a build/test made with the latest version of msvc. Windows 10 64 bits. msvc 2019 64 bits. git clone --branch remote/origins/REL_13_4 https://github.com/postgres/postgres/ postgres_13_4 cd postgres_13_4 cd src cd tools cd msvc build install c:\postgres_bench cd\postgres_bench\bin initdb -D c:\postgres_bench\data -E UTF-8 -U postgres -W pg_ctl -D c:\postgres_bench\data -l c:\postgres_bench\log\log1 start psql -U postgres postgres=# select version(); version PostgreSQL 13.4, compiled by Visual C++ build 1929, 64-bit (1 row) postgres=# create table sampletest (a varchar, b varchar); CREATE TABLE postgres=# insert into sampletest (a, b) postgres-# select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar postgres-# from generate_series(1,10); INSERT 0 10 postgres=# postgres=# CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) postgres-# RETURNS real AS $$ postgres$# BEGIN postgres$# RETURN case when str is null then val else str::real end; postgres$# EXCEPTION WHEN OTHERS TH
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Em dom., 29 de ago. de 2021 às 13:03, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu: > >Sure, there's no question that message translation will have *some* cost. > >But on my machine it is an incremental tens-of-percent kind of cost, > >and that is the result you're getting as well. So it's not very clear > >where these factor-of-several-hundred differences are coming from. > >A hypothesis that has not yet come up, may be some defect in the code > generation, > >by the previous msvc compiler used, because in all my tests I always use > the latest version, > >which has several corrections in the code generation part. > > > > > Hello all, > > I don't think this reproduces the issue I experience. I saw a difference > of around 500x! What you see is 5x, which according to Tom would be > expected for an execution path involving exceptions. And NLS should have an > impact as well since more work happens. From the numbers you published, I > see 10-15% change which again would be expected? > Yes, It seems to me that is expected for NLS usage. > > I cannot think of anything that would be specific to me with regards to > this scenario given that I have tried it in quite a few environments from > plain stock installs. Until one of you is able to reproduce this, you may > be chasing other issues. > I think I'm unable to reproduce the issue, because I didn't use any plain stock installs. Postgres env tests here, is a fresh build with the latest msvc. I have no intention of repeating the issue, with something exactly the same as your environment, but with a very different environment. Can you show the version of Postgres, at your Windows 10 env, who got this result? Planning Time: 0.171 ms Execution Time: 88031.585 ms regards, Ranier Vilela
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Em dom., 29 de ago. de 2021 às 10:35, Tom Lane escreveu: > Ranier Vilela writes: > > I retested this case with HEAD, and it seems to me that NLS does affect > it. > > Sure, there's no question that message translation will have *some* cost. > But on my machine it is an incremental tens-of-percent kind of cost, > and that is the result you're getting as well. So it's not very clear > where these factor-of-several-hundred differences are coming from. > A hypothesis that has not yet come up, may be some defect in the code generation, by the previous msvc compiler used, because in all my tests I always use the latest version, which has several corrections in the code generation part. View this test with one of the attempts to reproduce the problem. msvc: 19.29.30133 para x64 windows 10 64 bits Postgres: 12.8 postgres=# select version(); version PostgreSQL 12.8, compiled by Visual C++ build 1929, 64-bit (1 row) postgres=# drop table sampletest; DROP TABLE postgres=# create table sampletest (a varchar, b varchar); CREATE TABLE postgres=# insert into sampletest (a, b) postgres-# select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar postgres-# from generate_series(1,10); INSERT 0 10 postgres=# postgres=# CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) postgres-# RETURNS real AS $$ postgres$# BEGIN postgres$# RETURN case when str is null then val else str::real end; postgres$# EXCEPTION WHEN OTHERS THEN postgres$# RETURN val; postgres$# END; postgres$# $$ LANGUAGE plpgsql COST 1 IMMUTABLE; CREATE FUNCTION postgres=# explain (analyze,buffers,COSTS,TIMING) postgres-# select MAX(toFloat(b, null)) as "b" from sampletest; QUERY PLAN Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual time=339.978..339.979 rows=1 loops=1) Buffers: shared hit=644 -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.032..18.132 rows=10 loops=1) Buffers: shared hit=637 Planning Time: 3.631 ms Execution Time: 340.330 ms (6 rows) postgres=# explain (analyze,buffers,COSTS,TIMING) postgres-# select MAX(toFloat(a, null)) as "a" from sampletest; QUERY PLAN Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual time=1724.902..1724.903 rows=1 loops=1) Buffers: shared hit=640 -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.021..23.489 rows=10 loops=1) Buffers: shared hit=637 Planning Time: 0.150 ms Execution Time: 1724.930 ms (6 rows) regards, Ranier Vilela
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Em sáb., 28 de ago. de 2021 às 22:55, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu: > > >> -Original Message- >> From: Tom Lane >> Sent: Saturday, August 28, 2021 15:51 >> To: l...@laurent-hasson.com >> Cc: Andrew Dunstan ; Justin Pryzby >> ; Ranier Vilela ; pgsql- >> performa...@postgresql.org >> Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 >> and 13.4 >> >> "l...@laurent-hasson.com" writes: >> > SET lc_messages = 'C'; >> > show lc_messages; --> OK 'C' >> >> > explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(b, > null)) as >> > "b" from sampletest ... >> > Execution Time: 175.600 ms >> >> > explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, > null)) as >> > "a" from sampletest ... >> > Execution Time: 88031.585 ms >> >> > Doesn't seem to make a difference unless I misunderstood what you >> were asking for regarding the locale? >> >> Hmm. This suggests that whatever effect Andrew found with NLS is >> actually not the explanation for your problem. So I'm even more >> confused than before. >> >>regards, tom lane > > I am so sorry to hear... So, curious on my end: is this something that you > are not able to reproduce on your environments? On my end, I did reproduce > it on different VMs and my local laptop, across windows Server 2012 and > Windows 10, so I'd figure it would be pretty easy to reproduce? > What does reproduction have to do with solving the problem? Can you tell how many commits there are between the affected versions? I retested this case with HEAD, and it seems to me that NLS does affect it. postgres=# create table sampletest (a varchar, b varchar); CREATE TABLE postgres=# insert into sampletest (a, b) postgres-# select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar postgres-# from generate_series(1,10); INSERT 0 10 postgres=# postgres=# CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) postgres-# RETURNS real AS $$ postgres$# BEGIN postgres$# RETURN case when str is null then val else str::real end; postgres$# EXCEPTION WHEN OTHERS THEN postgres$# RETURN val; postgres$# END; postgres$# $$ LANGUAGE plpgsql COST 1 IMMUTABLE; CREATE FUNCTION postgres=# explain (analyze,buffers,COSTS,TIMING) postgres-# select MAX(toFloat(b, null)) as "b" from sampletest; QUERY PLAN Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual time=386.990..386.991 rows=1 loops=1) Buffers: shared hit=643 read=1 -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.032..17.325 rows=10 loops=1) Buffers: shared hit=637 Planning: Buffers: shared hit=13 read=13 Planning Time: 0.967 ms Execution Time: 387.989 ms (8 rows) postgres=# explain (analyze,buffers,COSTS,TIMING) postgres-# select MAX(toFloat(a, null)) as "a" from sampletest; QUERY PLAN Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual time=1812.556..1812.557 rows=1 loops=1) Buffers: shared hit=639 read=1 -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.026..20.866 rows=10 loops=1) Buffers: shared hit=637 Planning Time: 0.152 ms Execution Time: 1812.587 ms (6 rows) postgres=# SET lc_messages = 'C'; SET postgres=# drop table sampletest; DROP TABLE postgres=# create table sampletest (a varchar, b varchar); CREATE TABLE postgres=# insert into sampletest (a, b) postgres-# select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar postgres-# from generate_series(1,10); INSERT 0 10 postgres=# postgres=# CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) postgres-# RETURNS real AS $$ postgres$# BEGIN postgres$# RETURN case when str is null then val else str::real end; postgres$# EXCEPTION WHEN OTHERS THEN postgres$# RETURN val; postgres$# END; postgres$# $$ LANGUAGE plpgsql COST 1 IMMUTABLE; CREATE FUNCTION postgres=# explain (analyze,buffers,COSTS,TIMING) postgres-# select MAX(toFloat(b, null)) as "b" from sampletest; QUERY PLAN -
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Em dom., 22 de ago. de 2021 às 18:12, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu: > > >> -Original Message- >> From: Tom Lane >> Sent: Sunday, August 22, 2021 16:11 >> To: l...@laurent-hasson.com > > Cc: Justin Pryzby ; Ranier Vilela >> ; pgsql-performa...@postgresql.org >> Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 >> and 13.4 >> >> "l...@laurent-hasson.com" writes: >> > I do have a Linux install of 13.3, and things work beautifully, so > this is >> definitely a Windows thing here that started in V12. >> >> It's good to have a box around it, but that's still a pretty large > box :-(. >> >> I'm hoping that one of our Windows-using developers will see if they > can >> reproduce this, and if so, try to bisect where it started. >> Not sure how to make further progress without that. >> >>regards, tom lane > > Hello Tom, > > If there is any way I can help further... I am definitely not able to do a > dev environment and local build, but if we have a windows developer > reproducing the issue between 11 and 12, then that should help. If someone > makes a debug build available to me, I can provide additional help based on > that. > Please, download from this link (Google Drive): https://drive.google.com/file/d/13kPbNmk54lR6t-lwcwi-63UdM55sA27t/view?usp=sharing Postgres Debug (64 bits) HEAD. regards, Ranier Vilela
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Em dom., 22 de ago. de 2021 às 14:50, Tom Lane escreveu: > Justin Pryzby writes: > > This looks useful, thanks. It seems like maybe win64 builds are very > slow > > running this: > > > exec_stmt_block() / > > BeginInternalSubTransaction() / > > AbortSubTransaction() / > > reschedule_timeouts() / > > schedule_alarm() / > > setitimer() / > > pg_timer_thread() / > > WaitForSingleObjectEx () > > Hmm ... we should not be there unless there are active timeout events, > which there aren't by default. I wonder whether either Ranier or > Laurent have statement_timeout or some similar option enabled. > Tom, none settings, all default from Postgres install. regards, Ranier Vilela
Re: Big performance slowdown from 11.2 to 13.3
Em qui., 22 de jul. de 2021 às 14:28, Peter Geoghegan escreveu: > On Thu, Jul 22, 2021 at 10:11 AM Tom Lane wrote: > > No, he already tried, upthread. The trouble is that he's on a Windows > > machine, so get_hash_mem is quasi-artificially constraining the product > > to 2GB. And he needs it to be a bit more than that. Whether the > > constraint is hitting at the ngroups stage or it's related to actual > > memory consumption isn't that relevant. > > Somehow I missed that part. > > > What I'm wondering about is whether it's worth putting in a solution > > for this issue in isolation, or whether we ought to embark on the > > long-ignored project of getting rid of use of "long" for any > > memory-size-related computations. There would be no chance of > > back-patching something like the latter into v13, though. > > +1. Even if we assume that Windows is a low priority platform, in the > long run it'll be easier to make it more like every other platform. > > The use of "long" is inherently suspect to me. It signals that the > programmer wants something wider than "int", even if the standard > doesn't actually require that "long" be wider. This seems to > contradict what we know to be true for Postgres, which is that in > general it's unsafe to assume that long is int64. It's not just > work_mem related calculations. There is also code like logtape.c, > which uses long for block numbers -- that also exposes us to risk on > Windows. > > By requiring int64 be used instead of long, we don't actually increase > risk for non-Windows platforms to any significant degree. I'm pretty > sure that "long" means int64 on non-Windows 64-bit platforms anyway. > I wonder if similar issues not raise from this [1]. (b/src/backend/optimizer/path/costsize.c) cost_tuplesort uses *long* to store sort_mem_bytes. I suggested switching to int64, but obviously to no avail. +1 to switch long to int64. regards, Ranier Vilela [1] https://www.postgresql.org/message-id/CAApHDvqhUYHYGmovoGWJQ1%2BZ%2B50Mz%3DPV6bW%3DQYEh3Z%2BwZTufPQ%40mail.gmail.com
Re: Planning performance problem (67626.278ms)
Em dom., 20 de jun. de 2021 às 14:50, Manuel Weitzman <
manuelweitz...@gmail.com> escreveu:
> Hello everyone,
>
> > Apparently, the planner isn't reusing the data boundaries across
> alternative
> > plans. It would be nicer if the planner remembered each column boundaries
> > for later reuse (within the same planner execution).
>
> I've written a very naive (and crappy) patch to show how adding
> memorization to get_actual_variable_range() could help the planner on
> scenarios with a big number of joins.
>
> For the previous example,
>
> > explain (analyze, buffers)
> > select * from a
> > join b b1 on (b1.a = a.a)
> > join b b2 on (b2.a = a.a)
> > where b1.a in (1,100,1,100,101);
>
> each time you add a join clause the planner has to read an extra ~5[K]
> buffers and gets about 200[ms] slower.
>
> 1 join
> Planning:
>Buffers: shared hit=9 read=27329
> Planning Time: 101.745 ms
> Execution Time: 0.082 ms
>
> 2 joins
> Planning:
>Buffers: shared hit=42 read=81988
> Planning Time: 303.237 ms
> Execution Time: 0.102 ms
>
> 3 joins
> Planning:
>Buffers: shared hit=94 read=136660
> Planning Time: 508.947 ms
> Execution Time: 0.155 ms
>
> 4 joins
> Planning:
>Buffers: shared hit=188 read=191322
> Planning Time: 710.981 ms
> Execution Time: 0.168 ms
>
>
> After adding memorization the cost in buffers remains constant and the
> latency deteriorates only marginally (as expected) with each join.
>
> 1 join
> Planning:
>Buffers: shared hit=10 read=27328
> Planning Time: 97.889 ms
> Execution Time: 0.066 ms
>
> 2 joins
> Planning:
>Buffers: shared hit=7 read=27331
> Planning Time: 100.589 ms
> Execution Time: 0.111 ms
>
> 3 joins
> Planning:
>Buffers: shared hit=9 read=27329
> Planning Time: 105.669 ms
> Execution Time: 0.134 ms
>
> 4 joins
> Planning:
>Buffers: shared hit=132 read=27370
> Planning Time: 155.716 ms
> Execution Time: 0.219 ms
>
>
> I'd be happy to improve this patch into something better. Though I'd
> like suggestions on how to do it:
> I have this idea of creating a local "memorization" struct instance within
> standard_planner(). That would require passing on a pointer down until
> it reaches get_actual_variable_range(), which I think would be quite
> ugly, if done just to improve the planner for this scenario.
> Is there any better mechanism I could reuse from other modules? (utils
> or cache, for example).
>
Without going into the merits of whether this cache will be adopted or not,
I have some comments about the code.
1. Prefer to use .patch instead of .diff, it makes it easier for browsers
such as firefox to read and show the content automatically.
2. New struct?
Oid is unsigned int, lower than int64.
Better struct is:
+struct ActualVariableRangeCache {
+ int64 min_value; /* 8 bytes */
+ int64 max_value; /* 8 bytes */
+ Oid indexoid; /* 4 bytes */
+ bool has_min; /* 1 byte */
+ bool has_max; /*1 byte */
+};
Takes up less space.
3. Avoid use of type *long*, it is very problematic with 64 bits.
Windows 64 bits, long is 4 (four) bytes.
Linux 64 bits, long is 8 (eight) bytes.
4. Avoid C99 style declarations
for(unsigned long i = 0;)
Prefer:
size_t i;
for(i = 0;)
Helps backpatching to C89 versions.
regards,
Ranier Vilela
Re: waiting for client write
Em sáb., 12 de jun. de 2021 às 05:20, Ayub Khan escreveu: > Ranier, > > This issue is only with queries which are slow, if it's an MTU issue then > it should be with all the APIs. > > I tried on Aurora db and I see same plan and also same slowness > I think it is more indicative that the problem is in the network. Despite having a proposed solution (MTU), you still have to ensure that the main problem happens (*packet loss*). regards, Ranier Vilela
Re: waiting for client write
Em sex., 11 de jun. de 2021 às 15:19, Ayub Khan escreveu: > Ranier, > > I verified the link you gave and also checked AWS documentation and found > the exact output as shown in AWS: > > https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/network_mtu.html > > [ec2-user ~]$ tracepath amazon.com > 1?: [LOCALHOST] pmtu 9001 > 1: ip-xxx-xx-xx-1.us-west-1.compute.internal (xxx.xx.xx.x) 0.187ms pmtu > 1500 > > Should the LOCALHOST pmtu needs to be updated to 1500 ? > > Or us-west-1.compute.internal should be set to 9000. I think both must match. 9000 are jumbo frames. The bigger the better. Try switching to 9000 first and 1500 if it does not work. regards, Ranier Vilela
Re: waiting for client write
Em sex., 11 de jun. de 2021 às 14:59, Ayub Khan escreveu: > Ranier, > > I tried to VACCUM ANALYZE the tables involved multiple times and also > tried the statistics approach as well > Ayub you can try by the network side: https://stackoverflow.com/questions/50298447/postgres-jdbc-client-getting-stuck-at-reading-from-socket " We found out that this was caused by the database server's MTU setting. MTU was set to 9000 by default and resulted in packet loss. Changing it to 1500 resolved the issue." regards, Ranier Vilela
Re: waiting for client write
t;restaurant_idx$$_274b003d" on restaurant f > (cost=0.29..2.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=89) > Index Cond: (restaurant_id = 1528) > -> Seq Scan on mark m (cost=0.00..1.03 rows=3 width=12) (actual > time=0.000..0.001 rows=3 loops=89) > Planning Time: 2.078 ms > Execution Time: 5.141 ms > > My guess is a bad planner (or slow planner) because of wrong or inconsistent parameters. You must define precise parameters before calling a Prepared Statement or Planner will try to guess to do the best. But this is a simple "guess" and can be completely wrong. regards, Ranier Vilela
Re: waiting for client write
Em sex., 11 de jun. de 2021 às 13:28, Ayub Khan
escreveu:
> Vijay,
>
> Both tomcat and postgresql are on the same region as that of the database
> server. It is an RDS so I do not have shell access to it.
>
> Jeff,
>
> The tomcat profile is suggesting that it's waiting for a response from the
> database server.
>
> Tomcat and RDS are in the same availability region as eu-central-1a
>
> On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan wrote:
>
>> attached is the screenshot of RDS performance insights for AWS and it
>> shows high waiting client writes. The api performance is slow. I read that
>> this might be due to IOPS on RDS. However we have 80k IOPS on this test
>> RDS.
>>
>> Below is the query which is being load tested
>>
>> SELECT
>>
>>a.menu_item_id,
>>a.menu_item_name,
>>a.menu_item_category_id,
>>b.menu_item_category_desc,
>>c.menu_item_variant_id,
>>c.menu_item_variant_type_id,
>>c.price,
>>c.size_id,
>>c.parent_menu_item_variant_id,
>>d.menu_item_variant_type_desc,
>>e.size_desc,
>>f.currency_code,
>>a.image,
>>a.mark_id,
>>m.mark_name
>>
>> FROM .menu_item_category AS b, .menu_item_variant AS
>> c,
>>.menu_item_variant_type AS d, .item_size AS e,
>> .restaurant AS f,
>>.menu_item AS a
>>
>>LEFT OUTER JOIN .mark AS m
>> ON (a.mark_id = m.mark_id)
>>
>> WHERE a.menu_item_category_id =
>> b.menu_item_category_id AND a.menu_item_id = c.menu_item_id AND
>>c.menu_item_variant_type_id =
>> d.menu_item_variant_type_id AND d.is_hidden = 'false' AND
>>c.size_id = e.size_id AND a.restaurant_id =
>> f.restaurant_id AND f.restaurant_id = 1528 AND
>>(a.menu_item_category_id = NULL OR NULL IS
>> NULL)
>>
>>AND c.menu_item_variant_id = (SELECT
>> min(menu_item_variant_id)
>> FROM
>> .menu_item_variant
>> WHERE
>> menu_item_id = a.menu_item_id AND deleted = 'N'
>> LIMIT 1) AND
>> a.active = 'Y'
>>AND (CONCAT_WS('', ',', a.hidden_branch_ids,
>> ',') NOT LIKE CONCAT_WS('', '%,4191,%') OR
>> NULL IS NULL)
>>AND .is_menu_item_available(a.menu_item_id, 'Y') = 'Y'
>>
>>ORDER BY a.row_order, menu_item_id;
>>
>> --Ayub
>>
> Can you post the results with: explain analyze?
EXPLAIN ANALYZE
SELECT
regards,
Ranier Vilela
Re: AWS forcing PG upgrade from v9.6 a disaster
Em qui., 10 de jun. de 2021 às 13:08, Dean Gibson (DB Administrator) < postgre...@mailpen.com> escreveu: > On 2021-06-10 03:29, Andrew Dunstan wrote: > > On 6/9/21 9:50 PM, Dean Gibson (DB Administrator) wrote: > > First, pg_dumpall (v13.3) errors out, because on RDS, you cannot be a > superuser, & it tries to dump protected stuff. If there is a way around > that, I'd like to know it, even though it's not an issue now. pg_dump works > OK, but of course you don't get the roles dumped. Fortunately, I kept script > files that have all the database setup, so I just ran them to create all the > relationships, & then used the pg_dump output. Worked flawlessly. > > This was added in release 12 specifically with RDS in mind: > > pg_dumpall --exclude-database > > cheers, andrew > > > I guess I don't understand what that option does: > > =>pg_dumpall -U Admin --exclude-database MailPen >zzz.sql > pg_dump: error: could not write to output file: No space left on device > pg_dumpall: error: pg_dump failed on database "MailPen", exiting > > I expected a tiny file, not 3.5GB. "MailPen" is the only database (other > than what's pre-installed). Do I need quotes on the command line? > See at: https://www.postgresql.org/docs/13/app-pg-dumpall.html Your cmd lacks = =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql regards, Ranier Vilela
Re: Plan not skipping unnecessary inner join
Em dom., 17 de mai. de 2020 às 10:31, Justin Pryzby escreveu: > On Sun, May 17, 2020 at 09:32:47AM -0300, Ranier Vilela wrote: > > I redid the same tests with vanila postgres and with empty tables. > > I'm surprised, why does the plan have 2550 rows in explain? > > That's the *estimated* rowcount. > > The planner tends to ignore table statistics which say the table is empty, > since that can lead to a terrible plan if it's not true (stats are out of > date > or autovacuum threshold not hit). > Thanks for the explanation. regards, Ranier Vilela
Re: Plan not skipping unnecessary inner join
I redid the same tests with vanila postgres and with empty tables. I'm surprised, why does the plan have 2550 rows in explain? regards, Ranier Vilela
