
Simple script to analyse your PostgreSQL database config, give tuning advice - LinuxBender
https://github.com/jfcoz/postgresqltuner
======
etaioinshrdlu
You know, PostGres is a great database system, but its core data structure is
prone to awful performance degradation if you have a lot of out of date rows,
and sometimes the autovacuumer can't keep up.

There is something to be said for simpler B-Tree style databases like MySQL
that are harder to screw up!

Also perhaps there could be an optional mode for Postgres that did something
to the effect of immediate vacuuming for all modification statements.
Collecting garbage as soon as its made. At the cost of slower statements, but
no long term degradation. More predictable performance.

~~~
Tostino
It's not that simple... The whole point of keeping dead rows around is so the
mvcc snapshot can show a consistent view of the database for any user within a
transaction. If you instantly cleaned up those dead rows, other users suddenly
don't have a consistent view of data, or have data disappear entirely on them.

The other way this is handled is to move dead rows to an undo log, and the min
heap only contains the current version of any specific row, and a pointer to
where to find the dead rows in the undo log.

The above approach keeps your main heap from bloating when updates and deletes
happen, but it causes transactions which reference the old version of the rows
to be slower, as well as slowing down rollbacks.

This is exactly what the zHeap project is doing along with the pluggable
storage API.

~~~
dap
> The above approach keeps your main heap from bloating when updates and
> deletes happen, but it causes transactions which reference the old version
> of the rows to be slower, as well as slowing down rollbacks.

I think that's consistent the parent's goal (and mine) of allowing ongoing
requests to be slower in exchange for not having to pay a major cost later to
vacuum.

~~~
Tostino
Yup, I meant for it to be consistent with the OPs concerns. I just wanted to
point out that "just paying for it up front" also has other trade-offs. For a
lot of use cases, the way PG went ends up better for performance than using an
undo log (after all the performance opimizations PG has put in place like
freezing pages, etc). And for some use cases an undo log is by far better, and
more consistent.

~~~
dap
I can see that. I've really only worked with a few workloads where this
behavior was quite bad. (In particular, the out-of-the-box policy to vacuum
when the dead tuple fraction reaches 20% means that for an INSERT-mostly
workload with a bit of UPDATE/DELETE, the vacuums get further and further
apart and take longer and longer.) In the worst one I've worked on, the
effects take several days or weeks to show up, but they degrade system
throughput by 50-80% (!) for days or weeks on end.

The experience makes me wonder if the database is designed primarily using
short-term, modest-scale workloads (which are much easier to test) and not
behavior that only shows up after several weeks of a continuous, heavy
workload. It obviously works for a lot of users.

------
sharadov
I use PGTune
[https://pgtune.leopard.in.ua/#/](https://pgtune.leopard.in.ua/#/) It spits
out optimal values based on your system configuration

------
tschellenbach
Interesting how this is tied to the PostgreSQL business model. Consultancy is
how the ecosystem funds development for PG. So they have no real reason to
automatically spot performance issues like for instance MongoDB Atlas does.
Curious side effect of the business model. (I much prefer PG over MongoDB, but
the automatic index suggestion thingie is pretty slick)

------
Bishonen88
Anything similar with python perhaps?

~~~
sandGorgon
It works with docker, so you don't have to worry about having perl.

> _docker run -it --rm jfcoz /postgresqltuner --host=dbhost --user=username
> --password=pass --database=testdb_

~~~
claudiawerner
Is not having Perl such a worry? I'd imagine most systems that use this also
use Git and that requires Perl, and I'm not aware of any GNU/Linux distro that
doens't come with Perl (5, of course) pre-installed. While we're speaking of
Perl, I just want to say how amazing Perl 6 is, especially compared to Perl 5,
in every regard - from syntax to arrays/lists to the quality of the
(admittedly small range of) libraries.

~~~
Ultimatt
+1 for Perl 6 shout out. Also there are increasingly nice things for use with
Postgres like
[https://github.com/CurtTilmes/perl6-dbpg](https://github.com/CurtTilmes/perl6-dbpg)
with [https://github.com/FCO/Red](https://github.com/FCO/Red)

------
josteink
Microsoft SQL Server has had this built in since “forever”, but instead of
looking at your config, it does its analysis based on workload trace,
analyzing how that workload performs and what particular queries are having
sub-par performance.

Thus the “tuning” matches your actual use.

If there can be done guaranteed improvements to your DBs performance based on
static analysis of the config-alone, why on earth is that not the standard
config? That seems like a no-brainer.

~~~
endorphone
The SQL Server tuning advisor has historically been terrible for any database
of any complexity at all. It imposes an enormous load, and the results are
usually contradictory and often very sub-optimal if not detrimental. Works
great for a hello world style database, though.

There was a post earlier
([https://news.ycombinator.com/item?id=19422554](https://news.ycombinator.com/item?id=19422554))
that I assume inspired this post and it was someone who benefited by
correcting the DBMS' very wrong assumptions about the cost of random IO, and
this was always the Achilles heel of SQL Server as well with any sort of fast
storage -- it absolutely refused to accept that anything could be faster than
about 150 IOPS, and would try to brute force through with terrible approaches.
In this case the guy could just change the configuration and it would properly
calculate query plan costs and execute accordingly, where SQL Server, to my
knowledge, _still_ doesn't let you change this costing, nor does it
dynamically determine it.

~~~
ximeng
On large databases, the Sql Server tool simply failed to complete its analysis
for me.

------
tschellenbach
PG tune is the standard pretty much, not sure about this new tool. In case
you're not familiar with PG tuning... Here's the TL/DR, don't even think about
going into production without running PG tune. The default postgres settings
are not optimized, assuming you have any decent load on the system this will
be a problem.

------
GordonS
By chance, I discovered pgTune just yesterday. Would be interested to know how
this differs?

~~~
contrahax
Based on scanning the README this looks at your remote DB instead of a
configuration file.

------
brightball
How's this different from
[https://github.com/le0pard/pgtune](https://github.com/le0pard/pgtune)

~~~
outworlder
They seem to be completely different.

pgtune writes config given some parameters and doesn't actually look at
running PG instances.

------
ntc
.

------
ddebernardy
The real odd thing here is that `work_mem` (and to a lesser degree
`maintenance_work_mem`) is still low enough out of the box that a script got
written to highlight that it's too low. Increasing it is the first step one
probably ought to look into when installing Postgres.

~~~
anarazel
Huge numbers of postgres installations are on small machines and/or machines
shared with other software. And whether you want a large work_mem _hugely_
depends on whether you want to run an OLTP workload with a few hundred
connections, or an analytics workload with like 2. It ain't as simple as you
paint it.

------
atonse
Love this idea, but is this better done as part of, say, an existing linting
framework? (update: oops, I thought this was about reading from your pg
configuration file, not the live DB)

You could make use of things like IDE syntax highlighting, and in some cases,
auto-fixing.

Glad to see they have a way to run it with docker, because I have too many bad
memories of missing perl modules when trying to run perl scripts in the early
2000s (but that also tells you how outdated I am from perl-world)

