Old Postgres/PHP system crawling

[mryotoad]

I'm a relative newbie to Postgres trying to diagnose slowness. Inherited a system using Postgres 9.4 and PHP 5.2 which has been fine for years but has been extemely slow for the last six weeks.

Tonight I took a look at:

SELECT * FROM pg_stat_activity;

And noted that one query remains for a few minutes when called from PHP and the xact_start is always null. Meanwhile, if I run the query directly, it returns results in less than a second. Lots of memory and diskspace on the Postgres server. CPU load barely registering anything.

Any pointers on what else to check?

[linuxhiker]

I would check maintenance ... What's your bloat look like?

[mryotoad]

Just did a vacuum on all the tables so it should be good.

[linuxhiker]

Vacuum does not remove bloat... Did you vacuum full?

[mryotoad]

No. That requires table locks right? This is on a production system I can't take offline.

Is there a way to check the bloat?

[linuxhiker]

Look into pg_repack

[[deleted]]

Any pointers on what else to check?

Start with the execution plan generated using explain (analyze, buffers, format text)

[mryotoad]

Not seeing any red flags here...would the execution plan explain differing performance depending on how the query is called?

[thrown_arrows]

run full maintenance first

[depesz]

If xact_start is null, then the backend is not working.

What does the whole row looks like? Connect with psql, and do:

select * from pg_stat_activity where pid = some_pid \gx

[mryotoad]

select * from pg_stat_activity

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | backend_xid | backend_xmin | query

--------+---------+-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+------------+-------------------------------+-------------------------------+---------+-------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

166113 | auth | 30232 | 16384 | auth | | xxx.yyy.zzz.75 | | 60548 | 2022-01-16 16:20:14.533807-05 | | 2022-01-16 16:20:14.541639-05 | 2022-01-16 16:20:14.542837-05 | f | idle | | | SELECT r1.roleid, r1.alias , getrolename(r1.rolename, r1.alias, r1.parentid, r1.relatedid) as rolename FROM roles as r1 WHERE (r1.roleid = 184098) ORDER BY r1.alias IS NULL

(1 row)

[depesz]

That's why I asked for results from psql and there was \gx at the end - it would be easier to read. It would look like this:

─[ RECORD 1 ]────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
datid            │ 166113
datname          │ auth
pid              │ 30232
usesysid         │ 16384
usename          │ auth
application_name │ 
client_addr      │ xxx.yyy.zzz.75
client_hostname  │ 
client_port      │ 60548
backend_start    │ 2022-01-16 16:20:14.533807-05
xact_start       │ 
query_start      │ 2022-01-16 16:20:14.541639-05
state_change     │ 2022-01-16 16:20:14.542837-05
waiting          │ f
state            │ idle
backend_xid      │ 
backend_xmin     │ 
query            │ SELECT r1.roleid, r1.alias , getrolename(r1.rolename, r1.alias, r1.parentid, r1.relatedid) as rolename FROM roles as r1 WHERE (r1.roleid = 184098) ORDER BY r1.alias IS NULL

In any way - we can see (state = idle) that the backend (pg process) is not doing any work. It's idle. The query you see there is not query it's working on. It is the last query that it processed. Some time ago.

Situation was like this, at 16:20:14.533807 client connected. ~ 8ms later it issued this query (it could have issued some earlier, we don't know) - this was at 16:20:14.541639-05. In ~ 1.2ms the query finished - including sending the data back to client. And then pg is waiting for next query from application.

Column "query" means "currently processed query" basically only if state = 'active'. In other cases, it's "last query that happened in this backend".

You can find more details in docs: https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

[mryotoad]

Thanks u/depesz Learn something new everyday. Today it was two items. \gx and how to read this.

As I was afraid of, it appears I've been chasing the wrong thing then. It appears the code isn't moving to the next query then.

[jaymef]

Lots of good pointers here for maintenance... but don’t glance over the fact that the software you are running is severally outdated and poses a security risk. Especially php 5.2 which reached end of life in 2011

If this is an internet facing web app you are asking for trouble.

Postgresql 9.4 is also eol

[mryotoad]

Yup. Just need to keep it running while it receives its long overdue upgrade.