반응형

PostgreSQL에서 reog 대상 테이블 선정 반환되는 사이즈 확인

 

0. 결론

- 정확하게 반환되는 사이즈는 확인 불가 (free 공간 비슷하게 공간 반환)

--> 데이터가 block 저장 저장가능 모든 공간을 사용할 있는 데이터 사이즈 경우 free size 반환되나, 남은 공간이 하나의 Row를 저장할 공간이 되지 않을 경우 , 새로운 block 저장하므로 free 공간이 생길 밖에 없으므로 free 공간이 반환된다고 수는 없다

- pgstattuple 전체 테이블에 대한 full scan 수행 한다

 

1. 테스트 데이터 생성

테이블 생성 drop table if exists test_reog ;


create table test_reog
( item_code      char(12) ,
  order_id       char(12) ,
  order_flag     char(1) , 
  order_date     timestamp ,
  check_flag     char(1) ,
  check_date     timestamp ,
  delivery_flag  char(1) ,
  delivery_date  timestamp ,
  delivery_complite_flag char(1) ,
  delivery_complite_date timestamp ) ;
데이터 삽입 do $insert$
declare
  i int;
begin
  for i in 1..10000000 loop
    insert into test_reog values ( concat('item',lpad(i::bpchar,8,'0')) , 'admin' , 'Y' , now() ,  NULL , NULL , NULL , NULL , NULL , NULL ) ;
  end loop;
end $insert$ ;
사이즈 확인 select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze , pg_size_pretty(pg_relation_size( 'test_reog' )) as table_size from pg_stat_all_tables where relname= 'test_reog' ;


 schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size
------------+-----------+------------+-----------+------------+-----------------+--------------+------------
 scott      | test_reog |   10000000 |         0 |          0 |                 |              | 730 MB

 

2. 데이터 변경

전체 데이터 중에서 1/100 변경

update 속도 향상을 위해서 인덱스 생성

인덱스 생성 create index test_reog_item_code on test_reog(item_code) ;
사이즈 확인 select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,
       pg_size_pretty(pg_table_size('test_reog')) as table_size ,
       pg_size_pretty(pg_indexes_size('test_reog')) as idx_size
from pg_stat_all_tables where relname= 'test_reog' ;


 schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup |        last_autovacuum        | last_analyze | table_size | idx_size
------------+-----------+------------+-----------+------------+-------------------------------+--------------+------------+----------
 scott      | test_reog |   10000006 |         0 |          0 | 2024-01-24 13:21:02.058931+09 |              | 730 MB     | 301 MB
데이터 변경 do $update$
declare
  i int;
begin
  for i in 1..10000000 loop
    if mod(i,100) = 0 then
      update test_reog
      set    check_flag = 'Y' , check_date = now()
      where  item_code = concat('item',lpad(i::bpchar,8,'0'))::bpchar ;
    end if;
  end loop;
end $update$ ;
사이즈 확인 select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,
       pg_size_pretty(pg_table_size('test_reog')) as table_size ,
       pg_size_pretty(pg_indexes_size('test_reog')) as idx_size
from pg_stat_all_tables where relname= 'test_reog' ;


 schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup |        last_autovacuum        | last_analyze | table_size | idx_size
------------+-----------+------------+-----------+------------+-------------------------------+--------------+------------+----------
 scott      | test_reog |   10000006 |    100000 |     100000 | 2024-01-24 13:21:02.058931+09 |              | 739 MB     | 301 MB

 

3. 데이터 확인

현재 테이블의 데이터 중에서 free 공간이 얼마나 되는지 확인해 보자

 

테스트 1 평균 tuple size tuple count 이용해서 계산해 본다
테이블의 평균 칼럼 사이즈(pg_stats.avg_width) 합한 후에 전체 테이블 사이즈와 비교 작업 진행


analyze test_reog ;


