Re: pgsql: Compute XID horizon for page level index vacuum on primary.
От | Thomas Munro |
---|---|
Тема | Re: pgsql: Compute XID horizon for page level index vacuum on primary. |
Дата | |
Msg-id | CA+hUKGLCwPF0S4Mk7S8qw+DK0Bq65LueN9rofAA3HHSYikW-Zw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: pgsql: Compute XID horizon for page level index vacuum onprimary.
|
Список | pgsql-committers |
On Wed, Mar 27, 2019 at 1:06 PM Andres Freund <andres@anarazel.de> wrote: > Compute XID horizon for page level index vacuum on primary. Hi Andres, I have a virtual machine running FreeBSD 12.0 on i386 on which contrib/test_decoding consistently self-deadlocks in the "rewrite" test, with the stack listed below. You can see that we wait for a share lock that we already hold exclusively. Peter Geoghegan spotted the problem: this code path shouldn't access syscache, or at least not for a catalog table. He suggested something along these lines: --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -6977,7 +6977,10 @@ heap_compute_xid_horizon_for_tuples(Relation rel, * simplistic, but at the moment there is no evidence of that or any idea * about what would work better. */ - io_concurrency = get_tablespace_io_concurrency(rel->rd_rel->reltablespace); + if (IsCatalogRelation(rel)) + io_concurrency = 1; + else + io_concurrency = get_tablespace_io_concurrency(rel->rd_rel->reltablespace); prefetch_distance = Min((io_concurrency) + 10, MAX_IO_CONCURRENCY); /* Start prefetching. */ Indeed that seems to fix the problem for me. * frame #0: 0x28c04ca1 libc.so.7`__sys__umtx_op + 5 frame #1: 0x28bed0ab libc.so.7`sem_clockwait_np + 283 frame #2: 0x28bed1ae libc.so.7`sem_wait + 62 frame #3: 0x0858d837 postgres`PGSemaphoreLock(sema=0x290141a8) at pg_sema.c:316 frame #4: 0x08678b94 postgres`LWLockAcquire(lock=0x295365a4, mode=LW_SHARED) at lwlock.c:1244 frame #5: 0x08639d8f postgres`LockBuffer(buffer=129, mode=1) at bufmgr.c:3565 frame #6: 0x08187f7d postgres`_bt_getbuf(rel=0x31c3e95c, blkno=1, access=1) at nbtpage.c:806 frame #7: 0x081887f9 postgres`_bt_getroot(rel=0x31c3e95c, access=1) at nbtpage.c:323 frame #8: 0x081932aa postgres`_bt_search(rel=0x31c3e95c, key=0xffbfad00, bufP=0xffbfb31c, access=1, snapshot=0x08b27c58) at nbtsearch.c:99 frame #9: 0x08195bfc postgres`_bt_first(scan=0x31db73a0, dir=ForwardScanDirection) at nbtsearch.c:1246 frame #10: 0x08190f96 postgres`btgettuple(scan=0x31db73a0, dir=ForwardScanDirection) at nbtree.c:245 frame #11: 0x0817d3fa postgres`index_getnext_tid(scan=0x31db73a0, direction=ForwardScanDirection) at indexam.c:550 frame #12: 0x0817d6a8 postgres`index_getnext_slot(scan=0x31db73a0, direction=ForwardScanDirection, slot=0x31df2320) at indexam.c:642 frame #13: 0x0817b4c9 postgres`systable_getnext(sysscan=0x31df242c) at genam.c:450 frame #14: 0x0887e3a3 postgres`ScanPgRelation(targetRelId=1213, indexOK=true, force_non_historic=false) at relcache.c:365 frame #15: 0x088742e1 postgres`RelationBuildDesc(targetRelId=1213, insertIt=true) at relcache.c:1055 frame #16: 0x0887356a postgres`RelationIdGetRelation(relationId=1213) at relcache.c:2030 frame #17: 0x080d7ac5 postgres`relation_open(relationId=1213, lockmode=1) at relation.c:59 frame #18: 0x081cc2b6 postgres`table_open(relationId=1213, lockmode=1) at table.c:43 frame #19: 0x0886597b postgres`SearchCatCacheMiss(cache=0x31c2b200, nkeys=1, hashValue=1761185739, hashIndex=3, v1=1663, v2=0, v3=0, v4=0) at catcache.c:1357 frame #20: 0x088622db postgres`SearchCatCacheInternal(cache=0x31c2b200, nkeys=1, v1=1663, v2=0, v3=0, v4=0) at catcache.c:1299 frame #21: 0x08862354 postgres`SearchCatCache1(cache=0x31c2b200, v1=1663) at catcache.c:1167 frame #22: 0x0888406a postgres`SearchSysCache1(cacheId=61, key1=1663) at syscache.c:1119 frame #23: 0x088834de postgres`get_tablespace(spcid=1663) at spccache.c:136 frame #24: 0x08883617 postgres`get_tablespace_io_concurrency(spcid=0) at spccache.c:217 frame #25: 0x08155a82 postgres`heap_compute_xid_horizon_for_tuples(rel=0x31cbee40, tids=0x31df146c, nitems=3) at heapam.c:6980 frame #26: 0x0817b09d postgres`table_compute_xid_horizon_for_tuples(rel=0x31cbee40, items=0x31df146c, nitems=3) at tableam.h:708 frame #27: 0x0817b03a postgres`index_compute_xid_horizon_for_tuples(irel=0x31c3e95c, hrel=0x31cbee40, ibuf=129, itemnos=0xffbfbb8c, nitems=3) at genam.c:306 frame #28: 0x0818ae92 postgres`_bt_delitems_delete(rel=0x31c3e95c, buf=129, itemnos=0xffbfbb8c, nitems=3, heapRel=0x31cbee40) at nbtpage.c:1111 frame #29: 0x0818405b postgres`_bt_vacuum_one_page(rel=0x31c3e95c, buffer=129, heapRel=0x31cbee40) at nbtinsert.c:2270 frame #30: 0x08180a4f postgres`_bt_findinsertloc(rel=0x31c3e95c, insertstate=0xffbfcce0, checkingunique=true, stack=0x00000000, heapRel=0x31cbee40) at nbtinsert.c:736 frame #31: 0x0817f40c postgres`_bt_doinsert(rel=0x31c3e95c, itup=0x31db69f4, checkUnique=UNIQUE_CHECK_YES, heapRel=0x31cbee40) at nbtinsert.c:281 frame #32: 0x08190416 postgres`btinsert(rel=0x31c3e95c, values=0xffbfce54, isnull=0xffbfce34, ht_ctid=0x31db42e4, heapRel=0x31cbee40, checkUnique=UNIQUE_CHECK_YES, indexInfo=0x31db67dc) at nbtree.c:203 frame #33: 0x0817c173 postgres`index_insert(indexRelation=0x31c3e95c, values=0xffbfce54, isnull=0xffbfce34, heap_t_ctid=0x31db42e4, heapRelation=0x31cbee40, checkUnique=UNIQUE_CHECK_YES, indexInfo=0x31db67dc) at indexam.c:212 frame #34: 0x0823cca4 postgres`CatalogIndexInsert(indstate=0x31db9228, heapTuple=0x31db42e0) at indexing.c:140 frame #35: 0x0823cd72 postgres`CatalogTupleUpdate(heapRel=0x31cbee40, otid=0x31db42e4, tup=0x31db42e0) at indexing.c:215 frame #36: 0x088768ed postgres`RelationSetNewRelfilenode(relation=0x31c2fb38, persistence='p', freezeXid=0, minmulti=0) at relcache.c:3508 frame #37: 0x0823b5df postgres`reindex_index(indexId=2672, skip_constraint_checks=true, persistence='p', options=0) at index.c:3700 frame #38: 0x0823bf00 postgres`reindex_relation(relid=1262, flags=18, options=0) at index.c:3946 frame #39: 0x08320063 postgres`finish_heap_swap(OIDOldHeap=1262, OIDNewHeap=16580, is_system_catalog=true, swap_toast_by_content=true, check_constraints=false, is_internal=true, frozenXid=673, cutoffMulti=1, newrelpersistence='p') at cluster.c:1673 frame #40: 0x0831f5a3 postgres`rebuild_relation(OldHeap=0x31c2ff68, indexOid=0, verbose=false) at cluster.c:629 frame #41: 0x0831eecd postgres`cluster_rel(tableOid=1262, indexOid=0, options=0) at cluster.c:435 frame #42: 0x083f7c1d postgres`vacuum_rel(relid=1262, relation=0x28b4b9dc, params=0xffbfd670) at vacuum.c:1743 frame #43: 0x083f6f87 postgres`vacuum(relations=0x31d6c1cc, params=0xffbfd670, bstrategy=0x31d6c090, isTopLevel=true) at vacuum.c:372 frame #44: 0x083f6837 postgres`ExecVacuum(pstate=0x31ca2c90, vacstmt=0x28b4ba54, isTopLevel=true) at vacuum.c:175 frame #45: 0x0869f145 postgres`standard_ProcessUtility(pstmt=0x28b4bb18, queryString="VACUUM FULL pg_database;", context=PROCESS_UTILITY_TOPLEVEL, params=0x00000000, queryEnv=0x00000000, dest=0x28b4bc90, completionTag="") at utility.c:670 frame #46: 0x0869e68e postgres`ProcessUtility(pstmt=0x28b4bb18, queryString="VACUUM FULL pg_database;", context=PROCESS_UTILITY_TOPLEVEL, params=0x00000000, queryEnv=0x00000000, dest=0x28b4bc90, completionTag="") at utility.c:360 frame #47: 0x0869ddfb postgres`PortalRunUtility(portal=0x31bee090, pstmt=0x28b4bb18, isTopLevel=true, setHoldSnapshot=false, dest=0x28b4bc90, completionTag="") at pquery.c:1175 frame #48: 0x0869ce02 postgres`PortalRunMulti(portal=0x31bee090, isTopLevel=true, setHoldSnapshot=false, dest=0x28b4bc90, altdest=0x28b4bc90, completionTag="") at pquery.c:1321 frame #49: 0x0869c363 postgres`PortalRun(portal=0x31bee090, count=2147483647, isTopLevel=true, run_once=true, dest=0x28b4bc90, altdest=0x28b4bc90, completionTag="") at pquery.c:796 frame #50: 0x08696e68 postgres`exec_simple_query(query_string="VACUUM FULL pg_database;") at postgres.c:1215 frame #51: 0x08695eec postgres`PostgresMain(argc=1, argv=0x31be6658, dbname="contrib_regression", username="munro") at postgres.c:4247 frame #52: 0x085aedb0 postgres`BackendRun(port=0x31be1000) at postmaster.c:4399 frame #53: 0x085adf9a postgres`BackendStartup(port=0x31be1000) at postmaster.c:4090 frame #54: 0x085accd5 postgres`ServerLoop at postmaster.c:1703 frame #55: 0x085a9d95 postgres`PostmasterMain(argc=8, argv=0xffbfe608) at postmaster.c:1376 frame #56: 0x0849ec92 postgres`main(argc=8, argv=0xffbfe608) at main.c:228 frame #57: 0x080bf5eb postgres`_start1(cleanup=0x28b1e540, argc=8, argv=0xffbfe608) at crt1_c.c:73 frame #58: 0x080bf4b8 postgres`_start at crt1_s.S:49 (lldb) print num_held_lwlocks (int) $0 = 1 (lldb) print held_lwlocks[0] (LWLockHandle) $1 = { lock = 0x295365a4 mode = LW_EXCLUSIVE } -- Thomas Munro https://enterprisedb.com
В списке pgsql-committers по дате отправления: