PostgreSQL optimization based on PgTune

[Anth0rx]

Currently the only optimization of the PostgreSQL container is adjusting the amount of maximum connections to the database.

The tool PgTune helps to optimize the following parameters of PostgreSQL:

• max_connections
• shared_buffers
• effective_cache_size
• maintenance_work_mem
• checkpoint_completion_target
• wal_buffers
• default_statistics_target
• random_page_cost
• effective_io_concurrency
• work_mem
• min_wal_size
• max_wal_size
• max_worker_processes
• max_parallel_workers_per_gather
• max_parallel_workers
• max_parallel_maintenance_workers

The implemention for the calculations can be found here.

Maybe some of those considerations are also relevant for the default PostgreSQL container. What do you think?

[szaimen]

Hi, in general I would accept PRs that add some optimizations based on the hardware postgres runs on but this would need to be done automatically since I don't want to add config options for this. Also I just wanted to mention that I will not be able to work on this in the coming months.

So if this can be done in an automated way and maybe keep the logic in the upstream project without forking things, a contribution would be very welcome :)

[Anth0rx]

I think two or maybe three configuration options via environment variables would be needed as not every deployment solely runs the Nexcloud AIO stack. Especially the maximum amount of memory and PostgreSQL workers reserved for AIO should be set externally.

Can you tell me how you came to the max_connections calculation of MEMORY/50+3?

The settings adjustments could be implemented in start.sh of the PostgreSQL container of AIO or which upstream project do you mean?

[szaimen]

Can you tell me how you came to the max_connections calculation of MEMORY/50+3?

It was taken over from here: https://github.com/nextcloud-snap/nextcloud-snap/blob/88b796324e372d411d843ce408fd2228148b0089/src/php/bin/start-php-fpm#L37-L40

The settings adjustments could be implemented in start.sh of the PostgreSQL container of AIO or which upstream project do you mean?

I meant that the calculation should still be done by e.g. this script: https://github.com/le0pard/pgtune/blob/master/assets/selectors/configuration.js that could possibly be added during the container build and put into start.sh in order to base the calculations on it.

[Anth0rx]

It was taken over from here: https://github.com/nextcloud-snap/nextcloud-snap/blob/88b796324e372d411d843ce408fd2228148b0089/src/php/bin/start-php-fpm#L37-L40

The calculation there concerns the maximum php-fpm children. I would assume this is not the same as the maximum database connections. Or does every php-fpm process create one database connection at max? Is the PHP_FPM_MAX_CHILDREN environment variable set in AIO as well?

I meant that the calculation should still be done by e.g. this script: https://github.com/le0pard/pgtune/blob/master/assets/selectors/configuration.js that could possibly be added during the container build and put into start.sh in order to base the calculations on it.

Do you mean creating a build image with the configuration.js file and doing the calculations locally? Maybe that is more complicated than implementing it directly into the start.sh script.

[szaimen]

It was taken over from here: https://github.com/nextcloud-snap/nextcloud-snap/blob/88b796324e372d411d843ce408fd2228148b0089/src/php/bin/start-php-fpm#L37-L40

The calculation there concerns the maximum php-fpm children. I would assume this is not the same as the maximum database connections. Or does every php-fpm process create one database connection at max? Is the PHP_FPM_MAX_CHILDREN environment variable set in AIO as well?

It is adjusted like this in AIO currently:

all-in-one/Containers/postgresql/start.sh

Line 153 in 27d99ef

sed -i "s|^max_connections =.*|max_connections = $MAX_CONNECTIONS|" "/var/lib/postgresql/data/postgresql.conf"

I meant that the calculation should still be done by e.g. this script: https://github.com/le0pard/pgtune/blob/master/assets/selectors/configuration.js that could possibly be added during the container build and put into start.sh in order to base the calculations on it.

Do you mean creating a build image with the configuration.js file and doing the calculations locally? Maybe that is more complicated than implementing it directly into the start.sh script.

Yes, doing the calculations locally would eliminate the need to configure things manually from the outside. My main concern would be that we somehow do not need to fork the logic and can just somehow use the upstream project without having to maintain our own logic going forward.

[Anth0rx]

It is adjusted like this in AIO currently:

all-in-one/Containers/postgresql/start.sh

Line 153 in 27d99ef

sed -i "s|^max_connections =.*|max_connections = $MAX_CONNECTIONS|" "/var/lib/postgresql/data/postgresql.conf"

This setting is regarding the PostgreSQL configuration option max_connections. What I meant was if AIO also sets the PHP_FPM_MAX_CHILDREN environment variable of php-fpm like the snap package does.

Yes, doing the calculations locally would eliminate the need to configure things manually from the outside. My main concern would be that we somehow do not need to fork the logic and can just somehow use the upstream project without having to maintain our own logic going forward.

Okay, I understand but I think using a single JavaScript file, keeping it up to date and make it work with the start.sh script needs more maintenance than simply implementing the calculations directly in bash.

[szaimen]

Btw, on which AIO version are you currently?

Also why do you think that postgres performance optimizations are necessary in AIO and can you proove that with some testing in Nextcloud?

[szaimen]

Probably a good one is also for run in {1..10}; do time php occ files:scan-app-data preview -q; done.

[szaimen]

Probably a good one is also for run in {1..10}; do time php occ files:scan-app-data preview -q; done.

Actually after thinking about this a bit more is this one probably the best for database testing

[Anth0rx]

Thanks for the extensive testing. Here are my stats:

Nextcloud info:

• Nextcloud AIO: v5.2.2 via Portainer
• Folder amount: 4753
• Files amount: 1507

System info

• Operating System: OpenMediaVault 6.3.10-2 (Shaitan)
• Host: HP ProLiant G7 N40L MicroServer

With default settings

bash-5.1$ for run in {1..10}; do time php occ files:scan-app-data preview -q; done

real	0m53.138s
user	0m13.176s
sys	0m10.923s

real	0m53.303s
user	0m13.417s
sys	0m10.880s

real	0m52.348s
user	0m13.172s
sys	0m10.620s

real	0m52.120s
user	0m12.773s
sys	0m10.873s

real	0m53.308s
user	0m13.345s
sys	0m10.920s

real	0m52.974s
user	0m13.206s
sys	0m10.901s

real	0m52.746s
user	0m13.176s
sys	0m10.848s

real	0m52.951s
user	0m13.202s
sys	0m10.876s

real	0m52.924s
user	0m12.936s
sys	0m11.126s

real	0m52.036s
user	0m12.962s
sys	0m10.671s

With PGTune optimization for SSDs

bash-5.1$ for run in {1..10}; do time php occ files:scan-app-data preview -q; done

real	0m51.455s
user	0m12.818s
sys	0m10.486s

real	0m51.501s
user	0m12.643s
sys	0m10.696s

real	0m50.841s
user	0m12.742s
sys	0m10.226s

real	0m51.488s
user	0m12.945s
sys	0m10.362s

real	0m38.374s
user	0m11.111s
sys	0m5.273s

real	0m50.904s
user	0m12.876s
sys	0m10.241s

real	0m51.273s
user	0m12.922s
sys	0m10.298s

real	0m51.277s
user	0m13.079s
sys	0m10.073s

real	0m51.063s
user	0m13.004s
sys	0m10.146s

real	0m51.159s
user	0m12.827s
sys	0m10.304s

With PGTune optimizations for HDDs

Changing "Data Storage" to "HDD" effectively reduces the random_page_cost and effective_io_concurrency settings.

bash-5.1$ for run in {1..10}; do time php occ files:scan-app-data preview -q; done

real	0m39.965s
user	0m11.292s
sys	0m5.810s

real	0m51.218s
user	0m12.730s
sys	0m10.400s

real	0m51.163s
user	0m12.720s
sys	0m10.407s

real	0m51.656s
user	0m13.269s
sys	0m10.049s

real	0m51.369s
user	0m12.951s
sys	0m10.203s

real	0m51.279s
user	0m12.917s
sys	0m10.206s

real	0m51.509s
user	0m13.169s
sys	0m10.072s

real	0m51.865s
user	0m12.844s
sys	0m10.606s

real	0m51.195s
user	0m12.638s
sys	0m10.471s

real	0m51.425s
user	0m12.912s
sys	0m10.301s

Results

The results show that optimizing the settings only results in a speedup of ~1-2 seconds. Based on that it does not seem to be worth optimizing PostgreSQL settings.

Maybe we can extend those benchmarks to other occ commands. @szaimen: Do you have other commands in mind worth testing?

[szaimen]

The results show that optimizing the settings only results in a speedup of ~1-2 seconds. Based on that it does not seem to be worth optimizing PostgreSQL settings.

I agree.

Maybe we can extend those benchmarks to other occ commands. @szaimen: Do you have other commands in mind worth testing?

Possibly you could try php occ files:scan --all -q if it does not take too long. Otherwise you could try it with a specific user like so php occ files:scan <userid> -q.

These are the two most important commands that currently come into my mind that require fast database operation. So if the files:scan command also does not produce big differences, I would vote for not implementing this.

[szaimen]

Leaving this here in order to find it more easily later:

To truly benchmark end-to-end performance improvements I would need means to test from Nextcloud directly. Do you know of any way to accomplish this?

Unfortunately we currently do not have a way to test the database performance.

However I could think about something like:

1. First disable all optional addons in the AIO interface and possibly further Nextcloud apps that might limit performance measurments. Additonally stop the apache container in order to make sure that nothing is written to Nextcloud in the meantime. Then:

# Go into the container
docker exec --user www-data -it nextcloud-aio-nextcloud bash

# Execute files preview scan 10 times and measure performance
for run in {1..10}; do time php occ files:scan-app-data preview -q; done

# exit the container
exit

Then you could do the same above after adjusting the postgres config by hand to fit the recommendeed config of PgTune and restarting the database container.

Finally post all the output here and we can check if it really improves things and how much. Does this sound like a plan to you? :)

As a sidenote: Of course you could also measure any other occ command in this way (like e.g. time php occ files:scan -q --all) but scanning preview files is probably still one of the main commands of Nextcloud that needs database operations.