select a.tablename , pg_size_pretty(a.row_cnt * b.avg_row_byte) as tb_avg_used_size ,
       pg_size_pretty(pg_table_size('test_reog')) as tb_real_size ,
  ((a.row_cnt * b.avg_row_byte)::float/pg_table_size('test_reog')::float)*100 as tb_used_per
from   (select 'test_reog' as tablename , count(*) as row_cnt from test_reog ) a ,
       (select 'test_reog' as tablename , sum(avg_width) as avg_row_byte from pg_stats where tablename ='test_reog' )  b
where  a.tablename = b.tablename ;


 tablename | tb_avg_used_size | tb_real_size |    tb_used_per   
-----------+------------------+--------------+-------------------
 test_reog | 591 MB           | 739 MB       | 79.98350709122421


전체 table size : 739 MB
tuple 사용하는 size : 591 MB
대략 141 MB 공간이 비어 있는 것으로 나옴
테스트 2 pgstattuple 익스텐션을 사용하여 계산


create extension pgstattuple;


SELECT * FROM pgstattuple('scott.test_reog');


 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 774922240 |    10000000 | 721600000 |         93.12 |                0 |              0 |                  0 |   10273580 |         1.33


select pg_size_pretty(774922240::numeric) as table_size,
       pg_size_pretty(721600000::numeric) as tuple_size,
       pg_size_pretty(10273580::numeric) as free_size ;


 table_size | tuple_size | free_size
------------+------------+-----------
 739 MB     | 688 MB     | 10033 kB


전체 테이블 중에 1.33% free 공간으로 나오며, 실제 테이블 사이즈에서 tuple 사용하는 사이즈를 빼면 51 MB 나오지만 freespace 10 MB 정도를 나타낸다

 

테스트 #1, 테스트 #2 결과가 너무 다르므로 실제 vacuum full 수행해 본다

 

4. vacuum full 수행

 

vacuum full 수행 acuum full test_reog ;
사이즈 확인 select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,
       pg_size_pretty(pg_table_size('test_reog')) as table_size ,
       pg_size_pretty(pg_indexes_size('test_reog')) as idx_size
from pg_stat_all_tables where relname= 'test_reog' ;


 schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup |        last_autovacuum        |         last_analyze          | table_size | idx_size
------------+-----------+------------+-----------+------------+-------------------------------+-------------------------------+------------+----------
 scott      | test_reog |    9999426 |    100000 |     100000 | 2024-01-24 13:21:02.058931+09 | 2024-01-24 13:32:09.581285+09 | 732 MB     | 301 MB

 

5. 테스트

예상으로는 free size 10 MB 줄어서 729 MB 되기를 기대 하였으나 테이블 사이즈가 732 MB 7 MB정도 줄어 들었다

그래서 다시 auto vacuum 수행되지 않도록 테이블에 설정 다시 테스트 진행

 

 

테이블 생성 drop table if exists test_reog ;


create table test_reog
( item_code      char(12) ,
  order_id       char(12) ,
  order_flag     char(1) , 
  order_date     timestamp ,
  check_flag     char(1) ,
  check_date     timestamp ,
  delivery_flag  char(1) ,
  delivery_date  timestamp ,
  delivery_complite_flag char(1) ,
  delivery_complite_date timestamp ) ;
 
alter table test_reog set (autovacuum_enabled = false) ;
데이터 입력 do $insert$
declare
  i int;
begin
  for i in 1..10000000 loop
    insert into test_reog values ( concat('item',lpad(i::bpchar,8,'0')) , 'admin' , 'Y' , now() ,  NULL , NULL , NULL , NULL , NULL , NULL ) ;
  end loop;
end $insert$ ;


select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze , pg_size_pretty(pg_relation_size( 'test_reog' )) as table_size from pg_stat_all_tables where relname= 'test_reog' ;


 schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size
------------+-----------+------------+-----------+------------+-----------------+--------------+------------
 scott      | test_reog |   10000000 |         0 |          0 |                 |              | 730 MB


create index test_reog_item_code on test_reog(item_code) ;


select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,
       pg_size_pretty(pg_table_size('test_reog')) as table_size ,
       pg_size_pretty(pg_indexes_size('test_reog')) as idx_size
from pg_stat_all_tables where relname= 'test_reog' ;


 schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size | idx_size
------------+-----------+------------+-----------+------------+-----------------+--------------+------------+----------
 scott      | test_reog |   10000000 |         0 |          0 |                 |              | 730 MB     | 301 MB
데이터 변경 do $update$
declare
  i int;
begin
  for i in 1..10000000 loop
    if mod(i,100) = 0 then
      update test_reog
      set    check_flag = 'Y' , check_date = now()
      where  item_code = concat('item',lpad(i::bpchar,8,'0'))::bpchar ;
    end if;
  end loop;
end $update$ ;


select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,
       pg_size_pretty(pg_table_size('test_reog')) as table_size ,
       pg_size_pretty(pg_indexes_size('test_reog')) as idx_size
from pg_stat_all_tables where relname= 'test_reog' ;


 schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size | idx_size
------------+-----------+------------+-----------+------------+-----------------+--------------+------------+----------
 scott      | test_reog |   10000000 |    100000 |     100000 |                 |              | 739 MB     | 301 MB
psstattuple select pg_size_pretty(table_len) as table_size , pg_size_pretty(tuple_len) as tuple_size , pg_size_pretty(free_space) as free_size , tuple_count , dead_tuple_count , tuple_percent , free_percent from pgstattuple('scott.test_reog');
 table_size | tuple_size | free_size | tuple_count | dead_tuple_count | tuple_percent | free_percent
------------+------------+-----------+-------------+------------------+---------------+--------------
 739 MB     | 688 MB     | 3002 kB   |    10000000 |           100000 |         93.12 |          0.4


현재는 vacuum 수행되지 않아서 dead tuple 공간을 차지 하고 있으므로 free 공간이 3MB임을 있다
vacuum
pgstattuple
vacuum test_reog ;


select pg_size_pretty(table_len) as table_size , pg_size_pretty(tuple_len) as tuple_size , pg_size_pretty(free_space) as free_size , tuple_count , dead_tuple_count , tuple_percent , free_percent from pgstattuple('scott.test_reog');
 table_size | tuple_size | free_size | tuple_count | dead_tuple_count | tuple_percent | free_percent
------------+------------+-----------+-------------+------------------+---------------+--------------
 739 MB     | 688 MB     | 10036 kB  |    10000000 |                0 |         93.12 |         1.33


vacuum 수행 dead tuple 있던 공간이 사용가능 공간으로 변경 되어서 free 공간이 10MB 늘어
vacuum full vacuum full test_reog ;


select schemaname , relname , n_live_tup , n_tup_upd, n_dead_tup , last_autovacuum , last_analyze ,
       pg_size_pretty(pg_table_size('test_reog')) as table_size ,
       pg_size_pretty(pg_indexes_size('test_reog')) as idx_size
from pg_stat_all_tables where relname= 'test_reog' ;


 schemaname |  relname  | n_live_tup | n_tup_upd | n_dead_tup | last_autovacuum | last_analyze | table_size | idx_size
------------+-----------+------------+-----------+------------+-----------------+--------------+------------+----------
 scott      | test_reog |   10000000 |    100000 |          0 |                 |              | 732 MB     | 301 MB


역시나 10 MB 공간이 반환 되기를 기대 하였으나, 7 MB 공간만이 줄어 들었다


select pg_size_pretty(table_len) as table_size , pg_size_pretty(tuple_len) as tuple_size , pg_size_pretty(free_space) as free_size , tuple_count , dead_tuple_count , tuple_percent , free_percent from pgstattuple('scott.test_reog');
 table_size | tuple_size | free_size | tuple_count | dead_tuple_count | tuple_percent | free_percent
------------+------------+-----------+-------------+------------------+---------------+--------------
 732 MB     | 688 MB     | 2969 kB   |    10000000 |                0 |         94.05 |          0.4


pgstattuple 다시 정보를 조회해 보면 free 공간이 3 MB 존재 한다
테이블 사이즈 739 MB에서 10 MB 줄어든 729 MB 기대 하였으나 732 MB 테이블 사이즈를 가지며, 3 MB free 공간이 있는 상태이다
그러면 732 MB - 3 MB 하면 729 MB 예상했던 사이즈로 생각해 수가 있다 ?
3MB ? vacuum full 해서 모든 데이터를 다시 insert 했음에도 3MB free 공간이 존재하는 이유를 찾아보자


select relpages from pg_class where relname = 'test_reog' ;
 relpages
----------
    93660


전체 블록 개수는 93660


select 2969 *1024 / 93660 ;
 ?column?
----------
       32


free 공간(2969 kb) 블로 개수로 나누면 블록당 32 byte 여유 공간이 존재 한다는


블록을 살펴 보자 !!!


CREATE EXTENSION pageinspect;


테이블의 블록을 처음부터 확인해 보자


select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',0))
union all
select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',1))
union all
select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',2))
union all
select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',3))
union all
select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',93658))
union all
select lower , upper , special , pagesize from page_header(get_raw_page('test_reog',93659)) ;
 lower | upper | special | pagesize
-------+-------+---------+----------
   452 |   488 |    8192 |     8192
   452 |   488 |    8192 |     8192
   452 |   488 |    8192 |     8192
   452 |   488 |    8192 |     8192
   452 |   488 |    8192 |     8192
   332 |  2648 |    8192 |     8192





블록의 lower ~ upper까지의 공간이 free 공간인데, 마지막 블록을 제외하고 488-452 하면 36 byte free 공간을 가지고 있는 것이 확인된다


전체 free 공간을 더해 보자


do $select$
declare
  i int ;
  free_sum int := 0 ;
  free int := 0 ;
begin
  for i in 0..93658 loop
    select upper - lower
    into   free        
from page_header(get_raw_page('test_reog', i )) ;


free_sum := free_sum + free ;
free := 0 ;
  end loop;
  RAISE NOTICE 'free : % ' , free_sum ;
end $select$ ;
NOTICE:  free : 3412564




select pg_size_pretty(3412564::numeric) ;
 pg_size_pretty
----------------
 3333 kB


정확하게 일치 하지 않지만, 비슷한 사이즈의 free 공간이 있음을 확인 있다




하나의 tuple 평균 사이즈를 확인해 보면


analyze test_reog ;


select 'test_reog' as tablename , sum(avg_width) as avg_row_byte from pg_stats where tablename ='test_reog' ;
 tablename | avg_row_byte
-----------+--------------
 test_reog |           62


하나의 tuple 평균 사이즈가 62 byte 이므로 하나의 tuple 저장할 공간으로 62 byte 되지 않을 경우 다른 블록에 데이터를 insert 하므로 블록 free 공간이 존재 함을
확인 있다


정확하게 free 공간이 일치 하였으면 깔끔했을 같으나, 대략적인 원인 정도만 파악을 해서 약간 아쉬움(?) 남는다

 

6. Reog 대상 확인 쿼리

아래 쿼리는 테이블 크기가 500MB 이상인 테이블을 대상으로 freespace 많은 테이블들 순으로 조회 하는 쿼리 입니다

 

select schemaname , relname ,  pg_size_pretty(tb_byte) tb_byte , pg_size_pretty(free_byte) free_byte , free_per
from   ( select schemaname ,
                relname ,
                (pgstattuple(concat(schemaname,'.',relname))).table_len tb_byte, 
                (pgstattuple(concat(schemaname,'.',relname))).free_space free_byte,
                (pgstattuple(concat(schemaname,'.',relname))).free_percent free_per
         from   pg_stat_user_tables ) user_tb
where   tb_byte > 500000000
order by free_byte desc ;


 schemaname |  relname  | tb_byte | free_byte | free_per
------------+-----------+---------+-----------+----------
 scott      | test_reog | 732 MB  | 2969 kB   |      0.4

 

반응형

+ Recent posts