PostgreSQL Plus - Fujitsu

decubitisannouncerΔιαχείριση Δεδομένων

27 Νοε 2012 (πριν από 4 χρόνια και 4 μήνες)

310 εμφανίσεις

1
April 2005
FUJITSU Ltd.
PostgreSQL Plus
腠Approach to enhance the functionality for enterprises 腠
2
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
Enhanced functionality for enterprise databases
Vacuum less operation

Want a nicer way to recycle the disk spaces and circulate the XID
Archive log as a standard feature

Want to have a recovery function when disks break down, as standard function
Max utilization of available CPU resources

Can’t we raise the processing performance of the same machine?
Handling of a large amount of data

Get rid of the disk partition constraints

Speed up the data loading
Enhanced PostgreSQL

Want to archive the function enhancing of the OSS
3
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
Approach to the enhancement
Support for OSS Community
V7.3 腆Support in improving JDBC driver
V8.0 腆Table Space
Nested Transaction (Savepoint)
Java stored procedure
Commercial Version of PostgreSQL Plus
High reliability by use of archive log operation
Continuous operation by Vacuum less feature
Improve multiprocessing performance by 100% of
CPU usage
4
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
Achievement Method 1
Data
PostgreSQL

API

C,C++,Java,PHP,Perl,tcl

PostgreSQL

API

C,C++,Java,PHP,Perl,tcl

PostgreSQL’s API

C,C++,Java,PHP,Perl,Tcl, etc

PostgreSQL Storage Manager

Control Read/Write data
and logs to the disk
PostgreSQL’sSQL processor

Interpret and execute the query
New Storage Manager

“ESM for PostgreSQL”

Speed up the I/O processing which writes data
and logs to the disk
膜Vacuum less
膜All logs are stored on the disk besides data
Replace
Data, logs
DiskDisk
Logs
Modified the part of the original
PostgreSQL’ssource codes
to plug the new storage
manager in
PostgreSQL Plus
Upper layer is the same. Totally
interchangeable with PostgreSQL
5
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
Achievement Method 2
Introducing the concept of DB space
Better disk partitioning even without RAID
Easy setup and operation
Setup with GUI, Easiness is standardized
Reducing the difference between the original
and the Plus
Commands such as VACUUM are still there but not
needed.
Features such as DB space腩Table Spaceare handled
by GUI
6
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
Achievement Method 3
Vacuum less area management

Unnecessary area is recycled when
transaction ends

64bits transaction ID
1Million transaction / second for approx.
58million years
7
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
Achievement Method 4

Improved data stability using archive log
Write archive log during transaction
Restore the latest data by rolling forward
the archive log
8
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
Steady loading performance
0
50
100
150
200
[Measurement Environment]
CPU腆2GHz2
Memory腆2GB (use 3MB)
Input Data腆binary format
PowerGres Plus’s
COPY command
PostgreSQL’s
COPY command
Approx 24times difference
Performance of data loading of Plus is ten times or more than that of the original
Minute
The performance
difference is remarkable
as the number of indexes
is increased
0
1
2
Number of Indexes
9
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
Concurrent Execution Performance (Linux)
164.415373.9366341.9060349.3956138.7385873.93663
184.0754112.19354.591457.4684952.81783112.193
255.1655163.022281.6886680.2372372.84768163.0222
374.6646220.9041189.6291162.965596.13934220.9041
442.4270.4971232.3411217.7248131.0904240.4971
513301.4577293.3702247.9234152.0523231.4577
555322.533306.9374275.6456169.5784232.533
0
100
200
300
400
500
600
700
800
900
1248163264
Number of Concurrency
Transactionʗsecʢtpsʣ
PowerGres Plus
腩Linux
PostgreSQL
腩Linux
4 processors
[Measurement Environment]
CPU腆Pentium Xeon 1.6GHz
腩MAX num of CPU: 4
Memory腆6GB
Disk Device腆5 x SCSI disks
2 processors
1 processors
4 processors
2 processors
1 processors

Use “pgbench”which is a benchmarking application of the original PostgreSQL
10
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
High-performance technology
11
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
SMA(Scalable Multiunit Architecture)
Staging
Controller
Staging
Staging
Controller
Controller
Appl
Appl
. Unit
. Unit
Appl
Appl
. Unit
. Unit
Appl
Appl
. Unit
. Unit
I/O Unit
I/O Unit
I/O Unit
I/O Unit
I/O Unit
I/O Unit
Log Unit
Log Unit
Log Unit
Log Unit
PostgreSQL
Server process
organizes units
effectively by staging
architecture
Dictionary
Dictionary


Unit
applications
•divides process into a unit in DBMS
•controls CPU as a pipeline using the original dispatching architecture
(Staging Controller)
Database
dictionary
data
log data
12
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
➥makes the best use of the multi threading feature
➥reduces OS dispatching cost for switching running units


Staging Controller
Thread腩=CPU amount
RUNNING
WATING
UNEXECUT
ABLE
Process
Execution job flows
“1 RUNNING in 1 thread”
E
E
E
E
R
R
R
R
W
W
W
W
For one client
application
PostgreSQL Server Process
Staging Control
13
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
腅Why Dynamic Queue Control ?
Distributing processes into separate threads when accepting transactions is
not very effective. This is because uneven processing time makesthe load
on threads unbalanced.


Staging Controller
PostgreSQL Server Process
Dynamic Queue Control
E
E
R
W
W
Thread腩=CPU amount
Process
W
When a processing
ends in a thread and
there is another queue
waiting for a thread,
that queue is moved to
execute in the free
thread without delay.
E
R
R
For one client
application
Dynamic Queue Control
14
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005
is to synchronize Log deletion and Write-Back data caching timing for
increasing the transaction response.
update1
Log
update3
Log
update2
Log
update4
Log
update5
Log
update6
Log
update1
update3
update6
update4
update2
update5
update1
update2
update3
Write-Back
Data Cache
(Public Buffer)
Temporary Log File
assured Recovery term
Past Problems
腅New update log won’t be
made when an old log is full.
This causes decreasing of on-
line response.
腅Large amount of write-back
data caching at once causes
decreasing of on-line
response.
Past Problems
腅New update log won’t be
made when an old log is full.
This causes decreasing of on-
line response.
腅Large amount of write-back
data caching at once causes
decreasing of on-line
response.
We need to write the
caching data back to the
disk and synchronize
the data.
Stealth Synch technology
Optimizing update timing
and amount of deleted
update logs,
keeps the balance of update
log deletion and write-back
data caching
Stealth Synch technology
Optimizing update timing
and amount of deleted
update logs,
keeps the balance of update
log deletion and write-back
data caching
Stealth Synch technology
15
All Rights Reserved, Copyright ©FUJITSU LIMITED 2005