Re: [PERFORM] SSD and RAID
On 2012-03-05 23:37, Mark Kirkwood wrote: Which brings up the question of should it be a pair in RAID 1 or just a singe drive? Traditionally this would have been a no brainer "Of course you want RAID 1 or RAID 10"! However our experience with SSD failure modes points to firmware bugs as primary source of trouble - and these are likely to impact both drives (nearly) simultaneously in a RAID 1 configuration. Also the other major issue to watch - flash write limit exhaustion - is also likely to hit at the same time for a pair of drives in RAID 1. What are other folks who are using SSDs doing? This is exactly the reason why in a set of new hardware I'm currently evaluating two different brands of manufacturers for the spindles (behind bbwc for wal, os, archives etc) and ssds (on mb sata ports). For the ssd's we've chosen the Intel 710 and OCZ Vertex 2 PRO, however that last one was EOL and OCZ offered to replace it by the Deneva 2 (http://www.oczenterprise.com/downloads/solutions/ocz-deneva2-r-mlc-2.5in_Product_Brief.pdf). Still waiting for a test Deneva though. One thing to note is that linux software raid with md doesn't support discard, which might shorten the drive's expected lifetime. To get some numbers I tested the raid 1 of ssd's setup for mediawear under a PostgreSQL load earlier, see http://archives.postgresql.org/pgsql-general/2011-11/msg00141.php I would recommended that for every ssd considered for production use, test the ssd with diskchecker.pl on a filesystem that's mounted the same as you would with your data (e.g. with xfs or ext4 with nobarrier), and also do a mediawear test like the one described in the linked pgsql-general threar above, especially if you're chosing to run on non-enterprise marketed ssds. regards, Yeb PS: we applied the same philosophy (different brands) also to motherboards, io controllers and memory, but after testing, we liked one IO controllers software so much more than the other so we chose to have only one. Also stream memory performance of one motherboard showed a significant performance regression in the higher thread counts that we decided to go for the other brand for all servers. -- Yeb Havinga http://www.mgrid.net/ Mastering Medical Data -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SSD and RAID
On 2012-03-06 09:34, Andrea Suisani wrote: On 03/06/2012 09:17 AM, Yeb Havinga wrote: PS: we applied the same philosophy (different brands) also to motherboards, io controllers and memory, but after testing, we liked one IO controllers software so much more than the other so we chose to have only one. Also stream memory performance of one motherboard showed a significant performance regression in the higher thread counts that we decided to go for the other brand for all servers. care to share motherboard winning model? thanks Andrea On http://i.imgur.com/vfmvu.png is a graph of three systems, made with the multi stream scaling (average of 10 tests if I remember correctly) test. The red and blue are 2 X 12 core opteron 6168 systems with 64 GB DDR3 1333MHz in 8GB dimms Red is a Tyan S8230 Blue is a Supermicro H8DGI-G We tried a lot of things to rule out motherboards, such as swap memory of both systems, ensure BIOS settings are similar (e.g. ECC mode), update to latest BIOS where possible, but none of those settings improved the memory performance drop. Both systems were installed with kickstarted Centos 6.2, so also no kernel setting differences there.. regards, Yeb -- Yeb Havinga http://www.mgrid.net/ Mastering Medical Data -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Advice sought : new database server
On 04/03/12, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > I'd be grateful for advice on specifying a new server > ... > The existing server is a 2 x Quad core E5420 Xeon (2.5GHz) with 8GB of > RAM with an LSI battery-backed RAID 10 array of 4no 10K SCSI disks, > providing about 230GB of usable storage, 150GB of which is on an LV > providing reconfigurable space for the databases which are served off an > XFS formatted volume. In conversation on the list I've established that our current server (while fine for our needs) isn't performing terribly well. It could do with more RAM and the disk IO seems slow. That said, I'm keen to buy a new server to improve on the current performance, so I've taken the liberty of replying here to my initial mail to ask specifically about new server recommendations. The initial plan was to share some of the load between the current and new server, and to buy something along the following lines: > 1U chassis with 8 2.5" disk bays > 2x Intel Xeon E5630 Quad-Core / 4x 2.53GHz / 12MB cache > 8 channel Areca ARC-1880i (PCI Express x8 card) > presumably with BBU (can't see it listed at present) > 2 x 300GB SAS 2.5" disks for operating system > (Possibly also 300GB SATA VelociRaptor/10K RPM/32MB cache > RAID 1 > 4 x 300GB SAS 2.5" storage disks > RAID 10 > 48.0GB DDR3 1333MHz registered ECC (12x 4.0GB modules) However, after comments on the list, I realise I could get two servers with the following specs for the same price as the above: 2x Intel Xeon E5620 Quad-Core / 4x 2.40GHz / 12MB cache 48.0GB DDR3 1066MHz registered ECC 4 channel Areca ARC-1212 (PCI Express x4 card) + BBU 4 x WD Raptors in RAID 10 (in 3.5" adapters) In other words, for GBP 5k I can get two servers that may better meet between them my requirements (lots of memory, reasonably fast disks) than a single server. A salient point is that individual databases are currently less than 1GB in size but will grow perhaps to be 2GB over the coming 18 months. The aim would be to contain all of the databases in memory on each server. I'd be very grateful for comments on this strategy. Rory -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeat execution of stable expressions
On Mon, Mar 5, 2012 at 6:41 PM, Peter van Hardenberg wrote: > On Mon, Mar 5, 2012 at 3:15 PM, Merlin Moncure wrote: >> I've complained many times that >> select (f()).*; >> >> will execute f() once for each returned field of f() since the server >> essentially expands that into: >> >> select f().a, f().b; >> > > oh, this is why we expand rows inside a WITH statement. > > it should probably be fixed, but you should find something like > > WITH fn AS SELECT f(), > SELECT (fn).a, (fn).b > > will make your life better sure, but WITH is an optimization fence. I use a lot of views, and if you wrap your view with WITH, then your quals won't get pushed through. ditto if you use the 'OFFSET 0' hack to keep the subquery from being flattened out. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeat execution of stable expressions
hi, > I've complained many times that > select (f()).*; > > will execute f() once for each returned field of f() since the server > essentially expands that into: > > select f().a, f().b; > > try it yourself, see: > create function f(a out text, b out text) returns record as $$ > begin > perform pg_sleep(1); > a := 'a'; b := 'b'; end; > $$ language plpgsql immutable; i ran into this regularly too. when f() is expensive then i try to rewrite the query so that the function only get called once per row. # explain analyze select (f()).*; QUERY PLAN -- Result (cost=0.00..0.51 rows=1 width=0) (actual time=2001.116..2001.117 rows=1 loops=1) Total runtime: 2001.123 ms # explain analyze select f.* from f() as f; QUERY PLAN --- Function Scan on f (cost=0.25..0.26 rows=1 width=64) (actual time=1000.928..1000.928 rows=1 loops=1) Total runtime: 1000.937 ms regards, jan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeat execution of stable expressions
On Tue, Mar 6, 2012 at 10:21 AM, Jan Otto wrote: > hi, > >> I've complained many times that >> select (f()).*; >> >> will execute f() once for each returned field of f() since the server >> essentially expands that into: >> >> select f().a, f().b; >> >> try it yourself, see: >> create function f(a out text, b out text) returns record as $$ >> begin >> perform pg_sleep(1); >> a := 'a'; b := 'b'; end; >> $$ language plpgsql immutable; > > > i ran into this regularly too. when f() is expensive then i try to rewrite > the query so that the > function only get called once per row. > > # explain analyze select (f()).*; > QUERY PLAN > -- > Result (cost=0.00..0.51 rows=1 width=0) (actual time=2001.116..2001.117 > rows=1 loops=1) > Total runtime: 2001.123 ms > > # explain analyze select f.* from f() as f; > QUERY PLAN > --- > Function Scan on f (cost=0.25..0.26 rows=1 width=64) (actual > time=1000.928..1000.928 rows=1 loops=1) > Total runtime: 1000.937 ms yeah -- that's pretty neat, but doesn't seem fit a lot of the cases I bump into. In particular, when stuffing composite types in the field list. You need the type to come back as a scalar so you can expand it a wrapper (especially when layering views). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Advice sought : new database server
On 03/04/2012 03:50 AM, Michael Friedl wrote: Hey! On 04.03.2012 10:58, Rory Campbell-Lange wrote: 1U chassis with 8 2.5" disk bays 2x Intel Xeon E5630 Quad-Core / 4x 2.53GHz / 12MB cache 8 channel Areca ARC-1880i (PCI Express x8 card) presumably with BBU (can't see it listed at present) 2 x 300GB SAS 2.5" disks for operating system (Possibly also 300GB SATA VelociRaptor/10K RPM/32MB cache RAID 1 4 x 300GB SAS 2.5" storage disks RAID 10 48.0GB DDR3 1333MHz registered ECC (12x 4.0GB modules) Sorry, no answer for your question and a bit offtopic. Why do you take SAS disks for the OS and not much cheaper SATA ones? Here's Intel's (very general) take. Your OS disks may not justify SAS on performance alone but other aspects may sway you. http://www.intel.com/support/motherboards/server/sb/CS-031831.htm Cheers, Steve -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SSD and RAID
On 06/03/12 21:17, Yeb Havinga wrote: One thing to note is that linux software raid with md doesn't support discard, which might shorten the drive's expected lifetime. To get some numbers I tested the raid 1 of ssd's setup for mediawear under a PostgreSQL load earlier, see http://archives.postgresql.org/pgsql-general/2011-11/msg00141.php Right, which is a bit of a pain - we are considering either formatting the drive with less capacity and using md RAID 1 or else doing the mirror in LVM to enable a working discard/trim. Regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
