Menu
▾
▴
postgres-xc-general
|
From: Iwan S. <iwa...@ya...> - 2012-12-28 23:54:42
|
Hi, I am newbie and need some advice. I read documentations but not fully understand the implementation of postgres-xc on existing server. We have two computers at office, one computer runs postgresql ver 9.1 and another computer for Apache 2.xx Web server. I want to make postgres cluster and add two more computers. My questions: 1. Should I reinstall existing DB server with postgres-xc? (Is there any documentations about migration?) 2. Should I use the same computer spec for every computer on cluster? Thank you Regards, Iwan |
|
From: Michael P. <mic...@gm...> - 2012-12-29 12:44:30
|
On Sat, Dec 29, 2012 at 8:54 AM, Iwan Sofana <iwa...@ya...> wrote:
> Hi,
>
> I am newbie and need some advice.
> I read documentations but not fully understand the implementation
> of postgres-xc on existing server.
>
> We have two computers at office, one computer runs postgresql ver 9.1
> and another computer for Apache 2.xx Web server.
> I want to make postgres cluster and add two more computers.
>
> My questions:
> 1. Should I reinstall existing DB server with postgres-xc?
>
You question is too much general, so... Before considering moving to an XC
server be sure about the following things:
1) Does your application really need XC? Are you looking for
write-scalability/read scalability? Depending on what you are looking for
PostgreSQL itself might be enough.
2) Are the queries used by your application compatible with what is
supported in XC (For example RETURNING and triggers are not supported in
version 1.0)
If after answering those questions you think that it is worth moving to an
XC server, then you will need to choose the correct distribution strategy
for each table of your application deoending on if you want to achieve a
model of read or write scalability.
For example with read scalability you could replication all the table on
all the nodes but the write scaling will suck.
You can also distribute the tables that are under heavy writes to achieve
write scale.
(Is there any documentations about migration?)
>
The only difference at the SQL level between XC and Postgres are for CREATE
TABLE, CREATE TABLE AS and ALTER TABLE (not in 1.0 for data redistribution)
where you can specify the type of distribution you want to use for a given
table. Please refer here:
http://postgres-xc.sourceforge.net/docs/1_0/sql-createtable.html
http://postgres-xc.sourceforge.net/docs/1_0/sql-createtableas.html
2. Should I use the same computer spec for every computer on cluster?
>
You are honestly not obliged to have the same spec for each computer in an
XC cluster.
For example, let's consider 2 examples in the case where you want to create
a cluster with 2 servers:
1) All the coordinators on 1 server and all the datanodes on 1 server:
Coordinators are CPU-bound and datanodes are IO bound, so you will need
more CPU power for the server with Coordinators and won't really need
powerful disks there. On the contrary, the Datanodes will need better disks
as they store data.
2) Coordinator and Datanodes on the same server.
In this case similar spec is recommended.
Please note that as you can also distribute tables on a portion of nodes,
you are able to balance the join or data materialization on certain
dedicated nodes, making them require more or less CPU and shared memory
power.
--
Michael Paquier
http://michael.otacoo.com
|
|
From: Iwan S. <iwa...@ya...> - 2012-12-29 17:59:27
|
Hi Michael, Thank you for comprehensive explanation. About 2 months ago, we start using PostgreSQL on 4 CoreXeon. The size of database now increase up to 800 GB and CPU utilizationis about 40-60%. I am not sure the server is strong enough for next two/three months.I just thinking to replace it with a new one. But by doing this the old server become useable.It seems I need write scalability clustering. Regards, Iwan S. |
|
From: Michael P. <mic...@gm...> - 2012-12-30 13:40:14
|
On Sun, Dec 30, 2012 at 2:59 AM, Iwan Sofana <iwa...@ya...> wrote: > Hi Michael, > > Thank you for comprehensive explanation. > > About 2 months ago, we start using PostgreSQL on 4 Core > Xeon. The size of database now increase up to 800 GB and CPU utilization > is about 40-60%. > I heard the limit of a Postgres database is 2TB somewhere so you still got some time until reaching that but yes the CPU might become a bottleneck if the use rate has such an average. > I am not sure the server is strong enough for next two/three months. > I just thinking to replace it with a new one. > But by doing this the old server become useable. > It seems I need write scalability clustering. > Does your system use heavily read or write operations? The tables that are heavily read would need to be replicated to share the read load between nodes and the tables being written a lot would need to be distributed. -- Michael Paquier http://michael.otacoo.com |
|
From: Jim M. <ji...@gm...> - 2012-12-30 13:50:46
|
On Sun, Dec 30, 2012 at 8:40 AM, Michael Paquier <mic...@gm...>wrote: > > > On Sun, Dec 30, 2012 at 2:59 AM, Iwan Sofana <iwa...@ya...> wrote: > >> Hi Michael, >> >> Thank you for comprehensive explanation. >> >> About 2 months ago, we start using PostgreSQL on 4 Core >> Xeon. The size of database now increase up to 800 GB and CPU utilization >> is about 40-60%. >> > I heard the limit of a Postgres database is 2TB somewhere so you still got > some time until reaching that but yes the CPU might become a bottleneck if > the use rate has such an average. > The limit to a single table in PostgreSQL is 32TB so 800GB is still only a moderately sized database. What is the application doing to produce such a high CPU load? Is it many concurrent users? A lot of large sorts? etc? Understanding why the CPU is high will help determine if XC is the right solution to solve your problem. It could be that some simple tuning or database maintenance could reduce the CPU load and delay your need to move beyond a single server. > > >> I am not sure the server is strong enough for next two/three months. >> I just thinking to replace it with a new one. >> But by doing this the old server become useable. >> It seems I need write scalability clustering. >> > Does your system use heavily read or write operations? > The tables that are heavily read would need to be replicated to share the > read load between nodes and the tables being written a lot would need to be > distributed. > -- > Michael Paquier > http://michael.otacoo.com > > ------------------------------------------------------------------------------ > Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, > MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current > with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft > MVPs and experts. ON SALE this month only -- learn more at: > http://p.sf.net/sfu/learnmore_123012 > _______________________________________________ > Postgres-xc-general mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general > > |
|
From: Iwan S. <iwa...@ya...> - 2012-12-30 15:01:37
|
I am glade to hear that. > The limit to a single table in PostgreSQL is 32TB so 800GB is still only a moderately sized database. Hundred users from different area can access application and insert data. The application doing math calculation (average, sum, min, max) and store the result in several tables. > What is the application doing to produce such a high CPU load? Is it many concurrent users? A lot of large sorts? etc? > Understanding why the CPU is high will help determine if XC is the right solution to solve your problem. It could be that some simple tuning or database maintenance could reduce the CPU load and delay your need to move beyond a single server. |
|
From: Jim M. <ji...@gm...> - 2012-12-30 15:40:01
|
On Sun, Dec 30, 2012 at 10:01 AM, Iwan Sofana <iwa...@ya...> wrote: > I am glade to hear that. > > > > The limit to a single table in PostgreSQL is 32TB so 800GB is still only > a moderately sized database. > > Hundred users from different area can access application and insert data. > The application doing math calculation (average, sum, min, max) and store > the result in several tables. > About how many transactions per second are you seeing? 100, 1000, 10000? The aggregates (average, sum) are run over how many rows? 1000, 1 million, 100 million? > > > What is the application doing to produce such a high CPU load? Is it > many concurrent users? A lot of large sorts? etc? > > > Understanding why the CPU is high will help determine if XC is the right > solution to solve your problem. It could be that some simple tuning or > database maintenance could reduce the CPU load and delay your need to move > beyond a single server. > |
|
From: Iwan S. <iwa...@ya...> - 2012-12-31 12:42:01
|
At peek hour is about 1000 transaction per second, and the aggregates run over 1250000 rows, possibly will increase again. Send by Ojolinux -----Original Message----- From: Jim Mlodgenski <ji...@gm...> Date: Sun, 30 Dec 2012 10:39:55 To: Iwan Sofana<iwa...@ya...> Cc: <pos...@li...> Subject: Re: [Postgres-xc-general] Postgresql - Postgres-XC On Sun, Dec 30, 2012 at 10:01 AM, Iwan Sofana <iwa...@ya...> wrote: > I am glade to hear that. > > > > The limit to a single table in PostgreSQL is 32TB so 800GB is still only > a moderately sized database. > > Hundred users from different area can access application and insert data. > The application doing math calculation (average, sum, min, max) and store > the result in several tables. > About how many transactions per second are you seeing? 100, 1000, 10000? The aggregates (average, sum) are run over how many rows? 1000, 1 million, 100 million? > > > What is the application doing to produce such a high CPU load? Is it > many concurrent users? A lot of large sorts? etc? > > > Understanding why the CPU is high will help determine if XC is the right > solution to solve your problem. It could be that some simple tuning or > database maintenance could reduce the CPU load and delay your need to move > beyond a single server. > |
|
From: Mason S. <ma...@st...> - 2012-12-31 13:23:28
|
Hi Iwan, We don't want want to discourage you from using XC. Just give your self enough time to test where you can simulate the workload that you experience in production. If you can partition/distribute your tables cleanly such that all joins always take place locally, XC should help you. If that is not the case, XC will resort to shipping a lot of data to a single coordinator to join. So, you may notice that most operations seem to scale, but a handful of statements now take several minutes or more (something we have addressed in StormDB's fork of XC). As the others have said, you could look at tuning standard PG first, looking at shared_buffers, work_mem, etc., moving WAL off to its own disk. You may want to also log long queries by setting log_min_duration_statement to a couple of seconds (perhaps less) and see if any particular statement is slow due to a missing index or untuned parameters. On Mon, Dec 31, 2012 at 7:47 AM, Iwan Sofana <iwa...@ya...> wrote: > ** > At peek hour is about 1000 transaction per second, and the aggregates run > over 1250000 rows, possibly will increase again. > Send by Ojolinux > ------------------------------ > *From: * Jim Mlodgenski <ji...@gm...> > *Date: *Sun, 30 Dec 2012 10:39:55 -0500 > *To: *Iwan Sofana<iwa...@ya...> > *Cc: *<pos...@li...> > *Subject: *Re: [Postgres-xc-general] Postgresql - Postgres-XC > > > > On Sun, Dec 30, 2012 at 10:01 AM, Iwan Sofana <iwa...@ya...>wrote: > >> I am glade to hear that. >> >> >> > The limit to a single table in PostgreSQL is 32TB so 800GB is still >> only a moderately sized database. >> >> Hundred users from different area can access application and insert >> data. >> The application doing math calculation (average, sum, min, max) and store >> the result in several tables. >> > > About how many transactions per second are you seeing? 100, 1000, 10000? > The aggregates (average, sum) are run over how many rows? 1000, 1 million, > 100 million? > > >> >> > What is the application doing to produce such a high CPU load? Is it >> many concurrent users? A lot of large sorts? etc? >> >> > Understanding why the CPU is high will help determine if XC is the >> right solution to solve your problem. It could be that some simple tuning >> or database maintenance could reduce the CPU load and delay your need to >> move beyond a single server. >> > > > > ------------------------------------------------------------------------------ > Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, > MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current > with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft > MVPs and experts. SALE $99.99 this month only -- learn more at: > http://p.sf.net/sfu/learnmore_122412 > _______________________________________________ > Postgres-xc-general mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general > > -- Mason Sharp StormDB - http://www.stormdb.com The Database Cloud Postgres-XC Support and Services |
|
From: Iwan S. <iwa...@ya...> - 2012-12-30 14:30:32
|
The system use heavily write operations. > Does your system use heavily read or write operations? Does it mean I should make cluster system for specific table only? > The tables that are heavily read would need to be replicated to share the read load > between nodes and the tables being written a lot would need to be distributed. Regards, Iwan S. |
|
From: Michael P. <mic...@gm...> - 2012-12-30 14:34:39
|
On Sun, Dec 30, 2012 at 11:30 PM, Iwan Sofana <iwa...@ya...> wrote: > The system use heavily write operations. > > > > Does your system use heavily read or write operations? > > Does it mean I should make cluster system for specific table only? Yes, if your system uses a lot of write operations, you will need to balance the writes between nodes by distributing the tables. Btw, as Jim mentionned, it is true that 50%-60% of CPU is pretty high. Don't you have a couple of things running in background or isn't it possible to tune your application or PG server to reduce that? You should consider first tuning the systems that are in place before migrating your PG server to XC. -- Michael Paquier http://michael.otacoo.com |
Thanks for helping keep SourceForge clean.
X