jeudi 11 septembre 2008
pg_filedump sous PostgreSQL 8.1.4
Par david techer, jeudi 11 septembre 2008 à 18:12 :: PostGIS et PostgreSQL
pg_filedump est disponible à http://sources.redhat.com/rhdb/utilities.html. Il est notamment util pour pouvoir analyzer les pages de données (tables, index etc...) en offrant diverses informations sur les diverses pages.
compilation
Pour l'installation, il faut les sources. Comme j'ai PostgreSQL avec les sources. On décompresse celà dans postgresql-8.1.4/contrib.
En fonctions des remarques formulées dans README.pg_filedump, il faut modifier le fichier Makefile. Voici le contenu du mien.
# View README.pg_filedump first
CC=gcc
CFLAGS=-g -O -Wall -Wmissing-prototypes -Wmissing-declarations
INCLUDE=/usr/local/pgsql/include/server
# PGSQL MUST POINT TO pgsql SOURCE DIRECTORY
PGSQL=/home/jdtecher/sources/postgresql-8.1.4
CRC_SRC=${PGSQL}/src/backend/utils/hash
CRC_INCLUDE=${PGSQL}/src
all: pg_filedump
pg_filedump: pg_filedump.o pg_crc.o
${CC} ${CFLAGS} -o pg_filedump pg_filedump.o pg_crc.o
pg_filedump.o: pg_filedump.c
${CC} ${CFLAGS} -I${INCLUDE} pg_filedump.c -c
pg_crc.o: ${CRC_SRC}/pg_crc.c
${CC} ${CFLAGS} -I${CRC_INCLUDE} -I${INCLUDE} ${CRC_SRC}/pg_crc.c -c
clean:
rm -rf *.o pg_filedump
Ensuite
make
Une fois compilée, on peut alors l'utiliser
Utilisation
Sur ma base performance, j'ai la table test dont la structure est la suivante
performance=# \d test
Table « public.test »
Colonne | Type | Modificateurs
---------+---------+---------------
id | integer |
Insérons quelques données au moins 20 tuples
performance=# insert into test (select generate_series(1,20));
On attend quelques minutes le temps que tout soit écrit dans les fichiers de données. Pour continuer, il me faut l'oid de la table et celui de la table. On obtient celà en faisant
performance=# select a.oid as "oid de la base",b.relfilenode as "oid de la table" from pg_database a,pg_class b
where (a.datname,b.relname)=('performance','test');
oid de la base | oid de la table
----------------+-----------------
12745801 | 14111977
(1 ligne)
Et pour localiser la base
performance=# show data_directory; data_directory --------------------- /home/postgres/data
Ok. Maintenant on voit pouvoir l'utiliser. Ce qui donne donc
[root@jdtecher pg_filedump-8.1]# ./pg_filedump -x -S 8192 /home/postgres/data/base/12745801/14111977 ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1 * * File: /home/postgres/data/base/12745801/14111977 * Options used: -x -S 8192 * * Dump created on: Thu Sep 11 18:08:37 2008 ******************************************************************* Block 0 ********************************************************On fait maintenant une nouvelle insertion----- Block Offset: 0x00000000 Offsets: Lower 100 (0x0064) Block: Size 8192 Version 3 Upper 7392 (0x1ce0) LSN: logid 7 recoff 0x2f0e63b0 Special 8192 (0x2000) Items: 20 Free Space: 7292 Length (including item array): 104 ------ Item 1 -- Length: 36 Offset: 8152 (0x1fd8) Flags: USED Item 2 -- Length: 36 Offset: 8112 (0x1fb0) Flags: USED Item 3 -- Length: 36 Offset: 8072 (0x1f88) Flags: USED Item 4 -- Length: 36 Offset: 8032 (0x1f60) Flags: USED Item 5 -- Length: 36 Offset: 7992 (0x1f38) Flags: USED Item 6 -- Length: 36 Offset: 7952 (0x1f10) Flags: USED Item 7 -- Length: 36 Offset: 7912 (0x1ee8) Flags: USED Item 8 -- Length: 36 Offset: 7872 (0x1ec0) Flags: USED Item 9 -- Length: 36 Offset: 7832 (0x1e98) Flags: USED Item 10 -- Length: 36 Offset: 7792 (0x1e70) Flags: USED Item 11 -- Length: 36 Offset: 7752 (0x1e48) Flags: USED Item 12 -- Length: 36 Offset: 7712 (0x1e20) Flags: USED Item 13 -- Length: 36 Offset: 7672 (0x1df8) Flags: USED Item 14 -- Length: 36 Offset: 7632 (0x1dd0) Flags: USED Item 15 -- Length: 36 Offset: 7592 (0x1da8) Flags: USED Item 16 -- Length: 36 Offset: 7552 (0x1d80) Flags: USED Item 17 -- Length: 36 Offset: 7512 (0x1d58) Flags: USED Item 18 -- Length: 36 Offset: 7472 (0x1d30) Flags: USED Item 19 -- Length: 36 Offset: 7432 (0x1d08) Flags: USED Item 20 -- Length: 36 Offset: 7392 (0x1ce0) Flags: USED *** End of File Encountered. Last Block Read: 0 ***
performance=# insert into test (select generate_series(20,256));Ce qui va donner
root@jdtecher pg_filedump-8.1]# ./pg_filedump /home/postgres/data/base/12745801/14111977|grep -v Flag ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1 * * File: /home/postgres/data/base/12745801/14111977 * Options used: None * * Dump created on: Thu Sep 11 18:21:01 2008 ******************************************************************* Block 0 ********************************************************Un avant-dernier insert pour la route----- Block Offset: 0x00000000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0e9150 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 1 ******************************************************** ----- Block Offset: 0x00002000 Offsets: Lower 308 (0x0134) Block: Size 8192 Version 3 Upper 5312 (0x14c0) LSN: logid 7 recoff 0x2f0ea368 Special 8192 (0x2000) Items: 72 Free Space: 5004 Length (including item array): 312 ------ *** End of File Encountered. Last Block Read: 1 ***
insert into test (select generate_series(257,395));Ce qui donne donc
[root@jdtecher pg_filedump-8.1]# ./pg_filedump /home/postgres/data/base/12745801/14111977|grep -v Flag ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1 * * File: /home/postgres/data/base/12745801/14111977 * Options used: None * * Dump created on: Thu Sep 11 18:26:32 2008 ******************************************************************* Block 0 ********************************************************Pour compléter le dernier bloc n°2,----- Block Offset: 0x00000000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0e9150 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 1 ******************************************************** ----- Block Offset: 0x00002000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0ecce8 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 2 ******************************************************** ----- Block Offset: 0x00004000 Offsets: Lower 124 (0x007c) Block: Size 8192 Version 3 Upper 7152 (0x1bf0) LSN: logid 7 recoff 0x2f0ed368 Special 8192 (0x2000) Items: 26 Free Space: 7028 Length (including item array): 128 ------ *** End of File Encountered. Last Block Read: 2 ***
performance=# insert into test (select generate_series(396,554))car 554=396+158,. On a alors
[root@jdtecher pg_filedump-8.1]# ./pg_filedump /home/postgres/data/base/12745801/14111977|grep -v Flag ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1 * * File: /home/postgres/data/base/12745801/14111977 * Options used: None * * Dump created on: Thu Sep 11 18:30:10 2008 ******************************************************************* Block 0 ********************************************************Notre page est donc remplie!----- Block Offset: 0x00000000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0e9150 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 1 ******************************************************** ----- Block Offset: 0x00002000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0ecce8 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 2 ******************************************************** ----- Block Offset: 0x00004000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0f00a8 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ *** End of File Encountered. Last Block Read: 2 ***
On a pour le moment procéder qu'à des insertions. Et si on faisait des DELETE et UPDATE!!!
Comme 554/2=277, on va donc faireperformance=# delete from test where id<=277; DELETE 278 performance=# update test set id=-1 where id>=277; UPDATE 277Une page prend donc 185 items. Soit pour 277 tuples, il faut une page et demi. On va donc se retrouver avec 5 pages. Ce que confirme al commande suivante
[root@jdtecher pg_filedump-8.1]# ./pg_filedump /home/postgres/data/base/12745801/14111977|grep -v Flag ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1 * * File: /home/postgres/data/base/12745801/14111977 * Options used: None * * Dump created on: Thu Sep 11 18:36:45 2008 ******************************************************************* Block 0 ********************************************************----- Block Offset: 0x00000000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0f49b8 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 1 ******************************************************** ----- Block Offset: 0x00002000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0f9840 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 2 ******************************************************** ----- Block Offset: 0x00004000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0fec80 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 3 ******************************************************** ----- Block Offset: 0x00006000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f0fd288 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 4 ******************************************************** ----- Block Offset: 0x00008000 Offsets: Lower 388 (0x0184) Block: Size 8192 Version 3 Upper 4512 (0x11a0) LSN: logid 7 recoff 0x2f0fec80 Special 8192 (0x2000) Items: 92 Free Space: 4124 Length (including item array): 392 ------ *** End of File Encountered. Last Block Read: 4 ***
Les blocs 3 et 4 contiennent les UPDATE effectuées. Ce que confirme la requête suivante
performance=# select id,xmin,cmin,ctid from test where id=-1; id | xmin | cmin | ctid ----+----------+------+--------- -1 | 10405570 | 0 | (3,1) -1 | 10405570 | 0 | (3,2) -1 | 10405570 | 0 | (3,3) -1 | 10405570 | 0 | (3,4) -1 | 10405570 | 0 | (3,5) -1 | 10405570 | 0 | (3,6) -1 | 10405570 | 0 | (3,7) -1 | 10405570 | 0 | (3,8) ... ...... ..... ... ...... ..... ... ...... ..... -1 | 10405570 | 0 | (3,183) -1 | 10405570 | 0 | (3,184) -1 | 10405570 | 0 | (3,185) -1 | 10405570 | 0 | (4,1) -1 | 10405570 | 0 | (4,2) -1 | 10405570 | 0 | (4,3) ... ...... ..... ... ...... ..... ... ...... ..... -1 | 10405570 | 0 | (4,89) -1 | 10405570 | 0 | (4,90) -1 | 10405570 | 0 | (4,91) -1 | 10405570 | 0 | (4,92) (277 lignes)
La commande UPDATE d'identifiant de transaction n°10405570 a donc agit sur les blocs 3 et 4 (cf champs ctid).
On a donc fait fait 555 insertions, 277 deletes et 277 updates. Ce que confirme la requête suivante
performance=# select * from pg_stat_user_tables where relname='test'; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del ----------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+----------- 14111979 | public | test | 3 | 1109 | | | 555 | 277 | 278 (1 ligne)On fait un petit vacuum pour la route
performance=# VACUUM ANALYZE verbose test; INFO: vacuuming "public.test" INFO: "test": removed 555 row versions in 3 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": found 555 removable, 277 nonremovable row versions in 5 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.test" INFO: "test": scanned 5 of 5 pages, containing 277 live rows and 0 dead rows; 277 rows in sample, 277 estimated total rows VACUUMEt un full donnera
performance=# VACUUM full ANALYZE verbose test; INFO: vacuuming "public.test" INFO: "test": found 0 removable, 277 nonremovable row versions in 5 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 36 to 36 bytes long. There were 555 unused item pointers. Total free space (including removable row versions) is 26452 bytes. 3 pages are or will become empty, including 0 at the end of the table. 4 pages containing 26420 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": moved 277 row versions, truncated 5 to 2 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.test" INFO: "test": scanned 2 of 2 pages, containing 277 live rows and 0 dead rows; 277 rows in sample, 277 estimated total rows VACUUMEn réutilsiant pg_filedump, on doit avoir récupérer de la place.
[root@jdtecher pg_filedump-8.1]# ./pg_filedump /home/postgres/data/base/12745801/14111979|grep -v Flag ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1 * * File: /home/postgres/data/base/12745801/14111979 * Options used: None * * Dump created on: Thu Sep 11 19:18:05 2008 ******************************************************************* Block 0 ********************************************************C'est exactement ce qu'on voulait.----- Block Offset: 0x00000000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 792 (0x0318) LSN: logid 7 recoff 0x2f12b168 Special 8192 (0x2000) Items: 185 Free Space: 32 Length (including item array): 764 ------ Block 1 ******************************************************** ----- Block Offset: 0x00002000 Offsets: Lower 760 (0x02f8) Block: Size 8192 Version 3 Upper 4512 (0x11a0) LSN: logid 7 recoff 0x2f12cfe8 Special 8192 (0x2000) Items: 185 Free Space: 3752 Length (including item array): 764 ------ *** End of File Encountered. Last Block Read: 1 ***
...Lo band' moun' en france nana 2 3 z'affair zot y gagne pas comprend. Zot' la pa véki sak mon papa ek mon momon lé véki qaund zot lé té jeune....
)..;avec la gros doigt du collègue dessus