Re: Migrating to Postgresql and new hardware - Mailing list pgsql-performance

Comments in line, take em for what you paid for em.



> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Lars
> Sent: Tuesday, January 18, 2011 3:57 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Migrating to Postgresql and new hardware
>
> 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.

I would never try and talk someone out of switching but.... MyISAM? What
version of MySQL and did you pick MyISAM for a good reason or just happened
to end up there?



> 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.
>
> As new server we are thinking of PowerEdge R510, 1 * Xeon X5650, 24Gb
> RAM, H700 512MB NV Cache.

One would think you should notice a nice speed improvement, ceteris paribus,
since the X5650 will have ->significantly<- more memory bandwidth than the
5410s you are used to, and you are going to have a heck of a lot more ram
for things to cache in. I think the H700 is a step up in raid cards as well
but with only 4 disks your probably not maxing out there.



> Dell has offered two alternative SSDs:
> Samsung model SS805 (100GB Solid State Disk SATA 2.5").
> (http://www.plianttechnology.com/lightning_lb.php)
> Pliant model LB 150S (149GB Solid State Drive SAS 3Gbps 2.5").
> (http://www.samsung.com/global/business/semiconductor/products/SSD/Prod
> ucts_Enterprise_SSD.html)

The Samsung ones seems to indicate that they have protection in the event of
a power failure, and the pliant does not mention it.

Granted I haven't done or seen any pull the plug under max load tests on
either family, so I got nothing beyond that it is the first thing I have
looked at with every SSD that crosses my path.



>
> Both are SLC drives. The price of the Pliant is about 2,3 times the
> price of the Samsung (does it have twice the performance?).
>
> One alternative is 5 servers (1 shared and 4 shards) with 5 Samsung
> drives (4 in RAID 10 + 1 spare).
> Another alternative would be 3 servers (1 shared and 2 shards) with 5
> Pliant drives (4 in RAID 10 + 1 spare). This would be slightly more
> expensive than the first alternative but would be easier to upgrade
> with two new shard servers when it's needed.

As others have mentioned, how are you going to be doing your "shards"?



>
> Anyone have experience using the Samsung or the Pliant SSD? Any
> information about degraded performance over time?
> Any comments on the setups? How would an alternative with 15K disks (6
> RAID 10 + 1 spare, or even 10 RAID10 + 1 spare) compare?


You still may find that breaking xlog out to its own logical drive (2 drives
in raid 1) gives a speed improvement to the overall. YMMV - so tinker and
find out before you go deploying.

> How would these alternatives compare in I/O performance compared to the
> old setup?
> Anyone care to guess how the two alternatives would compare in
> performance running Postgresql?
> How would the hardware usage of Postgresql compare to MySqls?


I won't hazard a guess on the performance difference between PG w/ Fsync ON
and MySQL running with MyISAM.

If you can get your OS and PG tuned you should be able to have a database
that can have pretty decent throughput for an OLTP workload. Since that
seems to be the majority of your intended workload.


-Mark

>
>
>
> Regards
> /Lars
>
> --
> Sent via pgsql-performance mailing list (pgsql-
> performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance