扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >pg_repack bloat 处理测试初步
  • 782

0
分享到

pg_repack bloat 处理测试初步

2024-04-02 19:04:59 782人浏览 独家记忆
摘要

一、软件安装1.软件需求：postgresql-9.5.2.tar.gzpg_repack-1.3.4.zip2.安装pg_repack[root@localhost pg_repack-1.3.4]# e

一、软件安装


1.软件需求：

postgresql-9.5.2.tar.gz

pg_repack-1.3.4.zip


2.安装pg_repack


[root@localhost pg_repack-1.3.4]# export PATH=/opt/pgsql/9.5.2/bin:$PATH

[root@localhost pg_repack-1.3.4]# export LD_LIBRARY_PATH=/opt/pgsql/9.5.2/lib

[root@localhost pg_repack-1.3.4]# export MANPATH=/opt/pgsql/9.5.2/share/man:$MANPATH

[root@localhost pg_repack-1.3.4]# make

make[1]: Entering directory `/home/soft/pg_repack-1.3.4/bin'

GCc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-fORMat-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I/opt/pgsql/9.5.2/include -DREPACK_VERSION=1.3.4 -I. -I./ -I/opt/pgsql/9.5.2/include/server -I/opt/pgsql/9.5.2/include/internal -D_GNU_SOURCE   -c -o pg_repack.o pg_repack.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I/opt/pgsql/9.5.2/include -DREPACK_VERSION=1.3.4 -I. -I./ -I/opt/pgsql/9.5.2/include/server -I/opt/pgsql/9.5.2/include/internal -D_GNU_SOURCE   -c -o pgut/pgut.o pgut/pgut.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I/opt/pgsql/9.5.2/include -DREPACK_VERSION=1.3.4 -I. -I./ -I/opt/pgsql/9.5.2/include/server -I/opt/pgsql/9.5.2/include/internal -D_GNU_SOURCE   -c -o pgut/pgut-fe.o pgut/pgut-fe.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 pg_repack.o pgut/pgut.o pgut/pgut-fe.o -L/opt/pgsql/9.5.2/lib -lpq -L/opt/pgsql/9.5.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/9.5.2/lib',--enable-new-dtags  -lpgcommon -lpgport -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack

make[1]: Leaving directory `/home/soft/pg_repack-1.3.4/bin'

make[1]: Entering directory `/home/soft/pg_repack-1.3.4/lib'

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -DREPACK_VERSION=1.3.4 -I. -I./ -I/opt/pgsql/9.5.2/include/server -I/opt/pgsql/9.5.2/include/internal -D_GNU_SOURCE   -c -o repack.o repack.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -DREPACK_VERSION=1.3.4 -I. -I./ -I/opt/pgsql/9.5.2/include/server -I/opt/pgsql/9.5.2/include/internal -D_GNU_SOURCE   -c -o pgut/pgut-be.o pgut/pgut-be.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -DREPACK_VERSION=1.3.4 -I. -I./ -I/opt/pgsql/9.5.2/include/server -I/opt/pgsql/9.5.2/include/internal -D_GNU_SOURCE   -c -o pgut/pgut-spi.o pgut/pgut-spi.c

( echo '{ global:'; gawk '/^[^#]/ {printf "%s;\n",$1}' exports.txt; echo ' local: *; };' ) >exports.list

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -shared -Wl,--version-script=exports.list -o pg_repack.so repack.o pgut/pgut-be.o pgut/pgut-spi.o -L/opt/pgsql/9.5.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/9.5.2/lib',--enable-new-dtags  

sed 's,REPACK_VERSION,1.3.4,g' pg_repack.sql.in > pg_repack--1.3.4.sql;

sed 's,REPACK_VERSION,1.3.4,g' pg_repack.control.in > pg_repack.control

make[1]: Leaving directory `/home/soft/pg_repack-1.3.4/lib'

make[1]: Entering directory `/home/soft/pg_repack-1.3.4/regress'

make[1]: Nothing to be done for `all'.

