The paper describes the architecture and the design of PargreSQL parallel database management system (DBMS) for distributed memory multiprocessors. PargreSQL is based upon PostgreSQL open-source DBMS and exploits partitioned parallelism.
Currently open-source PostgreSQL DBMS [
One of the directions mentioned above is to adapt PostgreSQL for parallel query processing. In this paper we describe the architecture and design of PargreSQL parallel DBMS for analytical data processing on distributed multiprocessors. PargreSQL represents PostgreSQL with embedded partitioned parallelism.
The paper is organized as follows. Section 2 briefly discusses related work. Section 3 gives a description of the PostgreSQL DBMS architecture. Section 4 introduces design principles and architecture of PargreSQL DBMS. The results of experiments on the current partial implementation are shown in section 5. Section 6 contains concluding remarks and directions for future work.
The research on extension and improvement of PostgreSQL DBMS includes the following.
In [
There are papers investigating adoption of
PostgreSQL for parallel query processing as well. In [
This paper is supported by the Russian Foundation for Basic
Research (grant No. 09-07-00241-a).
to support distributed query processing. Several
limitations in PostgreSQL’s query engine and corresponding
query execution techniques to improve performance of
distributed query processing are presented. ParGRES [
In [
Our contribution is embedding partitioned
parallelism [
PostgreSQL is based on the client-server model. A session involves three processes into interaction: a frontend, a backend and a daemon (see fig. 1).
connects 1
1 -executor 1
The daemon handles incoming connections from frontends and creates a backend for each one. Each backend executes queries received from the related frontend. The activity diagram of a PostgreSQL session is shown in fig. 2.
There are following steps of query processing in PostgreSQL: parse, rewrite, plan/optimize, and execute.
Respective PostgreSQL subsystems are depicted in fig. 3. Parser checks the syntax of the query string and builds a parse tree. Rewriter processes the tree according accept fork connect send query recv result [more queries] else exec query send result
libpq to the rules specified by the user (e.g. view definitions). Planner creates an optimal execution plan for this query tree. Executor takes the execution plan and processes it recursively from the root. Storage provides functions to store and retrieve tuples and metadata. libpq implements frontend-backend interaction protocol and consists of two parts: the frontend (libpq-fe) and the backend (libpq-be). The former is deployed on the client side and serves as an API for the end-user application. The latter is deployed on the server side and serves as an API for libpq-fe, as shown in fig. 4. 4
PargreSQL utilizes the idea of partitioned parallelism [
The way the partitioning is done is defined by a fragmentation function, which for each tuple of the relation S0 P9 S9 ⋮ g n i g re ⋮ M Partitioning function calculates the number of the processor node which this tuple should be placed at. A query is executed in parallel on all processor nodes as a set of parallel agents. Each agent processes its own fragment and generates a partial query result. The partial results are merged into the resulting relation.
The architecture of PargreSQL, in contrast with PostgreSQL, assumes that a client connects to two or more servers (see fig. 6).
connects n k par_Frontend
The interaction sequence is shown in fig. 7. As opposed to PostgreSQL there are many daemons running in PargreSQL. A frontend connects to each of them, sends the same query to many backends, and receives the result relation.
2.1: create() d1 : Daemon
b1 : par_Backend 3.1: sendquery() 5.1: sendresult() 4.n: exchange() 1.1: connect() 1.n: connect() f : par_Frontend 3.n: sendquery() 2.n: create() dn : Daemon bn : par_Backend 5.n: sendresult() 4.1: exchange()
Parallel query processing in PargreSQL is done in more steps: parse, rewrite, plan/optimize, parallelize, execute, and balance. During the query execution each agent processes its own part of the relation independently so, to obtain the correct result, transfers of tuples are required. Parallelization stages creation of a parallel plan by inserting special exchange operators into the corresponding places of the plan. Balance provides loadbalancing of the server nodes.
PargreSQL subsystems are depicted in fig. 8. PostgreSQL is one of them. PargreSQL development involves changes in Storage, Executor and Planner subsystems of PostgreSQL.
The changes in the old code are needed to integrate it with the new subsystems. par Storage is responsible for storing partitioning metadata of relations. par Exchange encapsulates the exchange operator implementation. Exchange operator is meant to compute the distribution function for each tuple of the relation, send “alien” tuples to the other nodes, and receive “own” tuples in response.
There are however some new subsystems which do not require any changes in the old code: par libpq-fe and par Compat. par libpq-fe is a wrapper around libpq-fe, it is needed to propagate queries from an application to many servers. par Compat makes this propagation transparent to the application. app
MPS subsystem (Message Passing System) is used by Scatter and Gather to transmit tuples. Its interface is like MPI reduced to three methods: ISend, IRecv, and Test. They are actually implemented on top of MPI.
Figs. 14, 15, 16, and 17 show algorithms for next()
method of four exchange subnodes.
Exchange operator [
Split is meant to calculate fragmentation function for each tuple and choose whether to keep it on the processor node or send it to other processor node.
even := not even [even]
[odd] right.next left.next tuple left.next right.next tuple
At the moment we have implemented par libpq and par Exchange subsystems of PargreSQL. The implementation has been tested on the following query: select * from tab where tab.col % 10000 = 0
The query has been run against table tab consisting of 108 tuples. The speedup relative to PostgreSQL is shown in fig. 18.
6 5 p4 u d e e 3 p S 2 1
Linear
Actual 2 3 4 5
6
Nodes