From camille@ankama.com Mon Mar 14 11:54:38 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3059D55F4A for ; Mon, 14 Mar 2005 11:54:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84125-07 for ; Mon, 14 Mar 2005 11:54:36 +0000 (GMT) Received: from ankama.com (unknown [195.6.184.58]) by svr1.postgresql.org (Postfix) with ESMTP id 1CD5055EAE for ; Mon, 14 Mar 2005 11:54:34 +0000 (GMT) Received: from [192.168.0.18] by ankama.com (MDaemon.PRO.v7.2.3.R) with ESMTP id md50000019710.msg for ; Mon, 14 Mar 2005 13:07:08 +0100 Message-ID: <42357B92.8020904@ankama.com> Date: Mon, 14 Mar 2005 12:54:58 +0100 From: Camille Chafer Organization: Ankama User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: fr, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Hardware impact on performances Content-Type: multipart/alternative; boundary="------------020601090709030704080803" X-Authenticated-Sender: cchafer@ankama.com X-Spam-Processed: ankama.com, Mon, 14 Mar 2005 13:07:08 +0100 (not processed: message from valid local sender) X-Lookup-Warning: HELO/EHLO lookup on 192.168.0.18 does not match 80.15.140.119 X-MDRemoteIP: 80.15.140.119 X-Return-Path: camille@ankama.com X-MDaemon-Deliver-To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.55 tagged_above=0 required=5 tests=HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200503/406 X-Sequence-Number: 11111 This is a multi-part message in MIME format. --------------020601090709030704080803 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, I'm using PostgreSQL 8 for a mmorpg. The part of each operation is : select: 50%, update: 40%, insert: 10%. I have no more than 4-5 concurrent connections to the database, but each of them does A LOT of queries (several per second). The database size is about 1GB, but it'll probably be around 2GB in a fews months. The OS will be FreeBSD (version production 5.3 probably, or 4.10) At this time, i'm looking for a new server. Before to buy it, I grab some informations.. So, my question is : what would be the best hardware for this type of needs ? Of course, I'm not asking for a trademark and/or for prices, but for hints. - What is the most important part of the system : CPU ? RAM ? Disks ? - Is a server with 2 or more CPUs much better than a server with a single one, for a pgsql database ? - How much RAM do I need ? The size of the data ? Twice the size ? - I heard Raid1+0 is better than Raid 5. Is it right ? What would be the best configuration, regarding performances and security ? - Does the CPU type (i386, PowerPC, ....) matters ? - A lot of queries probably generate a lot of network output. Does the network controller matters ? - And finally, last question : is it possible to run a single postgresql database on several servers ? (hardware clustering) Thanks in advance for your answers, and sorry for my crap english (i'm french). Camille Chafer --------------020601090709030704080803 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi,

I'm using PostgreSQL 8 for a mmorpg.
The part of each operation is : select: 50%, update: 40%, insert: 10%.
I have no more than 4-5 concurrent connections to the database, but each of them does A LOT of queries (several per second).
The database size is about 1GB, but it'll probably be around 2GB in a fews months.
The OS will be FreeBSD (version production 5.3 probably, or 4.10)

At this time, i'm looking for a new server. Before to buy it, I grab some informations..
So, my question is : what would be the best hardware for this type of needs ?
Of course, I'm not asking for a trademark and/or for prices, but for hints.

- What is the most important part of the system : CPU ? RAM ? Disks ?
- Is a server with 2 or more CPUs much better than a server with a single one, for a pgsql database ?
- How much RAM do I need ? The size of the data ? Twice the size ?
- I heard Raid1+0 is better than Raid 5. Is it right ? What would be the best configuration, regarding performances and security ?
- Does the CPU type (i386, PowerPC, ....) matters ?
- A lot of queries probably generate a lot of network output. Does the network controller matters ?
- And finally, last question : is it possible to run a single postgresql database on several servers ? (hardware clustering)

Thanks in advance for your answers, and sorry for my crap english (i'm french).

Camille Chafer
--------------020601090709030704080803-- From dev@archonet.com Mon Mar 21 09:55:09 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E12D956407 for ; Mon, 21 Mar 2005 09:55:09 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25316-02 for ; Mon, 21 Mar 2005 09:55:07 +0000 (GMT) Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by svr1.postgresql.org (Postfix) with ESMTP id EBA5255286 for ; Mon, 21 Mar 2005 09:55:06 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 4.42) id 1DDJco-000Nna-2p; Mon, 21 Mar 2005 09:55:07 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 0C2C51684D; Mon, 21 Mar 2005 09:55:06 +0000 (GMT) Message-ID: <423E99F4.5000702@archonet.com> Date: Mon, 21 Mar 2005 09:55:00 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Camille Chafer Cc: pgsql-performance@postgresql.org Subject: Re: Hardware impact on performances References: <42357B92.8020904@ankama.com> In-Reply-To: <42357B92.8020904@ankama.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/416 X-Sequence-Number: 11121 Camille Chafer wrote: > Hi, > > I'm using PostgreSQL 8 for a mmorpg. > The part of each operation is : select: 50%, update: 40%, insert: 10%. > I have no more than 4-5 concurrent connections to the database, but each > of them does A LOT of queries (several per second). > The database size is about 1GB, but it'll probably be around 2GB in a > fews months. > The OS will be FreeBSD (version production 5.3 probably, or 4.10) > > At this time, i'm looking for a new server. Before to buy it, I grab > some informations.. > So, my question is : what would be the best hardware for this type of > needs ? > Of course, I'm not asking for a trademark and/or for prices, but for hints. > > - What is the most important part of the system : CPU ? RAM ? Disks ? Usually Disks/RAM. Since you've got a lot of updates/inserts, battery-backed write-cache on your raid controller would be good. > - Is a server with 2 or more CPUs much better than a server with a > single one, for a pgsql database ? With 2+ connections, each can be serviced by one CPU. Of course, if your disk I/O is saturated then it won't help. > - How much RAM do I need ? The size of the data ? Twice the size ? Ideally, enough to hold your "working set". That is, enough cache to store all pages/indexes you regularly access. > - I heard Raid1+0 is better than Raid 5. Is it right ? What would be the > best configuration, regarding performances and security ? It can depend - check the list archives for a lot of discussion on this. More disks is always better. > - Does the CPU type (i386, PowerPC, ....) matters ? Dual-Xeons have given problems. A lot of people seem to think Opteron-based systems provide good value. > - A lot of queries probably generate a lot of network output. Does the > network controller matters ? Well, obviously the more time spent handling network I/O, the less time you spend running queries. I'd think it would have to be a *lot* of activity to make a serious difference. > - And finally, last question : is it possible to run a single postgresql > database on several servers ? (hardware clustering) Not easily, and it probably wouldn't provide any performance benefit. Plenty of replication options though. > Thanks in advance for your answers, and sorry for my crap english (i'm > french). Your English is perfect. -- Richard Huxton Archonet Ltd