Thread: horizontal sharding

On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledreamers@gmail.com wrote:
> Hey guys
> what is a good way to horizontal shard in postgresql

"Sharding" is not really a technical term, so it's not really possible
to answer this question meaningfully as posed.

What is it that you actually want to accomplish here exactly?  What
are your goals, and what are your priorities on those goals?  What
will you trade off to achieve them?

> 1. pgpool 2
> 2. gridsql
>
> which is a better way to use sharding
>
> also is it possible to paritition without changing client code

Almost certainly not.  This would be one of the trade-offs mentioned
above.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Jim Mlodgenski wrote:
>
>
>     also is it possible to paritition without changing client code
>
> Yes, but it depends on the SQL in your client code. If you are just
> using simple SQL with no stored functions, you should be able to slip
> in either solution without changing the client code.
>

 what about queries that need to do joins or aggregate reporting across
the partitions?!?  I can't see how that could be done transparently
short of something like Oracle RAC.

On Mon, Jun 15, 2009 at 11:36 AM, John R Pierce<pierce@hogranch.com> wrote:
> Jim Mlodgenski wrote:
>>
>>
>>    also is it possible to paritition without changing client code
>>
>> Yes, but it depends on the SQL in your client code. If you are just using
>> simple SQL with no stored functions, you should be able to slip in either
>> solution without changing the client code.
>
> what about queries that need to do joins or aggregate reporting across the
> partitions?!?  I can't see how that could be done transparently short of
> something like Oracle RAC.

If you use inheritance and therefore put the child tables on the same
server, it's invisible to the client that the table is partitioned.
Make sure you've got constraint_exclusion turned on.

On Wed, Jun 24, 2009 at 11:03:07PM -0700, mobiledreamers@gmail.com wrote:
> GOals are to scale our game for 10000 simultaneous connection to db
> right now db crashing at 500 or 1000 sim connections

Consider using pgbouncer to multiplex your database connections.

If that doesn't work, consider hiring one of the PostgreSQL consulting
outfits like Command Prompt, Endpoint, OmniTI, or the one I work for,
PostgreSQL Experts <http://www.pgexperts.com/>

Cheers,
David.
> On Mon, Jun 15, 2009 at 8:07 AM, David Fetter <david@fetter.org>
> wrote:
>
> > On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledreamers@gmail.com
> > wrote:
> > > Hey guys what is a good way to horizontal shard in postgresql
> >
> > "Sharding" is not really a technical term, so it's not really
> > possible to answer this question meaningfully as posed.
> >
> > What is it that you actually want to accomplish here exactly?
> > What are your goals, and what are your priorities on those goals?
> > What will you trade off to achieve them?
> >
> > > 1. pgpool 2 2. gridsql
> > >
> > > which is a better way to use sharding
> > >
> > > also is it possible to paritition without changing client code
> >
> > Almost certainly not.  This would be one of the trade-offs
> > mentioned above.

--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Thu, Jun 25, 2009 at 12:03 AM, <mobiledreamers@gmail.com> wrote:
> GOals are to scale our game for 10000 simultaneous connection to db
> right now db crashing at 500 or 1000 sim connections

Definitely look into pooling.  I've got a pretty hefty database server
(16 15k5 drives, HW RAID, 32Gig RAM and 8 cores) and I wouldn't DREAM
of trying to handle 10,000 simo connections with it.

mobiledreamers@gmail.com wrote:
> can someone show how to setup pgpool 2 in parallel *query* mode ie
> horizontal partitioning

did you look at
http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel
?

I'd pay close attention to the restrictions here, too...
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction

I thought pgool II was abandonware...

On Fri, Jun 26, 2009 at 7:50 PM, <mobiledreamers@gmail.com> wrote:
> Hey john
> thanks
> i did read both extensively, but finishing all the steps parallel mode or
> horizontal partitioning mode doesnot work!
> but this is my conf file
>
> # backend_hostname, backend_port, backend_weight
> # here are examples
> backend_hostname0 = 'localhost'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/mnt/work/database'
> backend_hostname1 = 'marc.somehost.com'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/mnt/work/database'
> this is the problem
>  pgpool-II-2.2.2]$ createdb -p 9999
> createdb: could not connect to database postgres: server closed the
> connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
>
------------------------------------------------------------------------------------------------------------------------------------
> Error in log
> tail -f /tmp/pgpool.log
> 2009-06-26 17:24:07 LOG: pid 5561: pgpool successfully started
> 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match
> between between master(0) slot[1] (50331648)
> 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed
> tail -f /tmp/pgpool.log
> 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: backend key data received
> 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: transaction state: I
> 2009-06-26 18:49:40 DEBUG: pid 23466: I am 23466 accept fd 7
> 2009-06-26 18:49:40 DEBUG: pid 23466: Protocol Major: 3 Minor: 0 database:
> postgres user: mark
> 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 0 backend
> 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 1 backend
> 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 0
> length: 8
> 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 1
> length: 8
> 2009-06-26 18:49:40 ERROR: pid 23466: pool_read_int: data does not match
> between between master(0) slot[1] (50331648)
> 2009-06-26 18:49:40 ERROR: pid 23466: pool_do_auth: read auth kind failed
>
> On Fri, Jun 26, 2009 at 5:56 PM, John R Pierce <pierce@hogranch.com> wrote:
>>
>> mobiledreamers@gmail.com wrote:
>>>
>>> can someone show how to setup pgpool 2 in parallel *query* mode ie
>>> horizontal partitioning
>>
>> did you look at
>> http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel
>> ?
>>
>> I'd pay close attention to the restrictions here, too...
>> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction
>>
>>
>
>
>
> --
> Bidegg worlds best auction site
> http://bidegg.com
>



