Hi,
Please pardon me if this question is already answered in the documentation,
Wiki, or the mailing list archive. The problem is, that I don't know the
exact term to search for - I've tried searching for "linear scalability"
and "concurrency vs performance" but didn't find what I was looking for.
an 24, 2019 at 12:46 AM Saurabh Nanda
wrote:
> Hi,
>
> Please pardon me if this question is already answered in the
> documentation, Wiki, or the mailing list archive. The problem is, that I
> don't know the exact term to search for - I've tried searching for "linear
Hi Jeff,
Thank you for replying.
> wal_sync_method=fsync
>
>
Why this change?
Actually, I re-checked and noticed that this config section was left to
it's default values, which is the following. Since the commented line said
`wal_sync_method = fsync`, I _assumed_ that's the default value.
>
>
> PGOPTIONS="-c synchronous_commit=off" pgbench -T 3600 -P 10
>
>
> I am currently running all my benchmarks with synchronous_commit=off and
> will get back with my findings.
>
It seems that PGOPTIONS="-c synchronous_commit=off" has a significant
impact. However, I still can not understa
> Do you know which of the settings is causing lower TPS ?
> I suggest to check shared_buffers.
>
I'm trying to find this, but it's taking a lot of time in re-running the
benchmarks changing one config setting at a time. Thanks for the tip
related to shared_buffers.
>
> If you haven't done it,
>
> It is usually not acceptable to run applications with
> synchronous_commit=off, so once you have identified that the bottleneck is
> in implementing synchronous_commit=on, you probably need to take a deep
> dive into your hardware to figure out why it isn't performing the way you
> need/want/ex
Here's the previous table again -- trying to prevent the wrapping.
+++-+
|| synchronous_commit=on | sync_commit=off |
+++
All this benchmarking has led me to a philosophical question, why does PG
need shared_buffers in the first place? What's wrong with letting the OS do
the caching/buffering? Isn't it optimised for this kind of stuff?
An update. It seems (to my untrained eye) that something is wrong with the
second SSD in the RAID configuration. Here's my question on serverfault
related to what I saw with iostat -
https://serverfault.com/questions/951096/difference-in-utilisation-reported-by-iostat-for-two-identical-disks-in-rai
>
> You should probably include the detailed hardware you are working on -
> especially for the SSD, the model can have a big impact, as well as its
> wear.
>
What's the best tool to get meaningful information for SSD drives?
-- Saurabh.
>
> smartctl is a good start
>
Here's the output of `smartctl --xall /dev/sda` --
https://gist.github.com/saurabhnanda/ec3c95c1eb3896b3efe55181e7c78dde
I've disabled RAID so /dev/sda is the only disk which is being currently
used.
I'm still seeing very weird numbers. There seems to be absolutely
I've disabled transpare huge-pages and enabled huge_pages as given below.
Let's see what happens. (I feel like a monkey pressing random buttons
trying to turn a light bulb on... and I'm sure the monkey would've had it
easier!)
AnonHugePages: 0 kB
ShmemHugePages:0 kB
HugePages_Total
>
> Do you know which of the settings is causing lower TPS ?
>
> I suggest to check shared_buffers.
>
> If you haven't done it, disabling THP and KSM can resolve performance
> issues,
> esp. with large RAM like shared_buffers, at least with older kernels.
>
> https://www.postgresql.org/message-id/2
Yet another update:
a) I've tried everything with me EX41-SSD server on Hetzner and nothing is
increasing the performance over & above the default configuration.
b) I tried commissioning a new EX41-SSD server and was able to replicate
the same pathetic performance numbers.
c) I tried another cloud
> c) I tried another cloud hosting provider (E2E Networks) and just the raw
> performance numbers (with default configuration) are blowing Hetzner out of
> the water.
>
I noticed that on E2E, the root filesystem is mounted with the following
options:
/dev/xvda on / type ext4
(rw,noatime,nodi
Hi,
I'm going crazy trying to optimise my Postgres config for a production
setting [1] Once I realised random changes weren't getting my anywhere, I
finally purchased PostgreSQL 10 - Higher Performance [2] and understood the
impact of shared_buffers.
IIUC, shared_buffers won't have any significan
>
> That is likely correct, but the data will likely be stored in the OS file
> cache, so reading it from there will still be pretty fast.
>
Right -- but increasing shared_buffers won't increase my TPS, right? Btw, I
just realised that irrespective of shared_buffers, my entire DB is already
in mem
Run https://github.com/n-st/nench and benchmark the underlying vps first.
On Tue 29 Jan, 2019, 11:59 PM Bob Jolliffe The following is output from analyzing a simple query on a table of
> 13436 rows on postgresql 10, ubuntu 18.04.
>
> explain analyze select * from chart order by name;
>
perf tuning
works and what impact shared_buffers has on perf. I'll just run my DB in
production with default settings and hope no one complains about the system
being slow!
-- Saurabh.
On Tue, Jan 29, 2019 at 11:40 PM Saurabh Nanda
wrote:
> That is likely correct, but the data will likely
19 matches
Mail list logo
