Hi there,

I wrote a similar tool do suggest tuning parameters and also based on pgtune.

Take a look at http://pgconfig.org

Do you wanna help me? or perhaps want talk about it?

Thanks

Is your feature request related to a problem? Please describe.
A performance improvement is seen when using hugepages, but planning for how many to use is difficult.

Describe the solution you'd like
There are many articles out there about the performance improvement of using huge_pages with postgresql

Example: https://www.percona.com/blog/why-linux-hugepages-are-super-important-for-database-servers-a-case-with-postgresql/
another example: https://www.enterprisedb.com/blog/improving-postgresql-performance-without-making-changes-postgresql

Trying to estimate and plan how many to allocate is difficult. We use the pgtune website to help us estimate and template the configs for different sized instances of our database, and it would be great if it could give us a guideline of how many hugepages tor reserve as well. (it appears to be shared memory, plus connections, plus some other factors to consider)

Describe alternatives you've considered
I have looked at individual systems, to see what the tuning should be, but they are often being used differently, so not the same number comes up

I do understand this is not part of the postgresql.conf file, and might be out of the scope of this tool.

Based on this article it would be useful to ask for the disk (HDD, SSD,...) and set the random_page_cost, seq_page_cost accordingly.

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

Hey Leopard,

Not sure you correctly count work_mem when max_worker_processes used.

From your formula, as I understood, you set:
max_parallel_workers_per_gather = max_worker_processes / 2, so for example,
if I set 16 workers, it will count 8 parallel workers.

So, for example, when I don't set numbers of CPUs, in results you propose to set work_mem to 100MB (for example). But if I set 4 CPU what mean in your script:

max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

you will show work_mem = 50MB, because you count it as (work_mem / max_parallel_workers_per_gather)

In other words with max_parallel_workers_per_gather = 2 in your formula you decrease work_mem for 50%, but in Postgresql max_parallel_workers_per_gather = 2 is default value, so with or without this value in your script it must show the same work_mem.

Would it please be possible to confirm that the max_wal_size settings are correct? I am getting low values for "desktop" scenarios, e.g. as opposed to the default value of 1GB:
e.g.

# DB Version: 10
# OS Type: linux
# DB Type: desktop
# Total Memory (RAM): 30 GB
# Number of Connections: 50

max_connections = 50
shared_buffers = 1920MB
effective_cache_size = 7680MB
work_mem = 32MB
maintenance_work_mem = 1920MB
min_wal_size = 100MB
max_wal_size = 100MB
checkpoint_completion_target = 0.5
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6

Basic support will just be adding 12 to the version drop down but we may also need some tweaks to the settings / configuration based on 12. (I don't know if there are any particular changes)

(Many thanks for creating PGTune!)

Is your feature request related to a problem? Please describe.
I'm running postgresql off a NVME Flash drive and it's starting to be really common.

Describe the solution you'd like
Add : Data Storage Type - "NVME SSD" under Data Store

Additional context
Which configuration should i look into if i would like to make use of my high IOPS nvme drive.

• High Read up to 3500MB/s
• High IOPS - 600,000 IOPS

Which settings should I look into if i would like to reduce the number of writes done to the disk as flash storage life span actually depends on amount of writes done to the disk.

bit of a random question. I just tried out pgtune and it's awesome. Made a large performance improvement for me!

Would it be possible to point me to the algorithms that you use to come up with the config? I'd like to incorporate something similar into my installer software to automate some postgres config settings.

Hello,

I put following information on the web https://pgtune.leopard.in.ua/ and suggested work_mem value seems to be out of range for subject version.

DB version: 9.6
OS Type: Linux/OS X
DB Type: Desktop applications
Total Memory: 1GB
Number of CPUs: 1
Number of connections: 500
Hard drive type: SSD

Suggested SQL script works except below one:

ALTER SYSTEM SET
 work_mem = '54kB'
> ERROR:  54 is outside the valid range for parameter "work_mem" (64 .. 2147483647)

i've experienced an issue where system has more than 16 gb of ram the script is failing because the memoryvalue is not in the valid range.
according to line 143 i found that : "// Cap maintenance RAM at 2GB on servers with lots of memory"
by checking it further i found that the value that i passed to total memory was still in GB and not converted with the -1MB (line 147)
i fixed the issue by adding "=" operator at line 145.
the new value looks like this: if (maintenanceWorkMemValue >= memoryLimit) { :

Running postgresql 10 in Debian 9 (stretch), applying the PgTune parameters with these parameters prevents the server from restarting:

# DB Version: 10
# OS Type: linux
# DB Type: mixed
# Total Memory (RAM): 7 GB
# Number of Connections: 8
max_connections = 8

The error is:

postgres: max_wal_senders must be less than max_connections         
pg_ctl: could not start server

Default value in /etc/postgresql/10/main/postgresql.conf is 10:
#max_wal_senders = 10 # max number of walsender processes:
Suggested fix (?): if max_connections <10 then set max_wal_senders =max_connections
(works for me so far; unsure whether this is the best performance-wise).
Cheers,
Mayeul

Hi,

First of all, thank for this very usefull tool.

When choosing Windows for the OS, the value for the Shared_buffers is always 512Mb.

From version 9.6 of PG, this rule is not valid anymore i think (verified with a PG expert), and the rule of 1/4 of the RAM should be applied.

What do you think ?

Thanks.

please create the same in odoo configuration.
limit_memory_hard ?
limit_memory_soft ?
limit_time_cpu ?
limit_time_real ?
workers ?

Describe the bug
A clear and concise description of what the bug is.
If you set CPUs=1 for Postgres > 10, you get a max_parallel_workers_per_gather of 2. If you set CPU 2 in the same case, the logic at https://github.com/le0pard/pgtune/blob/master/assets/selectors/configuration.js#L269 doesn't kick in, and you end up with a max_parallel_workers_per_gather of 2.

This is because pgtune assumes the parallel defaults for any case where there are less than 2 CPUs. However, the defaults are more aggressive than the pgtune calculations when CPU == 2 (for example).

The question is: should the minimum settings for parallel workers be the default settings? I don't think so. The minimum should probably be what the computation gets us in the parallelSettings selector.

https://www.postgresql.org/docs/13/release-13.html#id-1.11.6.16.4

Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro)

Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:

SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);

Applying that formula, 2 would become 3, 200 -> 1176, 300 -> 1885 (these can of course be rounded).

But, I don't really understand the change in version 13, so maybe they don't need to be changed?

Is your feature request related to a problem? Please describe.

I noticed AWS RDS has variables like DBInstanceClassMemory and DBInstanceVCPU for memory and CPU values respectively (Official Docs).
It would be nice to use the values for some of the config parameters given that the parameters are calculated based on simple formulas.

ex. Setting the effective_cache_size to 75% of RAM would be {DBInstanceClassMemory/98304}

Describe the solution you'd like

Have an option in the postgresql.conf page to "show as AWS RDS values" where the parameters show the formula using RDS variables and not the raw numbers.

Describe alternatives you've considered

Simpler option may be to just how the parameter values in their expected units (ex. shared_buffers in 8kb units, not in Gb units).

Additional Context

Some other examples

• shared_buffers: {DBInstanceClassMemory/32768} (25% of memory)
• effective_cache_size: {DBInstanceClassMemory/98304} (75% of memory)
• maintenance_work_mem: LEAST({DBInstanceClassMemory/ 8 / 8}, 262144) (Or simply /64 instead of /8 /8)
• max_worker_processes: GREATEST(${DBInstanceVCPU*2},8)
• max_parallel_workers: GREATEST(${DBInstanceVCPU/2},8)
• max_parallel_maintenance_workers: LEAST(${DBInstanceVCPU/2},4)

I calculated the DB Instance Percentages using the following formula: ((1024 * 1024 * Percentage / 100) / 8)
Where:

• 1024 * 1024 converts GB to kB.
• Percentage/100 is the percentage
• /8 b/c Postgres memory values works with blocks of 8kB

:(

Hi, github pages is returning 404 for the site.

This is not an issue, more of a heads up. I'm using PostgreSQL 11 installed using Homebrew on macOS Mojave. One of the parameters that was generated by pgtune at your website is:

ALTER SYSTEM SET effective_io_concurrency = '200';

It returned the following error:

ERROR: invalid value for parameter "effective_io_concurrency": 200 DETAIL: effective_io_concurrency must be set to 0 on platforms that lack posix_fadvise()

I searched for this error prior to posting this. effective_io_concurrency is presumably not supported on macOS.

thanks!

Is there already a pg 15 settings tuning? Or would 14 work too?

Is it possible to add some recommended setings based on number of cores and memory for the parrallell options in 9.6

Hi Alexey Vasiliev,
Can you support "optimal for very high memory systems"?
memory > 3000G
Regards,
Son

Hi,

simple and great tool, i love it.

A little suggestion, when you choose a configuration, it would be nice to show it as a commentary in the postgreSql settings.
So we can remember which options was selected.

Like this :

# DB Version : 9.4
# OS Type : Linux/OS X
# DB Type : Web applications
# Total Memory (RAM) : 4 GB
# Number of Connections : 100

max_connections = 100
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 10485kB
maintenance_work_mem = 256MB
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

Thanks, Thomas

Thanks for keeping this very useful tool going!

I noticed that
max_connections = 10
The system wouldn't restart
max_connections = 11
and above worked.

Thanks.

Hi:
Is there a plan to implement an api?
The idea is to be able to use pgtune in an automated way from an ansible playbook for example.

Describe differences between DB Types in tooltip

Has there been any thoughts about supporting values for 9.6 Asynchronous Behavior settings?

Hi! Why does this happen?...

Cheers!

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

See http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=88e982302684246e8af785e78a467ac37c76dee9

Describe the bug
A clear and concise description of what the bug is.

To Reproduce
Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

I'm sorry... I'm not good enough in Postgres to comment. But, below comment is found in HN:

The suggestions pgtune makes are really bad. It sets max_wal_size way too low, max_parallel_workers_per_gather to ridiculously high values, shared_buffers way too low, the work_mem settings make no sense whatsoever.

https://news.ycombinator.com/item?id=22219283

If anything can be improved based on above comment, it will be good.

After continuous trial and error method, I have found out that Windows has a limit of 2GB for maintenance work memory. Even setting it to 2048MB causes Postgres not to start. So I had to change it to 2047MB. Please modify the application to lower the recommended value for windows configurations.

Possible linked SO Answer,
https://stackoverflow.com/a/33997819

?

It seems, that it is bad idea and you get much better efficiency putting small value like 2. Why is that?

I am running postgres in a container but I don't know what to set the container limits exactly to.

I'd like to see that pg_tune also recommends something for the shm-size.

Inspiration:

• https://stackoverflow.com/a/65731661
• https://blog.hasanbul.li/2021/03/03/changing-shm-size-for-postgresql-in-a-docker-container/

This might be out of scope, but I think it would be very beneficial to people running it in a container.
I am also not sure yet on how to calculate the best value for this yet.

Is this program still available for the apt package manager? I am new to programming and Linux and cannot figure out how to install this package on my Debian 12 system (Raspbian). apt install pgtune returns Unable to locate package pgtune.

thanks in advance

Hi,
thx for great tool.

After postgresql 9.4 you can use ALTER SYSTEM SET to change individual options. IMO it will be good that those statements are also generated. This way you can very easy change configuration.

So that you get something like this:

ALTER SYSTEM SET max_connections = '16';
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET checkpoint_completion_target = '0.7';
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET default_statistics_target = '100';

br

Specifying 'Windows' as the 'OS Type' still provides 'effective_io_concurrency' as a setting to change in the postgresql.conf file, even though it's not settable on non-Posix systems. Adding in the recommended setting causes the Postgres service to not start due to conf errors:

LOG:  parameter "effective_io_concurrency" cannot be changed
2018-03-02 10:43:16 NZDT FATAL:  configuration file "C:/postgres/postgres9.4/data/postgresql.conf" contains errors

The SSD and HDD values for randomPageCost and effectiveIoConcurrency seem to be swapped.