High CPU usage postgres

[smsoousa]

Helo! I am in the second installation of a nominatim server, in a VPS of 16gb of RAM, 6 cores of CPU and 400gb SSD. The first installation is consuming only 3% of CPU, the second installation is consuming above 50% of CPU, both servers have the same configuration. I'm using pgtune for better distribution of resources in postgres. The configuration is as follows:

DB Version: 10
OS Type: linux
Total Memory (RAM): 16 GB
CPUs num: 6
Data Storage: ssd

max_connections = 40
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 17476kB
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6

I have tried to reduce these resources, but still, the CPU consumption does not go down. I also noticed that there are countless open postgres processes, when using the top command

I'm using S.O Ubuntu 18, nominatim 3.5.2

[lonvia]

50% CPU usage does sound resonable for a Nominatim import. There are a lot of computations to be done.

[smsoousa]

After installation, the configuration is being used by the two servers, the installation and import process was done equally on both servers. What intrigues me is that one consumes almost nothing of CPU but the other is consuming above 50%

[mtmail]

We've seen reports where an index is broken or missing. That goes a bit into postgresql query debugging. utils/check_import_finished.php has a step to look for broken indexes (https://github.com/osm-search/Nominatim/blob/v3.6.0/utils/check_import_finished.php#L143). The step looking for existence of indexes has only a few hardcoded names, there would be too many to list.

You can try comparing the sizes of indexes between the servers, maybe it's obvious that one is missing.

SELECT
  C.relname,
  C.relkind,
  T.spcname AS tablespace,
  pg_size_pretty(sum(pg_relation_size(C.oid))) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace  N ON (N.oid = C.relnamespace)
LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE
  C.relname LIKE 'idx_%' AND C.relkind = 'i'
GROUP BY C.relname, C.relkind, tablespace
ORDER BY sum(pg_relation_size(C.oid)) DESC;

If that doesn't show anything obvious you'd need to check current running queries, prefix with 'EXPLAIN' and see where they differ. It's a pretty manual debugging process.

With cloud servers and virtualized machines (VMware I think) we've had reports were it turned out the storage was network mounted or had other abstractions (e.g. AWS has multiple types of storage options with different IO limits) and became the bottleneck, no amount of postgresql tuning helped.

[smsoousa]

I received this feedback, I'm sorry I couldn't identify any missing data. If you can help me with that, I will be grateful.

[smsoousa]

[smsoousa]

[lonvia]

I'm still unclear what exactly these servers are doing when they use so vastly different CPU. I assumed you are talking about the initial import of the database but apparently that is done. So, please be more specific. Do you send search requests? Reverse search requests? Is something else running on these servers? Do you send exactly the same request to both servers or different ones? If they are different, then how many requests does each server handle? And what exactly is 'DB type: dw'?

[smsoousa]

I will try to be a little clearer, I did two nominatim installations, on two equal servers, with equal configurations. Both installations went well, I am using it for reverse geocoding only, I have only 10 ips consuming requests from each server. My question is, because one server is consuming 3% of the CPU and the other is above 50%, reaching up to 90% at peak times. Since both installations used the same postgres tuning parameters, both have the same hardware. And both had an OK check after import completion. That is, both servers are functioning normally, however, one is consuming much more than the CPU. Both servers have only nominatim installed.

[lonvia]

So do I understand you right that each server handles approximately 10 requests/s? Are the requests similar for the two servers or distinctly different? For example, does one server get requests for one country and the other requests for another?

First of all, try an 'ANALYSE' on both servers. If that doesn't help...

What are the average execution times for requests on the two servers (enable query logging in the Nominatim configuration)? Are they distinctly different? Are there specific queries that take much longer?

If there is a difference in query time: see that you find one query which runs significantly longer on one server than on the other (run the query multiple times on each server to be sure that it is not just a glitch in the system). Now run that query with the additional parameter debug=1. You get a list of SQL queries that are executed. Run each of the queries on each of the servers and see if any of them has a vastly different execution time. If you find one, take the query and run it with EXPLAIN ANALYSE SELECT ... on both servers. Report the output.