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
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;
>
>
> 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
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
> 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
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
>
> 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
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
>
> 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
>
> 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.
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
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?
Here's the previous table again -- trying to prevent the wrapping.
+++-+
|| synchronous_commit=on | sync_commit=off |
+++
>
> 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
> 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,
>
>
> 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
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.
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,
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.
19 matches
Mail list logo
