Dedicated PostgreSQL Server (performance issues)

[RobiWan]

Hi,
Is there anyone here running PostgreSQL Server on a dedicated machine running Linux or Windows?
I have performance problems with certain actions. Especially when I run a script. What takes a few seconds with SQLite, takes 10-15 minutes with PostgreSQL.

[aaronj]

I use PostgreSQL on a dedicated Linux system to host Photo Supreme.
I have over 340,000 items in my catalog and I certainly don't have any operation I've encountered that takes 10-15 minutes.

If you can describe something specific that is causing a 10-15 minute delay I can test and see what results I get with the same action.

In case you hadn't seen it, I will note that there is documentation that includes some suggested configuration settings for PostgreSQL here (replaced slashes with spaces due to link posting restriction):
https: manualsu.idimager.com version7 QuickInstall-PostgreSQL14-PhotoSupreme.pdf

If you have even a modest amount of RAM on the system, the shared_buffers setting in particular is likely to be very low if it is still set to a default value. Other of the recommended settings are likely not set at all/commented off if you are using the default config.

Antivirus can absolutely kill PostgreSQL and cause major performance issues absent appropriate exceptions and the need for appropriate exceptions is also noted in the doc referenced above.

My only performance-related complaint is related to users with restricted views ('View on the Catalog' setting set to "Restrict Catalog View"), which causes various actions to be slowed down substantially for those view-restricted users since every query Photo Supreme runs on their behalf layers on an additional permission query to filter by what they are allowed to see. This made some things like populating counts for categories with many layers of nested labels (deeper nesting increases complexity) take several times longer than for unrestricted users, but nothing anywhere close to pushing completion times as high as the 10-15 minute range. As long as you aren't using a user with "Restrict Catalog View" set this shouldn't impact you, but certainly turn that off if it is on but not needed.

[RobiWan]

Thank you for your answer.
My machine(s) are VM's running under Linux KVM
Both Linux and Windows have the following parameters:

Code: Select all

RAM - 48GB
CPU - 8 Cores
HDD - 500GB dedicated SSD 

Everything runs so far fast and satisfactory but as soon as I start a script, it takes several minutes until the result is there.
Here is an example. In one file I have entered some images

Code: Select all

C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\22\_rr_7512.psd
C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\22\_rr_7512.dng
C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\22\_rr_7512.cr2
C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\22\_rr_7513.dng
C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\22\_rr_7513.cr2
C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\22\_rr_7516.dng
C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\22\_rr_7516.cr2
C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\26\_rr_8603.dng
C:\DSLR\DSLR-RAW\ExtHDD 2504\EOS5DMKIII\2016\04\26\_rr_8603.cr2
C:\DSLR\DSLR-RAW\ExtHDD 2507\EOS R5\2022\02\07\_rr00413.dng
C:\DSLR\DSLR-RAW\ExtHDD 2507\EOS R5\2022\02\07\_rr00413.cr3

Then I call the script via the Scripter: Collecti Files from File.psc

I will try now to rebuild SearchData, maybe this helps.

[aaronj]

From experience I can say system specs certainly should be capable of running a perfectly functional PostgreSQL instance if properly configured (I'm doing fine with 32GB RAM, 2 CPUs/8 cores total for PostgreSQL hosting and network file share hosting but running directly on the physical Linux system), but note that it is the postgres.conf configuration file for the instance of PostgreSQL that I was referring to which will likely have some poorly optimized default settings for the server regardless of whether the host system's specs are sufficient. PostgreSQL will never utilize all the RAM in the world if you run it with the default configuration created when it is installed.

I ran a similar set of 11 full path references (i.e. the same number in your list) into the same script and I got the result list back in 24 seconds.

This list of 11 items took 24 seconds for me to return with the script:

Code: Select all

\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_007_M1.jpg
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_006_M1.jpg
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_004_M1.jpg
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_003_M1.jpg
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_002_E2_M1.jpg
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_001_M1.jpg
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_001_E1.tif
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_001_E3.tif
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_010.tif
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_011_E1.tif
\\mediahost2\content_share_3\Scanned\Images\1_Negatives\2_MediumFormat_Color\MMFC_011_M1.jpg

I watched my PostgreSQL server's memory utilization when the script ran, and it didn't go up appreciably, meaning any substantially sized index it utilized was likely already held in memory. Some of the potential key indexes for the search tables are between 0.6 and 2.1 GB each in my database with the three most-scanned indexes associated with search tables totaling 4.3 GB in size, so their performance would be substantially impacted if I were using the default configuration that would include memory allocations too small to allow those indexes to be held in memory since that would make all queries much more heavily reliant on disk IO and many times slower as a result.

There always could be some other specific issue with what you are seeing, but unless the PostgreSQL servers postgres.conf is changed from the default it was installed with (and the server restarted after the change, of course) that is the logical culprit.

[RobiWan]

Here is my current running config

Code: Select all

data_directory = '/var/lib/postgresql/14/main'
hba_file = '/etc/postgresql/14/main/pg_hba.conf'
ident_file = '/etc/postgresql/14/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/14-main.pid'
listen_addresses = 'localhost,xenpsql001.rjap.de'
port = 5432
max_connections = 200
unix_socket_directories = '/var/run/postgresql'
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 12GB
work_mem = 48MB
maintenance_work_mem = 1GB
dynamic_shared_memory_type = posix
temp_file_limit = -1
max_files_per_process = 1000
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
synchronous_commit = off
wal_buffers = 64MB
checkpoint_timeout = 30min
max_wal_size = 32GB
min_wal_size = 16GB
log_temp_files = 0
effective_cache_size = 24GB
log_line_prefix = '%m [%p] %q%u@%d '
log_timezone = 'localtime'
cluster_name = '14/main'
stats_temp_directory = '/var/run/postgresql/14-main.pg_stat_tmp'
autovacuum = no
datestyle = 'iso, dmy'
timezone = 'localtime'
lc_messages = 'de_DE.UTF-8'
lc_monetary = 'de_DE.UTF-8'
lc_numeric = 'de_DE.UTF-8'
lc_time = 'de_DE.UTF-8'
default_text_search_config = 'pg_catalog.german

[aaronj]

Unless you have a massive catalog I wouldn't expect those settings to be a problem in terms of being too low.

Regardless of how it is triggered, the operation hitting some bottleneck related to disk IO is still the likely suspect for any system, but it is especially so since you are running in a KVM guest. See e.g. https://www.postgresql.org/message-id/C ... .gmail.com

The already-addressed concern with IO on the one end is when PostgreSQL isn't given enough memory and at the application level it is heavily relying on direct, application-initiated IO, but on the other end, if you allocate too much memory you may end up with heavy swap usage.

Specifically to reduce instances where PostgreSQL became heavily reliant on swapping I reduced some of the configured settings

Code: Select all

shared_buffers = 6GB (recommendation would be 8GB)
work_mem = 30MB
maintenance_work_mem = 512MB
effective_cache_size = 10GB (recommendation would be 16GB)
max_wal_size = 4GB
min_wal_size = 80MB

Even though I don't know that it could reasonably be expected to impact the performance of the specific operations in question, I did note the substantially smaller WAL size I have configured (it is probably the original default value) since additional substantial sources of IO are at least potentially relevant if you are being impacted by an IO bottleneck.

I would also suggest that if you want to keep PostgreSQL isolated from the host OS but have some flexibility in configuration, running it in a docker container instead of a KVM guest, for example, eliminates many potential bottlenecks present with KVM and would make any remaining performance issues substantially easier to diagnose. If interested, while the study the link above references isn't available directly from IBM's web site any more, it is still available on archive.org and goes into various stats comparing host OS, Docker and KVM. It is a few years old (from 2014) but the general principals probably haven't changed much. An example of one of the multiple points where KVM had a substantial negative impact is MySQL transaction throughput where the study found "Docker has similar performance to native, with the difference asymptotically approaching 2% at higher con-currency. KVM has much higher overhead, higher than 40% in all measured cases" https://web.archive.org/web/20140816174 ... c25482.pdf

[RobiWan]

Thank you for your answer. My problem is solved. I think the settings I have are already very good for the VM and also the databases I use.
The problem was because it was not the first time that the idSearchData and idSearchDataValue tables were broken.
After I have rectreated both tables, works all fine for me.