Re: Speedup of relation deletes during recovery
Fujii Masao writes: > Hi, > > When multiple relations are deleted at the same transaction, > the files of those relations are deleted by one call to smgrdounlinkall(), > which leads to scan whole shared_buffers only one time. OTOH, > during recovery, smgrdounlink() (not smgrdounlinkall()) is called > for each file to delete, which leads to scan shared_buffers multiple times. > Obviously this can cause to increase the WAL replay time very much > especially when shared_buffers is huge. Wonder if this is the case for streaming standbys replaying truncates also? Just couple days ago I was running a pg_upgrade test scenario but did not reach the point of upgrade yet. We made snapshots of our large reporting system putting them into a master and 1-slave streaming replication configuration. There are hundreds of unlogged tables that we wish to trunc before the upgrade to save time during the rsyncing of standbys, since a standard rsync replicates all data which is timeconsumeing and useless sending to the standbys. Indeed the tables are already trunc'd on the test master since it too was a recovered system upon initial start but the truncates took place anyway since it's part of our framework. It ran in just a few seconds on master. The standby however was $slow replaying these truncates which we noticed because the upgrade wil not proceed until master and 1 or more standbys are confirmed all at same checkpoint. I straced the standby's startup process to find it unlinking lots of tables, getting -1 on the unlink syscall since the non-init fork files were already missing. I can't describe just how slow if was but took minutes and the lines being output by strace were *not* blowing up my display as happens generally when any busy process is straced. And, the test systems were config'd with $large shared buffers of 64G. Please advise > > To alleviate this situation, I'd like to propose to change the recovery > so that it also calls smgrdounlinkall() only one time to delete multiple > relation files. Patch attached. Thought? > > Regards, -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: Speedup of relation deletes during recovery
Fujii Masao writes: > Hi, > > When multiple relations are deleted at the same transaction, > the files of those relations are deleted by one call to smgrdounlinkall(), > which leads to scan whole shared_buffers only one time. OTOH, > during recovery, smgrdounlink() (not smgrdounlinkall()) is called > for each file to delete, which leads to scan shared_buffers multiple times. > Obviously this can cause to increase the WAL replay time very much > especially when shared_buffers is huge. Forgot to mention version in my TLDR prev reply :-) version PostgreSQL 9.5.12 on x86_64-pc-linux-gnu (Ubuntu 9.5.12-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit (1 row) > > To alleviate this situation, I'd like to propose to change the recovery > so that it also calls smgrdounlinkall() only one time to delete multiple > relation files. Patch attached. Thought? > > Regards, -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: Racing DEADLOCK on PostgreSQL 9.3
Nick Dro writes: > Hi, > I have a stock table. > > One of the users in the system ran this query: update stock set > quantity=quantity-5 where stockid=100 (from his client application). > On the same time I ran from pg-admin this query: > > do $$ > begin > alter table stock disable trigger stock_aftertrigger; > update stock set stock=0 where stockid=106; > alter table stock enable trigger stock_aftertrigger; > end; $$ > > What actualy happened is that both queries were stuck on waiting > (after 3 minutes I decided to investagate as there quries should be > extremly fast!). I suspect your alter trigger job was blocked first by something else and the more trivial update blocked behind you, which is not a *deadlock* but a legit case of MVCC. A real case of deadlock should have been broken in about 1s by the lock management policy unless you are running a configuration with huge deadlock timeout. That your alter statement needs a heavy lock means that it can be easily blocked and in so doing, block anything else whatsoever also requiring access to same objects. > I ran also this query: > > SELECT > pid, > now() - pg_stat_activity.query_start AS duration, > query, > state, * > FROM pg_stat_activity > WHERE waiting > > > and both users were on waiting. When I stopped my query the other > user got imiddiate result, then I reran mine which also finished > immidiatly. > I don't understand why both queries were stuck, the logic thing is > that one ran and the other one is waiting (if locks aquired etc) it > doesnt make senece that both queries are on waiting. waiting for what > exactly? > > > Any thoughts on this issue? > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: Index corruption / planner issue with one table in my pg 11.6 instance
Peter Geoghegan writes: > On Mon, Dec 9, 2019 at 6:33 PM Michael Paquier wrote: > >> Something new as of 11 is that btree indexes can be built in parallel, >> and before releasing it we found some bugs with covering indexes. >> Perhaps we have an issue hidden behind one of these, but hard to be >> sure. > > I doubt it. > > Jeremy did not report queries that give wrong answers. He only said > that the optimizer refused to use one particular index, before a > VACUUM FULL seemingly corrected the problem. OTOH, Jeremy did report > using contrib/amcheck on the index, which didn't complain. (Note also > that the amcheck functions will throw an error with an !indisvalid > index.) I suspect this was due to indcheckxmin=true for the involved index and the documented (but IMO confusing) interplay w/broken hot-chains and visibility. Checking the same DB today, I find 35 indexes across the entire system having indcheckxmin=true, including one on the same table, though not the same index that Pg refused to use recently. Many of the indexes have very old xmins and thus s/b all considered in plans. I was able to get that remaining index out of the indcheckxmin=true list by... 1. Reindexing $index (did not change anything) 2. begin; drop; create; commit (still in the list but with a much newer xmin.) 3. Vac-Full the table again (and now the index is gone from the indcheckxmin=true list.) Please advise. Thx -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
Re: [patch] Add schema total size to psql \dn+
Gilles Darold writes:
> Le 21/02/2019 à 18:28, Julien Rouhaud a écrit :
>
>> On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold
>> wrote:
>>> Le 21/02/2019 à 12:01, Julien Rouhaud a écrit :
>>>> On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold
>>>> wrote:
>>>>>> When we want to get total size of all relation in a schema we have to
>>>>>> execute one of our favorite DBA query. It is quite simple but what
>>>>>> about displaying schema size when using \dn+ in psql ?
>>>>>> [...]
>>>>>> The attached simple patch adds this feature. Is there any cons adding
>>>>>> this information? The patch tries to be compatible to all PostgreSQL
>>>>>> version. Let me know if I have missed something.
>> I have a few comments about the patch.
>>
>> You're using pg_class LEFT JOIN pg_namespace while we need INNER JOIN
>> here AFAICT. Also, you're using pg_relation_size(), so fsm, vm won't
>> be accounted for. You should also be bypassing the size for 8.0-
>> servers where there's no pg_*_size() functions.
>
>
> I agree all points. Attached is a new version of the patch that use
> pg_total_relation_size() and a filter on relkind IN ('r','m','S'), JOIN
> fixes and no size report before 8.1.
Beware that those pg_relation_size() functions are going to block in
cases where existing objects are (for example) in transactionss such
as...
begin;
truncate foo;
big-nasty-reporting-jobs...;
Thus a bare-metal tallying of pg_class.relpages for heap/index/toast,
along with missing the FSM/VM size could be $preferred.
And/or at least mentioning this caveat in the related manual section :-)
FWIW
>
>
> Thanks for the review.
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
Re: pg_upgrade: Pass -j down to vacuumdb
Peter Eisentraut writes: > On 02/01/2019 20:47, Jesper Pedersen wrote: > >> Well, that really depends. The user passed -j to pg_upgrade in order for >> the upgrade to happen faster, so maybe they would expect, as I would, >> that the ANALYZE phase would happen in parallel too. > > pg_upgrade -j reduces the *downtime* caused by pg_upgrade during the > upgrade process. Also, during said downtime, nothing else is happening, > so you can use all the resources of the machine. > > Once the system is back up, you don't necessarily want to use all the > resources. The analyze script is specifically written to run while > production traffic is active. If you just want to run the analyze as > fast as possible, you can just run vacuumdb -j directly, without using > the script. Peter, I'm skeptical here. I might permit a connection to a just pg_upgraded DB prior to any analyze being known finished only for the most trivial case. At my site however, *trivial* systems are a small minority. In fact, our automated upgrade workflow uses our home-built parallel analyzer which predates vacuumdb -j. Apps are not allowed into the DB until a fast 1st pass has been done. We run it in 2 phases... $all preceeding upgrade steps w/system locked out analyze-lite (reduced stats target) open DB for application traffic analyze-full Of course we are increasing downtime by disallowing app traffic till finish of analyze-lite however the assumption is that many queries would be too slow to attempt without full analyzer coverage, albiet at a reduced stats target. IMO this is certainly a case of no 1-size-fits-all solution so perhaps a --analyze-jobs option :-) FWIW Thanks > Moreover, it's not clear that pg_upgrade and vacuumdb are bound the same > way, so it's not a given that the same -j number should be used. > > Perhaps more documentation would be useful here. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
SegFault on 9.6.14
Greetings Hackers. We have a reproduceable case of $subject that issues a backtrace such as seen below. The query that I'd prefer to sanitize before sending is <30 lines of at a glance, not terribly complex logic. It nonetheless dies hard after a few seconds of running and as expected, results in an automatic all-backend restart. Please advise on how to proceed. Thanks! bt #0 initscan (scan=scan@entry=0x55d7a7daa0b0, key=0x0, keep_startblock=keep_startblock@entry=1 '\001') at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/heap/heapam.c:233 #1 0x55d7a72fa8d0 in heap_rescan (scan=0x55d7a7daa0b0, key=key@entry=0x0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/heap/heapam.c:1529 #2 0x55d7a7451fef in ExecReScanSeqScan (node=node@entry=0x55d7a7d85100) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeSeqscan.c:280 #3 0x55d7a742d36e in ExecReScan (node=0x55d7a7d85100) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:158 #4 0x55d7a7445d38 in ExecReScanGather (node=node@entry=0x55d7a7d84d30) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeGather.c:475 #5 0x55d7a742d255 in ExecReScan (node=0x55d7a7d84d30) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:166 #6 0x55d7a7448673 in ExecReScanHashJoin (node=node@entry=0x55d7a7d84110) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeHashjoin.c:1019 #7 0x55d7a742d29e in ExecReScan (node=node@entry=0x55d7a7d84110) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:226 -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
Re: SegFault on 9.6.14
Tomas Vondra writes: > On Mon, Jul 15, 2019 at 06:48:05PM -0500, Jerry Sievers wrote: > >>Greetings Hackers. >> >>We have a reproduceable case of $subject that issues a backtrace such as >>seen below. >> >>The query that I'd prefer to sanitize before sending is <30 lines of at >>a glance, not terribly complex logic. >> >>It nonetheless dies hard after a few seconds of running and as expected, >>results in an automatic all-backend restart. >> >>Please advise on how to proceed. Thanks! >> >>bt >>#0 initscan (scan=scan@entry=0x55d7a7daa0b0, key=0x0, >>keep_startblock=keep_startblock@entry=1 '\001') >>at >> /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/heap/heapam.c:233 >>#1 0x55d7a72fa8d0 in heap_rescan (scan=0x55d7a7daa0b0, >>key=key@entry=0x0) at >>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/heap/heapam.c:1529 >>#2 0x55d7a7451fef in ExecReScanSeqScan (node=node@entry=0x55d7a7d85100) >>at >>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeSeqscan.c:280 >>#3 0x55d7a742d36e in ExecReScan (node=0x55d7a7d85100) at >>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:158 >>#4 0x55d7a7445d38 in ExecReScanGather (node=node@entry=0x55d7a7d84d30) >>at >>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeGather.c:475 >>#5 0x55d7a742d255 in ExecReScan (node=0x55d7a7d84d30) at >>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:166 >>#6 0x55d7a7448673 in ExecReScanHashJoin (node=node@entry=0x55d7a7d84110) >>at >>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeHashjoin.c:1019 >>#7 0x55d7a742d29e in ExecReScan (node=node@entry=0x55d7a7d84110) at >>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:226 >> >> > > Hmmm, that means it's crashing here: > >if (scan->rs_parallel != NULL) >scan->rs_nblocks = scan->rs_parallel->phs_nblocks; <--- here >else >scan->rs_nblocks = RelationGetNumberOfBlocks(scan->rs_rd); > > But clearly, scan is valid (otherwise it'd crash on the if condition), > and scan->rs_parallel must me non-NULL. Which probably means the pointer > is (no longer) valid. > > Could it be that the rs_parallel DSM disappears on rescan, or something > like that? No clue but something I just tried was to disable parallelism by setting max_parallel_workers_per_gather to 0 and however the query has not finished after a few minutes, there is no crash. Please advise. Thx > > > regards -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
Re: SegFault on 9.6.14
Tomas Vondra writes: > On Mon, Jul 15, 2019 at 07:22:55PM -0500, Jerry Sievers wrote: > >>Tomas Vondra writes: >> >>> On Mon, Jul 15, 2019 at 06:48:05PM -0500, Jerry Sievers wrote: >>> >>>>Greetings Hackers. >>>> >>>>We have a reproduceable case of $subject that issues a backtrace such as >>>>seen below. >>>> >>>>The query that I'd prefer to sanitize before sending is <30 lines of at >>>>a glance, not terribly complex logic. >>>> >>>>It nonetheless dies hard after a few seconds of running and as expected, >>>>results in an automatic all-backend restart. >>>> >>>>Please advise on how to proceed. Thanks! >>>> >>>>bt >>>>#0 initscan (scan=scan@entry=0x55d7a7daa0b0, key=0x0, >>>>keep_startblock=keep_startblock@entry=1 '\001') >>>>at >>>> /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/heap/heapam.c:233 >>>>#1 0x55d7a72fa8d0 in heap_rescan (scan=0x55d7a7daa0b0, >>>>key=key@entry=0x0) at >>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/heap/heapam.c:1529 >>>>#2 0x55d7a7451fef in ExecReScanSeqScan >>>>(node=node@entry=0x55d7a7d85100) at >>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeSeqscan.c:280 >>>>#3 0x55d7a742d36e in ExecReScan (node=0x55d7a7d85100) at >>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:158 >>>>#4 0x55d7a7445d38 in ExecReScanGather (node=node@entry=0x55d7a7d84d30) >>>>at >>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeGather.c:475 >>>>#5 0x55d7a742d255 in ExecReScan (node=0x55d7a7d84d30) at >>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:166 >>>>#6 0x55d7a7448673 in ExecReScanHashJoin >>>>(node=node@entry=0x55d7a7d84110) at >>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeHashjoin.c:1019 >>>>#7 0x55d7a742d29e in ExecReScan (node=node@entry=0x55d7a7d84110) at >>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:226 >>>> >>>> >>> >>> Hmmm, that means it's crashing here: >>> >>>if (scan->rs_parallel != NULL) >>>scan->rs_nblocks = scan->rs_parallel->phs_nblocks; <--- here >>>else >>>scan->rs_nblocks = RelationGetNumberOfBlocks(scan->rs_rd); >>> >>> But clearly, scan is valid (otherwise it'd crash on the if condition), >>> and scan->rs_parallel must me non-NULL. Which probably means the pointer >>> is (no longer) valid. >>> >>> Could it be that the rs_parallel DSM disappears on rescan, or something >>> like that? >> >>No clue but something I just tried was to disable parallelism by setting >>max_parallel_workers_per_gather to 0 and however the query has not >>finished after a few minutes, there is no crash. >> > > That might be a hint my rough analysis was somewhat correct. The > question is whether the non-parallel plan does the same thing. Maybe it > picks a plan that does not require rescans, or something like that. > >>Please advise. >> > > It would be useful to see (a) exacution plan of the query, (b) full > backtrace and (c) a bit of context for the place where it crashed. > > Something like (in gdb): > >bt full >list >p *scan The p *scan did nothing unless I ran it first however my gdb $foo isn't strong presently. I'll need to sanitize the explain output but can do so ASAP and send it along. Thx! $ gdb /usr/lib/postgresql/9.6/bin/postgres core GNU gdb (Ubuntu 7.11.1-0ubuntu1~16.5) 7.11.1 Copyright (C) 2016 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details. This GDB was configured as "x86_64-linux-gnu". Type "show configuration" for configuration details. For bug reporting instructions, please see: <http://www.gnu.org/software/gdb/bugs/>. Find the GDB manual and other documentation resources online at: <http://www.gnu.org/software/gdb/doc
Re: SegFault on 9.6.14
Tomas Vondra writes: > On Mon, Jul 15, 2019 at 08:20:00PM -0500, Jerry Sievers wrote: > >>Tomas Vondra writes: >> >>> On Mon, Jul 15, 2019 at 07:22:55PM -0500, Jerry Sievers wrote: >>> >>>>Tomas Vondra writes: >>>> >>>>> On Mon, Jul 15, 2019 at 06:48:05PM -0500, Jerry Sievers wrote: >>>>> >>>>>>Greetings Hackers. >>>>>> >>>>>>We have a reproduceable case of $subject that issues a backtrace such as >>>>>>seen below. >>>>>> >>>>>>The query that I'd prefer to sanitize before sending is <30 lines of at >>>>>>a glance, not terribly complex logic. >>>>>> >>>>>>It nonetheless dies hard after a few seconds of running and as expected, >>>>>>results in an automatic all-backend restart. >>>>>> >>>>>>Please advise on how to proceed. Thanks! >>>>>> >>>>>>bt >>>>>>#0 initscan (scan=scan@entry=0x55d7a7daa0b0, key=0x0, >>>>>>keep_startblock=keep_startblock@entry=1 '\001') >>>>>>at >>>>>> /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/heap/heapam.c:233 >>>>>>#1 0x55d7a72fa8d0 in heap_rescan (scan=0x55d7a7daa0b0, >>>>>>key=key@entry=0x0) at >>>>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/heap/heapam.c:1529 >>>>>>#2 0x55d7a7451fef in ExecReScanSeqScan >>>>>>(node=node@entry=0x55d7a7d85100) at >>>>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeSeqscan.c:280 >>>>>>#3 0x55d7a742d36e in ExecReScan (node=0x55d7a7d85100) at >>>>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:158 >>>>>>#4 0x55d7a7445d38 in ExecReScanGather >>>>>>(node=node@entry=0x55d7a7d84d30) at >>>>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeGather.c:475 >>>>>>#5 0x55d7a742d255 in ExecReScan (node=0x55d7a7d84d30) at >>>>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:166 >>>>>>#6 0x55d7a7448673 in ExecReScanHashJoin >>>>>>(node=node@entry=0x55d7a7d84110) at >>>>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeHashjoin.c:1019 >>>>>>#7 0x55d7a742d29e in ExecReScan (node=node@entry=0x55d7a7d84110) at >>>>>>/build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execAmi.c:226 >>>>>> >>>>>> >>>>> >>>>> Hmmm, that means it's crashing here: >>>>> >>>>>if (scan->rs_parallel != NULL) >>>>>scan->rs_nblocks = scan->rs_parallel->phs_nblocks; <--- here >>>>>else >>>>>scan->rs_nblocks = RelationGetNumberOfBlocks(scan->rs_rd); >>>>> >>>>> But clearly, scan is valid (otherwise it'd crash on the if condition), >>>>> and scan->rs_parallel must me non-NULL. Which probably means the pointer >>>>> is (no longer) valid. >>>>> >>>>> Could it be that the rs_parallel DSM disappears on rescan, or something >>>>> like that? >>>> >>>>No clue but something I just tried was to disable parallelism by setting >>>>max_parallel_workers_per_gather to 0 and however the query has not >>>>finished after a few minutes, there is no crash. >>>> >>> >>> That might be a hint my rough analysis was somewhat correct. The >>> question is whether the non-parallel plan does the same thing. Maybe it >>> picks a plan that does not require rescans, or something like that. >>> >>>>Please advise. >>>> >>> >>> It would be useful to see (a) exacution plan of the query, (b) full >>> backtrace and (c) a bit of context for the place where it crashed. >>> >>> Something like (in gdb): >>> >>>bt full >>>list >>>p *scan >> >>The p *scan did nothing unless I ran it first however my gdb $foo isn't >>strong presently. > > Hmm, the rs_parallel pointer looks sane (it's not
Re: SegFault on 9.6.14
Thomas Munro writes:
> On Wed, Jul 17, 2019 at 11:06 AM Jerry Sievers wrote:
>
>> (gdb) p *scan->rs_parallel
>> Cannot access memory at address 0x7fa673a54108
>
> So I guess one question is: was it a valid address that's been
> unexpectedly unmapped, or is the pointer corrupted? Any chance you
> can strace the backend and pull out the map, unmap calls?
I'll dig further.
Here is a sanitized look at the query and explain plan...
The segfault happens $immediately upon issuance of the query.
begin;
-- This setting makes the segfault go away
--set local max_parallel_workers_per_gather to 0;
explain
select v.account_id, COUNT(cnt.clicks), te.description,
l.product_id
from thing3.thing10 te
join thing3.thing9 pv on pv.page_view_id = te.page_view_id
join thing3.thing11 v on v.visit_id = pv.visit_id
left join thing6.thing12 l on v.account_id=l.account_id
left join lateral (
select MAX(v.visit_id)
,COUNT(*) as clicks
from thing3.thing10 te
join thing3.thing9 pv on pv.page_view_id =
te.page_view_id
join thing3.thing11 v on v.visit_id = pv.visit_id
where te.description in ('thing7',
'thing8')
and v.account_id=l.account_id
GROUP BY v.account_id, v.visit_id
order by v.account_id, v.visit_id desc
limit 1
)cnt on true
where (te.description in ('thing4',
'thing5')
or te.description like'%auto%')
and te.created_at > '2019-06-24 00:00:00'
--and l.loan_status_id in (5,6)
group by v.account_id, te.description,
l.product_id;
abort;
BEGIN
QUERY PLAN
---
GroupAggregate (cost=12300178.71..12300179.79 rows=48 width=44)
Group Key: v.account_id, te.description, l.product_id
-> Sort (cost=12300178.71..12300178.83 rows=48 width=44)
Sort Key: v.account_id, te.description, l.product_id
-> Nested Loop Left Join (cost=251621.81..12300177.37 rows=48
width=44)
-> Gather (cost=1001.55..270403.27 rows=48 width=40)
Workers Planned: 3
-> Nested Loop Left Join (cost=1.56..269398.47 rows=15
width=40)
-> Nested Loop (cost=1.13..269391.71 rows=14
width=32)
-> Nested Loop (cost=0.57..269368.66 rows=39
width=32)
-> Parallel Seq Scan on thing10 te
(cost=0.00..269228.36 rows=39 width=32)
Filter: ((created_at > '2019-06-24
00:00:00'::timestamp without time zone) AND (((description)::text = ANY
('{thing4,thing5}'::text[])) OR ((description)::text ~~ '%auto%'::text)))
-> Index Scan using page_views_pkey on
thing9 pv (cost=0.57..3.59 rows=1 width=8)
Index Cond: (page_view_id =
te.page_view_id)
-> Index Scan using visits_pkey on thing11 v
(cost=0.56..0.58 rows=1 width=8)
Index Cond: (visit_id = pv.visit_id)
-> Index Scan using index_loans_on_account_id on
thing12 l (cost=0.42..0.46 rows=2 width=8)
Index Cond: (v.account_id = account_id)
-> Limit (cost=250620.25..250620.27 rows=1 width=20)
-> GroupAggregate (cost=250620.25..250620.27 rows=1
width=20)
Group Key: v_1.visit_id
-> Sort (cost=250620.25..250620.26 rows=1 width=8)
Sort Key: v_1.visit_id DESC
-> Hash Join (cost=1154.34..250620.24 rows=1
width=8)
Hash Cond: (te_1.page_view_id =
pv_1.page_view_id)
-> Gather (cost=1000.00..250452.00
rows=3706 width=4)
Workers Planned: 3
-> Parallel Seq Scan on thing10
te_1 (cost=0.00..249081.40 rows=1195 width=4)
Filter: ((description)::text
= ANY ('{thing7,thing8}'::text[]))
-> Hash (cost=152.85..152.85 rows=119
width=12)
Re: SegFault on 9.6.14
Thomas Munro writes: > On Wed, Jul 17, 2019 at 11:06 AM Jerry Sievers wrote: > >> (gdb) p *scan->rs_parallel >> Cannot access memory at address 0x7fa673a54108 > > So I guess one question is: was it a valid address that's been > unexpectedly unmapped, or is the pointer corrupted? Any chance you > can strace the backend and pull out the map, unmap calls? There were about 60k lines from strace including these few... mmap(NULL, 528384, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f3d0127a000 mmap(NULL, 266240, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f3d01239000 mmap(NULL, 287624, PROT_READ|PROT_WRITE, MAP_SHARED, 124, 0) = 0x7f3d011f2000 mmap(NULL, 262504, PROT_READ|PROT_WRITE, MAP_SHARED, 124, 0) = 0x7f3d011b1000 munmap(0x7f3d011b1000, 262504) = 0 Thx -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
Re: SegFault on 9.6.14
Thomas Munro writes: > On Wed, Jul 17, 2019 at 11:33 AM Jerry Sievers wrote: > >> -> Nested Loop Left Join (cost=251621.81..12300177.37 rows=48 >> width=44) >>-> Gather (cost=1001.55..270403.27 rows=48 width=40) > >>-> Limit (cost=250620.25..250620.27 rows=1 width=20) > >>-> Gather (cost=1000.00..250452.00 >> rows=3706 width=4) > > One observation is that it's a rescan a bit like the one in the > unsuccessful repro attempt I posted, but it has *two* Gather nodes in > it (and thus two parallel query DSM segments), and only one of them > should be rescanned, and from the backtrace we see that it is indeed > the expected one, the one under the Limit operator. Neither of them > should be getting unmapped in the leader though and AFAIK nothing > happening in the workers could cause this effect, the leader would > have to explicitly unmap the thing AFAIK. > > On Wed, Jul 17, 2019 at 11:42 AM Jerry Sievers wrote: >> mmap(NULL, 287624, PROT_READ|PROT_WRITE, MAP_SHARED, 124, 0) = 0x7f3d011f2000 >> mmap(NULL, 262504, PROT_READ|PROT_WRITE, MAP_SHARED, 124, 0) = 0x7f3d011b1000 >> munmap(0x7f3d011b1000, 262504) = 0 > > Ok, there go our two parallel query DSM segments, and there it is > being unmapped. Hmm. Any chance you could attach a debugger, and > "break munmap", "cont", and then show us the backtrace "bt" when that > is reached? gdb /usr/lib/postgresql/9.6/bin/postgres 21640 GNU gdb (Ubuntu 7.11.1-0ubuntu1~16.5) 7.11.1 Copyright (C) 2016 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details. This GDB was configured as "x86_64-linux-gnu". Type "show configuration" for configuration details. For bug reporting instructions, please see: <http://www.gnu.org/software/gdb/bugs/>. Find the GDB manual and other documentation resources online at: <http://www.gnu.org/software/gdb/documentation/>. For help, type "help". Type "apropos word" to search for commands related to "word"... Reading symbols from /usr/lib/postgresql/9.6/bin/postgres...Reading symbols from /usr/lib/debug/.build-id/04/6f55a5ce6ce05064edfc8feee61c6cb039d296.debug...done. done. Attaching to program: /usr/lib/postgresql/9.6/bin/postgres, process 21640 Reading symbols from /usr/lib/x86_64-linux-gnu/libxml2.so.2...Reading symbols from /usr/lib/debug/.build-id/d3/57ce1dba1fab803eddf48922123ffd0a303676.debug...done. done. Reading symbols from /lib/x86_64-linux-gnu/libpam.so.0...(no debugging symbols found)...done. Reading symbols from /lib/x86_64-linux-gnu/libssl.so.1.0.0...Reading symbols from /usr/lib/debug/.build-id/ff/69ea60ebe05f2dd689d2b26fc85a73e5fbc3a0.debug...done. done. Reading symbols from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0...Reading symbols from /usr/lib/debug/.build-id/15/ffeb43278726b025f020862bf51302822a40ec.debug...done. done. Reading symbols from /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2...(no debugging symbols found)...done. Reading symbols from /lib/x86_64-linux-gnu/librt.so.1...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/librt-2.23.so...done. done. Reading symbols from /lib/x86_64-linux-gnu/libdl.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libdl-2.23.so...done. done. Reading symbols from /lib/x86_64-linux-gnu/libm.so.6...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libm-2.23.so...done. done. Reading symbols from /usr/lib/x86_64-linux-gnu/libldap_r-2.4.so.2...Reading symbols from /usr/lib/debug/.build-id/38/90d33727391e4a85dc0f819ab0aa29bb5dfc86.debug...done. done. Reading symbols from /lib/x86_64-linux-gnu/libsystemd.so.0...(no debugging symbols found)...done. Reading symbols from /lib/x86_64-linux-gnu/libc.so.6...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libc-2.23.so...done. done. Reading symbols from /usr/lib/x86_64-linux-gnu/libicuuc.so.55...Reading symbols from /usr/lib/debug/.build-id/46/3d8b610702d64ae0803c7dfcaa02cfb4c6477b.debug...done. done. Reading symbols from /lib/x86_64-linux-gnu/libz.so.1...Reading symbols from /usr/lib/debug/.build-id/8d/9bd4ce26e45ef16075c67d5f5eeafd8b562832.debug...done. done. Reading symbols from /lib/x86_64-linux-gnu/liblzma.so.5...(no debugging symbols found)...done. Reading symbols from /lib/x86_64-linux-gnu/libaudit.so.1...(no debugging symbols found)...done. Reading symbols from /usr/lib/x86_64-linux-gnu/libkrb5.so.3...(no debugging symbols found)...done. Reading symbols from /usr/lib/x86_64-linux-gnu/libk5crypto.so.3...(no deb
Re: SegFault on 9.6.14
Thomas Munro writes: > On Wed, Jul 17, 2019 at 12:05 PM Jerry Sievers wrote: > >> Program received signal SIGUSR1, User defined signal 1. > > Oh, we need to ignore those pesky signals with "handle SIGUSR1 noprint > nostop". Is this the right sequencing? 1. Start client and get backend pid 2. GDB; handle SIGUSR1, break, cont 3. Run query 4. bt Thanks Don't think I am doing this correctly. Please advise. handle SIGUSR1 noprint nostop SignalStop Print Pass to program Description SIGUSR1 NoNo Yes User defined signal 1 (gdb) break munmap Breakpoint 1 at 0x7f3d09331740: file ../sysdeps/unix/syscall-template.S, line 84. (gdb) cont Continuing. Breakpoint 1, munmap () at ../sysdeps/unix/syscall-template.S:84 84 ../sysdeps/unix/syscall-template.S: No such file or directory. (gdb) bt #0 munmap () at ../sysdeps/unix/syscall-template.S:84 #1 0x55666e12d7f4 in dsm_impl_posix (impl_private=0x22, elevel=19, mapped_size=0x556670205890, mapped_address=0x556670205888, request_size=0, handle=, op=DSM_OP_DETACH) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/storage/ipc/dsm_impl.c:259 #2 dsm_impl_op (op=op@entry=DSM_OP_DETACH, handle=, request_size=request_size@entry=0, impl_private=impl_private@entry=0x556670205880, mapped_address=mapped_address@entry=0x556670205888, mapped_size=mapped_size@entry=0x556670205890, elevel=19) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/storage/ipc/dsm_impl.c:176 #3 0x55666e12efb1 in dsm_detach (seg=0x556670205860) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/storage/ipc/dsm.c:738 #4 0x55666df31369 in DestroyParallelContext (pcxt=0x556670219b68) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/access/transam/parallel.c:750 #5 0x55666e0357bb in ExecParallelCleanup (pei=0x7f3d012218b0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execParallel.c:575 #6 0x55666e047ca2 in ExecShutdownGather (node=node@entry=0x55667033bed0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeGather.c:443 #7 0x55666e0359f5 in ExecShutdownNode (node=0x55667033bed0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execProcnode.c:820 #8 0x55666e0777e1 in planstate_tree_walker (planstate=0x55667033b2b0, walker=0x55666e0359a0 , context=0x0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/nodes/nodeFuncs.c:3636 #9 0x55666e0777e1 in planstate_tree_walker (planstate=0x55667033b040, walker=0x55666e0359a0 , context=0x0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/nodes/nodeFuncs.c:3636 #10 0x55666e0777e1 in planstate_tree_walker (planstate=planstate@entry=0x55667033a6c8, walker=walker@entry=0x55666e0359a0 , context=context@entry=0x0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/nodes/nodeFuncs.c:3636 #11 0x55666e0359df in ExecShutdownNode (node=node@entry=0x55667033a6c8) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execProcnode.c:830 #12 0x55666e04d0ff in ExecLimit (node=node@entry=0x55667033a428) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeLimit.c:139 #13 0x55666e035d28 in ExecProcNode (node=node@entry=0x55667033a428) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execProcnode.c:531 #14 0x55666e051f69 in ExecNestLoop (node=node@entry=0x55667031c660) ---Type to continue, or q to quit---at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeNestloop.c:174 #15 0x55666e035e28 in ExecProcNode (node=node@entry=0x55667031c660) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execProcnode.c:476 #16 0x55666e054989 in ExecSort (node=node@entry=0x55667031c3f0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeSort.c:103 #17 0x55666e035de8 in ExecProcNode (node=0x55667031c3f0) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execProcnode.c:495 #18 0x55666e041fe9 in fetch_input_tuple (aggstate=aggstate@entry=0x55667031ba18) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeAgg.c:598 #19 0x55666e043bb3 in agg_retrieve_direct (aggstate=0x55667031ba18) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeAgg.c:2078 #20 ExecAgg (node=node@entry=0x55667031ba18) at /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeAgg.c:1903 #21 0x55666e035
Re: SegFault on 9.6.14
Thomas Munro writes: > On Wed, Jul 17, 2019 at 12:26 PM Jerry Sievers wrote: > >> Is this the right sequencing? >> >> 1. Start client and get backend pid >> 2. GDB; handle SIGUSR1, break, cont >> 3. Run query >> 4. bt > > Perfect, thanks. I think I just spotted something: Dig that! Great big thanks to you and Tomas, et al for jumping on this. Please let know if there's anything else I can submit that would be helpful. > >> #11 0x55666e0359df in ExecShutdownNode (node=node@entry=0x55667033a6c8) >> at >> /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/execProcnode.c:830 >> #12 0x55666e04d0ff in ExecLimit (node=node@entry=0x55667033a428) >> at >> /build/postgresql-9.6-5O8OLM/postgresql-9.6-9.6.14/build/../src/backend/executor/nodeLimit.c:139 > > https://github.com/postgres/postgres/blob/REL9_6_STABLE/src/backend/executor/nodeLimit.c#L139 > > Limit thinks it's OK to "shut down" the subtree, but if you shut down a > Gather node you can't rescan it later because it destroys its shared > memory. Oops. Not sure what to do about that yet. > > > -- > Thomas Munro > https://enterprisedb.com > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
Re: found xmin from before relfrozenxid on pg_catalog.pg_authid
Hackers, felt like reporting this relevant tidbit in case of interest... My site is among the few who've hit this bug. We observed recently a case of pg_database having joined pg_authid and pg_auth_members in the bad xmin, unable to vacuum shcatalog group, however... pg_database then started working again without any intervention on our part so apparently due to some relevant system activity. We did later do a restart to correct the problem for those other 2 catalogs but , will try the global init file hack mentioned below later if/when we face this anew before running a fixed Pg version. Many thanks! Matheus de Oliveira writes: > Hello again... > > On Tue, Jun 19, 2018 at 12:53 PM, Andres Freund > wrote: > > ... > > After that, yes, deleting the > global/pg_internal.init file is the way to go, and I can't think > of a > case where it's problematic, even without stopping the server. > > > > Just to let you know. I applied the work around in the affected > server and seemed to work way, so far no error. > > Thank you a lot for all the information and help. > > Best regards, > -- > Matheus de Oliveira > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: Deadlock in multiple CIC.
Jeff Janes writes: > On Tue, Dec 26, 2017 at 8:31 AM, Alvaro Herrera < > alvhe...@alvh.no-ip.org> wrote: > > Jeff Janes wrote: > > c3d09b3bd23f5f6 fixed it so concurrent CIC would not deadlock > (or at least > > not as reliably as before) by dropping its own snapshot before > waiting for > > all the other ones to go away. > > > > With commit 8aa3e47510b969354ea02a, concurrent CREATE INDEX > CONCURRENTLY on > > different tables in the same database started deadlocking > against each > > other again quite reliably. > > > > I think the solution is simply to drop the catalog snapshot as > well, as in > > the attached. > > Thanks for the analysis -- it sounds reasonable to me. However, > I'm > wondering why you used the *Conditionally() version instead of > plain > InvalidateCatalogSnapshot(). > > > My thinking was that if there was for some reason another snapshot > hanging around, that dropping the catalog snapshot unconditionally > would be a correctness bug, while doing it conditionally would just > fail to avoid a theoretically avoidable deadlock. So it seemed > safer. > > > I think they must have the same effect in > practice (the assumption being that you can't run CIC in a > transaction > that may have other snapshots) but the theory seems simpler when > calling > the other routine: just do away with the snapshot always, period. > > > That is probably true. But I never even knew that catalog snapshots > existed until yesterday, so didn't want to make make assumptions > about what else might exist, to avoid introducing new bugs similar to > the one that 8aa3e47510b969354ea02a fixed. > > > > This is back-patchable to 9.4, first branch which has MVCC > catalog > scans. It's strange that this has gone undetected for so long. > > > Since the purpose of CIC is to build an index with minimal impact on > other users, I think wanting to use it in concurrent cases might be > rather rare. In a maintenance window, I wouldn't want to use CIC > because it is slower and I'd rather just hold the stronger lock and > do it fast, and as a hot-fix outside a maintenance window I usually > wouldn't want to hog the CPU with concurrent builds when I could do Hmmm, given that most/all large sites lately are probably running on hw with dozens or perhaps hundreds of CPUs/threads, I can see DBAs not being too concerned about "hogging". > them sequentially instead. Also, since deadlocks are "expected" > errors rather than "should never happen" errors, and since the docs > don't promise that you can do parallel CIC without deadlocks, many > people would probably shrug it off (which I initially did) rather > than report it as a bug. I was looking into it as an enhancement > rather than a bug until I discovered that it was already enhanced and Agree such an edge case not a high priority to support for the above reasons but good to assuming no breakage in some other regard :-) > then undone. > > Cheers, > > Jeff > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
