Re: Migrating to Postgresql and new hardware

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