DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:cc:references:in-reply-to:subject:date:message-id
	:mime-version:content-type:content-transfer-encoding:x-mailer
	:thread-index:content-language;
	b=fuceSb+CRV9Es5pGQiWXqwUtUU1NqGPgPAPP0TLz8fD/XeeyRcK7qLDBfHZ2opOP5B
	4BCCmEZhcwqFzX6yfxlCQV0P1hgUEQCLM7wOtMWZ7MrU0mRF3d7Kgu2GiK05ohri0lTJ
	VGfX7QrHM5WIBHWvPqX+VXSU3eNs8tg95B9Ow=
From: "mark" <dvlhntr@gmail.com>
To: "'Lars'" <la@unifaun.com>
Cc: <pgsql-performance@postgresql.org>
References: <E0B1224AD40B544B9DE1BCC6008E95220D1F192CC2@UF05.unifaun.se>
In-Reply-To: <E0B1224AD40B544B9DE1BCC6008E95220D1F192CC2@UF05.unifaun.se>
Subject: Re: Migrating to Postgresql and new hardware
Date: Tue, 18 Jan 2011 21:09:38 -0700
Message-ID: <006601cbb78e$b1c26fe0$15474fa0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: Acu2/msmPjOaaXoBRWGeizdpHCNH0QAiww4g
Content-Language: en-us

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