Message-ID: <4D35E743.60603@squeakycode.net>
Date: Tue, 18 Jan 2011 13:17:23 -0600
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
To: Lars <la@unifaun.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Migrating to Postgresql and new hardware
References: <E0B1224AD40B544B9DE1BCC6008E95220D1F192CC2@UF05.unifaun.se>
In-Reply-To: <E0B1224AD40B544B9DE1BCC6008E95220D1F192CC2@UF05.unifaun.se>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 1/18/2011 4:56 AM, Lars wrote:
> Hi,
>
> We are in the process of moving a web based application from a MySql
> to Postgresql database. Our main reason for moving to Postgresql is
> problems with MySql (MyISAM) table locking. We will buy a new set of
> servers to run the Postgresql databases.
>
> The current setup is five Dell PowerEdge 2950 with 2 *  XEON E5410,
> 4GB RAM. PERC 5/I 256MB NV Cache, 4 * 10K Disks (3 in RAID 5 + 1
> spare).
>
> One server is used for shared data. Four servers are used for sharded
> data. A user in the system only has data in one of the shards. There
> is another server to which all data is replicated but I'll leave that
> one out of this discussion. These are dedicated database servers.
> There are more or less no stored procedures. The shared database size
> is about 20GB and each shard database is about 40GB (total of 20 + 40
> * 4 = 180GB). I would expect the size will grow 10%-15% this year.
> Server load might increase with 15%-30% this year. This setup is disk
> I/O bound. The overwhelming majority of sql statements are fast
> (typically single row selects, updates, inserts and deletes on
> primary key) but there are some slow long running (10min) queries.
>

No idea what mysql thinks a shard is, but in PG we have read-only hot
standby's.

The standby database is exactly the same as the master (save a bit of 
data that has not been synced yet.)  I assume you know this... but I'd 
really recommend trying out PG's hot-standby and make sure it works the 
way you need (because I bet its different than mysql's).

Assuming the "shared" and the "sharded" databases are totally different 
(lets call them database a and c), with the PG setup you'd have database 
a on one computer, then one master with database b on it (where all 
writes go), then several hot-standby's mirroring database b (that 
support read-only queries).

As for the hardware, you'd better test it.  Got any old servers you 
could put a real-world workload on?  Or just buy one new server for 
testing?  Its pretty hard to guess what your usage pattern is (70% read, 
  small columns, no big blobs (like photos), etc)... and even then we'd 
still have to guess.

I can tell you, however, having your readers and writers not block each 
other is really nice.

Not only will I not compare apples to oranges, but I really wont compare 
apples in Canada to oranges in Japan. :-)

-Andy