Skip to content

Instantly share code, notes, and snippets.

@michail-nikolaev

michail-nikolaev/min-max-balance-read-desc-aid.bench

Last active March 9, 2020 22:29
Show Gist options
  • Save michail-nikolaev/8b538dbc13c79f2562676526d2f297ab to your computer and use it in GitHub Desktop.
Save michail-nikolaev/8b538dbc13c79f2562676526d2f297ab to your computer and use it in GitHub Desktop.
Download ZIP
could not find left sibling of block
Raw
min-max-balance-read-desc-aid.bench
BEGIN;
select * from pgbench_accounts order by aid desc limit 1;
END;
Raw
postgresql.auto.conf
autovacuum = off
synchronous_standby_names = '*'
synchronous_commit = 'remote_apply'
Raw
postgresql.auto.conf-replica
primary_conninfo = 'user=postgres host=127.0.0.1 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
port = 6543
Raw
prepare
pgbench.exe -i -s 10 -U postgres
psql.exe -U postgres -c "delete from pgbench_accounts where aid IN (select aid from pgbench_accounts order by aid desc limit 850000)"
pgbench.exe -f D:\Dev\postgres\min-max-balance-read-desc-aid.bench -j 1 -c 5 -n -P 1 -T 10000 -U postgres -p 6543
Raw
reproduce
dropping old tables...
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 1.71 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 3.07 s (drop tables 0.03 s, create tables 0.01 s, client-side generate 1.78 s, vacuum 0.60 s, primary keys 0.65 s).
DELETE 500000
progress: 1.0 s, 0.0 tps, lat 0.000 ms stddev 0.000
progress: 2.0 s, 5.0 tps, lat 1407.663 ms stddev 171.363
progress: 3.0 s, 3.0 tps, lat 1298.025 ms stddev 26.079
progress: 4.0 s, 5.0 tps, lat 1426.899 ms stddev 160.895
progress: 5.0 s, 2.0 tps, lat 1619.343 ms stddev 0.456
progress: 6.0 s, 3.0 tps, lat 1295.005 ms stddev 25.030
progress: 7.0 s, 5.0 tps, lat 1427.230 ms stddev 161.585
progress: 8.0 s, 3.0 tps, lat 1292.527 ms stddev 24.482
progress: 9.0 s, 3.0 tps, lat 1498.587 ms stddev 167.970
progress: 10.0 s, 4.0 tps, lat 1468.590 ms stddev 154.101
progress: 11.0 s, 3.0 tps, lat 1294.389 ms stddev 25.168
progress: 12.0 s, 5.0 tps, lat 1427.253 ms stddev 159.547
progress: 13.0 s, 3.0 tps, lat 1502.312 ms stddev 171.999
progress: 14.0 s, 3.0 tps, lat 1295.905 ms stddev 28.510
progress: 15.0 s, 4.0 tps, lat 1473.029 ms stddev 155.217
progress: 16.0 s, 3.0 tps, lat 1297.688 ms stddev 27.391
progress: 17.0 s, 3.0 tps, lat 1502.872 ms stddev 171.357
progress: 18.0 s, 5.0 tps, lat 1421.864 ms stddev 153.495
2020-03-09 18:36:30.297 MSK [563960] ERROR: could not find left sibling of block 2728 in index "pgbench_accounts_pkey"
2020-03-09 18:36:30.297 MSK [563960] STATEMENT: select * from pgbench_accounts order by aid desc limit 1;
2020-03-09 18:36:30.297 MSK [563961] ERROR: could not find left sibling of block 2728 in index "pgbench_accounts_pkey"
2020-03-09 18:36:30.297 MSK [563961] STATEMENT: select * from pgbench_accounts order by aid desc limit 1;
pgbench: error: client 1 script 0 aborted in command 1 query 0: ERROR: could not find left sibling of block 2728 in index "pgbench_ac
counts_pkey"
pgbench: error: client 2 script 0 aborted in command 1 query 0: ERROR: could not find left sibling of block 2728 in index "pgbench_ac
counts_pkey"
progress: 19.0 s, 8649.5 tps, lat 0.693 ms stddev 16.915
progress: 20.0 s, 11618.9 tps, lat 0.257 ms stddev 0.015
progress: 21.0 s, 11410.5 tps, lat 0.261 ms stddev 0.022
Raw
stack-1
> postgres.exe!_bt_walk_left(RelationData * rel, int buf, SnapshotData * snapshot)Строка 2278 C
postgres.exe!_bt_readnextpage(IndexScanDescData * scan, unsigned int blkno, ScanDirection dir)Строка 2084 C
postgres.exe!_bt_steppage(IndexScanDescData * scan, ScanDirection dir)Строка 1949 C
postgres.exe!_bt_next(IndexScanDescData * scan, ScanDirection dir)Строка 1476 C
postgres.exe!btgettuple(IndexScanDescData * scan, ScanDirection dir)Строка 284 C
postgres.exe!index_getnext_tid(IndexScanDescData * scan, ScanDirection direction)Строка 526 C
postgres.exe!index_getnext_slot(IndexScanDescData * scan, ScanDirection direction, TupleTableSlot * slot)Строка 621 C
postgres.exe!IndexNext(IndexScanState * node)Строка 133 C
postgres.exe!ExecScanFetch(ScanState * node, TupleTableSlot *(*)(ScanState *) accessMtd, bool(*)(ScanState *, TupleTableSlot *) recheckMtd)Строка 134 C
postgres.exe!ExecScan(ScanState * node, TupleTableSlot *(*)(ScanState *) accessMtd, bool(*)(ScanState *, TupleTableSlot *) recheckMtd)Строка 182 C
postgres.exe!ExecIndexScan(PlanState * pstate)Строка 540 C
postgres.exe!ExecProcNodeFirst(PlanState * node)Строка 445 C
postgres.exe!ExecProcNode(PlanState * node)Строка 246 C
postgres.exe!ExecLimit(PlanState * pstate)Строка 95 C
postgres.exe!ExecProcNodeFirst(PlanState * node)Строка 445 C
postgres.exe!ExecProcNode(PlanState * node)Строка 246 C
postgres.exe!ExecutePlan(EState * estate, PlanState * planstate, bool use_parallel_mode, CmdType operation, bool sendTuples, unsigned __int64 numberTuples, ScanDirection direction, _DestReceiver * dest, bool execute_once)Строка 1646 C
postgres.exe!standard_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, unsigned __int64 count, bool execute_once)Строка 378 C
postgres.exe!ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, unsigned __int64 count, bool execute_once)Строка 309 C
postgres.exe!PortalRunSelect(PortalData * portal, bool forward, long count, _DestReceiver * dest)Строка 914 C
postgres.exe!PortalRun(PortalData * portal, long count, bool isTopLevel, bool run_once, _DestReceiver * dest, _DestReceiver * altdest, QueryCompletion * qc)Строка 756 C
postgres.exe!exec_simple_query(const char * query_string)Строка 1244 C
postgres.exe!PostgresMain(int argc, char * * argv, const char * dbname, const char * username)Строка 4301 C
postgres.exe!BackendRun(Port * port)Строка 4510 C
postgres.exe!SubPostmasterMain(int argc, char * * argv)Строка 5039 C
postgres.exe!main(int argc, char * * argv)Строка 198 C
blkno 1441 unsigned int
buf 4414 int
lblkno 571 unsigned int
obknum 1437 unsigned int
+ opaque 0x0000000006a8cf70 {btpo_prev=571 btpo_next=1438 btpo={level=0 xact=0 } ...} BTPageOpaqueData *
+ page 0x0000000006a8af80 "\x3" char *
+ rel 0x0000000000b633f8 {rd_node={spcNode=1663 dbNode=12656 relNode=24747 } rd_smgr=0x0000000000dca0f0 {smgr_rnode=...} ...} RelationData *
+ snapshot 0x0000000000b97838 {snapshot_type=SNAPSHOT_MVCC (0) xmin=6991104 xmax=6991104 ...} SnapshotData *
tries 5 int
------------------------------
blkno 1438 unsigned int
dir BackwardScanDirection ScanDirection
+ opaque 0x0000000006a8cf70 {btpo_prev=571 btpo_next=1438 btpo={level=0 xact=0 } ...} BTPageOpaqueData *
+ page 0x0000000006a8af80 "\x3" char *
+ rel 0x0000000000b633f8 {rd_node={spcNode=1663 dbNode=12656 relNode=24747 } rd_smgr=0x0000000000dca0f0 {smgr_rnode=...} ...} RelationData *
+ scan 0x0000000000b8c8e8 {heapRelation=0x0000000000b5c318 {rd_node={spcNode=1663 dbNode=12656 relNode=24739 } ...} ...} IndexScanDescData *
+ so 0x0000000000baaa88 {qual_ok=true numberOfKeys=0 keyData=0x0000000000000000 <NULL> ...} BTScanOpaqueData *
status true bool
------------------------------
blkno 1438 unsigned int
dir BackwardScanDirection ScanDirection
+ scan 0x0000000000b8c8e8 {heapRelation=0x0000000000b5c318 {rd_node={spcNode=1663 dbNode=12656 relNode=24739 } ...} ...} IndexScanDescData *
+ so 0x0000000000baaa88 {qual_ok=true numberOfKeys=0 keyData=0x0000000000000000 <NULL> ...} BTScanOpaqueData *
status true bool
Raw
stack-2
> postgres.exe!_bt_walk_left(RelationData * rel, int buf, SnapshotData * snapshot)Строка 2278 C
postgres.exe!_bt_readnextpage(IndexScanDescData * scan, unsigned int blkno, ScanDirection dir)Строка 2084 C
postgres.exe!_bt_steppage(IndexScanDescData * scan, ScanDirection dir)Строка 1949 C
postgres.exe!_bt_endpoint(IndexScanDescData * scan, ScanDirection dir)Строка 2447 C
postgres.exe!_bt_first(IndexScanDescData * scan, ScanDirection dir)Строка 1096 C
postgres.exe!btgettuple(IndexScanDescData * scan, ScanDirection dir)Строка 252 C
postgres.exe!index_getnext_tid(IndexScanDescData * scan, ScanDirection direction)Строка 526 C
postgres.exe!index_getnext_slot(IndexScanDescData * scan, ScanDirection direction, TupleTableSlot * slot)Строка 621 C
postgres.exe!IndexNext(IndexScanState * node)Строка 133 C
postgres.exe!ExecScanFetch(ScanState * node, TupleTableSlot *(*)(ScanState *) accessMtd, bool(*)(ScanState *, TupleTableSlot *) recheckMtd)Строка 134 C
postgres.exe!ExecScan(ScanState * node, TupleTableSlot *(*)(ScanState *) accessMtd, bool(*)(ScanState *, TupleTableSlot *) recheckMtd)Строка 199 C
postgres.exe!ExecIndexScan(PlanState * pstate)Строка 540 C
postgres.exe!ExecProcNodeFirst(PlanState * node)Строка 445 C
postgres.exe!ExecProcNode(PlanState * node)Строка 246 C
postgres.exe!ExecLimit(PlanState * pstate)Строка 95 C
postgres.exe!ExecProcNodeFirst(PlanState * node)Строка 445 C
postgres.exe!ExecProcNode(PlanState * node)Строка 246 C
postgres.exe!fetch_input_tuple(AggState * aggstate)Строка 411 C
postgres.exe!agg_retrieve_direct(AggState * aggstate)Строка 1782 C
postgres.exe!ExecAgg(PlanState * pstate)Строка 1597 C
postgres.exe!ExecProcNodeFirst(PlanState * node)Строка 445 C
postgres.exe!ExecProcNode(PlanState * node)Строка 246 C
postgres.exe!ExecutePlan(EState * estate, PlanState * planstate, bool use_parallel_mode, CmdType operation, bool sendTuples, unsigned __int64 numberTuples, ScanDirection direction, _DestReceiver * dest, bool execute_once)Строка 1646 C
postgres.exe!standard_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, unsigned __int64 count, bool execute_once)Строка 378 C
postgres.exe!ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, unsigned __int64 count, bool execute_once)Строка 309 C
postgres.exe!PortalRunSelect(PortalData * portal, bool forward, long count, _DestReceiver * dest)Строка 914 C
postgres.exe!PortalRun(PortalData * portal, long count, bool isTopLevel, bool run_once, _DestReceiver * dest, _DestReceiver * altdest, QueryCompletion * qc)Строка 756 C
postgres.exe!exec_simple_query(const char * query_string)Строка 1244 C
postgres.exe!PostgresMain(int argc, char * * argv, const char * dbname, const char * username)Строка 4301 C
postgres.exe!BackendRun(Port * port)Строка 4510 C
postgres.exe!SubPostmasterMain(int argc, char * * argv)Строка 5039 C
postgres.exe!main(int argc, char * * argv)Строка 198 C
[Внешний код]
blkno 4947 unsigned int
buf 1289 int
lblkno 4960 unsigned int
obknum 4937 unsigned int
+ opaque 0x0000000005452f70 {btpo_prev=4960 btpo_next=4955 btpo={level=0 xact=0 } ...} BTPageOpaqueData *
+ page 0x0000000005450f80 "\x2" char *
+ rel 0x0000000000b67330 {rd_node={spcNode=1663 dbNode=12656 relNode=24622 } rd_smgr=0x0000000000bb64a0 {smgr_rnode=...} ...} RelationData *
+ snapshot 0x0000000000e0bbc8 {snapshot_type=SNAPSHOT_MVCC (0) xmin=6991031 xmax=6991031 ...} SnapshotData *
tries 5 int
----------------------------------
Raw
vacuum
psql.exe -U postgres -c "vacuum pgbench_accounts"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment