Re: how to do profile for pg?
but I need a quick demo to see the memory profiling or CPU profiling. I hope a blog or a video which is better for me. Thanks. Replied Message From mailto:aleksan...@timescale.com; >Aleksander Alekseevaleksan...@timescale.com Date 09/21/2023 22:02 To mailto:pgsql-hackers@lists.postgresql.org; >pgsql-hackerspgsql-hackers@lists.postgresql.org Cc mailto:jack...@gmail.com; >jack...@gmail.com Subject Re: how to do profile for pg? Hi jacktby, PostgreSQL is literally a large and complicated program in C. Thus it can be profiled as such. E.g. you can use `perf` and build flamegraphs using `perf record`. Often pgbench is an adequate tool to compare before and after results.There are many other tools available depending on what exactly you want to profile - CPU, lock contention, disk I/O, etc. People write books (plural) on the subject. Personally I would recommend System Performance, Enterprise and the Cloud, 2nd Edition and BPF Performance Tools by Brendan Gregg. -- Best regards, Aleksander Alekseev
how to do profile for pg?
How does pg index page optimize dead tuples?
As far as I know, when a index page is full, if you insert a new tuple here, you will split it into two pages. But pg won't delete the half tuples in the old page in real. So if there is another tuple inserted into this old page, will pg split it again? I think that's not true, so how it solve this one? please give me a code example,thanks. jack...@gmail.com
Howdoes; pg; index; page; optimize; dead; tuples?;
As far as I know, when a index page is full, if you insert a new tuple here, you will split it into two pages. But pg won't delete the half tuples in the old page in real. So if there is another tuple inserted into this old page, will pg split it again? I think that's not true, so how it solve this one? please give me a code example,thanks. jack...@gmail.com
How to get the real type use oid in internal codes?
I can use relation struct to get all attributes' typeoid, so which funcion I can use to get the real type. jack...@gmail.com
How does pg implement the visiblity of one tuple for specified transaction?
Suppose there is a transaction running, how it knows the tuples that are visible for it? jack...@gmail.com
What's MultiXactId?
in src/include/access/htup_details.h, I find out this: #define HEAP_XMAX_IS_MULTI 0x1000 /* t_xmax is a MultiXactId */ what's MultiXactId? Can you give me a scenario to make this bit as 1? jack...@gmail.com
Re: Re: What's the prefix?
From: David G. Johnston
Date: 2023-02-27 00:27
To: jack...@gmail.com
CC: pgsql-hackers
Subject: Re: What's the prefix?
On Sun, Feb 26, 2023 at 9:16 AM jack...@gmail.com wrote:
use these sqls:
create table t(a text);
insert into t values('a');
select lp,lp_len,t_data from heap_page_items(get_raw_page('t',0));
lp | lp_len | t_data
++
1 | 26 | \x0561
as you can see, the 61 is 'a', so what's the 05??? strange.
text is variable length so there is header information built into the datatype
representation that indicates how long the content is.
David J.
No， this is the varlena struct:
struct varlena
{
char vl_len_[4]; /* Do not touch this field directly! */
char vl_dat[FLEXIBLE_ARRAY_MEMBER]; /* Data content is here */
};
when I insert 'a', this struct will be {
vl_len : 00 00 00 05
vl_dat: 'a'
}
the t_data should be \x000561, but it's \x0561? strange
--
jack...@gmail.com
What's the prefix?
use these sqls:
create table t(a text);
insert into t values('a');
select lp,lp_len,t_data from heap_page_items(get_raw_page('t',0));
lp | lp_len | t_data
++
1 | 26 | \x0561
as you can see, the 61 is 'a', so what's the 05??? strange.
jack...@gmail.com
Re: Re: Give me more details of some bits in infomask!!
From: Tomas Vondra Date: 2023-02-26 23:23 To: jack...@gmail.com; pgsql-hackers Subject: Re: Give me more details of some bits in infomask!! On 2/26/23 15:30, jack...@gmail.com wrote: > here are the source codes from src/include/access/htup_details.h. > /* > * information stored in t_infomask: > */ > #define HEAP_HASNULL0x0001/* has null attribute(s) */ > #define HEAP_HASVARWIDTH0x0002/* has variable-width attribute(s) */ > #define HEAP_HASEXTERNAL0x0004/* has external stored attribute(s) */ > #define HEAP_HASOID_OLD0x0008/* has an object-id field */ > #define HEAP_XMAX_KEYSHR_LOCK0x0010/* xmax is a key-shared locker */ > #define HEAP_COMBOCID0x0020/* t_cid is a combo CID */ > #define HEAP_XMAX_EXCL_LOCK0x0040/* xmax is exclusive locker */ > #define HEAP_XMAX_LOCK_ONLY0x0080/* xmax, if valid, is only a locker */ > > And I can't understand these attrs: I suggest you try something like 'git grep HEAP_HASEXTERNAL' which shows you where the flag is used, which should tell you what it means. These short descriptions generally assume you know enough about the internals. > 1. external stored attribute(s), what is this? can you give a create > statement to show me? external = value stored in a TOAST table > 2. xmax is a key-shared locker/exclusive locker/only a locker, so how > you use this? can you give me a scenario? > let me try to explain it: > if there is a txn is trying to read this heaptuple, > the HEAP_XMAX_KEYSHR_LOCK bit will be set to 1. > if there is a txn is trying to delete/update this heaptuple, > the HEAP_XMAX_EXCL_LOCK bit will be set to 1. > but for HEAP_XMAX_LOCK_ONLY, I can't understand. > And another thought is that these three bit can have only one to be set > 1 at most. I believe HEAP_XMAX_LOCK_ONLY means the xmax transaction only locked the tuple, without deleting/updating it. > 3. t_cid is a combo CID? what's a CID? give me an example please. CID means "command ID" i.e. sequential ID assigned to commands in a single session (for visibility checks, so that a query doesn't see data deleted by earlier commands in the same session). See src/backend/utils/time/combocid.c for basic explanation of what "combo CID" is. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company > I believe HEAP_XMAX_LOCK_ONLY means the xmax transaction only locked the > tuple, without deleting/updating it. if so, you mean when I read this tuple, this bit will be set 1, but I think this is duplicat with HEAP_XMAX_KEYSHR_LOCK. > CID means "command ID" i.e. sequential ID assigned to commands in a > single session (for visibility checks, so that a query doesn't see data > deleted by earlier commands in the same session). See > src/backend/utils/time/combocid.c for basic explanation of what "combo > CID" is. I think if cid is used for visibility checks in one session, that's meaingless, beacause we can use the t_xmin and t_xmax to get this goal. Is tis
Re: Re: Why the lp_len is 28 not 32?
From: Tomas Vondra
Date: 2023-02-26 23:07
To: jack...@gmail.com; pgsql-hackers
Subject: Re: Why the lp_len is 28 not 32?
On 2/26/23 15:35, jack...@gmail.com wrote:
> use these sqls below:
> create table t(a int);
> insert into t values(1);
> select lp,lp_off,lp_len,t_data from heap_page_items(get_raw_page('t',0));
> lp | lp_off | lp_len | t_data
> +++
> 1 | 8160 | 28 | \x0100
>
Pretty sure this is because we align the data to MAXALIGN, and on x86_64
that's 8 bytes. 28 is not a multiple of 8 while 32 is.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
yes, So it should be 32 bytes not 28bytes, but the sql result is 28 !!
that's false
-----
jack...@gmail.com;
Why the lp_len is 28 not 32?
use these sqls below:
create table t(a int);
insert into t values(1);
select lp,lp_off,lp_len,t_data from heap_page_items(get_raw_page('t',0));
lp | lp_off | lp_len | t_data
+++
1 | 8160 | 28 | \x0100
jack...@gmail.com
Give me more details of some bits in infomask!!
here are the source codes from src/include/access/htup_details.h. /* * information stored in t_infomask: */ #define HEAP_HASNULL 0x0001 /* has null attribute(s) */ #define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */ #define HEAP_HASEXTERNAL 0x0004 /* has external stored attribute(s) */ #define HEAP_HASOID_OLD 0x0008 /* has an object-id field */ #define HEAP_XMAX_KEYSHR_LOCK 0x0010 /* xmax is a key-shared locker */ #define HEAP_COMBOCID 0x0020 /* t_cid is a combo CID */ #define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax is exclusive locker */ #define HEAP_XMAX_LOCK_ONLY 0x0080 /* xmax, if valid, is only a locker */ And I can't understand these attrs: 1. external stored attribute(s), what is this? can you give a create statement to show me? 2. xmax is a key-shared locker/exclusive locker/only a locker, so how you use this? can you give me a scenario? let me try to explain it: if there is a txn is trying to read this heaptuple, the HEAP_XMAX_KEYSHR_LOCK bit will be set to 1. if there is a txn is trying to delete/update this heaptuple, the HEAP_XMAX_EXCL_LOCK bit will be set to 1. but for HEAP_XMAX_LOCK_ONLY, I can't understand. And another thought is that these three bit can have only one to be set 1 at most. 3. t_cid is a combo CID? what's a CID? give me an example please. -- jack...@gmail.com
How to solve "too many Lwlocks taken"?
I'm trying to write am table_am extension. But I get "too many Lwlocks taken" after I insert too many tuples. So I try to use UnLockBuffers() everywhere; but it still give me "too many Lwlocks taken", So how should I release All locks? -- jack...@gmail.com
Where is the filter?
When I use 'select * from t where a = 1'; And I debug to find where the 'a = 1' is used, when I arrive ExecScan in src/backend/executor/execScan.c, line 158, where this 'a = 1' is stored in? jack...@gmail.com
what's the meaning of key?
I'm doing research on heap_am, and for heap_beginscan func, I find out that there is a arg called nkeys, I use some sqls as examples like 'select * from t;' and 'select * from t where a = 1', but it is always zero, can you give me some descriptions for this? what's it used for? jack...@gmail.com
How to implement read operations for my own access method?
Hi,I'm trying to implement my own access method. But I find the functions baout read is difficult. Can you give me an existed easy extension that impelment the tableamroutine to reference? I ho[e it's not complicated like heap_am,and it support insert sqls and select sqls.Thanks -- jack...@gmail.com
Where is the logig to create a table file?
When I use 'create table t(a int);'; suppose that this table t's oid is 1200, then postgres will create a file named 1200 in the $PGDATA/base, So where is the logic code in the internal? -- jack...@gmail.com
How to write a new tuple into page?
Hi, I'm trying to construct a new tuple type, that's not heaptuple, When I get a tupleTableSlot, I will get data info from it and then I will constuct a new tuple, and now I need to put it into a physical page, how should I do? -- jack...@gmail.com
回复: access method xxx does not exist
I'm trying to add a new index, but when I finish it, I use “ create index xxx_index on t1 using xxx(a); ”,it gives me access method "spb" does not exist And I don't know where this message is from, can you grve me its position? jack...@gmail.com
access method xxx does not exist
I'm trying to add a new index, but when I finish it, I use “ create index xxx_index on t1 using xxx(a); ”,it gives me access method "spb" does not exist And I don't know where this message is from, can you grve me its position? jack...@gmail.com
confused with name in the pic
typedef struct A_Expr
{
pg_node_attr(custom_read_write)
NodeTag type;
A_Expr_Kind kind; /* see above */
List *name; /* possibly-qualified name of operator */
Node *lexpr; /* left argument, or NULL if none */
Node *rexpr; /* right argument, or NULL if none */
int location; /* token location, or -1 if unknown */
} A_Expr;
I run a sql like select a,b from t3 where a > 1; and I get the parseTree for
selectStmt:
why the name is '-' but not '>'?
jack...@gmail.com
1.png
Description: Binary data
