系统管理 维护监控 简单生活
2009年九月
PostgreSQL partitioning with plproxy (part II)
九 3rd
声明: 此文我在学习找到的并转载过来,原文需要翻墙 原文这里 this article is not the original,i just quote it from http://kaiv.wordpress.com and source link is here
In the last post i described how you can setup plproxy and create a basic horizontally partitioned cluster. Now we will take a look at another real life usage: building a read-only cluster for your database
Distributing read-only load
The simplest real world usage for plproxy would be it’s use for redirecting read-only queries to read-only replicas of master database. The replicated databases can be filled with data via Londiste that is part of the SkyTools package, setup tutorial can be found here or with Slony which is a more heavyweight solution but from my own experience also harder to setup and maintain though definitely at the time being better documented.
A typical read-only cluster could look like on the following schema. The databases with the letter (P) on them are connection poolers. We ourself use PgBouncer but pgpool is also a choice.
The poolers are needed to minimize the number of open connections to a database also execution plans are cached on a connection basis. Of course everything will work fine also without the poolers. Dashed bold arrows represent replicas.
In this setup the plproxy functions determine the database to which the query is redirected. Read&write queries go to master database and read-only queries are distributed based on the algorithm you define to read-only replicas.
Setting up replication itself is relatively easy once you have passed the painful skytools installation process.
First let us create both replicas from write database toward ro1 & ro2. ro1 configuration file looks like this:
replica1.ini
[londiste] job_name = londiste_master_to_r1 provider_db = dbname=write subscriber_db = dbname=ro1 # it will be used as sql ident so no dots/spaces pgq_queue_name = londiste.write pidfile = %(job_name)s.pid logfile = %(job_name)s.log use_skylog = 0
replica2.ini is basically the same only job name and database name need to be changed. Now let’s install Londiste on provider (write) and subscribers (ro1,ro2) and start the replication daemons:
mbpro:~/temp kristokaiv$ londiste.py replica1.ini provider install mbpro:~/temp kristokaiv$ londiste.py replica1.ini subscriber install mbpro:~/temp kristokaiv$ londiste.py replica2.ini subscriber install mbpro:~/temp kristokaiv$ londiste.py replica1.ini replay -d mbpro:~/temp kristokaiv$ londiste.py replica2.ini replay -d
The next thing you need to do is to setup the ticker process on the database where write is performed. The ticker creates sync events so running it with shorter intervals will reduce latency. My configuration file looks like this:
ticker_write.ini
[pgqadm] job_name = ticker_write db = dbname=write # how often to run maintenance [minutes] maint_delay_min = 1 # how often to check for activity [secs] loop_delay = 0.1 logfile = %(job_name)s.log pidfile = %(job_name)s.pid use_skylog = 0
To start the ticker as a daemon just run:
mbpro:~/temp kristokaiv$ pgqadm.py ticker_write.ini ticker -d
Lets create a simple table that we will replicate from master to read-only’s
mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key, password text);" write mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key, password text);" ro1 mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key, password text);" ro2
And add it to replication
mbpro:~/temp kristokaiv$ londiste.py replica1.ini provider add users mbpro:~/temp kristokaiv$ londiste.py replica1.ini subscriber add users mbpro:~/temp kristokaiv$ londiste.py replica2.ini subscriber add users
After some time the tables should be up to date. Insert a new record in the write database and check if it’s delivered to both read-only db’s.
The functions to insert and select from users table:
CREATE OR REPLACE FUNCTION public.add_user(
in i_username text,
in i_password text,
out status_code text
) AS $$
BEGIN
PERFORM 1 FROM users WHERE username = i_username;
IF NOT FOUND THEN
INSERT INTO users (username, password) VALUES (i_username, i_password);
status_code = 'OK';
ELSE
status_code = 'user exists';
END IF;
RETURN;
END; $$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.add_user(
in i_username text,
in i_password text,
out status_code text
) TO plproxy;
CREATE OR REPLACE FUNCTION login(
in i_username text,
in i_password text,
out status_code text
) AS $$
BEGIN
SELECT 'OK' FROM users u WHERE username = i_username AND password = i_password INTO status_code;
IF NOT FOUND THEN status_code = 'FAILED'; END IF;
RETURN;
END; $$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION login(
in i_username text,
in i_password text,
out status_code text
) TO plproxy;
Just for the comfort of those actually trying to follow these steps, here is how the proxy databases
cluster config:
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions (cluster_name text)
RETURNS SETOF text AS $$
BEGIN
IF cluster_name = 'readonly' THEN
RETURN NEXT 'host=127.0.0.1 dbname=ro1';
RETURN NEXT 'host=127.0.0.1 dbname=ro2';
RETURN;
ELSIF cluster_name = 'write' THEN
RETURN NEXT 'host=127.0.0.1 dbname=write';
RETURN;
END IF;
RAISE EXCEPTION 'no such cluster%', cluster_name;
END; $$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
in cluster_name text,
out key text,
out val text)
RETURNS SETOF record AS $$
BEGIN
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text) RETURNS int AS $$
SELECT 1;
$$ LANGUAGE SQL;
The last thing left to do is to create the plproxy function definitions that will redirect the login function calls against read-only databases and add_user calls against write database:
CREATE OR REPLACE FUNCTION public.login(
in i_username text,
in i_password text,
out status_code text
) AS $$
CLUSTER 'readonly'; RUN ON ANY;
$$ LANGUAGE plproxy;
CREATE OR REPLACE FUNCTION public.add_user(
in i_username text,
in i_password text,
out status_code text
) AS $$
CLUSTER 'write';
$$ LANGUAGE plproxy;
This is it, the read-only cluster is ready. Note that even though creating such a read-only cluster seems simple and a quick solution for your performance problems it is not a silver bullet solution. Asynchronous replication often creates more problems than it solves so be careful to replicate only non-timecritical data or guarantee a fallback solution when data is not found (eg. proxy function first checks readonly database and if data is not found looks the data up from write db)
PostgreSQL partitioning with plproxy (PART 1)
九 3rd
声明: 此文我在学习找到的并转载过来,原文需要翻墙 原文这里 this article is not the original,i just quote it from http://kaiv.wordpress.com and source link is here
Skype has developed many handy tools for creating a database cluster and this series of posts is intended to shed some light on their rather undocumented features. At the base of it stand’s plproxy. The best way to describe it’s features would be “dblink on steroids”. This short tutorial will explain how to install plproxy, do simple remote database calls and setup a simple horizontally partitioned database cluster.
Partitioning for dummys
Partitioning let’s you distribute the database load and data between multiple database servers.
The principle itself is simple. Let’s say you have one table that contains the users login credentials but the problem is that there are millions of users that daily log in to their account. This of cause creates a lot of load not talking about the huge table it needs for storage. First we need a criteria based on which we choose what server contains what data. We could do this by the usernames first character. Users from a-j go to first server and usernames beginning with k-z go to the second server. It does work but probably one of the servers gets more load than the other. The most common option is to choose the partition based on the hash of the primary key value, in our case the username. Using a hashing function will distribute the users between servers very evenly. What you need to know about hashing is that hash function basically calculates a number based on any given input it can handle:
select hashtext('kristokaiv1') = 1116512480
select hashtext('kristokaiv2') = 1440348351
select hashtext('kristokaiv3') = -219299073
How it works internally is beyond the scope of this post.
Now let’s say we have 2 partitions, then we could get the partition number based on the username hash like this
partition nr = hashtext($1) & 1
The & 1 will give us the last bit of the number which can be either 0 or 1 which is the number we will use to choose the partition the user data will be stored in. If it’s 0 the data goes to partition 0 and if it’s one the data goes to partition 1
select hashtext('kristokaiv1') & 1 = 0 -> partition 0
select hashtext('kristokaiv2') & 1 = 1 -> partition 1
select hashtext('kristokaiv3') & 1 = 1 -> partition 1
How plproxy works
The concept itself is rather simple – plproxy is a new language created inside the PostgreSQL database that enables to make remote database calls exactly as you do with dblink. The syntax is really straightforward – the following statement creates a new plproxy function in the database that when run will connect to the database remotedb, execute the function get_user_email(text) and return the results.
localdb=#
CREATE FUNCTION get_user_email(username text) RETURNS text AS $$
CONNECT 'dbname=remotedb host=123.456.123.21 user=myuser';
$$ LANGUAGE plproxy;
Lets create a dummy function in the remotedb that will respond to the call
remotedb=#
create function get_user_email(text)
returns text as $$
select 'me@somewhere.com'::text;
$$ language sql;
On execution we will see exactly the same results as we would when executing the query on remotedb
localdb=#
select * from get_user_email('tere');
get_user_email
------------------
me@somewhere.com
(1 row)
Of course this is just a really simple example and i will get back to the more complex syntax later, let’s first take a look on how to install the plproxy language.
Installing plproxy
Plproxy can be downloaded from http://pgfoundry.org/projects/plproxy/ but i strongly suggest you get the newest version from the pgfoundry CVS, instructions how to set it up are here. You have to have the PostgreSQL developement environment installed and the folder where the PostgreSQL configuration info tool (pg_config) is needs to be in included in your $PATH variable. If those prerequisites are met then the installation is simple:
$ make $ make install $ make installcheck
If you don’t manage to get it working by yourself there is always the mailing list to help you get started.
The final step is to install the language into the database, this can’t be done as for other languages (createlang utility) instead you have to execute the plproxy.sql file that will create the language call handler. locate plproxy.sql is how i found it but it should be somewhere under contrib.
$ psql -f /usr/local/pgsql/share/contrib/plproxy.sql queries CREATE FUNCTION CREATE LANGUAGE
Now everything should be done and you can test the setup with the simple plproxy function in the syntax example.
Setting up our first cluster
Let’s create a simple cluster that consists of 3 databases (in my example they are all running on the same PostgreSQL instance). One proxy database called queries and 2 partitions queries_0000 and queries_0001. Horizontal partitioning is done based on username, It’s the most common way for partitioning as most of the data in the database is usually user related eg. users login, users orders, users payments, users settings…
The database cluster setup is stored inside plpgsql functions that plproxy calls.
There are 3 functions that you _MUST_ create for the cluster configuration to work properly. So let’s create them on the proxy database “queries”.
1) plproxy.get_cluster_version(cluster_name text)
This function called on each request and is used to determine if the configuration for a cluster has been changed, if the version number it returns is higher than the cached version number partitions configuration is reloaded. Let’s start with the first version of our configuration like this:
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text) RETURNS int AS $$
BEGIN
IF cluster_name = 'queries' THEN
RETURN 1;
END IF;
END;
$$ LANGUAGE plpgsql;
2) plproxy.get_cluster_partitions(cluster_name text)
Function should return the connection strings for all partitions in the correct order.
Because of some unreasonable limitation the total count must be power of 2. This is a unreasonable limitation that can easily be overcome but let’s discuss this in another post.
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text) RETURNS SETOF text AS $$
BEGIN
IF cluster_name = 'queries' THEN
RETURN NEXT 'host=127.0.0.1 dbname=queries_0000';
RETURN NEXT 'host=127.0.0.1 dbname=queries_0001';
RETURN;
END IF;
RAISE EXCEPTION 'no such cluster: %', cluster_name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
If postgres username is not specified in the connection string the name of CURRENT_USER will be used. As plproxy does not know any passwords, the partition databases should trust connections from the proxy database.
3) plproxy.get_cluster_config(cluster_name text)
This is the equivalent of an init file. It should return the configuration parameters as key – value pairs. All of them are optional but you still need the dummy placeholder function:
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config (cluster_name text, out key text, out val text)
RETURNS SETOF record AS $$
BEGIN
RETURN;
END;
$$ LANGUAGE plpgsql;
The details of configuration parameters and what they do can be found in the plproxy documentation.
Now the setup is complete and we can start playing around with our new cluster.
Let’s create a new table to store usernames on both partitions
#queries_0000=# CREATE TABLE users (username text PRIMARY KEY); #queries_0001=# CREATE TABLE users (username text PRIMARY KEY);
Also we must create a new function that is used to insert new usernames into the table:
CREATE OR REPLACE FUNCTION insert_user(i_username text) RETURNS text AS $$
BEGIN
PERFORM 1 FROM users WHERE username = i_username;
IF NOT FOUND THEN
INSERT INTO users (username) VALUES (i_username);
RETURN 'user created';
ELSE
RETURN 'user already exists';
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Now let’s create the proxy function on the proxy database that will call the partitions
queries=#
CREATE OR REPLACE FUNCTION insert_user(i_username text) RETURNS TEXT AS $$
CLUSTER 'queries'; RUN ON hashtext(i_username);
$$ LANGUAGE plproxy;
Filling the partitions with random data:
SELECT insert_user('user_number_'||generate_series::text) FROM generate_series(1,10000);
Now if we go to the partition databases we will see that both of them are filled
and the distribution is quite even.
queries_0001 count(*) -> 5071 queries_0000 count(*) -> 4930
最新评论