Post on 20-Jan-2015
description
JPUG 2010 1
PostgreSQL
9.0
NTT
2010-06-19
2JPUG 2010
: PostgreSQL 9.0
:
: PostgreSQL
PGCon 2010
3JPUG 2010
PostgreSQL 9.0
1977
20032004
2005
2006
20072008
2009
2000
19961986
PostgreSQL
6.0
PostgreSQL
6.0
7.37.3
7.47.4
IngressIngress
POSTGRESPOSTGRES
•Windows•
• (PITR)
•
•Windows•
• (PITR)
•
8.0
••2
•
••2
•
8.1
•CPU
••GIN:
•CPU
••GIN:
8.2
•HOT:
•VACUUM
•
•HOT:
•VACUUM
•
8.3
2010
Window
•VACUUM
• DBMS
Window
•VACUUM
• DBMS
8.
•
•• /
•
•
•• /
•
9.0 (2010/8 )
9.0 5
2 / 3
9.0.0
4JPUG 2010
PostgreSQL 9.0
1.
2. ( )
3. VACUUM FULL
/
LISTEN/NOTIFY /
Windows 64-bit
( URL )
http://developer.postgresql.org/pgdocs/postgres/release-9-0.html
http://lets.postgresql.jp/documents/technical/9.0/
1 2 3
5JPUG 2010
1.
9.0 2
( )
VACUUM
/
PostgreSQL
•
•==
6JPUG 2010
vs.
WAL pg_standbyarchive_command
( v8.4)
READ WRITE READ WRITE
(v9.0)
READ WRITE READ WRITE
wal receiverwal sender WAL
8.4
9.0 WAL
( )
WAL
( 1 )
9.09.0
7JPUG 2010
vs.
○
( )
○
DB( )DB
PK , DDL ※SQL
31010☆
○
2.32.09.0
pgpool-IISlony-I
※PK= , DDL=☆
8JPUG 2010
(UNIQUE)
“ ” “ ”
(EXCLUDE)
“ ” “ ”
GPS
2.
9JPUG 2010
text gist contrib/btree_gist
period ( 9.1 )
CREATE TABLE placement (
object text,
location box, --
EXCLUDE USING gist (location WITH &&)
);
CREATE TABLE reservation (
room text,
during period, -- { , }
EXCLUDE USING gist (room WITH =, during WITH &&)
);
&& “ ”
gist
10JPUG 2010
3. VACUUM FULL
VACUUM FULL
8.4 “ ”
9.0 “ ”
VACUUM ( )
FULL
FULL+TRUNCATE
VACUUM FULL UPVACUUM FULL UP
11JPUG 2010
PostgreSQL
,
,
,
,
9.0
8.4
-REINDEX
+CLUSTER
VACUUM
FULL
VACUUM
VACUUM
VACUUM FULL (8.4)
REINDEX
VACUUM FULL (9.0) CLUSTER REINDEX
REINDEX
JPUG 2010 12
13JPUG 2010
8.1 8.3
CPU
8.2
8.3
2CPU
8CPU
16CPU
8.1
?
8.0
14JPUG 2010
- 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
Scaling PostgreSQL on SMP Architectures Doug Tolbert (Unisys), PGCon 2007, Ottawa, 2007-05-24http://www.pgcon.org/2007/schedule/events/16.en.html
core
8 16core
CPU
バージョンごとの、ＣＰＵ１個に対する相対性能
15JPUG 2010
8.3
HOT 2
HOT8.2
FILLFACTOR
8.3 8.3
16JPUG 2010
– HOT (Heap-Only Tuples)
HOT
pgbench40%
VACUUM
VACUUM
Fill Factor
100%( )90~95%
ALTER TABLE tablenameSET (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
Fill Factor
40%UP!
17JPUG 2010
-
Checkpoint
Checkpoint Next Checkpoint
Time [sec]
Thro
ughput
[TP
M]
8.2
8.3
18JPUG 2010
SQL /
Bitmap Scanmin/max
Window
IN, EXISTS
ORDER BY LIMIT
“ ” Seq Scan
8.2
8.3
8.1
8.4
9.0
O/R ※
※ ORM SQL JOIN
19JPUG 2010
8.2
8.3
VACUUM
VACUUM
8.2 8.3
8.1
autovacuum
HOT
autovacuum
8.49.0
VACUUM
FULL
XID
8.0
VACUUM
7.4
REINDEX
XIDVACUUM
20JPUG 2010
PostgreSQL is NoSQL!
Not Only SQL : SQL( )
SQL
8.3
9.1
8.4
8.2
6.0
(GIN)
FDW
XML
(GiST)
SQL/MED
JSON
( / )
9.0
21JPUG 2010
VACUUM
: 8.3
JPUG 2010 22
PostgreSQL
23JPUG 2010
PGCon 2010
2010 5 20 21 @
150 ( )
35
9.0
9.1
24JPUG 2010
PGCon2010
:
Caixa Economica Federal ( )
22,000 ATM, 7 /
Enova Financial ( )
OLTP 1.3TB, 4000 / , 24
MySQL PostgreSQL
9.0
PgMQ :
Eventually Consistent
Postgres-XC
ACID
+
25JPUG 2010
: 9.0 9.1
PostgreSQL 9.0α
( : )
PostgreSQL 9.1( , )
MERGE : UPDATE, INSERTREPLACE, UPSERT SQL “MERGE”
Predicate Lock
SE-PgSQL : ,
SQL/MED : , DB
, JSON
PGCon 2010 http://wiki.postgresql.org/wiki/PgCon_2010_Developer_Meeting
??
26JPUG 2010
: PostgreSQL
“ ”
1
RDBMS , DBMS MySQL
SQL
27JPUG 2010
PostgreSQL “9.0”
“ ”
PostgreSQL 9.0 9.1
“ ”