PostgreSQL horizontal scaling using PL / Proxy.

Very hard to start writing an article. Ie very hard to come up with an opening statement. I want to tell everything at once :) But no. We will be consistent.
I'll start with the fact that quite recently passed Highload ++ 2008 on which I was able to visit.
I will say right away - the event was held at the highest level, there were many reports and all were very interesting.
One of the most memorable presentations was Asko Oya's lecture on the infrastructure of database servers on Skype. The lecture was more concerned with the various means by which such server performance is achieved.
According to Asko, the Skype database will withstand even if all the inhabitants of the Earth want to connect to Skype in one moment.

Arriving home really wanted to try it all live. What I will tell you now. Immediately make a reservation - the database structure for the test is taken from the example on the site of the developers themselves and naturally has nothing to do with the actual download.
The article will describe how to distribute the load should be done after it is already hot and the base falls, but this is not quite so. With the help of this article, I just want to prepare beginners and not experienced developers and at the same time get them to think about the fact that the possibility of load distribution between servers must be provided when designing the system. And this will not be considered the very “premature optimization” about which they write so much and which they are so afraid of.

UPD: As habraiser descentspb correctly noted , the article has an annoying error. Due to my carelessness, I thought that the PgBouncer should be installed between the proxy and the client. But, as it turned out, the problem that I solved with the help of PgBouncer will not be solved if you install it that way. Correctly it is necessary to establish a bouncer between the nodes and the proxy. Moreover, this is exactly what is recommended in the official manual on the PL / Proxy website.
In any case, using PgBouncer as indicated in my diagram will also give a performance boost. (Unload proxy).
')

1. Who is to blame?

So, if you are a developer and create something big and high enough, you will sooner or later encounter the fact that the database does not withstand the load. Requests come a lot and the iron is simply not able to cope with them.
Methods for solving this problem have already been discussed more than once; I will only give a list of what seems to me the most effective.

- Optimize the code.
- We increase the server capacity.
- Caching (looking for articles on memcache tags).
- We distribute the load between the servers.

Let's stop on the last point.

2. What to do?

So the code is optimized, the server has no place to get the whole database in the cache and nevertheless it falls off from a single request. It's time to do horizontal scaling.
Oh yeah, I still haven't mentioned that PostgreSQL article. And what are you still using MySQL? Then we go to you :)
In my humble opinion, if the project is really serious, then the base should be a bit more serious than MySQL. All the more so for Postgres there are such wonderful scaling tools. (Maybe for MySQL there? Waiting for the response article :)).

3. And what does it eat with?

PL / Proxy is a language for remotely calling functions on PostgreSQL database servers, as well as for partitioning data.
The scheme of work is shown in the picture. About PgBouncer I will tell below.

Usually your application simply makes a database request. In our case, the application also makes a normal database request. It just calls not pure SQL-code, but a previously written function.
Next, the database determines on which of the nodes the required data is located.
And redirects the request to the desired server.
The request is executed and returned to the main server, after which the data is returned to the application.

Everything seems to be fine, but with a large number of requests, PL / Proxy creates a large
the number of connections to the nodes, and this creates a new process, Postgres (fork), which does not affect performance very well. To solve this problem you need PgBouncer.
PgBouncer is ... mm ... how can I say this so as not to establish ... connections multiplexer. It looks like a normal Postgres process, but inside it manages the request queues, which makes it possible to speed up the server times. Of the thousands of requests received by PgBouncer to the database will reach only a few dozen.
To evaluate the bonus from using this great tool, just look at the schedule for loading the database server on two sites before and after turning on PgBouncer. The picture is taken from the performance of Postgres by Nikolay Samokhvalov.

4. Give me these soft French buns.

4.1. Installing PgBouncer

The installation process is not at all original:
Downloading the package (as of this writing, the latest version was 1.2.3)
pgfoundry.org/frs/?group_id=1000258
Unpack:
#tar -xzvf pgbouncer-1.2.3.tgz
:
#cd pgbouncer-1.2.3
#./configure
#make
#make install

Create a configuration file:
/etc/pgbouncer/pgbouncer.ini

[databases]
testdb = host=localhost port=5432 dbname=testdb

