http://liuhaifeng.com

On May 30, 2012, at 4:13 AM, Igor Shmain wrote:

I need to design a solution for a database which = will grow and will require a horizontal split at some = moment.
Here is how I = am planning to do it: Every record in every table has a shard number. = After the database becomes too large (gets too many requests), the = tables need to be horizontally split. It means that for every table all = records with some specific shard numbers need to be moved to a new = database.
My plan is to = (1) create a copy of the database on a new server (backup/restore?), (2) = synchronize the databases (how?), and then (3) forward all requests for = the moved shards to the new database. (I will get rid of the unneeded = shards later). An important requirement: there should be no outage for = the users.

Thank you in advance,
-igorS
To: "'Haifeng Liu'" Cc: References: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> In-Reply-To: Subject: Re: Data split -- Creating a copy of database without outage Date: Wed, 30 May 2012 11:14:02 -0400 Message-ID: <00b101cd3e76$d9f04e40$8dd0eac0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00B2_01CD3E55.52E209A0" X-Mailer: Microsoft Outlook 14.0 Content-Language: en-ca Thread-Index: AQKV3/IsTUoVocuh2ViALPGAauRL7gGNpQIClUS52wA= X-Pg-Spam-Score: -1.6 (-) X-Archive-Number: 201205/99 X-Sequence-Number: 39588 This is a multipart message in MIME format. ------=_NextPart_000_00B2_01CD3E55.52E209A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thank you, Liu, for your suggestion. I might be missing something (I am new to postgres), but it seems that your suggestion will not help much in my case. Since the number of db requests will grow with time (more users will come into the system), the current server will become incapable of serving all the requests quickly enough. The idea is to increase overall calculation capacity of the system by adding more servers to it. Throwing more hardware to a single main server is not an option in my case. Creating multiple replicas (slaves) is also not an good option - it would be way more efficient to have a group of db servers, each serving only some subset of users and hosting data for those users only. Buying new servers in advance is not an option too. What I am looking for is switching some of the users to another db server when the capacity of the existing server(s) is not enough. The point is to do it without interrupting the users' work (so they do not see that horrible "Sorry! This site is under maintenance."). If I missed something it would be very kind of you to point this out. Thank you once again, -igorS From: Haifeng Liu [mailto:liuhaifeng@live.com] Sent: May-29-12 9:13 PM To: Igor Shmain Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Data split -- Creating a copy of database without outage Why not use a partitioned table? You can write a trigger to control which partition the coming data should be inserted. Regards, ---- Liu Haifeng Home: http://liuhaifeng.com On May 30, 2012, at 4:13 AM, Igor Shmain wrote: Can you please help with advice? I need to design a solution for a database which will grow and will require a horizontal split at some moment. Here is how I am planning to do it: Every record in every table has a shard number. After the database becomes too large (gets too many requests), the tables need to be horizontally split. It means that for every table all records with some specific shard numbers need to be moved to a new database. My plan is to (1) create a copy of the database on a new server (backup/restore?), (2) synchronize the databases (how?), and then (3) forward all requests for the moved shards to the new database. (I will get rid of the unneeded shards later). An important requirement: there should be no outage for the users. What are the ways to achieve it? Thank you in advance, -igorS ------=_NextPart_000_00B2_01CD3E55.52E209A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Thank you, Liu, for your suggestion.

I might be missing something (I am new to postgres), but it seems = that your suggestion will not help much in my case. Since the = number of db requests will grow with time (more users will come into the = system), the current server will become incapable of serving all the = requests quickly enough.

The idea is to increase overall calculation capacity of the system by = adding more servers to it. Throwing more hardware to a single main = server is not an option in my case. Creating multiple replicas (slaves) = is also not an good option – it would be way more efficient to = have a group of db servers, each serving only some subset of users and = hosting data for those users only. Buying new servers in advance is not = an option too.

What I am looking for is switching some of the users to another db = server when the capacity of the existing server(s) is not enough. The = point is to do it without interrupting the users’ work (so they do = not see that horrible “Sorry! This site is under = maintenance…”).

If I missed something it would be very kind of you to point this = out.

Thank you once again,
-igorS

From:= Haifeng = Liu [mailto:liuhaifeng@live.com]
Sent: May-29-12 9:13 = PM
To: Igor Shmain
Cc: = pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Data split -- = Creating a copy of database without = outage

Why not use = a partitioned table? You can write a trigger to control which partition = the coming data should be inserted.

Regards,
----
Liu Haifeng
Home: http://liuhaifeng.com

On = May 30, 2012, at 4:13 AM, Igor Shmain wrote:

Can you = please help with advice?
I need to = design a solution for a database which will grow and will require a = horizontal split at some moment.
Here is = how I am planning to do it: Every record in every table has a shard = number. After the database becomes too large (gets too many requests), = the tables need to be horizontally split. It means that for every table = all records with some specific shard numbers need to be moved to a new = database.
My plan is = to (1) create a copy of the database on a new server (backup/restore?), = (2) synchronize the databases (how?), and then (3) forward all requests = for the moved shards to the new database. (I will get rid of the = unneeded shards later). An important requirement: there should be no = outage for the users.
What are = the ways to achieve it?
=
Thank you = in advance,
-igorS
=

