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
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?
>
>
;> .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
-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
ime=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
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
tive 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
"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
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
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
>
ether either Ranier or
> Laurent have statement_timeout or some similar option enabled.
>
Tom, none settings, all default from Postgres install.
regards,
Ranier Vilela
> 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:
>
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 i
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...@postgr
onment,
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
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
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
uck?
> 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
gt; (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
..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
nge 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
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
t 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
s 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
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
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
>
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
>
gt; 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
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.
>
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
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
&
; > 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 direct
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
m 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
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?
"
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
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
ts transmitted, 4 received, 0% packet loss, time 3000ms
>
3000 ms?
Are sure that haven't packet loss?
regards,
Ranier Vilela
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
>
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
= 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
iguration.
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
ta_raw:frd_raw_sc_n_20220729t00-20220729t235959'::text;
::text?
regards,
Ranier Vilela
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
>
iles?
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
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
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
>
9811, ...
> ... ,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
ally 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
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>
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
cript (running on the same CPU), I get ~1000
> request/s.
>
Can you share kernel and python detalis? (version, etc).
regards,
Ranier Vilela
ndex as a foreign key, it seems to me that it
is not being considered.
My 2cents.
Best regards,
Ranier Vilela
53 matches
Mail list logo