--
When fascism comes to America, it will be intolerance sold as diversity.

On Mon, Jun 15, 2009 at 11:36 AM, John R Pierce<pierce@hogranch.com> wrote:
> Jim Mlodgenski wrote:
>>
>>
>>    also is it possible to paritition without changing client code
>>
>> Yes, but it depends on the SQL in your client code. If you are just using
>> simple SQL with no stored functions, you should be able to slip in either
>> solution without changing the client code.
>
> what about queries that need to do joins or aggregate reporting across the
> partitions?!?  I can't see how that could be done transparently short of
> something like Oracle RAC.

You could make something reasonably fast and partitioned with pl/proxy
and views.

Oh crap I think I mixed it up in my head with slony II.  nevermind.  :)

On Sat, Jun 27, 2009 at 1:17 AM, <mobiledreamers@gmail.com> wrote:
> no it is very good and well designed the whole app code doesnt need to
> change
>
> we jus need to configure the pgpool insertion logic
>
> and it has a cool pgpooladmin in php like phppgadmin
>
> if only i can get it to work
>
> On Fri, Jun 26, 2009 at 10:53 PM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> I thought pgool II was abandonware...
>>
>> On Fri, Jun 26, 2009 at 7:50 PM, <mobiledreamers@gmail.com> wrote:
>> > Hey john
>> > thanks
>> > i did read both extensively, but finishing all the steps parallel mode
>> > or
>> > horizontal partitioning mode doesnot work!
>> > but this is my conf file
>> >
>> > # backend_hostname, backend_port, backend_weight
>> > # here are examples
>> > backend_hostname0 = 'localhost'
>> > backend_port0 = 5432
>> > backend_weight0 = 1
>> > backend_data_directory0 = '/mnt/work/database'
>> > backend_hostname1 = 'marc.somehost.com'
>> > backend_port1 = 5432
>> > backend_weight1 = 1
>> > backend_data_directory1 = '/mnt/work/database'
>> > this is the problem
>> >  pgpool-II-2.2.2]$ createdb -p 9999
>> > createdb: could not connect to database postgres: server closed the
>> > connection unexpectedly
>> >         This probably means the server terminated abnormally
>> >         before or while processing the request.
>> >
>> >
------------------------------------------------------------------------------------------------------------------------------------
>> > Error in log
>> > tail -f /tmp/pgpool.log
>> > 2009-06-26 17:24:07 LOG: pid 5561: pgpool successfully started
>> > 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match
>> > between between master(0) slot[1] (50331648)
>> > 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed
>> > tail -f /tmp/pgpool.log
>> > 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: backend key data
>> > received
>> > 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: transaction state: I
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: I am 23466 accept fd 7
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: Protocol Major: 3 Minor: 0
>> > database:
>> > postgres user: mark
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 0
>> > backend
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 1
>> > backend
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 0
>> > length: 8
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 1
>> > length: 8
>> > 2009-06-26 18:49:40 ERROR: pid 23466: pool_read_int: data does not match
>> > between between master(0) slot[1] (50331648)
>> > 2009-06-26 18:49:40 ERROR: pid 23466: pool_do_auth: read auth kind
>> > failed
>> >
>> > On Fri, Jun 26, 2009 at 5:56 PM, John R Pierce <pierce@hogranch.com>
>> > wrote:
>> >>
>> >> mobiledreamers@gmail.com wrote:
>> >>>
>> >>> can someone show how to setup pgpool 2 in parallel *query* mode ie
>> >>> horizontal partitioning
>> >>
>> >> did you look at
>> >>
>> >> http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel
>> >> ?
>> >>
>> >> I'd pay close attention to the restrictions here, too...
>> >>
>> >> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction
>> >>
>> >>
>> >
>> >
>> >
>> > --
>> > Bidegg worlds best auction site
>> > http://bidegg.com
>> >
>>
>>
>>
>> --
>> When fascism comes to America, it will be intolerance sold as diversity.
>
>
>
> --
> Bidegg worlds best auction site
> http://bidegg.com
>



--
When fascism comes to America, it will be intolerance sold as diversity.

On Fri, 26 Jun 2009, mobiledreamers@gmail.com wrote:

> 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match between between master(0) slot[1] (50331648)
> 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed

My guess is that you have different types of connections being made
between the local database and the remote one.  See
http://pgfoundry.org/pipermail/pgpool-general/2007-September/000708.html
for some comments about the restrictions here, and
http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html for
background on the file you'll need to edit in order to change this
situation.

If you still can't resolve your problem, you should ask about it on the
pgpool-general mailing list rather than this one:
http://lists.pgfoundry.org/pipermail/pgpool-general/
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD