Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 03:30 PM, Merlin Moncure wrote: On Tue, May 21, 2013 at 7:19 PM, Greg Smith wrote: On 5/20/13 6:32 PM, Merlin Moncure wrote: [cut] The only really huge gain to be had using SSD is commit rate at a low client count. There you can easily do 5,000/second instead of a spinning di

Re: [PERFORM] Very slow inner join query Unacceptable latency.

On Wednesday, May 22, 2013 10:03 PM fburgess wrote: > I did perform a explain analyze on the query. Explain analyze doesn't help to collect statistics. You should use Analyze . Ideally optimizer should have slected the best plan, but just to check you can once try with SET enable_hashjoin=off

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 07:17 PM, Merlin Moncure wrote: > According the the data sheet it is power safe. > > http://investors.micron.com/releasedetail.cfm?ReleaseID=732650 > http://www.micron.com/products/solid-state-storage/client-ssd/m500-ssd Wow, that seems like a pretty good deal then assuming i

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 23/05/13 14:26, Mark Kirkwood wrote: On 23/05/13 14:22, Greg Smith wrote: On 5/22/13 10:04 PM, Mark Kirkwood wrote: Make that quite a few capacitors (top right corner): http://regmedia.co.uk/2013/05/07/m500_4.jpg There are some more shots and descriptions of the internals in the excellent

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 23/05/13 14:22, Greg Smith wrote: On 5/22/13 10:04 PM, Mark Kirkwood wrote: Make that quite a few capacitors (top right corner): http://regmedia.co.uk/2013/05/07/m500_4.jpg There are some more shots and descriptions of the internals in the excellent review at http://techreport.com/review/

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/13 10:04 PM, Mark Kirkwood wrote: Make that quite a few capacitors (top right corner): http://regmedia.co.uk/2013/05/07/m500_4.jpg There are some more shots and descriptions of the internals in the excellent review at http://techreport.com/review/24666/crucial-m500-ssd-reviewed That

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On Wednesday, May 22, 2013, Joshua D. Drake wrote: > > On 05/22/2013 04:37 PM, Merlin Moncure wrote: >> >> On Wed, May 22, 2013 at 5:42 PM, Joshua D. Drake wrote: >>> >>> I am curious how the 710 or S3700 stacks up against the new M500 from >>> Crucial? I know Intel is kind of the goto for these

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 23/05/13 13:32, Mark Kirkwood wrote: On 23/05/13 13:01, Joshua D. Drake wrote: On 05/22/2013 04:37 PM, Merlin Moncure wrote: On Wed, May 22, 2013 at 5:42 PM, Joshua D. Drake wrote: I am curious how the 710 or S3700 stacks up against the new M500 from Crucial? I know Intel is kind of the

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/13 4:57 PM, Merlin Moncure wrote: Oh, the major vendors will still keep their rip-off going on a little longer selling their storage trays, raid controllers, entry/mid level SANS, SAS HBAs etc at huge markup to customers who don't need them (some will still need them, but the bar suddenly

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/13 6:42 PM, Joshua D. Drake wrote: I am curious how the 710 or S3700 stacks up against the new M500 from Crucial? I know Intel is kind of the goto for these things but the m500 is power off protected and rated at: Endurance: 72TB total bytes written (TBW), equal to 40GB per day for 5 year

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 23/05/13 13:01, Joshua D. Drake wrote: On 05/22/2013 04:37 PM, Merlin Moncure wrote: On Wed, May 22, 2013 at 5:42 PM, Joshua D. Drake wrote: I am curious how the 710 or S3700 stacks up against the new M500 from Crucial? I know Intel is kind of the goto for these things but the m500 is p

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 04:37 PM, Merlin Moncure wrote: On Wed, May 22, 2013 at 5:42 PM, Joshua D. Drake wrote: I am curious how the 710 or S3700 stacks up against the new M500 from Crucial? I know Intel is kind of the goto for these things but the m500 is power off protected and rated at: Endurance: 7

Re: [PERFORM] Very slow inner join query Unacceptable latency.

On Wed, May 22, 2013 at 7:41 AM, wrote: > PostgreSQL 9.1.6 on linux > >From the numbers in your attached plan, it seems like it should be doing a nested loop from the 580 rows (it thinks) that match in SARS_ACTS_RUN against the index on sars_run_id to pull out the 3297 rows (again, it think, th

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On Wed, May 22, 2013 at 5:42 PM, Joshua D. Drake wrote: > I am curious how the 710 or S3700 stacks up against the new M500 from > Crucial? I know Intel is kind of the goto for these things but the m500 is > power off protected and rated at: Endurance: 72TB total bytes written (TBW), > equal to 40G

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 01:57 PM, Merlin Moncure wrote: On Wed, May 22, 2013 at 3:06 PM, Greg Smith wrote: You bet, and I haven't recommended anyone buy a 710 since the announcement. However, "hit the street" is still an issue. No one has been able to keep DC S3700 drives in stock very well yet. It t

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On May 22, 2013, at 4:06 PM, Greg Smith wrote: > And there are some other products with interesting price/performance/capacity > combinations that are also sensitive to wearout. Seagate's hybrid drives > have turned interesting now that they cache writes safely for example. > There's no cheap

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On Wed, May 22, 2013 at 3:06 PM, Greg Smith wrote: > You bet, and I haven't recommended anyone buy a 710 since the announcement. > However, "hit the street" is still an issue. No one has been able to keep > DC S3700 drives in stock very well yet. It took me three tries through > Newegg before my

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/13 3:51 PM, Merlin Moncure wrote: s3700 is rated for 10 drive writes/day for 5 years. so, for 200gb drive, that's 200gb * 10/day * 365 days * 5, that's 3.65 million gigabytes or ~ 3.5 petabytes. Yes, they've improved on the 1.5PB that the 710 drives topped out at. For that particular d

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 02:51 PM, Merlin Moncure wrote: s3700 is rated for 10 drive writes/day for 5 years. so, for 200gb drive, that's 200gb * 10/day * 365 days * 5, that's 3.65 million gigabytes or ~ 3.5 petabytes. Nice. And on that note: http://www.tomshardware.com/reviews/ssd-dc-s3700-raid-0-benchm

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On Wed, May 22, 2013 at 2:30 PM, Greg Smith wrote: > On 5/22/13 3:06 PM, Joshua D. Drake wrote: >> >> Greg, can you elaborate on the SSD + Xlog issue? What type of burn >> through are we talking about? > > > You're burning through flash cells at a multiple of the total WAL write > volume. The sys

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/13 3:06 PM, Joshua D. Drake wrote: Greg, can you elaborate on the SSD + Xlog issue? What type of burn through are we talking about? You're burning through flash cells at a multiple of the total WAL write volume. The system I gave iostat snapshots from upthread (with the Intel 710 hit

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 11:06 AM, Greg Smith wrote: I have some moderately fast SSD based transactional systems that are still using traditional drives with battery-backed cache for the sequential writes of the WAL volume, where the data volume is on Intel 710 disks. WAL writes really burn through flash

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 12:31 PM, David Boreham wrote: Device: r/sw/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdd 2702.80 19.40 19.67 0.1614.91 273.68 71.74 0.37 100.00 sdd 2707.60 13.00 19.53 0.1014.78 276.61 90.34 0.37 100.00 That's an Intel 710 being cr

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 01:06 PM, Greg Smith wrote: There's a corresponding price hit though, and having to provision PCI-E cards is a pain in some systems. Oh, totally. Specialist devices like RAMSAN, FusionIO, Virident, or Whiptail are hideously expensive, even compared to high-end SSDs. I was just

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/13 12:56 PM, Shaun Thomas wrote: Well, you may not be able to make that claim, but I can. While we don't use Intel SSDs, our first-gen FusinoIO cards can deliver about 20k PostgreSQL TPS of our real-world data right off the device before caching effects start boosting the numbers. I've

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/2013 8:18 AM, Greg Smith wrote: They can easily hit that number. Or they can do this: Device: r/sw/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdd 2702.80 19.40 19.67 0.1614.91 273.68 71.74 0.37 100.00 sdd 2707.60 13.00 19.53 0.1014.78 2

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 05/22/2013 08:30 AM, Merlin Moncure wrote: I'm not claiming to work with extremely high transaction rate systems but then again neither are most of the people reading this list. Disk drives are obsolete for database installations. Well, you may not be able to make that claim, but I can. Whi

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/13 11:05 AM, Merlin Moncure wrote: unfortunately, I don't have a s3700 to test with, but based on everything i've seen it looks like it's a mostly solved problem. (for example, see here: http://www.storagereview.com/intel_ssd_dc_s3700_series_enterprise_ssd_review). Tests that drive the

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On Wed, May 22, 2013 at 9:18 AM, Greg Smith wrote: > On 5/22/13 9:30 AM, Merlin Moncure wrote: >> >> That's most certainly *not* the only gain to be had: random read rates >> of large databases (a very important metric for data analysis) can >> easily hit 20k tps. So I'll stand by the figure. > >

Re: [PERFORM] Very slow inner join query Unacceptable latency.

PostgreSQL 9.1.6 on linux Original Message Subject: Re: [PERFORM] Very slow inner join query Unacceptable latency. From: Jaime Casanova Date: Tue, May 21, 2013 2:59 pm To: Freddie Burgess Cc: psql performance list

[PERFORM] Advice on optimizing select/index

Hi, I have a database where one of my tables (Adverts) are requested a LOT. It's a relatively narrow table with 12 columns, but the size is growing pretty rapidly. The table is used i relation to another one called (Car), and in the form of "cars has many adverts". I have indexed the foreign key

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On 5/22/13 9:30 AM, Merlin Moncure wrote: That's most certainly *not* the only gain to be had: random read rates of large databases (a very important metric for data analysis) can easily hit 20k tps. So I'll stand by the figure. They can easily hit that number. Or they can do this: Device:

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

On Tue, May 21, 2013 at 7:19 PM, Greg Smith wrote: > On 5/20/13 6:32 PM, Merlin Moncure wrote: > >> When it comes to databases, particularly in the open source postgres >> world, hard drives are completely obsolete. SSD are a couple of >> orders of magnitude faster and this (while still slow in c