Re: Migrating to Postgresql and new hardware

От: Strange, John W
Тема: Re: Migrating to Postgresql and new hardware
Дата: ,
Msg-id: EF37296944B47C40ADDCCB7BFD6289FE046FCD5EFC@EMASC201VS01.exchad.jpmchase.net
(см: обсуждение, исходный текст)
Ответ на: Migrating to Postgresql and new hardware  (Lars)
Ответы: Re: Migrating to Postgresql and new hardware  ("Mark Felder")
Re: Migrating to Postgresql and new hardware  (Lars)
Список: pgsql-performance

Скрыть дерево обсуждения

Migrating to Postgresql and new hardware  (Lars, )
 Re: Migrating to Postgresql and new hardware  (Andy Colson, )
  Re: Migrating to Postgresql and new hardware  (Andy Colson, )
  Re: Migrating to Postgresql and new hardware  (Lars, )
 Re: Migrating to Postgresql and new hardware  ("Strange, John W", )
  Re: Migrating to Postgresql and new hardware  ("Mark Felder", )
  Re: Migrating to Postgresql and new hardware  (Lars, )
 Re: Migrating to Postgresql and new hardware  ("mark", )
  Re: Migrating to Postgresql and new hardware  (Lars, )
   Re: Migrating to Postgresql and new hardware  (Craig Ringer, )
    Re: Migrating to Postgresql and new hardware  (Andy Colson, )
     Re: Migrating to Postgresql and new hardware  (Scott Marlowe, )
   Re: Migrating to Postgresql and new hardware  (Greg Smith, )
    Re: Migrating to Postgresql and new hardware  (Lars, )
 Re: Migrating to Postgresql and new hardware  (Craig Ringer, )
 Re: Migrating to Postgresql and new hardware  (Scott Marlowe, )

Are you going to RAID the SSD drives at all?  You would likely be better off with a couple of things.

- increasing ram to 256GB on each server to cache most of the databases. (easy, and cheaper than SSD)
- move to fusionIO
- move to SLC based SSD, warning not many raid controllers will get the performance out of the SSD's at this time.

Of the three I would suggest #1, and #2, the cost of a SLC SSD raid will cost more than the fusionIO drive and still
notmatch the fusionIO drive performance.
 

Of course this is based on my experience, and I have my fireproof suit since I mentioned the word fusionIO :)

- John

-----Original Message-----
From:  [mailto:] On Behalf Of Lars
Sent: Tuesday, January 18, 2011 4:57 AM
To: 
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.
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
inRAID 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
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.
 

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

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
wouldbe slightly more expensive than the first alternative but would be easier to upgrade with two new shard servers
whenit's needed.
 

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?
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?



Regards
/Lars

-- 
Sent via pgsql-performance mailing list ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.


В списке pgsql-performance по дате сообщения:

От: "Mark Felder"
Дата:
Сообщение: Re: Migrating to Postgresql and new hardware
От: "mark"
Дата:
Сообщение: Re: Migrating to Postgresql and new hardware