Comments in line, take em for what you paid for em. > -----Original Message----- > From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance- > owner@xxxxxxxxxxxxxx] On Behalf Of Lars > Sent: Tuesday, January 18, 2011 3:57 AM > To: pgsql-performance@xxxxxxxxxxxxxx > Subject: 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