[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = md5
auth_file = users.txt
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
pool_mode = statement # PL/Proxy
admin_users = root

Create a file with authentication.
/etc/pgbouncer/users.txt
"testdb_user" "testdb_user_password"

Run:
/usr/local/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini -u postgres
The -d switch indicates what needs to be started in daemon mode, and the -u switch indicates on whose behalf the pgbouncer process should be run.

For gentoo users, a pleasant surprise in the form of a start script:
/etc/init.d/pgbouncer
#!/sbin/runscript

depend() {
need postgresql
use pgsql
}
start() {
ebegin "Starting Pgbouncer"
start-stop-daemon --start --background --exec /usr/local/bin/pgbouncer --chdir /etc/pgbouncer/ -- -d pgbouncer.ini -u postgres
eend $? "Failed to start Pgbouncer"
}
stop() {
ebegin "Stopping Pgbouncer"
start-stop-daemon --pidfile /var/run/pgbouncer/pgbouncer.pid --stop
eend $? "Failed to stop Pgbouncer"
}


Now, as a DSN in your application, you will need to change only the port to which you connect to the database from 5432 to 6543 and begin to compare server load before and after.

4.2 Installing Pl / Proxy

To conduct this experiment, we need 3 Postgres servers.
One of them, let's call his proxy, will proxify requests for the other two.
Let's call them node1 and node2.
For pl / proxy to work correctly, it is recommended to use a number of nodes equal to powers of two.
I assume that you have already installed Postgres.
Install PL / Proxy on the proxy server.
Downloading the latest version of pl / proxy: pgfoundry.org/frs/?group_id=1000207
As usual:
#. / configure
#make
#make install
This is where Postgres itself needs to be restarted.
And now the fun begins.

For the test, we will create a new proxytest database on each node:

CREATE DATABASE proxytest
WITH OWNER = postgres
ENCODING = 'UTF8' ;


And inside this database we will create Schema with the name plproxy. The official instructions did not have this item, but for some reason all the called functions tried to be called exactly like this: plproxy.functioname ().

CREATE SCHEMA plproxy
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA plproxy TO postgres;
GRANT ALL ON SCHEMA plproxy TO public ;


And add one label to it:

CREATE TABLE plproxy.users
(
user_id bigint NOT NULL DEFAULT nextval( 'plproxy.user_id_seq' ::regclass),
username character varying (255),
email character varying (255),
CONSTRAINT users_pkey PRIMARY KEY (user_id)
)
WITH (OIDS= FALSE );
ALTER TABLE plproxy.users OWNER TO postgres;


Now create a function to add data to these tables:

CREATE OR REPLACE FUNCTION plproxy.insert_user(i_username text, i_emailaddress text)
RETURNS integer AS
$BODY$
INSERT INTO plproxy.users (username, email) VALUES ($1,$2);
SELECT 1;
$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION plproxy.insert_user(text, text) OWNER TO postgres;


With the notes over. Let's start setting up the server.
As with all nodes, there should be a database on the main server (proxy):

CREATE DATABASE proxytest
WITH OWNER = postgres
ENCODING = 'UTF8' ;


And the corresponding schema:

CREATE SCHEMA plproxy
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA plproxy TO postgres;
GRANT ALL ON SCHEMA plproxy TO public ;


Now you need to tell the server that this database is managed by pl / proxy:

CREATE OR REPLACE FUNCTION plproxy.plproxy_call_handler()
RETURNS language_handler AS
'$libdir/plproxy' , 'plproxy_call_handler'
LANGUAGE 'c' VOLATILE
COST 1;
ALTER FUNCTION plproxy.plproxy_call_handler() OWNER TO postgres;
-- language
CREATE LANGUAGE plproxy HANDLER plproxy_call_handler;


Also, in order for the server to know where and which nodes it has, you need to create 3 service functions that pl / proxy will use in its work:

CREATE OR REPLACE FUNCTION plproxy.get_cluster_config( IN cluster_name text, OUT " key " text, OUT val text)
RETURNS SETOF record AS
$BODY$
BEGIN
-- lets use same config for all clusters
key := 'connection_lifetime' ;
val := 30*60; -- 30m
RETURN NEXT ;
RETURN ;
END ;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION plproxy.get_cluster_config(text) OWNER TO postgres;


An important function whose code will need to be corrected. It will need to specify the DSN node:

REATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS
$BODY$
BEGIN
IF cluster_name = 'clustertest' THEN
RETURN NEXT 'dbname=proxytest host=node1 user=postgres' ;
RETURN NEXT 'dbname=proxytest host=node2 user=postgres' ;
RETURN ;
END IF ;
RAISE EXCEPTION 'Unknown cluster' ;
END ;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION plproxy.get_cluster_partitions(text) OWNER TO postgres;


And last:

CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text)
RETURNS integer AS
$BODY$
BEGIN
IF cluster_name = 'clustertest' THEN
RETURN 1;
END IF ;
RAISE EXCEPTION 'Unknown cluster' ;
END ;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION plproxy.get_cluster_version(text) OWNER TO postgres;


Well, actually the most important function that will be called directly in the application:

CREATE OR REPLACE FUNCTION plproxy.insert_user(i_username text, i_emailaddress text)
RETURNS integer AS
$BODY$
CLUSTER 'clustertest' ;
RUN ON hashtext(i_username);
$BODY$
LANGUAGE 'plproxy' VOLATILE
COST 100;
ALTER FUNCTION plproxy.insert_user(text, text) OWNER TO postgres;


Questions on the function code are accepted in the comments, but please note that I am not a Postgres guru, but just a student.

And now we test! :)

We connect to the proxy server on port 6543 (we will immediately work via PgBouncer).
And we enter the data into the database:

SELECT insert_user( 'Sven' , 'sven@somewhere.com' );
SELECT insert_user( 'Marko' , 'marko@somewhere.com' );
SELECT insert_user( 'Steve' , 'steve@somewhere.com' );


Now you can connect to each of the nodes and if you did everything correctly and without errors, then the first two entries will be on node1 node, and the third entry will be on node2 node.

We try to extract data.
To do this, we write a new server function:

CREATE OR REPLACE FUNCTION plproxy.get_user_email(i_username text)
RETURNS SETOF text AS
$BODY$
CLUSTER 'clustertest' ;
RUN ON hashtext(i_username) ;
SELECT email FROM plproxy.users WHERE username = i_username;
$BODY$
LANGUAGE 'plproxy' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION plproxy.get_user_email(text) OWNER TO postgres;



And try to call it:

select plproxy.get_user_email( 'Steve' );

In other words, I did it all.

5.And why are you so poor once so smart?

As you can see on the test example, there is nothing difficult in working with pl / proxy. But, I think everyone who was able to read this line up already understood that in real life, things are not so simple.
Imagine you have 16 nodes. Same it is necessary to synchronize somehow the code of functions. And what if the error creeps in - how to fix it quickly?
This question was also asked at the conference, to which Asko replied that the corresponding funds have already been implemented within Skype itself, but are not yet ready to give them to the opensource community.
The second problem that God forbid touches you when developing such a system is the problem of data redistribution at the moment when we want to add another node to the cluster.
This large-scale operation will have to be planned very carefully, having prepared all the servers in advance, having entered the data and then at one moment replacing the function code get_cluster_partitions.

6. Additional materials

Projects PlProxy and PgBouncer on the Skype developer site.

Asco's presentation on Highload ++
Postgres performance Nikolay Samokhvalov (Postgresmen)

7. Bonus for attentive

Already after I published the article I found one mistake in it and one defect.
I will describe here as it is difficult to edit an already written article.
1) In tables it uses a sequence named user_id_seq. But the SQL code for it is not given anywhere. Accordingly, if someone will just copy-paste the code, nothing will come of it. Correcting:

CREATE SEQUENCE plproxy.user_id_seq
INCREMENT 1
MINVALUE 0
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE plproxy.user_id_seq OWNER TO postgres;



2) During the insertion of data into the database, a sequence is generated for the user_id field. However, these sequences are two. And each works on its own node. Which will inevitably lead to two different users having the same user_id.
Accordingly, the insert_user function must be fixed in such a way that the new user_id would be taken from the sequence hosted on the proxy server and not on the nodes. In this way, duplication in the user_id field can be avoided.

ZY: All SQL code is highlighted in Source Code Highliter