CODEWITHSUNDEEP
postgresqlpostgis
Yuri Astrakhan
Yuri Astrakhan

Reputation: 10015

PostgreSQL limits itself to a single core CPU usage (debugging lock issue?)

Update after some research, it seems this question was incorrect - the 100% was representing all cores, not a single core, making the whole question moot. My sincere apologies to the community.

On PostgreSQL 10, PostGIS 2.5.2, without any data modifications (SELECT queries only), I have 40 identical GIS queries running in parallel (with different params), each taking ~20-500ms. Server has lots of RAM, NVME SSDs.

The CPU usage consistently shows 100% of a single core, implying that all queries are stuck waiting for something that cannot execute in parallel, but I am not sure how to find it.

Examining pg_stat_activity multiple times shows that all queries are active, and their wait_event could be one of these cases:

Is there a way to figure out what may be causing this?

Upvotes: 1

Views: 1560

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247225

That is surprising, as reading queries never lock on anything short of an ACCESS EXCLUSIVE lock that is required by operations like DROP TABLE, TRUNCATE, ALTER TABLE and similar statements.

Perhaps the locks are “light-weight locks” on internal PostgreSQL data structures, which are usually only held for a very short time. I don't know what in a PostGIS query could have high contention on such internal locks, but then you didn't show the statement or its execution plan, nor did you show the exact lock events.

If you have several concurrent queries that each take a long time like 500ms, the definitely should be running in parallel.

Apart from the possibilities of some internal lock contention, I can think of two explanations:

  1. Most of the queries are short enough that a single core suffices to process all the queries. Each connection spends most of its time waiting for the client.

  2. The system is I/O bound, so that most of the CPUs just twiddle their thumbs. That would be indicated by a CPU iowait% of 10 or more.

Upvotes: 1

Related Questions