Thread: [PoC] load balancing in libpq
Hi all,
I have just written the first PoC code to enable load balancing
in the libpq library.
This libpq enhancement is intended to allow PostgreSQL users to
take advantage of the replication in easier way.
With using this patch, PQconnectdb() function accepts multiple
connection info strings, and pick one of them by round-robin basis
to connect.
This example, shown in below, shows that PQconnect() accepts
a connection string that contains four different databases
(db1~db4) on different servers (dbhost1~dbhost4), and then,
connects them in round-robin basis.
I know there are several things to be considered, but at first,
I need your feedback or comments for this enhancement.
Do you think it would be useful?
Regards,
----------------------------------------------------------------
[snaga@devvm03 libpq_repli]$ cat libpq_lb_test.c
#include <stdio.h>
#include <libpq-fe.h>
void
_connect()
{
PGconn *conn;
PGresult *res;
conn = PQconnectdb("host=dbhost1 dbname=db1 user=snaga; host=dbhost2
dbname=db2 user=snaga; host=dbhost3 dbname=db3 user=snaga; host=dbhost4
dbname=db4 user=snaga");
res = PQexec(conn, "SELECT current_database()");
if ( PQresultStatus(res)==PGRES_TUPLES_OK )
{
printf("current_database = %s on %s\n", PQgetvalue(res, 0, 0),
PQhost(conn));
}
PQfinish(conn);
}
int
main(void)
{
int i;
for (i=0 ; i<8 ; i++)
_connect();
return 0;
}
[snaga@devvm03 libpq_repli]$ ./libpq_lb_test
current_database = db1 on dbhost1
current_database = db2 on dbhost2
current_database = db3 on dbhost3
current_database = db4 on dbhost4
current_database = db1 on dbhost1
current_database = db2 on dbhost2
current_database = db3 on dbhost3
current_database = db4 on dbhost4
[snaga@devvm03 libpq_repli]$
----------------------------------------------------------------
--
Satoshi Nagayasu <snaga@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
Attachment
On 23-09-2012 07:50, Satoshi Nagayasu wrote: > I have just written the first PoC code to enable load balancing > in the libpq library. > Your POC is totally broken. Just to point out two problems: (i) semicolon (;) is a valid character for any option in the connection string and (ii) you didn't think about PQsetdb[Login](), PQconnectdbParams() and PQconnectStartParams(). If you want to pursue this idea, you should think a way to support same option multiple times (one idea is host1, host2, etc). Isn't it easier to add support on your application or polling software? -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
<p dir="ltr">We historically have connection pooling as an external thing; with the high degree to which people keep implementingand reimplementing this, I think *something* more than we have ought to be built in.<p dir="ltr">This, with perhapsbetter implementation, might be an apropos start.<p dir="ltr">Parallel with LDAP: it takes very much this approach,where configuration typically offers a list of LDAP servers. I am not certain if OpenLDAP does round robin on thelist, or if it tries targets in order, stopping when it succeeds. A decent debate fits in, there.<p dir="ltr">I couldsee this being implemented instead via something alongside PGSERVICE; that already offers a well-defined way to capturea "registry" of connection configuration. Specifying a list of service names would allow the command line configurationto remain short and yet very flexible.<div class="gmail_quote">On 2012-09-23 10:01 AM, "Euler Taveira" <<ahref="mailto:euler@timbira.com">euler@timbira.com</a>> wrote:<br type="attribution" /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> On 23-09-2012 07:50, Satoshi Nagayasuwrote:<br /> > I have just written the first PoC code to enable load balancing<br /> > in the libpq library.<br/> ><br /> Your POC is totally broken. Just to point out two problems: (i) semicolon (;)<br /> is a valid characterfor any option in the connection string and (ii) you<br /> didn't think about PQsetdb[Login](), PQconnectdbParams()and<br /> PQconnectStartParams(). If you want to pursue this idea, you should think a<br /> way to supportsame option multiple times (one idea is host1, host2, etc).<br /><br /> Isn't it easier to add support on your applicationor polling software?<br /><br /><br /> --<br /> Euler Taveira de Oliveira - Timbira <a href="http://www.timbira.com.br/"target="_blank">http://www.timbira.com.br/</a><br /> PostgreSQL: Consultoria, Desenvolvimento,Suporte 24x7 e Treinamento<br /><br /><br /> --<br /> Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></blockquote></div>
2012/09/24 1:07, Christopher Browne wrote: > We historically have connection pooling as an external thing; with the > high degree to which people keep implementing and reimplementing this, I > think *something* more than we have ought to be built in. > > This, with perhaps better implementation, might be an apropos start. > > Parallel with LDAP: it takes very much this approach, where > configuration typically offers a list of LDAP servers. I am not certain > if OpenLDAP does round robin on the list, or if it tries targets in > order, stopping when it succeeds. A decent debate fits in, there. > > I could see this being implemented instead via something alongside > PGSERVICE; that already offers a well-defined way to capture a > "registry" of connection configuration. Specifying a list of service > names would allow the command line configuration to remain short and yet > very flexible. Thanks for the comment. As you pointed out, I think it would be a start point to implement new simple load-balancing stuff. That's what I actually intended. My clients often ask me easier way to take advantage of replication and load-balancing. I know there are several considerations to be discussed, such as API compatibility issue, but it would be worth having in the core (or around the core). And I also know many people are struggling with load-balancing and master-failover things for the PostgreSQL replication. If those people are trying implementing their own load-balancing stuff in their apps again and again, it's time to consider implementing it to deliver and/or leverage with the potential of PostgreSQL replication. Regards, > > On 2012-09-23 10:01 AM, "Euler Taveira" <euler@timbira.com > <mailto:euler@timbira.com>> wrote: > > On 23-09-2012 07:50, Satoshi Nagayasu wrote: > > I have just written the first PoC code to enable load balancing > > in the libpq library. > > > Your POC is totally broken. Just to point out two problems: (i) > semicolon (;) > is a valid character for any option in the connection string and > (ii) you > didn't think about PQsetdb[Login](), PQconnectdbParams() and > PQconnectStartParams(). If you want to pursue this idea, you should > think a > way to support same option multiple times (one idea is host1, host2, > etc). > > Isn't it easier to add support on your application or polling software? > > > -- > Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ > PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org > <mailto:pgsql-hackers@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
On 23 September 2012 05:50, Satoshi Nagayasu <snaga@uptime.jp> wrote: > I have just written the first PoC code to enable load balancing > in the libpq library. > > This libpq enhancement is intended to allow PostgreSQL users to > take advantage of the replication in easier way. > > With using this patch, PQconnectdb() function accepts multiple > connection info strings, and pick one of them by round-robin basis > to connect. It's certainly worth thinking about. New ideas are good, if they have some justification. A load balancing solution that only works at connection level isn't much of a solution, IMHO. If you have multiple connection strings in the client program, its trivial to put them in an array and pick one randomly, then use that. So this is already available as a solution in user space. Also, any client based solution presumes we can ship lists of cluster metadata to clients, which we have no solution for, so it places a much greater burden on the administrator. Load balancing config needs to be held more centrally. -- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services