------=_NextPart_000_00B2_01CD3E55.52E209A0-- From cjames@emolecules.com Wed May 30 15:38:31 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 0CC24184B5A9 for ; Wed, 30 May 2012 12:38:31 -0300 (ADT) Received: from mail-ob0-f174.google.com ([209.85.214.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SZkyh-0000aU-N3 for pgsql-admin@postgresql.org; Wed, 30 May 2012 15:38:30 +0000 Received: by obbtb18 with SMTP id tb18so8546968obb.19 for ; Wed, 30 May 2012 08:38:13 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:x-gm-message-state; bh=GrspzgN4kcgFessxvpHA8wLDffumvtisHFP119Kx4Qs=; b=ima/iUq6ce4whY9/Ws8h5xnDuG14fCTukda2nTxRPaunlc2+eKSaupcsVNJ9m19p6I qfLSKsWm8JGfjvHr7U42ufLUvTYxCQQ0p5gJXrDFJwmKgOOibhQC1oz5VOjFMHOgiB95 /tZ3mI/WszOP8LGdL05rfxA9nqhM24ossQKHX8p6aVvFlzOJo1UEKvUUHjTTuK6FWJ8P xyf3Q4TumI9Bh11NU+7a8OmZeWlMTi+oje6s0Zdtz48d8gt0yuPMbnl5h3hG9l86v3Cs appnlgRHpAz0dQzA3OeuHEc2nLjDf2mRj+H8bgrTtLmGDz3f0+DUYxe9ASVEwDY6t/81 bPQQ== MIME-Version: 1.0 Received: by 10.182.53.100 with SMTP id a4mr15699183obp.3.1338392293423; Wed, 30 May 2012 08:38:13 -0700 (PDT) Received: by 10.182.50.131 with HTTP; Wed, 30 May 2012 08:38:13 -0700 (PDT) In-Reply-To: <00b101cd3e76$d9f04e40$8dd0eac0$@gmail.com> References: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> <00b101cd3e76$d9f04e40$8dd0eac0$@gmail.com> Date: Wed, 30 May 2012 08:38:13 -0700 Message-ID: Subject: Re: Data split -- Creating a copy of database without outage From: Craig James To: Igor Shmain Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=f46d0446311e38387304c142bd75 X-Gm-Message-State: ALoCoQmsbe/DKDZQpf9ijWgucPXoHZHPhmVhfV287vCUBcN2tw1XFC4PmfoI5ZTZ0lyhVT2YRIef X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201205/100 X-Sequence-Number: 39589 --f46d0446311e38387304c142bd75 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable On Wed, May 30, 2012 at 8:14 AM, Igor Shmain wrote: > Thank you, Liu, for your suggestion. **** > > ** ** > > I might be missing something (I am new to postgres), but it seems that > your suggestion will not help much in my case. Since the number of db > requests will grow with time (more users will come into the system), the > current server will become incapable of serving all the requests quickly > enough.**** > > ** ** > > The idea is to increase overall calculation capacity of the system by > adding more servers to it. Throwing more hardware to a single main server > is not an option in my case. Creating multiple replicas (slaves) is also > not an good option =96 it would be way more efficient to have a group of = db > servers, each serving only some subset of users and hosting data for thos= e > users only. Buying new servers in advance is not an option too.**** > > ** ** > > What I am looking for is switching some of the users to another db server > when the capacity of the existing server(s) is not enough. The point is t= o > do it without interrupting the users=92 work (so they do not see that > horrible =93Sorry! This site is under maintenance=85=94). > Londiste is very good at replicating a database. It's normally used for real-time backup or to load-balance read-only applications, but it can also be used for the situation you describe. At some point when you decide it's necessary to split your database, you would create a new database on a second server, create an empty duplicate schema (using pg_dump's schema-only feature) then install Londiste. Londiste would replicate your database in real time up to the point where you were ready to make the actual switch. To switch, you'd simply stop Londiste and simultaneously reconfigure your load-balancing system so that a subset of your users were directed to the new database. Then you could uninstall Londiste, and clean out each database by removing the user data that is for users on the other server. You might also consider partitioning. If you know in advance that you're going to be discarding large subsets of the data, it will be much more efficient if you partition it at the outset. When you split your system in two, the cleanup process will be nothing more than dropping partitions. You won't be plagued by bloated indexes and files. Craig > **** > > ** ** > > If I missed something it would be very kind of you to point this out.**** > > ** ** > > Thank you once again,**** > > -igorS**** > > ** ** > > ** ** > > *From:* Haifeng Liu [mailto:liuhaifeng@live.com] > *Sent:* May-29-12 9:13 PM > *To:* Igor Shmain > *Cc:* pgsql-admin@postgresql.org > *Subject:* Re: [ADMIN] Data split -- Creating a copy of database without > outage**** > > ** ** > > Why not use a partitioned table? You can write a trigger to control which > partition the coming data should be inserted.**** > > ** ** > > Regards,**** > > ----**** > > Liu Haifeng**** > > Home: http://liuhaifeng.com**** > > ** ** > > > > **** > > ** ** > > On May 30, 2012, at 4:13 AM, Igor Shmain wrote:**** > > > > **** > > Can you please help with advice?**** > > I need to design a solution for a database which will grow and will > require a horizontal split at some moment.**** > > Here is how I am planning to do it: Every record in every table has a > shard number. After the database becomes too large (gets too many > requests), the tables need to be horizontally split. It means that for > every table all records with some specific shard numbers need to be moved > to a new database.**** > > My plan is to (1) create a copy of the database on a new server > (backup/restore?), (2) synchronize the databases (how?), and then (3) > forward all requests for the moved shards to the new database. (I will ge= t > rid of the unneeded shards later). An important requirement: there should > be no outage for the users.**** > > What are the ways to achieve it?**** > > **** > > Thank you in advance, > -igorS**** > > **** > > ** ** > --f46d0446311e38387304c142bd75 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable
On Wed, May 30, 2012 at 8:14 AM, Igor Shmain <igor.shmain@gmail.com> wrote:

Thank you, Liu, for your suggestion. =

=A0<= /p>
I might be missing som= ething (I am new to postgres), but it seems that your suggestion will not h= elp much in my case. =A0Since the number of db requests will grow with time= (more users will come into the system), the current server will become inc= apable of serving all the requests quickly enough.

=A0<= /p>
The idea is to increas= e overall calculation capacity of the system by adding more servers to it. = Throwing more hardware to a single main server is not an option in my case.= Creating multiple replicas (slaves) is also not an good option =96 it woul= d be way more efficient to have a group of db servers, each serving only so= me subset of users and hosting data for those users only. Buying new server= s in advance is not an option too.

=A0<= /p>
What I am looking for = is switching some of the users to another db server when the capacity of th= e existing server(s) is not enough. The point is to do it without interrupt= ing the users=92 work (so they do not see that horrible =93Sorry! This site= is under maintenance=85=94).

Londiste is very good at replicating a da= tabase.=A0 It's normally used for real-time backup or to load-balance r= ead-only applications, but it can also be used for the situation you descri= be.=A0 At some point when you decide it's necessary to split your datab= ase, you would create a new database on a second server, create an empty du= plicate schema (using pg_dump's schema-only feature) then install Londi= ste.=A0 Londiste would replicate your database in real time up to the point= where you were ready to make the actual switch.

To switch, you'd simply stop Londiste and simultaneously reconfigur= e your load-balancing system so that a subset of your users were directed t= o the new database.=A0 Then you could uninstall Londiste, and clean out eac= h database by removing the user data that is for users on the other server.=

You might also consider partitioning.=A0 If you know in advance that yo= u're going to be discarding large subsets of the data, it will be much = more efficient if you partition it at the outset.=A0 When you split=A0 your= system in two, the cleanup process will be nothing more than dropping part= itions.=A0 You won't be plagued by bloated indexes and files.

Craig

=A0

=A0<= /p>
If I missed something = it would be very kind of you to point this out.

=A0<= /p>
Thank you once again,<= u>

-igorS
=A0

=A0<= /p>

From: Haifeng Liu [mailto:liuhaifeng@live.com]
Sent: May-29-12 9:13 PM
To: Igor Shmain
Cc: pgsql-admin@pos= tgresql.org
Subject: Re: [ADMIN] Data split -- Creating a cop= y of database without outage

=A0
Why not use a partitioned table? You can write a trigger to control whi= ch partition the coming data should be inserted.
=A0
<= span style=3D"font-size:13.5pt;font-family:"Helvetica","sans= -serif"">Regards,
----
Liu Haifeng

Home: http://liuhaifeng.com

=A0

=A0=
On May 30, 2012, at 4:13 AM, Igor Shma= in wrote:

<= /u>

Can you please help with advice?
<= p class=3D"MsoNormal" style=3D"margin-bottom:10.0pt;line-height:12.75pt"> I need to design a solution for a database which will grow and= will require a horizontal split at some moment.

<= span style=3D"font-size:11.0pt;font-family:"Calibri","sans-s= erif"">Here is how I am planning to do it: Every record in every table= has a shard number. After the database becomes too large (gets too many re= quests), the tables need to be horizontally split. It means that for every = table all records with some specific shard numbers need to be moved to a ne= w database.

<= span style=3D"font-size:11.0pt;font-family:"Calibri","sans-s= erif"">My plan is to (1) create a copy of the database on a new server= (backup/restore?), (2) synchronize the databases (how?), and then (3) forw= ard all requests for the moved shards to the new database. (I will get rid = of the unneeded shards later). An important requirement: there should be no= outage for the users.

<= span style=3D"font-size:11.0pt;font-family:"Calibri","sans-s= erif"">What are the ways to achieve it?
=A0
Thank you in advance,<= br> -igorS
=A0

=A0
=

--f46d0446311e38387304c142bd75-- From aalvarez@d2.com Wed May 30 17:12:42 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 8BDC2184B5AC for ; Wed, 30 May 2012 14:12:42 -0300 (ADT) Received: from mx2.d2.com ([66.251.81.121]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SZmRr-00015Y-If for pgsql-admin@postgresql.org; Wed, 30 May 2012 17:12:42 +0000 Received: from mxb.d2.com ([10.85.4.6]) by mx2.d2.com (8.13.8/8.13.8) with ESMTP id q4UI8gm4004169 for ; Wed, 30 May 2012 11:08:42 -0700 X-ASG-Debug-ID: 1338397937-01d10a6c961cd0cc0001-ysJW5H Received: from mail01.d2.com ([172.16.66.171]) by mxb.d2.com with ESMTP id 1UyFMjPq5hoV18IO for ; Wed, 30 May 2012 10:12:17 -0700 (PDT) X-Barracuda-Envelope-From: aalvarez@d2.com Received: from exbew2k3-02.d2.com (exbew2k3-02 [10.78.2.16]) by mail01.d2.com (8.13.8/8.13.8/Debian-3) with ESMTP id q4UHCH63017223 for ; Wed, 30 May 2012 10:12:17 -0700 Received: from [10.126.2.220] ([10.126.2.220] RDNS failed) by exbew2k3-02.d2.com with Microsoft SMTPSVC(6.0.3790.3959); Wed, 30 May 2012 10:12:17 -0700 Message-ID: <4FC654F1.7000809@d2.com> Date: Wed, 30 May 2012 10:12:17 -0700 From: Amador Alvarez User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US; rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Data split -- Creating a copy of database without outage References: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> <00b101cd3e76$d9f04e40$8dd0eac0$@gmail.com> X-ASG-Orig-Subj: Re: [ADMIN] Data split -- Creating a copy of database without outage In-Reply-To: <00b101cd3e76$d9f04e40$8dd0eac0$@gmail.com> Content-Type: multipart/alternative; boundary="------------090100090600090105020703" X-OriginalArrivalTime: 30 May 2012 17:12:17.0270 (UTC) FILETIME=[5D6F6160:01CD3E87] X-Barracuda-Connect: UNKNOWN[172.16.66.171] X-Barracuda-Start-Time: 1338397937 X-Barracuda-URL: http://10.85.4.6:8000/cgi-mod/mark.cgi X-Virus-Scanned: by bsmtpd at d2.com X-Barracuda-Spam-Score: 0.00 X-Barracuda-Spam-Status: No, SCORE=0.00 using per-user scores of TAG_LEVEL=1000.0 QUARANTINE_LEVEL=1000.0 KILL_LEVEL=9.0 tests=HTML_MESSAGE X-Barracuda-Spam-Report: Code version 3.2, rules version 3.2.2.98455 Rule breakdown below pts rule name description ---- ---------------------- -------------------------------------------------- 0.00 HTML_MESSAGE BODY: HTML included in message X-Spam-Status: NO, score=-2.4 required=3.0 tests=ALL_TRUSTED,AWL,BAYES_00, HTML_MESSAGE, HTML_TITLE_EMPTY, RATWARE_GECKO_BUILD autolearn=ham version=3.2.4 X-Spam-Checker-Version: SpamAssassin 3.2.4 (2008-01-01) on mx2.d2.com X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201205/101 X-Sequence-Number: 39590 This is a multi-part message in MIME format. --------------090100090600090105020703 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi , I would start with a single high performance tuned database focusing mainly on dealing efficiently with concurrent activity and identifying the real hot spots. If you check out that you really need to go forward on database power, consider on adding new databases and relocate some users whenever you need it. Take a look on the different options related to replication, tunning and database balancers, set up some testings and get a deeper understanding on your real needs and performance. A.A. On 05/30/2012 08:14 AM, Igor Shmain wrote: > > Thank you, Liu, for your suggestion. > > I might be missing something (I am new to postgres), but it seems that > your suggestion will not help much in my case. Since the number of db > requests will grow with time (more users will come into the system), > the current server will become incapable of serving all the requests > quickly enough. > > The idea is to increase overall calculation capacity of the system by > adding more servers to it. Throwing more hardware to a single main > server is not an option in my case. Creating multiple replicas > (slaves) is also not an good option -- it would be way more efficient > to have a group of db servers, each serving only some subset of users > and hosting data for those users only. Buying new servers in advance > is not an option too. > > What I am looking for is switching some of the users to another db > server when the capacity of the existing server(s) is not enough. The > point is to do it without interrupting the users' work (so they do not > see that horrible "Sorry! This site is under maintenance..."). > > If I missed something it would be very kind of you to point this out. > > Thank you once again, > > -igorS > > *From:*Haifeng Liu [mailto:liuhaifeng@live.com] > *Sent:* May-29-12 9:13 PM > *To:* Igor Shmain > *Cc:* pgsql-admin@postgresql.org > *Subject:* Re: [ADMIN] Data split -- Creating a copy of database > without outage > > Why not use a partitioned table? You can write a trigger to control > which partition the coming data should be inserted. > > Regards, > > ---- > > Liu Haifeng > > Home: http://liuhaifeng.com > > > > On May 30, 2012, at 4:13 AM, Igor Shmain wrote: > > > > Can you please help with advice? > > I need to design a solution for a database which will grow and will > require a horizontal split at some moment. > > Here is how I am planning to do it: Every record in every table has a > shard number. After the database becomes too large (gets too many > requests), the tables need to be horizontally split. It means that for > every table all records with some specific shard numbers need to be > moved to a new database. > > My plan is to (1) create a copy of the database on a new server > (backup/restore?), (2) synchronize the databases (how?), and then (3) > forward all requests for the moved shards to the new database. (I will > get rid of the unneeded shards later). An important requirement: there > should be no outage for the users. > > What are the ways to achieve it? > > Thank you in advance, > -igorS > --------------090100090600090105020703 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi ,

I would start with a single high performance tuned database focusing mainly on dealing efficiently with concurrent activity and identifying the real hot spots.
If you check out that you really need to go forward on database power, consider on adding new databases and relocate some users whenever you need it.

Take a look on the different options related to replication, tunning and database balancers, set up some testings and get a deeper understanding on your real needs and performance.

A.A.

On 05/30/2012 08:14 AM, Igor Shmain wrote:

Thank you, Liu, for your suggestion.

I might be missing something (I am new to postgres), but it seems that your suggestion will not help much in my case. Since the number of db requests will grow with time (more users will come into the system), the current server will become incapable of serving all the requests quickly enough.

The idea is to increase overall calculation capacity of the system by adding more servers to it. Throwing more hardware to a single main server is not an option in my case. Creating multiple replicas (slaves) is also not an good option – it would be way more efficient to have a group of db servers, each serving only some subset of users and hosting data for those users only. Buying new servers in advance is not an option too.

What I am looking for is switching some of the users to another db server when the capacity of the existing server(s) is not enough. The point is to do it without interrupting the users’ work (so they do not see that horrible “Sorry! This site is under maintenance…”).

If I missed something it would be very kind of you to point this out.

Thank you once again,

-igorS

From: Haifeng Liu [mailto:liuhaifeng@live.com]
Sent: May-29-12 9:13 PM
To: Igor Shmain
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Data split -- Creating a copy of database without outage

Why not use a partitioned table? You can write a trigger to control which partition the coming data should be inserted.

Regards,

----

Liu Haifeng

Home: http://liuhaifeng.com

On May 30, 2012, at 4:13 AM, Igor Shmain wrote:

Can you please help with advice?

I need to design a solution for a database which will grow and will require a horizontal split at some moment.

Here is how I am planning to do it: Every record in every table has a shard number. After the database becomes too large (gets too many requests), the tables need to be horizontally split. It means that for every table all records with some specific shard numbers need to be moved to a new database.

My plan is to (1) create a copy of the database on a new server (backup/restore?), (2) synchronize the databases (how?), and then (3) forward all requests for the moved shards to the new database. (I will get rid of the unneeded shards later). An important requirement: there should be no outage for the users.

What are the ways to achieve it?

Thank you in advance,
-igorS

--------------090100090600090105020703-- From igor.shmain@gmail.com Thu May 31 03:03:14 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 90396191F64D for ; Thu, 31 May 2012 00:03:14 -0300 (ADT) Received: from mail-yw0-f46.google.com ([209.85.213.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SZvfJ-0004Zm-K9 for pgsql-admin@postgresql.org; Thu, 31 May 2012 03:03:14 +0000 Received: by yhmm54 with SMTP id m54so352868yhm.19 for ; Wed, 30 May 2012 20:02:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :content-type:x-mailer:content-language:thread-index; bh=U3UkS6AI3CZsa6bL+VVNQlzVxemuyEMibmlRXe5kYWM=; b=aSBtyiyPVp5JeVFPp5QYU6tUbAy9Vb/gn0c3jCKX2g6oOe4xm3aVoMHlVBHzBaokxU ZzOR1k4ad2JmXFhmfRZXqjPmkJLF0Gpu3ycVNlzxtS6cDpV6FMqyZv2ELlPm5gbIdw3H hToMGw1pbZZ876CRfhh8FLZkdgAqgcEv/PwTzb3Nfjv2bTcoMPT5vC2OQWSq1PZLQ9Do xy/fur0xCgxWu+xydMV6r8sLontWjJgUWawj61kfG0YBf+a82PfIBblJKIWp4GDgjPjs fIfQUAVOErfmFyiCLKAxGK7QiAT6BXMe21sNBVoT/f8B9TK1n2d2gz96lFOJ6btNYhR/ oegA== Received: by 10.50.214.66 with SMTP id ny2mr3183915igc.0.1338433376694; Wed, 30 May 2012 20:02:56 -0700 (PDT) Received: from Tower (CPE001c1008f624-CM0011aec4f376.cpe.net.cable.rogers.com. [174.118.17.120]) by mx.google.com with ESMTPS id pa6sm593328igc.12.2012.05.30.20.02.54 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 30 May 2012 20:02:55 -0700 (PDT) From: "Igor Shmain" To: References: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> In-Reply-To: Subject: Re: Data split -- Creating a copy of database without outage Date: Wed, 30 May 2012 23:02:18 -0400 Message-ID: <013801cd3ed9$cbb36750$631a35f0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0139_01CD3EB8.44A23C80" X-Mailer: Microsoft Outlook 14.0 Content-Language: en-ca Thread-Index: AQKV3/IsTUoVocuh2ViALPGAauRL7gGNpQIClUWGRhA= X-Pg-Spam-Score: -0.7 (/) X-Archive-Number: 201205/102 X-Sequence-Number: 39591 This is a multipart message in MIME format. ------=_NextPart_000_0139_01CD3EB8.44A23C80 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thank you Craig and thank you Amador for your great help! Craig's ideas are very useful - I definitely want to look deeper into Londiste and to use partitioning. Amador's approach makes perfect sense to me. Best wishes, -igor ------=_NextPart_000_0139_01CD3EB8.44A23C80 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Thank you Craig and thank you Amador for your great = help!

Craig's ideas are very useful - I definitely want to look deeper into = Londiste and to use partitioning.

Amador's approach makes perfect sense to me.

Best wishes,
-igor

------=_NextPart_000_0139_01CD3EB8.44A23C80-- From Kevin.Grittner@wicourts.gov Sat Jun 2 15:11:54 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id EEC0812A65BB for ; Sat, 2 Jun 2012 12:11:54 -0300 (ADT) Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Sapzc-0003yL-7A for pgsql-admin@postgresql.org; Sat, 02 Jun 2012 15:11:54 +0000 Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Sat, 02 Jun 2012 10:11:38 -0500 Message-Id: <4FC9E6D70200002500047FA7@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.2 Date: Sat, 02 Jun 2012 10:11:34 -0500 From: "Kevin Grittner" To: , Subject: Re: Data split -- Creating a copy of database without outage Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201206/1 X-Sequence-Number: 39592 "Igor Shmain" wrote: > I need to design a solution for a database which will grow and will > require horizontal split at some moment. Just one more bit of "food for thought" -- we have a database with 3TB processing approximately 50 million database transactions per day (some with a great many statements or affecting many rows) running quite comfortably on a single machine (actually sharing that machine with a 2TB database on a separate drive array), without partitioning. We have done a lot of tuning. I'm not sure what your basis is for the assumption that you will need to split the database across machines; you might be right, but you might be engaging in "premature optimization". -Kevin From igor.shmain@gmail.com Sun Jun 3 00:57:44 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 78F8C12A65BC for ; Sat, 2 Jun 2012 21:57:44 -0300 (ADT) Received: from mail-ob0-f174.google.com ([209.85.214.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Saz8X-0004ZW-Ai for pgsql-admin@postgresql.org; Sun, 03 Jun 2012 00:57:43 +0000 Received: by obbtb18 with SMTP id tb18so5048982obb.19 for ; Sat, 02 Jun 2012 17:57:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :content-type:content-transfer-encoding:x-mailer:thread-index :content-language; bh=5IGJ2JruQ/CTqJXwK8tbq/aUoc1PIj+814BJx+y5vQc=; b=dfGOra4Ifb2fPZabHL7FnazPxGjfcfOSFEPlOQSP75Ft2eUSDcjp4kMFAWE3vUs4Wq 02MnCCkG4pmfL5oLXYvYwVJUyUBrUcgetc11l9SXjZqOal9s2qMdB3VNMEtJnhR/NwTM 38M0gAyN5fzTZm+VE/uDU/lqGr0/S6ZcJDE6CJTfkM3aUB5weV9UBzHFPRQndWmy0l+l vwpBfNDu/2oOOMQht3Qqab3EdA2sy/KWwrg1QEMXfCu9XZYQJkaK5ZHM4fpP81SsYNdp 8VxKLGpqMozJRjFzagK2hHan1eao0/OAL06Zz+Y7qA05F4xtpYcX0VPEMU5plHvsC2ls kcVw== Received: by 10.50.203.98 with SMTP id kp2mr5006552igc.42.1338685046916; Sat, 02 Jun 2012 17:57:26 -0700 (PDT) Received: from Tower (CPE001c1008f624-CM0011aec4f376.cpe.net.cable.rogers.com. [174.118.17.120]) by mx.google.com with ESMTPS id fv9sm4874995igc.8.2012.06.02.17.57.24 (version=TLSv1/SSLv3 cipher=OTHER); Sat, 02 Jun 2012 17:57:25 -0700 (PDT) From: "Igor Shmain" To: "'Kevin Grittner'" , References: <4FC9E6D70200002500047FA7@gw.wicourts.gov> In-Reply-To: <4FC9E6D70200002500047FA7@gw.wicourts.gov> Subject: Re: Data split -- Creating a copy of database without outage Date: Sat, 2 Jun 2012 20:57:25 -0400 Message-ID: <009f01cd4123$d89d9840$89d8c8c0$@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQLAKVofQVYRFYUrn76u2Yv6MeqI5JUB4jJA Content-Language: en-ca X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201206/2 X-Sequence-Number: 39593 Thank you for the "food for thoughts", Kevin :-) Would it be possible for you to mention what hardware (cpu, ram, disks, etc.) and software your system uses to support this db size and number of transactions? Regarding the original question: It was not a question of potency of postgres. The architecture I am working on is intended to be used by a web startup. If the product is successfully, many users can start using the service in a very short time. If that happens, there will be not time to re-architect the database and the applications; the database will need to be scaled almost overnight. Total number of requests per day is not a major criterion for this system. The response time for multiple hits in a short period of time is more important. The requirement is to serve thousands of requests per second. Buying a "super" computer, hoping that one day it will run at full throttle is not for startups. Getting such a powerful computer quickly and moving the database there is unrealistic. It makes more sense to design the system in a way so it can be easily and quickly distributed across many relatively inexpensive servers. That is why the sharding is needed. If you say something like "this is just purely theoretical", "what are the chances to get all those users", "things like that does not happen overnight", I would totally agree. But look at it from another angle: If only a few people use the application, the company will stay with a small server and will not lose much. But if the service is successful, the company will deploy a whole bunch of servers in a few hours and will be able to serve all the users quickly :-) It is a trade-off. More work now in exchange for having a scalable system tomorrow (yes, yes, it is also called premature optimization :-) And you know what, it does not look like too much extra work now :-) If you see real or potential problems in this logic, or heard about similar implementations, please mention that. I would appreciate it very much. Best wishes, -igor -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: June-02-12 11:12 AM To: igor.shmain@gmail.com; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Data split -- Creating a copy of database without outage "Igor Shmain" wrote: > I need to design a solution for a database which will grow and will > require horizontal split at some moment. Just one more bit of "food for thought" -- we have a database with 3TB processing approximately 50 million database transactions per day (some with a great many statements or affecting many rows) running quite comfortably on a single machine (actually sharing that machine with a 2TB database on a separate drive array), without partitioning. We have done a lot of tuning. I'm not sure what your basis is for the assumption that you will need to split the database across machines; you might be right, but you might be engaging in "premature optimization". -Kevin From jan.sture.nielsen@gmail.com Sun Jun 3 03:00:30 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 8352B169BF33 for ; Sun, 3 Jun 2012 00:00:30 -0300 (ADT) Received: from mail-wg0-f50.google.com ([74.125.82.50]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Sb13K-0006JC-5V for pgsql-admin@postgresql.org; Sun, 03 Jun 2012 03:00:28 +0000 Received: by wgbds11 with SMTP id ds11so3241647wgb.19 for ; Sat, 02 Jun 2012 20:00:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=w+1OgNHG2GHlFhzS1OBZEoYTHdw2VgRLFEkoCddbor8=; b=t79KG0loFLiKHLdyGIvVqckxDDjdkD6w0jt2QoedNf1Ng7Bl8siNu9zYX3UjCLVlgN 5WKF0NBy4CjuCCsuvigbJYAT5lzmykfPOZ9vnwhApTc5/pUyF4hgg/7FjTBzxtvXcpQW 9Ltt475bgRYivmf/CuOhkK4NPcnlgp9dJMim+fhNZ0SzfQIUSdl6ZvdEEwjX4Llz10vg HIBMNVVEDcdiovWuGbNQDqP6VKqlCFLwFI0cCjRmqLEq6v82XNBhaCdRWBH6yc1L/sQy i7StYaIpswfBME9erXJ+5bpcf/+WFsn1og9DAVxzsn1IkxwFoLEgvM0twj7nowz6AL41 wSOg== MIME-Version: 1.0 Received: by 10.216.141.74 with SMTP id f52mr6931888wej.103.1338692412749; Sat, 02 Jun 2012 20:00:12 -0700 (PDT) Received: by 10.216.244.204 with HTTP; Sat, 2 Jun 2012 20:00:12 -0700 (PDT) In-Reply-To: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> References: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> Date: Sat, 2 Jun 2012 21:00:12 -0600 Message-ID: Subject: Re: Data split -- Creating a copy of database without outage From: Jan Nielsen To: Igor Shmain Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=0016e6de011cb9b81e04c1889db0 X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201206/3 X-Sequence-Number: 39594 --0016e6de011cb9b81e04c1889db0 Content-Type: text/plain; charset=ISO-8859-1 Hi Igor, On Tue, May 29, 2012 at 2:13 PM, Igor Shmain wrote: > I need to design a solution for a database which will grow and will > require a horizontal split at some moment. > Ok > ** > > Here is how I am planning to do it: Every record in every table has a > shard number. After the database becomes too large (gets too many > requests), the tables need to be horizontally split. It means that for > every table all records with some specific shard numbers need to be moved > to a new database. > Since PG does not support the notion of a shard at the database layer, you will have to create this yourself. The key to scaling shards is ensuring there there are no cross-shard joins (or any other shared data); this is an application-design question so unless you "own" the application-design, this will be difficult to achieve. If you do own the design, the application's entity-tree will have to be carefully constructed to avoid any sharing of resources across shards. RDBMS programmers are taught just the opposite so this will require diligence from your team with a very clear, likely simple, application-entity design amenable to shard-ing. > **** > > My plan is to (1) create a copy of the database on a new server > (backup/restore?), (2) synchronize the databases (how?), and then (3) > forward all requests for the moved shards to the new database. (I will get > rid of the unneeded shards later). An important requirement: there should > be no outage for the users. > As I mentioned above, it is unlikely that you will be able to shard an RDBMS at this level; you need to look at the application entity-design. **** > > What are the ways to achieve it? > If you mean to dictate a shard solution, very few will be able to help. On the other hand, if you mean to ask for help in finding a "low-cost scale-able PG DB solution for a typical RDBMS application with the potential for rapid growth", then there are a number of options which have been employed with success. For predominately read applications, use a single master database replication strategy: http://www.postgresql.org/docs/current/static/different-replication-solutions.html For predominately write applications, you will likely need a multi-master solution (unless you can dictate the application entity-design) which is more complex territory from a scale-ability and application design standpoint but you might look at Postgres-XS and Londiste for ideas. Cheers, Jan --0016e6de011cb9b81e04c1889db0 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Igor,

On Tue, May 29, 2012 at 2:13 PM,= Igor Shmain <igor.shmain@gmail.com> wrote:

I= need to design a solution for a database which will grow and will require = a horizontal split at some moment.

=A0

Here is how I am planning to do it: Every record in every table has a sh= ard number. After the database becomes too large (gets too many requests), = the tables need to be horizontally split. It means that for every table all= records with some specific shard numbers need to be moved to a new databas= e.

Since PG does not support the notion= of a shard at the database layer, you will have to create this yourself. T= he key to scaling shards is ensuring there there are no cross-shard joins (= or any other shared data); this is an application-design question so unless= you "own" the application-design, this will be difficult to achi= eve. If you do own the design, the application's entity-tree will have = to be carefully constructed to avoid any sharing of resources across shards= . RDBMS programmers are taught just the opposite so this will require dilig= ence from your team with a very clear, likely simple, application-entity de= sign amenable to shard-ing.

=A0

My plan is to (1) create a copy of the database on a new server (backup/res= tore?), (2) synchronize the databases (how?), and then (3) forward all requ= ests for the moved shards to the new database. (I will get rid of the unnee= ded shards later). An important requirement: there should be no outage for = the users.

As I mentioned above, it is un= likely that you will be able to shard an RDBMS at this level; you need to l= ook at the application entity-design.

What are the ways to achieve i= t?

If you mean to dictate a= shard solution, very few will be able to help.=A0On the other hand, if you= mean to ask for help in finding a "low-cost scale-able PG DB solution= for a typical RDBMS application with the potential for rapid growth",= then there are a number of options which have been employed with success. = For predominately read applications, use a single master database replicati= on strategy:

=A0=A0http://www.postgresql.org/doc= s/current/static/different-replication-solutions.html

For predominately write applications, you will likely need a multi-mas= ter solution (unless you can dictate the application entity-design) which i= s more complex territory from a scale-ability and application design standp= oint but you might look at Postgres-XS and Londiste for ideas.

Cheers,

Jan

--0016e6de011cb9b81e04c1889db0-- From igor.shmain@gmail.com Tue Jun 5 17:16:06 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id A9A8F98F705 for ; Tue, 5 Jun 2012 14:16:06 -0300 (ADT) Received: from mail-yw0-f46.google.com ([209.85.213.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SbxMR-0003fS-Oq for pgsql-admin@postgresql.org; Tue, 05 Jun 2012 17:16:05 +0000 Received: by yhmm54 with SMTP id m54so4081199yhm.19 for ; Tue, 05 Jun 2012 10:15:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:content-type:x-mailer:thread-index:content-language; bh=AlbAr3BO/322da2Lulgj9ht2l1avnbbJvAXbh5kA3oU=; b=nd6Lb2mbGTyAMpXfAZyGwF9N857TIf/0Bggn08f7G3VBLRVPjgbqbWUknDYa/DrMgV EkQJ0ZIIGM8C2ZwhwMbhACv7Yz7cMZel/AIAGtMzfVp0FJiP3iQ5QUQMmrzR3gSdQT9U 8THGPsj7Ns9je0GArF8leKDWeI4uXwRyPLqSiakmuYPs83d9V0zQC7snJESXxqohfiQN B4YoHwqyxSVfDOtNfrXUecLGFES3ZUeI2imYfr695uvRIqTVugoXr2vBgIujeCw1muiI Ve0tlNVOWSPEaMlg20SEEW1de18cjNjuORPZSJNTJB9x9y6D4m052edMSAk83zg0saO1 XKvQ== Received: by 10.50.203.39 with SMTP id kn7mr3524742igc.53.1338916550253; Tue, 05 Jun 2012 10:15:50 -0700 (PDT) Received: from Tower (CPE001c1008f624-CM0011aec4f376.cpe.net.cable.rogers.com. [174.118.17.120]) by mx.google.com with ESMTPS id ud8sm2912239igb.4.2012.06.05.10.15.48 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 05 Jun 2012 10:15:48 -0700 (PDT) From: "Igor Shmain" To: "'Jan Nielsen'" Cc: References: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> In-Reply-To: Subject: Re: Data split -- Creating a copy of database without outage Date: Tue, 5 Jun 2012 13:15:30 -0400 Message-ID: <017001cd433e$d00c41f0$7024c5d0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0171_01CD431D.48FE7280" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQKV3/IsTUoVocuh2ViALPGAauRL7gITFWy2lUoh5/A= Content-Language: en-ca X-Pg-Spam-Score: -1.7 (-) X-Archive-Number: 201206/5 X-Sequence-Number: 39596 This is a multipart message in MIME format. ------=_NextPart_000_0171_01CD431D.48FE7280 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi Jan, Thank you so much for your input and sorry about the delay. I understand most of the limitations, compromises, and sacrifices that come with distributed databases (sharding). But definitely not all of them. After working with databases for about 25 years, I have some basic knowledge about how they work (mssql, oracle, sybase). But I am new to postgres and new to distributed db approach, so any feedback in those areas is extremely welcome :-) If anybody is aware of any successful implementations of distributed approach with postgres and any information around this area, please let me know. If I cannot find a suitable prototype and has to "invent the wheel", I hope to be able to contribute back to the community with my story one day. Thank you once again, -igor From: Jan Nielsen [mailto:jan.sture.nielsen@gmail.com] Sent: June-02-12 11:00 PM To: Igor Shmain Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Data split -- Creating a copy of database without outage Hi Igor, On Tue, May 29, 2012 at 2:13 PM, Igor Shmain wrote: I need to design a solution for a database which will grow and will require a horizontal split at some moment. Ok Here is how I am planning to do it: Every record in every table has a shard number. After the database becomes too large (gets too many requests), the tables need to be horizontally split. It means that for every table all records with some specific shard numbers need to be moved to a new database. Since PG does not support the notion of a shard at the database layer, you will have to create this yourself. The key to scaling shards is ensuring there there are no cross-shard joins (or any other shared data); this is an application-design question so unless you "own" the application-design, this will be difficult to achieve. If you do own the design, the application's entity-tree will have to be carefully constructed to avoid any sharing of resources across shards. RDBMS programmers are taught just the opposite so this will require diligence from your team with a very clear, likely simple, application-entity design amenable to shard-ing. My plan is to (1) create a copy of the database on a new server (backup/restore?), (2) synchronize the databases (how?), and then (3) forward all requests for the moved shards to the new database. (I will get rid of the unneeded shards later). An important requirement: there should be no outage for the users. As I mentioned above, it is unlikely that you will be able to shard an RDBMS at this level; you need to look at the application entity-design. What are the ways to achieve it? If you mean to dictate a shard solution, very few will be able to help. On the other hand, if you mean to ask for help in finding a "low-cost scale-able PG DB solution for a typical RDBMS application with the potential for rapid growth", then there are a number of options which have been employed with success. For predominately read applications, use a single master database replication strategy: http://www.postgresql.org/docs/current/static/different-replication-solution s.html For predominately write applications, you will likely need a multi-master solution (unless you can dictate the application entity-design) which is more complex territory from a scale-ability and application design standpoint but you might look at Postgres-XS and Londiste for ideas. Cheers, Jan ------=_NextPart_000_0171_01CD431D.48FE7280 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi Jan,

Thank you so much for your input and sorry about the = delay.

I understand most of the limitations, compromises, and sacrifices = that come with distributed databases (sharding). But definitely not all = of them. After working with databases for about 25 years, I have some = basic knowledge about how they work (mssql, oracle, sybase). But I am = new to postgres and new to distributed db approach, so any feedback in = those areas is extremely welcome :-)

If anybody is aware of any successful implementations of distributed = approach with postgres and any information around this area, please let = me know. If I cannot find a suitable prototype and has to “invent = the wheel”, I hope to be able to contribute back to the community = with my story one day.

Thank you once again,

-igor

From:= Jan = Nielsen [mailto:jan.sture.nielsen@gmail.com]
Sent: June-02-12 = 11:00 PM
To: Igor Shmain
Cc: = pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Data split -- = Creating a copy of database without outage

Hi Igor,

On Tue, May 29, 2012 at 2:13 PM, Igor Shmain <igor.shmain@gmail.com> = wrote:

I need to = design a solution for a database which will grow and will require a = horizontal split at some moment.

Here is how = I am planning to do it: Every record in every table has a shard number. = After the database becomes too large (gets too many requests), the = tables need to be horizontally split. It means that for every table all = records with some specific shard numbers need to be moved to a new = database.

Since PG does not support the notion of a shard at the = database layer, you will have to create this yourself. The key to = scaling shards is ensuring there there are no cross-shard joins (or any = other shared data); this is an application-design question so unless you = "own" the application-design, this will be difficult to = achieve. If you do own the design, the application's entity-tree will = have to be carefully constructed to avoid any sharing of resources = across shards. RDBMS programmers are taught just the opposite so this = will require diligence from your team with a very clear, likely simple, = application-entity design amenable to = shard-ing.

My plan is = to (1) create a copy of the database on a new server (backup/restore?), = (2) synchronize the databases (how?), and then (3) forward all requests = for the moved shards to the new database. (I will get rid of the = unneeded shards later). An important requirement: there should be no = outage for the users.

As I mentioned above, it is unlikely that you will be = able to shard an RDBMS at this level; you need to look at the = application entity-design.

What are = the ways to achieve it?

If you mean to dictate a shard solution, very few will = be able to help. On the other hand, if you mean to ask for help in = finding a "low-cost scale-able PG DB solution for a typical RDBMS = application with the potential for rapid growth", then there are a = number of options which have been employed with success. For = predominately read applications, use a single master database = replication strategy:

http://www.postgresql.org/docs/current/static/differen= t-replication-solutions.html

For predominately write applications, you will likely = need a multi-master solution (unless you can dictate the application = entity-design) which is more complex territory from a scale-ability and = application design standpoint but you might look at Postgres-XS and = Londiste for ideas.

Cheers,

Jan

------=_NextPart_000_0171_01CD431D.48FE7280-- From Kevin.Grittner@wicourts.gov Wed Jun 6 16:38:51 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 9951B1179B44 for ; Wed, 6 Jun 2012 13:38:51 -0300 (ADT) Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ScJFy-0004Xq-3Y for pgsql-admin@postgresql.org; Wed, 06 Jun 2012 16:38:50 +0000 Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Wed, 06 Jun 2012 11:38:37 -0500 Message-Id: <4FCF413B0200002500048112@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.2 Date: Wed, 06 Jun 2012 11:38:35 -0500 From: "Kevin Grittner" To: "Igor Shmain" , Subject: Re: Data split -- Creating a copy of database without outage References: <4FC9E6D70200002500047FA7@gw.wicourts.gov> <009f01cd4123$d89d9840$89d8c8c0$@gmail.com> In-Reply-To: <009f01cd4123$d89d9840$89d8c8c0$@gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201206/16 X-Sequence-Number: 39607 "Igor Shmain" wrote: > Would it be possible for you to mention what hardware (cpu, ram, > disks, etc.) and software your system uses to support this db size > and number of transactions? We have 4 Intel Xeon X7350 @ 2.93GHz for 16 cores with 128GB RAM. We've got a pair of drives in RAID 1 for OS on its own controller, four drives in RAID 10 for xlog directories on its own controller, and a couple RAID 5 arrays, each about 40 drives, for our two databases (3TB and 2TB). I'm not exactly clear on the controller configuration there except that I understand there are separate paths from two controllers to each drive. All controllers are using battery-backed cache configured for write-back. A machine like that is still capable of handling our current load; but the load is always increasing so we step up the hardware each time we replace a machine. The new server (able to handle about twice the load of the one I just described for our normal transaction mix) has 4 Intel Xeon X7560 @ 2.27GHz for 32 cores with 256GB RAM. We are replicating to each of the databases on these boxes using a pool of 6 database connections to process data from 72 circuit court databases and on the 2TB from other sources, like Supreme Court and Court of Appeals, Board of Bar Examiners, etc. For the read-only web load we have a pool of 30 database connections. Checking the monitoring system for the read-only web application, at the moment we are showing: Active Requests: 3 Requests Per Second: 148.66 Active Sessions: 9081 This is running through a firewall to an apache web server in our DMZ which just redirects through another firewall to a an apache web server which just functions as a load balancer which sends the requests to renderers (well, currently just one, since on the latest hardware one renderer handles the load) which runs Tomcat connecting to our custom Java middle tier on the database server machine which provides the connection pooling and manages each database transaction. Requests for "boilerplate" content are served before it gets to this point where it would show in this monitoring; this is just requests which require database content. One "request" above may run up to about 15 queries, many of which contain a large number of joins. While the load I show above would amount to about 13 million web requests if it went on 24 hours per day, load does drop at night. Last I heard, we had about 5 million requests per day, but that was a couple years ago and it seems to grow pretty steadily. Last I checked, the replication consisted of about two million database transactions per day, many of which have dozens (or hundreds) of statements modifying data. When idle time is detected on a replication source, it is used to compare source data to target, apply fixes to the target, and log the fixes for review. (These are infrequent, but I'm not comfortable running multi-master replication without such automated review.) > Buying a "super" computer, hoping that one day it will run at full > throttle is not for startups. Getting such a powerful computer > quickly and moving the database there is unrealistic. It makes > more sense to design the system in a way so it can be easily and > quickly distributed across many relatively inexpensive servers. > That is why the sharding is needed. I understand the scaling need, and certainly don't want to discount that. Cloud resources might be an alternative to sharding in that fashion, at least to a point. Before we moved to PostgreSQL we were using a commercial database which could not keep up with demand using just one box, so we load balanced between identical servers. Since the replication is asynchronous and we didn't want people potentially jumping around in time, we used session affinity from the renderers to particular database servers to keep a consistent timeline for each user session. This sort of approach is a viable alternative to sharding in some cases. I hope that helps. -Kevin From igor.shmain@gmail.com Sat Jun 9 02:21:40 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id C21D11D22A6E for ; Fri, 8 Jun 2012 23:21:40 -0300 (ADT) Received: from mail-ob0-f174.google.com ([209.85.214.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SdBJ3-0000mQ-A1 for pgsql-admin@postgresql.org; Sat, 09 Jun 2012 02:21:39 +0000 Received: by obbtb18 with SMTP id tb18so3240895obb.19 for ; Fri, 08 Jun 2012 19:21:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :content-type:content-transfer-encoding:x-mailer:thread-index :content-language; bh=Nc41SmHWtSjvqhCD6wVAYc5GGR+qgCYbFYhYIwflyI4=; b=0Te4OTFX/d/ZSNm5DuFoUjT9idhkLrxcym5461Mbl1UChn3Hcq4sxKQYlhokhdNZOC +Gexow1sz7rtMqYlxMTgrY8DdZ6oG0A0eRB7oxQJ4I6WuUh+O4zLMCJW0hbn0jGrwEQR +9lqk/oFOPANyb69gbKPbD/4Ozv8OdHwzs1NfT4dvcfZtJCh/0RYewRLbUDiWsoujB1T rsz/ktW5+0Vs5u5W/RBuINAyiDn9OJiBBp5ZiXmEiKK4sWUOLTHDW9sE2155XDioumHu UMJnFtYCJErMYKAVUMFIKTWdtzOCuI8+uU6HmYMH6Rz+n671f/dSjABGNaADDVEJ2U+R b03A== Received: by 10.50.168.100 with SMTP id zv4mr3724903igb.4.1339208483651; Fri, 08 Jun 2012 19:21:23 -0700 (PDT) Received: from Tower (CPE001c1008f624-CM0011aec4f376.cpe.net.cable.rogers.com. [174.118.17.120]) by mx.google.com with ESMTPS id ud8sm2317118igb.4.2012.06.08.19.21.21 (version=TLSv1/SSLv3 cipher=OTHER); Fri, 08 Jun 2012 19:21:21 -0700 (PDT) From: "Igor Shmain" To: "'Kevin Grittner'" , References: <4FC9E6D70200002500047FA7@gw.wicourts.gov> <009f01cd4123$d89d9840$89d8c8c0$@gmail.com> <4FCF413B0200002500048112@gw.wicourts.gov> In-Reply-To: <4FCF413B0200002500048112@gw.wicourts.gov> Subject: Re: Data split -- Creating a copy of database without outage Date: Fri, 8 Jun 2012 22:21:11 -0400 Message-ID: <043b01cd45e6$8abd9f90$a038deb0$@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQLAKVofQVYRFYUrn76u2Yv6MeqI5AGdSWcfAmQ64zGU62w3cA== Content-Language: en-ca X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201206/42 X-Sequence-Number: 39633 Thank you, Kevin, for this detailed info. It was very helpful. Best wishes, -igor -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: June-06-12 12:39 PM To: Igor Shmain; pgsql-admin@postgresql.org Subject: RE: [ADMIN] Data split -- Creating a copy of database without outage "Igor Shmain" wrote: > Would it be possible for you to mention what hardware (cpu, ram, > disks, etc.) and software your system uses to support this db size and > number of transactions? We have 4 Intel Xeon X7350 @ 2.93GHz for 16 cores with 128GB RAM. We've got a pair of drives in RAID 1 for OS on its own controller, four drives in RAID 10 for xlog directories on its own controller, and a couple RAID 5 arrays, each about 40 drives, for our two databases (3TB and 2TB). I'm not exactly clear on the controller configuration there except that I understand there are separate paths from two controllers to each drive. All controllers are using battery-backed cache configured for write-back. A machine like that is still capable of handling our current load; but the load is always increasing so we step up the hardware each time we replace a machine. The new server (able to handle about twice the load of the one I just described for our normal transaction mix) has 4 Intel Xeon X7560 @ 2.27GHz for 32 cores with 256GB RAM. We are replicating to each of the databases on these boxes using a pool of 6 database connections to process data from 72 circuit court databases and on the 2TB from other sources, like Supreme Court and Court of Appeals, Board of Bar Examiners, etc. For the read-only web load we have a pool of 30 database connections. Checking the monitoring system for the read-only web application, at the moment we are showing: Active Requests: 3 Requests Per Second: 148.66 Active Sessions: 9081 This is running through a firewall to an apache web server in our DMZ which just redirects through another firewall to a an apache web server which just functions as a load balancer which sends the requests to renderers (well, currently just one, since on the latest hardware one renderer handles the load) which runs Tomcat connecting to our custom Java middle tier on the database server machine which provides the connection pooling and manages each database transaction. Requests for "boilerplate" content are served before it gets to this point where it would show in this monitoring; this is just requests which require database content. One "request" above may run up to about 15 queries, many of which contain a large number of joins. While the load I show above would amount to about 13 million web requests if it went on 24 hours per day, load does drop at night. Last I heard, we had about 5 million requests per day, but that was a couple years ago and it seems to grow pretty steadily. Last I checked, the replication consisted of about two million database transactions per day, many of which have dozens (or hundreds) of statements modifying data. When idle time is detected on a replication source, it is used to compare source data to target, apply fixes to the target, and log the fixes for review. (These are infrequent, but I'm not comfortable running multi-master replication without such automated review.) > Buying a "super" computer, hoping that one day it will run at full > throttle is not for startups. Getting such a powerful computer quickly > and moving the database there is unrealistic. It makes more sense to > design the system in a way so it can be easily and quickly distributed > across many relatively inexpensive servers. > That is why the sharding is needed. I understand the scaling need, and certainly don't want to discount that. Cloud resources might be an alternative to sharding in that fashion, at least to a point. Before we moved to PostgreSQL we were using a commercial database which could not keep up with demand using just one box, so we load balanced between identical servers. Since the replication is asynchronous and we didn't want people potentially jumping around in time, we used session affinity from the renderers to particular database servers to keep a consistent timeline for each user session. This sort of approach is a viable alternative to sharding in some cases. I hope that helps. -Kevin