make[1]: Leaving directory `/home/soft/pg_repack-1.3.4/regress'

[root@localhost pg_repack-1.3.4]# make install

make[1]: Entering directory `/home/soft/pg_repack-1.3.4/bin'

/bin/mkdir -p '/opt/pgsql/9.5.2/bin'

/usr/bin/install -c  pg_repack '/opt/pgsql/9.5.2/bin'

make[1]: Leaving directory `/home/soft/pg_repack-1.3.4/bin'

make[1]: Entering directory `/home/soft/pg_repack-1.3.4/lib'

/bin/mkdir -p '/opt/pgsql/9.5.2/lib'

/bin/mkdir -p '/opt/pgsql/9.5.2/share/extension'

/bin/mkdir -p '/opt/pgsql/9.5.2/share/extension'

/usr/bin/install -c -m 755  pg_repack.so '/opt/pgsql/9.5.2/lib/pg_repack.so'

/usr/bin/install -c -m 644 .//pg_repack.control '/opt/pgsql/9.5.2/share/extension/'

/usr/bin/install -c -m 644  pg_repack--1.3.4.sql pg_repack.control '/opt/pgsql/9.5.2/share/extension/'

make[1]: Leaving directory `/home/soft/pg_repack-1.3.4/lib'

make[1]: Entering directory `/home/soft/pg_repack-1.3.4/regress'

make[1]: Nothing to be done for `install'.

make[1]: Leaving directory `/home/soft/pg_repack-1.3.4/regress'

[root@localhost pg_repack-1.3.4]# 


3.创建初始环境


[postgres@localhost ~]$ createdb bloatdb

[postgres@localhost ~]$ psql -d bloatdb -c "create extension pgstattuple;"

CREATE EXTENSION

[postgres@localhost ~]$ psql -d bloatdb -c "CREATE EXTENSION pg_repack;"

CREATE EXTENSION

[postgres@localhost ~]$ 

$ psql bloatdb

psql (9.5.2)

Type "help" for help.


bloatdb=# \dx

                                   List of installed extensions

    Name     | Version |   Schema   |                         Description                          

-------------+---------+------------+--------------------------------------------------------------

 pg_repack   | 1.3.4   | public     | Reorganize tables in PostgreSQL databases with minimal locks

 pgstattuple | 1.3     | public     | show tuple-level statistics

 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language

(3 rows)



二、静态（无活跃交易）膨胀整理测试


1.处理表tbl指定索引

1).准备环境

bloatdb=# create table tbl(id int primary key, first varchar(20),second varchar(20));

CREATE TABLE

bloatdb=# create index idx_tbl_first on tbl (first);

CREATE INDEX

bloatdb=# create index idx_tbl_second on tbl (second);

CREATE INDEX

bloatdb=# SELECT count(*) FROM tbl;

 count 

-------

     0

(1 row)


bloatdb=# SELECT pg_size_pretty(pg_total_relation_size('tbl'));

 pg_size_pretty 

----------------

 24 kB

(1 row)


bloatdb=# INSERT INTO tbl VALUES(generate_series(1,10000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

INSERT 0 10000

bloatdb=# SELECT count(*) FROM tbl;

 count 

-------

 10000

(1 row)


bloatdb=# SELECT pg_size_pretty(pg_total_relation_size('tbl'));

 pg_size_pretty 

----------------

 1584 kB

(1 row)


bloatdb=# 


更新列

bloatdb=# UPDATE tbl SET first= 'updated-001';

UPDATE 10000

bloatdb=# SELECT count(*) FROM tbl;

 count 

-------

 10000

(1 row)


bloatdb=# SELECT pg_size_pretty(pg_total_relation_size('tbl'));

 pg_size_pretty 

----------------

 3376 kB

(1 row)


bloatdb=# 


2).查询膨胀率

建立膨胀统计表

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" --create_stats_table


膨胀统计

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.idx_tbl_second.......................................................(52.69%) 417 kB wasted

2. public.idx_tbl_first........................................................(52.64%) 413 kB wasted

3. public.tbl_pkey.............................................................(57.79%) 388 kB wasted

[postgres@localhost ~]$ 


3).处理膨胀

指定数据库的特定索引

[postgres@localhost ~]$ pg_repack -d bloatdb --index idx_tbl_first

INFO: repacking index "public"."idx_tbl_first"

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl

1. public.idx_tbl_second.......................................................(52.69%) 417 kB wasted

2. public.tbl_pkey.............................................................(57.79%) 388 kB wasted

3. public.idx_tbl_first.....................................................(0.93%) 3121 bytes wasted

[postgres@localhost ~]$ 


2.处理表tbl所有索引

1).准备环境

bloatdb=# update tbl set second='chris';

UPDATE 10000

bloatdb=# SELECT count(*) FROM tbl;

 count 

-------

 10000

(1 row)


bloatdb=# SELECT pg_size_pretty(pg_total_relation_size('tbl'));

 pg_size_pretty 

----------------

 3600 kB

(1 row)


bloatdb=#

bloatdb=# update tbl set first='chris';

UPDATE 10000

bloatdb=# SELECT count(*) FROM tbl;

 count 

-------

 10000

(1 row)


bloatdb=# SELECT pg_size_pretty(pg_total_relation_size('tbl'));

 pg_size_pretty 

----------------

 4176 kB

(1 row)


bloatdb=# 

2).检查膨胀

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.idx_tbl_second.......................................................(59.94%) 820 kB wasted

2. public.idx_tbl_first........................................................(40.94%) 409 kB wasted

3. public.tbl_pkey.............................................................(28.73%) 193 kB wasted

[postgres@localhost ~]$ 


3).处理tbl表所有索引膨胀

[postgres@localhost ~]$ pg_repack -d bloatdb --table tbl --only-indexes

INFO: repacking indexes of "tbl"

INFO: repacking index "public"."idx_tbl_first"

INFO: repacking index "public"."idx_tbl_second"

INFO: repacking index "public"."tbl_pkey"

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.idx_tbl_first.....................................................(1.23%) 3028 bytes wasted

2. public.idx_tbl_second....................................................(1.23%) 3028 bytes wasted

3. public.tbl_pkey..........................................................(1.23%) 3028 bytes wasted

[postgres@localhost ~]$ 


3.处理tbl数据和索引膨胀

1).索引膨胀

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl

1. public.idx_tbl_first.........................................................(57.87%) 49 MB wasted

2. public.idx_tbl_second........................................................(39.29%) 34 MB wasted

3. public.tbl_pkey..............................................................(51.22%) 26 MB wasted

 

2).处理膨胀online VACUUM FULL 数据库bloatdb表tbl(数据和索引)

[postgres@localhost ~]$ pg_repack --no-order --table tbl -d bloatdb

INFO: repacking table "tbl"

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl

1. public.tbl_pkey..............................................................(0.0%) 0 bytes wasted

2. public.idx_tbl_second........................................................(0.0%) 0 bytes wasted

3. public.idx_tbl_first.........................................................(0.0%) 0 bytes wasted

[postgres@localhost ~]$ 


三、动态(有交易发生时)膨胀处理


1.整个表做膨胀处理

1).初始条件

-- clear table data

bloatdb=# select * from tbl;

 id | first | second 

----+-------+--------

(0 rows)


bloatdb=# 

bloatdb=# INSERT INTO tbl VALUES(generate_series(1,100000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

INSERT 0 100000

bloatdb=# UPDATE tbl SET first= 'updated-001';

UPDATE 100000

bloatdb=# 

-- check bloat

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.idx_tbl_second........................................................(67.26%) 17 MB wasted

2. public.idx_tbl_first.........................................................(67.46%) 17 MB wasted

3. public.tbl_pkey............................................................(63.91%) 9832 kB wasted

[postgres@localhost ~]$ 


2).大量插入数据同时做膨胀处理

statement_timeout=0, 视情况调整：maintenance_work_mem，wal_keep_segments(streaming,SSD<2000>)

先插入数据，过程中处理膨胀加上-T参数值为3600.

-- session 1：insert data

bloatdb=# INSERT INTO tbl VALUES(generate_series(100001,3000000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

光标闪烁

-- session 2：repack during insert

$ pg_repack -d bloatdb --no-order --table tbl --wait-timeout=3600

INFO: repacking table "tbl"

光标闪烁

############################## args: -j ###########################################

如果使用--table指定多个table时，会依次处理每个指定的表。如果整理使用-j参数，则pg_repack，在创建临时表索引时会启动多个后台进程并行创建索引，一般每建立一个索引都需要启动一个后台进程，直到min(j,tbl_idx_number<表中总的索引数>)数量的worker被创建完成。当指定j数量小于索引数量时，一个索引创建完成时，空闲的work会自动被分派去建立剩余索引。当指定j数量大于索引数量时，一次性分派索引总数个work来执行索引创建任务。

$ pg_repack -j 10 --no-order -d bloatdb --table tbl --wait-timeout=3600

NOTICE: Setting up workers.conns

INFO: repacking table "tbl"

LOG: Initial worker 0 to build index: CREATE UNIQUE INDEX index_22025 ON repack.table_22022 USING btree (id)

LOG: Initial worker 1 to build index: CREATE INDEX index_22027 ON repack.table_22022 USING btree (first)

LOG: Initial worker 2 to build index: CREATE INDEX index_22028 ON repack.table_22022 USING btree (second)

LOG: Command finished in worker 0: CREATE UNIQUE INDEX index_22025 ON repack.table_22022 USING btree (id)

LOG: Command finished in worker 1: CREATE INDEX index_22027 ON repack.table_22022 USING btree (first)

LOG: Command finished in worker 2: CREATE INDEX index_22028 ON repack.table_22022 USING btree (second)

$

指定多个表的情况，j < idx_numbers

$ pg_repack -j 2 --no-order -d bloatdb --table tbl -t tbl01 --wait-timeout=3600

NOTICE: Setting up workers.conns

INFO: repacking table "tbl"

LOG: Initial worker 0 to build index: CREATE UNIQUE INDEX index_22025 ON repack.table_22022 USING btree (id)

LOG: Initial worker 1 to build index: CREATE INDEX index_22027 ON repack.table_22022 USING btree (first)

LOG: Command finished in worker 0: CREATE UNIQUE INDEX index_22025 ON repack.table_22022 USING btree (id)

LOG: Assigning worker 0 to build index #2: CREATE INDEX index_22028 ON repack.table_22022 USING btree (second)

LOG: Command finished in worker 1: CREATE INDEX index_22027 ON repack.table_22022 USING btree (first)

LOG: Command finished in worker 0: CREATE INDEX index_22028 ON repack.table_22022 USING btree (second)

(处理过程中有长事务，会等待事务完成)

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

NOTICE: Waiting for 1 transactions to finish. First PID: 10426

INFO: repacking table "tbl01"

LOG: Initial worker 0 to build index: CREATE UNIQUE INDEX index_22065 ON repack.table_22062 USING btree (id)

LOG: Initial worker 1 to build index: CREATE INDEX index_22067 ON repack.table_22062 USING btree (first)

LOG: Command finished in worker 0: CREATE UNIQUE INDEX index_22065 ON repack.table_22062 USING btree (id)

LOG: Assigning worker 0 to build index #2: CREATE INDEX index_22068 ON repack.table_22062 USING btree (second)

LOG: Command finished in worker 1: CREATE INDEX index_22067 ON repack.table_22062 USING btree (first)

LOG: Command finished in worker 0: CREATE INDEX index_22068 ON repack.table_22062 USING btree (second)

$ 


##################################################################################

--session 1 finish insert

bloatdb=# INSERT INTO tbl VALUES(generate_series(100001,3000000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

INSERT 0 2900000

bloatdb=# 


-- session 2： finish repack

[postgres@localhost ~]$ pg_repack -d bloatdb --no-order --table tbl --wait-timeout=3600

INFO: repacking table "tbl"

-- session 2：膨胀检查

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.tbl_pkey..............................................................(0.0%) 0 bytes wasted

