On 18/01/11 18:56, 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.
Most people seem to simply move over to InnoDB when facing these issues,
saving themselves LOTS of pain over MyISAM while minimizing transition
costs. I assume you've rejected that, but I'm interested in why.
> The current setup is five Dell PowerEdge 2950 with 2 * XEON E5410, 4GB RAM. PERC 5/I 256MB NV Cache, 4 * 10K Disks
(3in 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.
Don't, if you want to have a similar thing going in your Pg deployment
later. Replication in Pg remains ... interesting. An n-to-m (or n-to-1)
replication setup can't be achieved with the built-in replication in
9.0; you need to use things like Slony-I, Bucardo, etc each of which
have their own limitations and quirks.
> These are dedicated database servers. There are more or less no stored procedures. The shared database size is about
20GBand each shard database is about 40GB (total of 20 + 40 * 4 = 180GB). I would expect the size will grow 10%-15%
thisyear. Server load might increase with 15%-30% this year. This setup is disk I/O bound. The overwhelming majority of
sqlstatements are fast (typically single row selects, updates, inserts and deletes on primary key) but there are some
slowlong running (10min) queries.
Since you're sharding (and thus clearly don't need strong cluster-wide
ACID) have you considered looking into relaxed semi-ACID / eventually
consistent database systems? If you're doing lots of simple queries and
few of the kind of heavy lifting reporting queries RDBMSs are great for,
it may be worth considering.
If your app uses a data acesss layer, it should be pretty easy to
prototype implementations on other databases and try them out.
Even if you do go for PostgreSQL, if you're not using memcached yet
you're wasting money and effort. You might get lots more life out of
your hardware with a bit of memcached love.
--
System & Network Administrator
POST Newspapers
