12 ms·
Simple script to analyse your PostgreSQL database config, give tuning advice
- atonse 8y agoLove 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)
- brightball 8y agoHow's this different from https://github.com/le0pard/pgtune https://github.com/le0pard/pgtune
- outworlder 8y agoThey seem to be completely different. pgtune writes config given some parameters and doesn't actually look at running PG instances.
- Bishonen88 8y agoAnything similar with python perhaps?
- sandGorgon 8y agoIt 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
- qaq 8y agoYou gonna pull a random image into prod and give it access to prod db :)?
- indigo945 8y agoIt's no more a "random image" than the script itself is a random software.
- qaq 8y agoit's a lot easier to audit a script
- dalore 8y agoDockerfile is a script of sorts too you can audit that
- semiquaver 8y agoNothing guarantees the image in docker hub has anything to do with the Dockerfile or this repo. This is a valid concern.
- dalore 8y agoExcept it does, docker hub builds it with that Dockerfile.
- semiquaver 8y agoDocker hub allows any user with the appropriate permissions to `docker push` any image they want.
- flukus 8y agoIt is just a perl script (in this case), just one that needs 45MB of baggage.
- zbuttram 8y agoAlso the Dockerfile seems to be in the repo. Looks like it just installs some apt packages and adds the script file: https://github.com/jfcoz/postgresqltuner/blob/master/Dockerfile https://github.com/jfcoz/postgresqltuner/blob/master/Dockerf...
- claudiawerner 8y agoIs 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.
- grafelic 8y agoIf you for some reason can not use docker and don't want to pollute your system globally with cpan packages, you can use a locallib install instead. CentOS example: sudo yum -y install postgresql-devel sudo useradd -d /home/pgsqltun -m -s /bin/bash -c"pgsql tuner user" psqltun sudo su - psqltun wget -O- https://cpanmin.us | perl - -l $HOME/perl5 App::cpanminus local::lib echo 'eval `perl -I $HOME/perl5/lib/perl5 -Mlocal::lib`' >> $HOME/.bash_profile echo 'export MANPATH=$HOME/perl5/man:$MANPATH' >> $HOME/.bash_profile . .bash_profile cpanm DBD::Pg <install postgresqltuner.pl>
- deleted 8y ago[deleted]
- GordonS 8y agoBy chance, I discovered pgTune just yesterday. Would be interested to know how this differs?
- contrahax 8y agoBased on scanning the README this looks at your remote DB instead of a configuration file.
- josteink 8y agoMicrosoft 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 8y agoThe 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 8y agoOn large databases, the Sql Server tool simply failed to complete its analysis for me.
- atlasduo 8y agoStandard config and actual use are very different things. The first one implies adequate performance for most use cases, while actual use implies adequate performance considering current use case. This tool seems to be analyzing current use case.
- Twirrim 8y ago
- rc_kas 8y agoComment to remind myself to look at this later.
- MeltySmelty 8y agoBookmarks work pretty great, too.
- etaioinshrdlu 8y agoYou 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 8y agoIt'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 8y ago> 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 8y agoYup, 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.
- ddebernardy 8y agoThe 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 8y agoHuge 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.
- sharadov 8y agoI use PGTune https://pgtune.leopard.in.ua/#/ https://pgtune.leopard.in.ua/#/ It spits out optimal values based on your system configuration
- tschellenbach 8y agoPG 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.
- tschellenbach 8y agoInteresting 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)
- ntc 8y ago.