CODEWITHSUNDEEP
postgresql
Ryo
Ryo

Reputation: 1035

Postgres CPU high usage after hours

I have a Application server with Postgresql database on Windows 2016, 8 virtual threads CPU and 32GB memory

I try to stress with 10 client to concurrent insert to database, each insert operation take about 500ms on a 2.5m records table

It's working fine in about 1hours and take ~2GB memory and 80% CPU, after that time, cpu go up to 100% ( memory not change) and postgres seem not responding and client could not connect to server

I check in Postgres log and see error : 

[2020-02-18 22:48:59 JST]  26344[16] WARNING:  worker took too long to start; canceled

also have no lock with this query check : 

SELECT bl.pid                 AS blocked_pid,
         a.usename              AS blocked_user,
         ka.query               AS current_or_recent_statement_in_blocking_process,
         ka.state               AS state_of_blocking_process,
         now() - ka.query_start AS blocking_duration,
         kl.pid                 AS blocking_pid,
         ka.usename             AS blocking_user,
         a.query                AS blocked_statement,
         now() - a.query_start  AS blocked_duration
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
  WHERE NOT bl.GRANTED;

No slow sql found in pg_stat_activity or pg lock

All memory,wal or checkpoint config is default

I also using postgresql ODBC for connection pool Could anyone suggest me a way to detect the problem?

Upvotes: 0

Views: 2018

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248215

If an INSERT takes half a second, you have a serious problem.

It looks like you are overloading the CPU resources on the database server. The solution would be to find the statements that take too much time when the database is not under load and tune them to be faster. Then you can handle more of them with your available CPU.

Upvotes: 0

Related Questions