Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYJJu-0001Le-M1 for pgsql-performance@arkaria.postgresql.org; Wed, 18 Mar 2015 19:07:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YYJJu-0007TY-2U for pgsql-performance@arkaria.postgresql.org; Wed, 18 Mar 2015 19:07:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YYJJr-0007QM-SW for pgsql-performance@postgresql.org; Wed, 18 Mar 2015 19:07:56 +0000 Received: from mail-oi0-x232.google.com ([2607:f8b0:4003:c06::232]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YYJJn-0007Yj-1K for pgsql-performance@postgresql.org; Wed, 18 Mar 2015 19:07:54 +0000 Received: by oigv203 with SMTP id v203so44842961oig.3 for ; Wed, 18 Mar 2015 12:07:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zetainteractive.com; s=google; h=reply-to:from:mime-version:thread-index:date:message-id:subject:to :content-type; bh=iaVduZDODmQTeQ+LPEfdVR/wBySvXkM5D7ODVmE99Gg=; b=dl5hxW55SK1gFQedVg8/6H6rWpf6ay4ecdB6xuPtlDVkG9KGyfBwajoUbjn54Fu4qh 6oShDGCn56YLWu03MZEPe2duOYkgE9/+E+1U6GL68g//wqJ5UOTyt3MNGrnHWmmCazWX A2f4Rm4KOOJwRywErvhwQQULE4xx0J8BAou5w= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:reply-to:from:mime-version:thread-index:date :message-id:subject:to:content-type; bh=iaVduZDODmQTeQ+LPEfdVR/wBySvXkM5D7ODVmE99Gg=; b=h0Wb2uKds462Ox3miK+GZBqs7dZGyrFqgjVbAVeziNCDNlAlbNE6oetzft+XXcvT4C AC3nZOUVCLcTQQ5rDihvaFC9apxbGtMWG5qAIwCea0Jwh7IlaYDwqHdnXlbzUbpap5x3 ZI1B3z5QfC8gm+XgY+Nz/H3bOx6UIx9E2uf1QhJLTSboiSE3Q4Y3axd5Fi/xjbdYfGxA TVPQtTxCBopfnHFSXLCvRckEP5DkeosCknw056Vpncqx/R1cSpv6veqwqoEqxzDlVwzz W+QTuzaHCVJrVFQZ8XLO49ULY2Ij0PSMk+QrCor2DT52FVmGgfw9lJbMJi4DTvE9Csye J7cQ== X-Gm-Message-State: ALoCoQnp+7VSQVI6p2dvLcrLKnaxpH9A7jvWeSm6ceorDVkIUj/eYBDGSplPqVAaBIWv4QbDNKE1 X-Received: by 10.60.97.35 with SMTP id dx3mr58411259oeb.6.1426705670430; Wed, 18 Mar 2015 12:07:50 -0700 (PDT) Reply-To: From: Vivekanand Joshi MIME-Version: 1.0 X-Mailer: Microsoft Outlook 15.0 thread-index: AdBhrokMU9MuMNZGS+66YMly82Z2HA== Date: Thu, 19 Mar 2015 00:37:49 +0530 Message-ID: <82780c1beb8bc319692c2455c5c3ab38@mail.gmail.com> Subject: Hardware Configuration and other Stuff To: pgsql-performance@postgresql.org, pgsql-admin@postgresql.org Content-Type: multipart/related; boundary=089e01227a18af93fb051194cc2d X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --089e01227a18af93fb051194cc2d Content-Type: multipart/alternative; boundary=089e01227a18af93f8051194cc2c --089e01227a18af93f8051194cc2c Content-Type: text/plain; charset=UTF-8 Hi Team, I don't know under which section does this question comes, so I am posting this question to both Admin and performance mailing list. Apologies in advance. Objective: We are planning to use PostgreSQL instead of Netezza for our data warehouse as well as database solutions. Right now, we have all our clients in one Netezza box. What we are thinking of migrating our clients to dedicated PostgreSQL for each of them. We will start with one of the client. If it works successfully, we will be migrating all the clients one by one. The objective is to get a better performance than our existing solution. We are hopeful of that mainly because of two reasons. Firstly, we will have a dedicated server for each of the client with good hardware instead of having one server with all the clients on that. Secondly, we can spend on hardware much easily than spending on a proprietary appliance. I am hoping this community can help us to know that what would be the good infrastructure/hardware that can help us in achieving our goal. Here are few of the statistics which might act as a starting point. Availability: High (24*7). User Data : 700 GB which will increase to 1.5 TB in next 2-3 years. Number of User Databases : 2 (One is the main database, other is used only for working tables where tables gets deleted in every 48 hours) Number of tables : 200 (in the main database), (2000-3000 in working database) Size of top 5 biggest tables : 20-40 GB No of users concurrently accessing the system : 5-6 with write access. 10 with read access. No of User Queries running on the system in a day : ~80K Read-only Queries (Select): ~60K Write queries: ~20K Data Import Queries: ~1K Typical Business Day : 18-20 hours. I can pass on few complex queries to let you guys know what are we doing. Here are few questions: 1.) I don't need a load balancing solution. It must be high availability server and I can work with asynchronous replication. The most important thing here would be recovery should be as fast as possible. What approach would you recommend? 2.) Recommendations on indexes, WAL, table spaces. I am not asking about on which key I need to make indexes, but an high level approach about how to keep them? This might come out as a weird question to many but please excuse me for being a novice. *Most Important Question:* 3.) What would be the ideal hardware configuration for this requirement? I know there is not a one-stop answer for this, but let's take it is a starting point. We can come to a proper conclusion after a discussion. What are the best on-line resources/books which can tell us about the hardware requirements? Warm Regards, Vivekanand Joshi +919654227927 [image: Zeta Interactive] 185 Madison Ave. New York, NY 10016 www.zetainteractive.com --089e01227a18af93f8051194cc2c Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable <= div class=3D"WordSection1">

Hi Team,

=C2=A0

I don't know under which sec= tion does this question comes, so I am posting this question to both Admin = and performance mailing list. Apologies in advance.

=C2=A0

Objective:

= =C2=A0

We are planning to use PostgreSQL instead = of Netezza for our data warehouse as well as database solutions. Right now,= we have all our clients in one Netezza box.=C2=A0 What we are thinking of = migrating our clients to dedicated PostgreSQL for each of them. We will sta= rt with one of the client. If it works successfully, we will be migrating a= ll the clients one by one. The objective is to get a better performance tha= n our existing solution. We are hopeful of that mainly because of two reaso= ns. Firstly, we will have a dedicated server for each of the client with go= od hardware instead of having one server with all the clients on that. Seco= ndly, we can spend on hardware much easily than spending on a proprietary a= ppliance.

=C2=A0

I am = hoping this community can help us to know that what would be the good infra= structure/hardware that can help us in achieving our goal.

=C2=A0

Here are few of the statistics w= hich might act as a starting point.

=C2=A0

Availability: High (24*7).

Us= er Data : 700 GB which will increase to 1.5 TB in next 2-3 years.

Number of User Databases : 2 (One is the main database, ot= her is used only for working tables where tables gets deleted in every 48 h= ours)

Number of tables : 200 (in the main databas= e), (2000-3000 in working database)

Size of top 5= biggest tables : 20-40 GB

No of users concurrent= ly accessing the system : 5-6 with write access. 10 with read access.

No of User Queries running on the system in a day : ~8= 0K

Read-only Queries (Select): ~60K

Write queries: ~20K

Data Import Qu= eries: ~1K

Typical Business Day : 18-20 hours.

=C2=A0

I can pass on few= complex queries to let you guys know what are we doing.

=C2=A0

Here are few questions:

=C2=A0

1.) I don't need a lo= ad balancing solution. It must be high availability server and I can work w= ith asynchronous replication. The most important thing here would be recove= ry should be as fast as possible.

