Re: Migrating to Postgresql and new hardware

Thanks for the reply!

MyISAM was chosen back in 2000. I'm not aware of the reasoning behind this choice...

Dell claims both the Samsung and the Pliant are safe to use.
Below is a quote from the Pliant datasheet:
"No Write Cache:
Pliant EFDs deliver outstanding
write performance
without any dependence on
write cache and thus does
not use battery/supercap."

> As others have mentioned, how are you going to be doing your "shards"?
Hmm... shards might not have been a good word to describe it. I'll paste what I wrote in another reply:
I used sharding as an expression for partitioning data into several databases.
Each user in the system is unaware of any other user. The user never accesses the private data of another user. Each user could in theory be assigned their own database server. This makes it easy to split the 40000 users over a number of database servers. There are some shared data that is stored in a special "shared" database.

/Lars

-----Ursprungligt meddelande-----
Från: mark [mailto:dvlhntr(at)gmail(dot)com]
Skickat: den 19 januari 2011 05:10
Till: Lars
Kopia: pgsql-performance(at)postgresql(dot)org
Ämne: RE: [PERFORM] Migrating to Postgresql and new hardware

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

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> owner(at)postgresql(dot)org] On Behalf Of Lars
> Sent: Tuesday, January 18, 2011 3:57 AM
> To: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance