Re: [PERFORM] Dissuade the use of exclusion constraint index

[Dave Cramer]

Adam,

I think the first thing to do is to make hackers aware of the specifics of
which indexes are being used etc so that the planner could be taught how to
use better ones.

Self contained examples do wonders

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 11 April 2018 at 01:59, Adam Brusselback 
wrote:

> Just wondering if anyone has any thoughts on what I can do to alleviate
> this issue?
>
> I'll kinda at a loss as to what to try to tweak for this.
>

Re: [PERFORM] Dissuade the use of exclusion constraint index

[Adam Brusselback]

> Self contained examples do wonders
Good point, will work on that and post once I have something usable.

Re: Latest advice on SSD?

[Mark Kirkwood]

The 512 Gb model is big enough that the SLC cache and performance is 
gonna be ok. What would worry me is the lifetime: individual 512 Gb 850 
EVOs are rated at 150 Tb over 5 years. Compare that to the Intel S3710 - 
400 Gb is rated at 8 Pb over 5 years. These drives are fast enough so 
that you *might* write more than 4x 150 = 600 Tb over 5 years...



In addition - Samsung are real cagey about the power loss reliability of 
these drives - I suspect that if you do lose power unexpectedly then 
data corruption will result (no capacitors to keep RAM cache in sync).



regards

Mark


On 11/04/18 13:39, Matthew Hall wrote:

The most critical bit of advice I've found is setting this preference:

https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0

I'm using 4 512GB Samsung 850 EVOs in a hardware RAID 10 on a 1U 
server with about 144 GB RAM and 8 Xeon cores. I usually burn up CPU 
more than I burn up disks or RAM as compared to using magnetic where I 
had horrible IO wait percentages, so it seems to be performing quite 
well so far.


Matthew Hall

On Apr 9, 2018, at 7:36 PM, Craig James > wrote:


One of our four "big iron" (spinning disks) servers went belly up 
today. (Thanks, Postgres and pgbackrest! Easy recovery.) We're 
planning to move to a cloud service at the end of the year, so bad 
timing on this. We didn't want to buy any more hardware, but now it 
looks like we have to.


I followed the discussions about SSD drives when they were first 
becoming mainstream; at that time, the Intel devices were king. Can 
anyone recommend what's a good SSD configuration these days? I don't 
think we want to buy a new server with spinning disks.


We're replacing:
  8 core (Intel)
48GB memory
  12-drive 7200 RPM 500GB
     RAID1 (2 disks, OS and WAL log)
     RAID10 (8 disks, postgres data dir)
     2 spares
  Ubuntu 16.04
  Postgres 9.6

The current system peaks at about 7000 TPS from pgbench.

Our system is a mix of non-transactional searching (customers) and 
transactional data loading (us).


Thanks!
Craig

--
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-