What approach w= ould you recommend?

=C2=A0

2.) Recommendations on indexes, WAL, table spaces. I am not asking abo= ut on which key I need to make indexes, but an high level approach about ho= w to keep them? This might come out as a weird question to many but please = excuse me for being a novice.

=C2=A0

Most Important Question:

= =C2=A0

3.) What would be the ideal hardware confi= guration for this requirement? I know there is not a one-stop answer for th= is, but let's take it is a starting point. We can come to a proper conc= lusion after a discussion.

=C2=A0

What are the best on-line resources/books which can tell us abou= t the hardware requirements?

=C2=A0

Warm Regards,


Vivekanand Joshi
+919654227927

=C2=A0

3D"Z=

18= 5 Madison Ave. New York, NY 10016

www.zetainteractive.co= m

=C2=A0

--089e01227a18af93f8051194cc2c-- --089e01227a18af93fb051194cc2d Content-Type: image/png; name="image001.png" Content-Disposition: inline; filename="image001.png" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: 3c3e2ea2871090fb_0.1 iVBORw0KGgoAAAANSUhEUgAAAIMAAAAoCAMAAADqmLl5AAAAAXNSR0ICQMB9xQAAAANQTFRFAAAA p3o92gAAAAF0Uk5TAEDm2GYAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAAZdEVYdFNvZnR3YXJlAE1p Y3Jvc29mdCBPZmZpY2V/7TVxAAAAHElEQVRYw+3BMQEAAADCoPVPbQwfoAAAAAAAPgYUoAAB8eFV /wAAAABJRU5ErkJggg== --089e01227a18af93fb051194cc2d--