2. public.idx_tbl_second........................................................(0.0%) 0 bytes wasted

3. public.idx_tbl_first.........................................................(0.0%) 0 bytes wasted

[postgres@localhost ~]$ 

-- session 1： 数据检查

bloatdb=# select count(*) from tbl ;

  count  

---------

 3000000

(1 row)


bloatdb=# 


2.指定tbl表所有索引膨胀处理

如果tbl表有多个索引情况下，默认处理方式，一个索引接着一个索引做膨胀处理即使指定了-j参数大于1。


1).准备数据


--session 1： insert data

bloatdb=# delete FROM tbl;

DELETE 3000000

bloatdb=# INSERT INTO tbl VALUES(generate_series(1,100000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

INSERT 0 100000

bloatdb=# update tbl set first='chris';

UPDATE 100000

bloatdb=# 


-- session 2：check bloat


[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.tbl_pkey..............................................................(41.14%) 28 MB wasted

2. public.idx_tbl_second.......................................................(4.32%) 4471 kB wasted

3. public.idx_tbl_first........................................................(2.96%) 2889 kB wasted

[postgres@localhost ~]$


2).online insert and repack

--session 1： insert large data

bloatdb=# INSERT INTO tbl VALUES(generate_series(100001,3000000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

光标闪烁


-- session 2：process bloat，during session 1 inert large data

[postgres@localhost ~]$ pg_repack -d bloatdb --table tbl --only-indexes --wait-timeout=3600

INFO: repacking indexes of "tbl"

INFO: repacking index "public"."idx_tbl_first"

INFO: repacking index "public"."idx_tbl_second"

光标闪烁

--session 1：insert finish

bloatdb=# INSERT INTO tbl VALUES(generate_series(100001,3000000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

INSERT 0 2900000

bloatdb=# 

--session 2：repack finish

[postgres@localhost ~]$ pg_repack -d bloatdb --table tbl --only-indexes -T 3600

INFO: repacking indexes of "tbl"

INFO: repacking index "public"."idx_tbl_first"

INFO: repacking index "public"."idx_tbl_second"

INFO: repacking index "public"."tbl_pkey"


3) check table data and index bloat

--session 2：check bloat

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.tbl_pkey..............................................................(0.0%) 0 bytes wasted

2. public.idx_tbl_first.........................................................(0.0%) 0 bytes wasted

3. public.idx_tbl_second........................................................(0.0%) 0 bytes wasted

[postgres@localhost ~]$ 

--session 1：check table data

bloatdb=# select count(*) from tbl;

  count  

---------

 3000000

(1 row)


bloatdb=# 



3.指定tbl表指定索引膨胀处理


注意：--index(默认使用concurrently方式创建指定索引)，无法与--only-indexes选项同时使用。

[postgres@localhost ~]$ pg_repack -d bloatdb --index idx_tbl_first --only-indexes 

ERROR: cannot specify --index (-i) and --only-indexes (-x)


1).准备数据

-- read data

bloatdb=# delete FROM tbl;

DELETE 3000000

bloatdb=# INSERT INTO tbl VALUES(generate_series(1,100000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

INSERT 0 100000

bloatdb=# update tbl set first='chris';

UPDATE 100000

bloatdb=# 

-- check bloat

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.idx_tbl_second........................................................(47.57%) 97 MB wasted

2. public.tbl_pkey.............................................................(9.44%) 7206 kB wasted

3. public.idx_tbl_first........................................................(3.11%) 3040 kB wasted

[postgres@localhost ~]$ 



2).online insert and repack

--session 1： insert large data

bloatdb=# INSERT INTO tbl VALUES(generate_series(100001,3000000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

光标闪烁


-- session 2：process bloat，during session 1 inert large data

[postgres@localhost ~]$ pg_repack -d bloatdb --index idx_tbl_second --wait-timeout=3600

INFO: repacking index "public"."idx_tbl_second"

光标闪烁


--session 1：insert finish

bloatdb=# INSERT INTO tbl VALUES(generate_series(100001,3000000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

INSERT 0 2900000

bloatdb=# 

--session 2：repack finish

[postgres@localhost ~]$ pg_repack -d bloatdb --index idx_tbl_second --wait-timeout=3600

INFO: repacking index "public"."idx_tbl_second"

[postgres@localhost ~]$ 


3) check table data and index bloat

--session 2：check bloat

[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=bloatdb" -t tbl 

1. public.idx_tbl_first........................................................(50.77%) 102 MB wasted

2. public.tbl_pkey...............................................................(47.6%) 65 MB wasted

3. public.idx_tbl_second........................................................(0.0%) 0 bytes wasted

[postgres@localhost ~]$ 

--session 1：check table data

bloatdb=# select count(*) from tbl;

  count  

---------

 3000000

(1 row)


bloatdb=# 

 


测试结论：

  1. 一般同等条件下，索引比数据更容易膨胀。

  2. 在磁盘空间较紧张的情况下，建议一条接着一条索引处理。

  3. 一般bloat处理所需磁盘空闲空间是对象size的2倍，所以处理前必须先关注空闲磁盘空间大小。

  4. 注意pg_repack版本对Pg版本的支持情况，9.6截至2016-11-26仍未支持，详见Http://pgxn.org/dist/pg_repack/doc/pg_repack.html#Releases。

  5. 处理存在在线交易的表或者索引对象的bloat时，注意设置超时参数--wait-timeout，一般设置为1800或3600（特别感谢李海龙建议）。

特别声明：本说明只针对此次测试环境，在生产环境要在业务低峰时期运行，为了保证系统数据安全，建议先备份数据，然后做膨胀处理


您可能感兴趣的文档:

--结束END--

本文标题: pg_repack bloat 处理测试初步

本文链接: https://lsjlt.com/news/46053.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • pg_repack bloat 处理测试初步
    一、软件安装1.软件需求：postgresql-9.5.2.tar.gzpg_repack-1.3.4.zip2.安装pg_repack[root@localhost pg_repack-1.3.4]# e...
    99+
    2024-04-02
  • PHP PHPUnit测试：初学者的一步步教程
    PHPUnit 是一个流行的 PHP 单元测试框架，它允许开发人员测试代码的各个方面，确保其准确性和可靠性。对于初学者来说，使用 PHPUnit 进行测试可能看起来很复杂，但通过分步指南，它可以变得更加容易。 第一步：安装 PHPUnit...
    99+
    2024-04-02
  • python 对信号 处理的 测试
     python 对信号 处理的测试 小结下： 每次信号 会将当前执行的函数挂起，进入 信号处理函数 如果信号处理函数还在处理，又来信号，当前函数仍然被挂起 执行完毕回到刚才挂起点继续执行 从下面输出 我们就可以看出来 ...
    99+
    2023-01-31
    信号 测试 python
  • 协会不处理测试条目
    今日不肯埋头，明日何以抬头！每日一句努力自己的话哈哈~哈喽，今天我将给大家带来一篇《协会不处理测试条目》，主要内容是讲解等等，感兴趣的朋友可以收藏或者有更好的建议在评论提出，我都会认真看的！大家一起...
    99+
    2024-04-05
  • 如何管理测试依赖项的初始化代码
    php小编新一将为您介绍如何管理测试依赖项的初始化代码。在进行软件开发时，往往需要使用各种测试工具和框架，而这些工具和框架可能需要一些初始化代码来进行配置和准备工作。管理这些初始化代码...
    99+
    2024-02-09
    标准库
  • 如何处理PHP开发中的单元测试和自动化测试
    随着软件开发行业的日益发展，单元测试和自动化测试成为了开发者们重视的环节。PHP作为一种广泛应用于Web开发的脚本语言，单元测试和自动化测试同样也在PHP开发中扮演着重要的角色。本文将介绍如何处理PHP开发中的单元测试和自动化测试，并提供一...
    99+
    2023-10-21
    自动化测试 单元测试 PHP开发
  • python列表处理效率对比测试
    #!/usr/bin/env import datetime class adair: def test1(n): lst=[] for i in range(n*10000): ...
    99+
    2023-01-31
    效率 对比测试 列表
  • 在 go echo 中测试 http 处理程序
    偷偷努力，悄无声息地变强，然后惊艳所有人！哈哈，小伙伴们又来学习啦~今天我将给大家介绍《在 go echo 中测试 http 处理程序》，这篇文章主要会讲到等等知识点，不知道大家对其都有多少了解，下...
    99+
    2024-04-04
  • 如何在 Golang 中测试错误处理？
    在 go 中测试错误处理的常见方法包括：使用 error.error() 检查错误信息是否为空字符串；使用 testing.t.fatalerror() 和 testing.t.erro...
    99+
    2024-05-14
    golang 错误处理
  • Android测试提升效率批处理脚本
    　　前言： 　　APP测试过程中，经常需要用的一些命令，如adb，每次敲命令，虽可以加深印象，但个人认为那即繁琐又浪费时间。本文贴出一些我使用的批处理，以及一点点小小技巧...
    99+
    2022-06-06
    效率 脚本 批处理 Android
  • Python怎么使用Pandas处理测试数据
    这篇文章主要介绍“Python怎么使用Pandas处理测试数据”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“Python怎么使用Pandas处理测试数据”文章能帮助大家解决问题。Python自动化测...
    99+
    2023-07-05
  • Python使用Pandas处理测试数据的方法
    目录Python自动化测试-使用Pandas来高效处理测试数据一、思考1.Pandas是什么？2.经典面试题二、使用pandas来操作Excel文件1.安装2.按列读取数据3.按行读...
    99+
    2023-02-21
    Python Pandas 处理测试数据 Python Pandas 自动化测试
  • C++信号处理sigaction函数的测试分析
    本篇内容主要讲解“C++信号处理sigaction函数的测试分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“C++信号处理sigaction函数的测试分析”吧!运行结果如图到此，相信大家对“C...
    99+
    2023-06-04
  • Golang 函数测试中的错误处理策略
    go 函数测试中的错误处理策略包括：使用内置 errors 包创建和管理错误。自定义错误类型以提供更具体的错误信息。使用 assert 断言简洁地检查错误条件。使用 fatal 和 sk...
    99+
    2024-04-16
    单元测试 错误处理 golang 标准库
  • golang函数错误处理中的单元测试
    单元测试 go 语言中的函数错误处理方法包括：创建模拟输入：创建受控错误的模拟对象。断言错误消息：匹配返回的错误消息与预期的消息。覆盖错误情况：编写针对所有可能错误情况的测试用例。 G...
    99+
    2024-05-01
    单元测试 错误处理 golang
  • PHP 单元测试与错误处理的实践
    单元测试可通过 phpunit 框架轻松快速地测试代码，错误处理则利用异常捕获和处理运行时错误。异常处理采用 try...catch 语句捕获异常，而错误报告通过 error_repor...
    99+
    2024-05-07
    php 单元测试
  • springboot vue接口测试HutoolUtil TreeUtil处理树形结构
    目录基于springboot+vue的测试平台开发一、引用 HutoolUtil二、建表三、后端接口实现1. Controller 层2. DAO层3. Service 层四、测试一...
    99+
    2024-04-02
  • springboot集成测试容器重启问题的处理
    目录背景测试用例的运行流程结论背景 spring boot test的项目中常用的测试框架， 最近在写集成测试的时候发现一个比较奇怪的问题，当我在运行多个测试用例的时候会偶尔重新启动...
    99+
    2024-04-02
  • Go 函数单元测试的错误处理策略
    在 go 函数单元测试中，错误处理有两种主要策略：1. 将错误表示为 error 类型的具体值，用于断言预期值；2. 使用通道向测试函数传递错误，适用于测试并发代码。实战案例中，使用错误...
    99+
    2024-05-02
    单元测试 错误处理
  • 如何处理Go语言中的并发测试问题
    在Go语言中处理并发测试问题时，可以采取以下几个步骤：1. 使用goroutine和channel：Go语言中可以使用gorouti...
    99+
    2023-10-09
    Go语言
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作