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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
