Download - PostgreSQL 8.3 Update
![Page 1: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/1.jpg)
1
PostgreSQL 8.3 Update8.3
PostgreSQL / NTT OSS
2007.6.23
2007.DB
http://www.ospn.jp/osc2007.db/
![Page 2: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/2.jpg)
2
8.1 8.3
8.1
8.2
8.3XML
GIN
HOT
fillfactor
autovacuum
autovacuum
![Page 3: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/3.jpg)
3
8.1 8.2
8.1
8.2
8.3XML
GIN
HOT
fillfactor
autovacuum
autovacuum
![Page 4: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/4.jpg)
4
CPU
0000
1111
2222
3333
4444
5555
1111 2222 4444 8888 12121212 16161616 20202020 24242424 28282828 32323232
8.08.08.08.0
8.18.18.18.1
8.28.28.28.2
8.2
8CPU
8.1
4CPU
8.0
2CPU
NUMA
Scaling PostgreSQL on SMP Architectures
Doug Tolbert (Unisys), PGCon 2007, Ottawa, 2007-05-24http://www.pgcon.org/2007/schedule/events/16.en.html
core
TPC-C(OLTP) CPU
SMP(8CPU )
バージョンごとの、CPU1個に対する相対性能
![Page 5: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/5.jpg)
5
• tsearch2
: SELECT isbn,title FROM booksWHERE fts @@ to_tsquery(‘word1 & word2’);
– GiST, GIN: 2
– ,
•
306MB532s3344ms39msGIN
×2.10×3.02×11.94×0.35
146MB176s280ms112msGiST
Full-Text Search in PostgreSQL
Oleg Bartunov PGCon 2007, Ottawa, 2007/5/23http://www.pgcon.org/2007/schedule/events/13.en.html
• → GiST
• → GIN
tsearch2 GiST GIN
![Page 6: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/6.jpg)
6
VACUUM
• UPDATE
–
–
VACUUM
![Page 7: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/7.jpg)
7
PostgreSQL 8.3
1.– HOT OLTP
2.–
3.– WAL
4. VACUUM– VACUUM autovacuum
![Page 8: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/8.jpg)
8
(1) 8.3
• HOT
–
– pgbench40%
•–
– VACUUM
• VACUUM
• FillFactor
–
– 100%( )90~95%
• ALTER TABLE nameSET (fillfactor=95);
pgbench -s400 (5GB)NTT OSS Center
Fill Factor TPS
100
120
140
160
180
200
220
240
260
280
70 75 80 85 90 95 100Fill Factor (%)
TPS
HOT
HOT
FillFactor
40%UP!
![Page 9: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/9.jpg)
9
HOT (1)
• 8.2 UPDATE
D
C
B
A
8→7002
12004
20003
10001
ID…
![Page 10: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/10.jpg)
10
HOT (2)
• 8.2 UPDATE
12D004
B
C
B
A
8002
7002
20003
10001
ID
UPDATE
××××3(I/O)
××××2(CPU)
![Page 11: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/11.jpg)
11
HOT (3)
• 8.3 HOT UPDATE
7B002
12D004
C
B
A
8002
20003
10001
ID
UPDATE
1→
××××1(I/O)
××××1(CPU)
HOT
1/2
1/3
Heap
Only
Tuple
![Page 12: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/12.jpg)
12
HOT (4)
• 8.3 HOT UPDATE
7B002
6B002
12D004
C
A
20003
10001
ID
UPDATE
HOT
VACUUM
××××1(I/O)
××××1(CPU)
HOT
VACUUM
![Page 13: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/13.jpg)
13
HOT
• UPDATE
– DELETE+INSERT
•
–
–
•
– FillFactor
– VACUUM
VACUUM
![Page 14: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/14.jpg)
14
PostgreSQL 8.3
1.– HOT OLTP
2.–
3.– WAL
4. VACUUM– VACUUM autovacuum
![Page 15: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/15.jpg)
15
(2) 8.3
• (LDC)
EnterpriseDB Performance Testing http://community.enterprisedb.com/ldc/
![Page 16: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/16.jpg)
16
•
– (write)
– (fsync)
8.2
8.3
PostgreSQL
I/O
I/O
![Page 17: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/17.jpg)
17
PostgreSQL 8.3
1.– HOT OLTP
2.–
3.– WAL
4. VACUUM– VACUUM autovacuum
![Page 18: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/18.jpg)
18
(3) 8.3
•– WAL I/O 1/2
• ( )
–• InfoFrame DB Maintenance (NEC)
• pg_bulkload ( , pgFoundry)
BEGIN;
TRUNCATE t;
COPY t FROM …;
COMMIT;0 20 40 60 80
pgbench -i -s30 ( COPY )
35%
NTT OSS Center
8.3
![Page 19: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/19.jpg)
19
(4) 8.3 VACUUM
• autovacuum VACUUM
– (autovacuum_max_workers)
8.2 8.31
→ VACUUM
A B
VACUUM
![Page 20: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/20.jpg)
20
8.1
8.2
8.3XML
GIN
HOT
fillfactor
autovacuum
autovacuum
![Page 21: PostgreSQL 8.3 Update](https://reader031.vdocuments.us/reader031/viewer/2022020206/54590118af795953128b4bdc/html5/thumbnails/21.jpg)
21
PostgreSQL 8.3
–
• HOT OLTP
–
• VACUUM, Background Writer ( )
–
• SQL/XML, , , etc.