Last active
January 6, 2024 01:43
-
-
Save mdcallag/f320b859a59770aaf2c12eebe138c946 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Problem -> Session doing DELETE statements is using a lot of CPU | |
| mdcallag 957203 1.5 67.3 10784028 10628892 ? Ss Jan05 10:09 postgres: checkpointer process | |
| mdcallag 957204 0.0 1.9 10783904 309232 ? Ss Jan05 0:32 postgres: writer process | |
| mdcallag 957205 1.3 0.1 10783904 18828 ? Ss Jan05 8:43 postgres: wal writer process | |
| mdcallag 957206 0.0 0.0 10784268 4380 ? Ss Jan05 0:04 postgres: autovacuum launcher process | |
| mdcallag 957207 0.0 0.0 13328 2296 ? Ss Jan05 0:33 postgres: stats collector process | |
| mdcallag 973816 98.9 7.0 10784860 1106752 ? Rs Jan05 246:53 postgres: mdcallag ib 127.0.0.1(57064) DELETE | |
| mdcallag 973817 1.4 67.2 10784848 10613388 ? Ss Jan05 3:35 postgres: mdcallag ib 127.0.0.1(57078) idle | |
| --- | |
| From explain analyze, planning takes ~100ms | |
| ib=# explain analyze delete from pi1 where(transactionid>=4804246 and transactionid<4804251); | |
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------------------------- | |
| Delete on pi1 (cost=0.57..8.59 rows=1 width=6) (actual time=0.014..0.014 rows=0 loops=1) | |
| -> Index Scan using pi1_pkey on pi1 (cost=0.57..8.59 rows=1 width=6) (actual time=0.013..0.013 rows=0 loops=1) | |
| Index Cond: ((transactionid >= 4804246) AND (transactionid < 4804251)) | |
| Planning time: 101.000 ms | |
| Execution time: 0.035 ms | |
| (5 rows) | |
| --- | |
| Thread stacks from PMP look like this: | |
| _bt_checkkeys,_bt_readpage,_bt_steppage,_bt_endpoint,_bt_first,btgettuple,FunctionCall2Coll,index_getnext_tid,index_getnext,get_actual_variable_range,ineq_histogram_selectivity,ineq_histogram_selectivity,scalarineqsel,scalargtsel,OidFunctionCall4Coll,restriction_selectivity,clause_selectivity,clauselist_selectivity,set_baserel_size_estimates,set_base_rel_sizes,make_one_rel,query_planner,grouping_planner,subquery_planner,standard_planner,pg_plan_query,pg_plan_queries,exec_simple_query,PostgresMain,BackendRun,BackendStartup,ServerLoop,PostmasterMain,main | |
| _bt_check_rowcompare,_bt_checkkeys,_bt_readpage,_bt_steppage,_bt_endpoint,_bt_first,btgettuple,FunctionCall2Coll,index_getnext_tid,index_getnext,get_actual_variable_range,ineq_histogram_selectivity,ineq_histogram_selectivity,scalarineqsel,scalarltsel,OidFunctionCall4Coll,restriction_selectivity,clause_selectivity,clauselist_selectivity,set_baserel_size_estimates,set_base_rel_sizes,make_one_rel,query_planner,grouping_planner,subquery_planner,standard_planner,pg_plan_query,pg_plan_queries,exec_simple_query,PostgresMain,BackendRun,BackendStartup,ServerLoop,PostmasterMain,main | |
| hash_search_with_hash_value,BufTableLookup,BufferAlloc,ReadBuffer_common,ReadBufferExtended,_bt_getbuf,_bt_steppage,_bt_endpoint,_bt_first,btgettuple,FunctionCall2Coll,index_getnext_tid,index_getnext,get_actual_variable_range,ineq_histogram_selectivity,ineq_histogram_selectivity,scalarineqsel,scalarltsel,OidFunctionCall4Coll,restriction_selectivity,clause_selectivity,clauselist_selectivity,set_baserel_size_estimates,set_base_rel_sizes,make_one_rel,query_planner,grouping_planner,subquery_planner,standard_planner,pg_plan_query,pg_plan_queries,exec_simple_query,PostgresMain,BackendRun,BackendStartup,ServerLoop,PostmasterMain,main | |
| --- | |
| For statements like this: | |
| delete from pi1 where(transactionid>=4765201 and transactionid<4765206); | |
| --- | |
| Table is: | |
| Column | Type | Modifiers | Storage | Stats target | Description | |
| ----------------+-----------------------------+-------------------------------------------------------------+----------+--------------+------------- | |
| transactionid | bigint | not null default nextval('pi1_transactionid_seq'::regclass) | plain | | | |
| dateandtime | timestamp without time zone | | plain | | | |
| cashregisterid | integer | not null | plain | | | |
| customerid | integer | not null | plain | | | |
| productid | integer | not null | plain | | | |
| price | integer | not null | plain | | | |
| data | character varying(4000) | | extended | | | |
| Indexes: | |
| "pi1_pkey" PRIMARY KEY, btree (transactionid) | |
| "pi1_marketsegment" btree (productid, customerid, price) | |
| "pi1_pdc" btree (price, dateandtime, customerid) | |
| "pi1_registersegment" btree (cashregisterid, customerid, price) | |
| --- | |
| Maybe analyze is needed? | |
| ib=# SELECT relname, relkind, reltuples, relpages from pg_class WHERE relname like 'pi1%'; | |
| relname | relkind | reltuples | relpages | |
| -----------------------+---------+-------------+---------- | |
| pi1 | r | 8e+08 | 7836581 | |
| pi1_marketsegment | i | 8e+08 | 3080312 | |
| pi1_pdc | i | 8e+08 | 3965991 | |
| pi1_pkey | i | 7.96881e+08 | 2186434 | |
| pi1_registersegment | i | 8e+08 | 3080312 | |
| pi1_transactionid_seq | S | 1 | 1 | |
| -[ RECORD 1 ]--+---------------------- | |
| relname | pi1 | |
| relnamespace | 2200 | |
| reltype | 16402 | |
| reloftype | 0 | |
| relowner | 10 | |
| relam | 0 | |
| relfilenode | 16400 | |
| reltablespace | 0 | |
| relpages | 7836581 | |
| reltuples | 8e+08 | |
| relallvisible | 0 | |
| reltoastrelid | 16404 | |
| relhasindex | t | |
| relisshared | f | |
| relpersistence | p | |
| relkind | r | |
| relnatts | 7 | |
| relchecks | 0 | |
| relhasoids | f | |
| relhaspkey | t | |
| relhasrules | f | |
| relhastriggers | f | |
| relhassubclass | f | |
| relispopulated | t | |
| relreplident | d | |
| relfrozenxid | 690 | |
| relminmxid | 1 | |
| relacl | | |
| reloptions | | |
| -[ RECORD 2 ]--+---------------------- | |
| relname | pi1_marketsegment | |
| relnamespace | 2200 | |
| reltype | 0 | |
| reloftype | 0 | |
| relowner | 10 | |
| relam | 403 | |
| relfilenode | 16409 | |
| reltablespace | 0 | |
| relpages | 3080312 | |
| reltuples | 8e+08 | |
| relallvisible | 0 | |
| reltoastrelid | 0 | |
| relhasindex | f | |
| relisshared | f | |
| relpersistence | p | |
| relkind | i | |
| relnatts | 3 | |
| relchecks | 0 | |
| relhasoids | f | |
| relhaspkey | f | |
| relhasrules | f | |
| relhastriggers | f | |
| relhassubclass | f | |
| relispopulated | t | |
| relreplident | n | |
| relfrozenxid | 0 | |
| relminmxid | 0 | |
| relacl | | |
| reloptions | | |
| -[ RECORD 3 ]--+---------------------- | |
| relname | pi1_pdc | |
| relnamespace | 2200 | |
| reltype | 0 | |
| reloftype | 0 | |
| relowner | 10 | |
| relam | 403 | |
| relfilenode | 16411 | |
| reltablespace | 0 | |
| relpages | 3965991 | |
| reltuples | 8e+08 | |
| relallvisible | 0 | |
| reltoastrelid | 0 | |
| relhasindex | f | |
| relisshared | f | |
| relpersistence | p | |
| relkind | i | |
| relnatts | 3 | |
| relchecks | 0 | |
| relhasoids | f | |
| relhaspkey | f | |
| relhasrules | f | |
| relhastriggers | f | |
| relhassubclass | f | |
| relispopulated | t | |
| relreplident | n | |
| relfrozenxid | 0 | |
| relminmxid | 0 | |
| relacl | | |
| reloptions | | |
| -[ RECORD 4 ]--+---------------------- | |
| relname | pi1_pkey | |
| relnamespace | 2200 | |
| reltype | 0 | |
| reloftype | 0 | |
| relowner | 10 | |
| relam | 403 | |
| relfilenode | 16407 | |
| reltablespace | 0 | |
| relpages | 2186434 | |
| reltuples | 7.96881e+08 | |
| relallvisible | 0 | |
| reltoastrelid | 0 | |
| relhasindex | f | |
| relisshared | f | |
| relpersistence | p | |
| relkind | i | |
| relnatts | 1 | |
| relchecks | 0 | |
| relhasoids | f | |
| relhaspkey | f | |
| relhasrules | f | |
| relhastriggers | f | |
| relhassubclass | f | |
| relispopulated | t | |
| relreplident | n | |
| relfrozenxid | 0 | |
| relminmxid | 0 | |
| relacl | | |
| reloptions | | |
| -[ RECORD 5 ]--+---------------------- | |
| relname | pi1_registersegment | |
| relnamespace | 2200 | |
| reltype | 0 | |
| reloftype | 0 | |
| relowner | 10 | |
| relam | 403 | |
| relfilenode | 16410 | |
| reltablespace | 0 | |
| relpages | 3080312 | |
| reltuples | 8e+08 | |
| relallvisible | 0 | |
| reltoastrelid | 0 | |
| relhasindex | f | |
| relisshared | f | |
| relpersistence | p | |
| relkind | i | |
| relnatts | 3 | |
| relchecks | 0 | |
| relhasoids | f | |
| relhaspkey | f | |
| relhasrules | f | |
| relhastriggers | f | |
| relhassubclass | f | |
| relispopulated | t | |
| relreplident | n | |
| relfrozenxid | 0 | |
| relminmxid | 0 | |
| relacl | | |
| reloptions | | |
| -[ RECORD 6 ]--+---------------------- | |
| relname | pi1_transactionid_seq | |
| relnamespace | 2200 | |
| reltype | 16399 | |
| reloftype | 0 | |
| relowner | 10 | |
| relam | 0 | |
| relfilenode | 16398 | |
| reltablespace | 0 | |
| relpages | 1 | |
| reltuples | 1 | |
| relallvisible | 0 | |
| reltoastrelid | 0 | |
| relhasindex | f | |
| relisshared | f | |
| relpersistence | p | |
| relkind | S | |
| relnatts | 10 | |
| relchecks | 0 | |
| relhasoids | f | |
| relhaspkey | f | |
| relhasrules | f | |
| relhastriggers | f | |
| relhassubclass | f | |
| relispopulated | t | |
| relreplident | n | |
| relfrozenxid | 0 | |
| relminmxid | 0 | |
| relacl | | |
| reloptions | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment