0

After application server start its connection pool fills quickly and application can't work properly. I went through pg_stat_activity and noticed lots of queries running for minutes: enter image description here

But postgres's slow query log does not show any long-running queries (i've set 100ms as minimum query time).

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2

I suggested that there are some locks on that table but SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED; shows nothing.

Structure of persistence_journal is pretty easy:

CREATE TABLE IF NOT EXISTS public.persistence_journal (
  persistence_id VARCHAR(255) NOT NULL,
  sequence_number BIGINT NOT NULL,
  created BIGINT NOT NULL,
  tags VARCHAR(255) DEFAULT NULL,
  message BYTEA NOT NULL,
  PRIMARY KEY(persistence_id, sequence_number)
);

persistence_journal contains 1 678 121 rows with ~37 000 unique persistence_ids with average 44 rows per persistence_id.

Running SELECT state, count(*) FROM pg_stat_activity GROUP BY state; shows:

active  1
idle    43
idle in transaction 45

This "transaction" contains two pretty simple queries:

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2 select max("deleted_to") from "persistence_deleted_to" where "persistence_id" = $1

persistence_deleted_to contains zero rows.

System's top shows almost zero CPU load.

PostgreSQL 9.4, Ubuntu 14.04

0

Your problem are the "idle in transaction" sessions. You should fix your application to avoid that. The queries in pg_stat_activity in those cases are the last query run, not the current one (since there isn't a current one).

  • I removed transaction (since I realized that I don't need any transaciton there) and the problem gone. But I am wondering how such simple read-only queries could lock each other? – Andrey Kuznetsov Apr 29 '16 at 20:24
  • FIgured out that it's db library bug: github.com/slick/slick/issues/1492 – Andrey Kuznetsov Apr 29 '16 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.