[Spacewalk-list] PostgreSQL performance problems

[Morten Middelthon]

Hi list,

we are having serious performance problems with our PostgreSQL database on our spacewalk-installation. The spacewalk server was at first very responsive, but as we’ve added clients, packages and errata is has become more and more sluggish.
F.ex entering the errata page slows the server considerably down and usually ends up in a timeout. At the same time I can see a postmaster postgres process running with high CPU usage. As a part of this I have turned on logging of slow queries. F.ex:

Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-1] 2015-12-08 10:48:56.142 CET LOG: duration: 206951.818 ms execute <unnamed>: SELECT E.id, E.update_date, E.synopsis AS ADVISORY_SYNOPSIS
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-2] #011 FROM rhnErrata E,
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-3] #011 (SELECT SNEC.errata_id
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-4] #011 FROM rhnServerNeededCache SNEC
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-5] #011 WHERE SNEC.server_id IN (SELECT server_id FROM rhnUserServerPerms USP WHERE USP.user_id = $1)
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-6] #011 AND errata_id IS NOT NULL
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-7] #011 GROUP BY SNEC.errata_id) X
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-8] #011 WHERE E.id = X.errata_id
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-9] #011ORDER BY E.update_date DESC, E.id
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-10] 2015-12-08 10:48:56.142 CET DETAIL: parameters: $1 = ’13’

In this case this particular query took 206951ms to execute, which I guess you call very slow.
In the Spacewalk web gui I can navigate through most of the pages without problems, but when I enter the errata page it grinds down to a halt and gives me a timeout

The server itself is a KVM virtual host with 4 CPUs and 16GB of RAM running an up-2-date RHEL 6. We have a total of 48 software channels, with 4 base channels, RHEL6 x86_64 & i386, RHEL5 x86_64 and RHEL7 x86_64. There are 640 registered clients, with most of them in the RHEL 6 x86_64 channel.

I have attached the postgresql.conf file, which has been run through pgtune

[Morten Middelthon]

Post by Morten Middelthon
Hi list,
we are having serious performance problems with our PostgreSQL database on our spacewalk-installation. The spacewalk server was at first very responsive, but as we’ve added clients, packages and errata is has become more and more sluggish.

<snip>

Perhaps also worth mentioning that we’re running an updated 2.4 spacewalk installation

Morten

[Jan Dobes]

Post by Morten Middelthon
Hi list,
we are having serious performance problems with our PostgreSQL database on our spacewalk-installation. The spacewalk server was at first very responsive, but as we’ve added clients, packages and errata is has become more and more sluggish.
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-1] 2015-12-08 10:48:56.142 CET LOG: duration: 206951.818 ms execute <unnamed>: SELECT E.id, E.update_date, E.synopsis AS ADVISORY_SYNOPSIS
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-2] #011 FROM rhnErrata E,
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-3] #011 (SELECT SNEC.errata_id
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-4] #011 FROM rhnServerNeededCache SNEC
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-5] #011 WHERE SNEC.server_id IN (SELECT server_id FROM rhnUserServerPerms USP WHERE USP.user_id = $1)
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-6] #011 AND errata_id IS NOT NULL
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-7] #011 GROUP BY SNEC.errata_id) X
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-8] #011 WHERE E.id = X.errata_id
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-9] #011ORDER BY E.update_date DESC, E.id
Dec 8 10:48:56 spacewalk1 postgres[14561]: [2-10] 2015-12-08 10:48:56.142 CET DETAIL: parameters: $1 = ’13’
In this case this particular query took 206951ms to execute, which I guess you call very slow.
In the Spacewalk web gui I can navigate through most of the pages without problems, but when I enter the errata page it grinds down to a halt and gives me a timeout
The server itself is a KVM virtual host with 4 CPUs and 16GB of RAM running an up-2-date RHEL 6. We have a total of 48 software channels, with 4 base channels, RHEL6 x86_64 & i386, RHEL5 x86_64 and RHEL7 x86_64. There are 640 registered clients, with most of them in the RHEL 6 x86_64 channel.
I have attached the postgresql.conf file, which has been run through pgtune
Morten A. Middelthon
Last Friday
System Administration and Development
[phone]

Hello,

we are aware we have this kind of performance problems. Some queries are
just not tested in specific large environments. Thank you for report and
we will try to improve it. If you find more slow WebUI pages, please
report them too.

Regards,

[Morten A. Middelthon]

Post by Jan Dobes
Hello,
we are aware we have this kind of performance problems. Some queries are
just not tested in specific large environments. Thank you for report and
we will try to improve it. If you find more slow WebUI pages, please
report them too.

Does the same apply to the Red Hat Satelite Server? We are looking into
purchasing the necessary subscriptions for this to replace the Spacewalk
server

[Silvio Moioli]

we are having serious performance problems with our PostgreSQL database on our spacewalk-installation. [...]PostgreSQL performance problemsl

Could you please share the results of the following queries:

EXPLAIN ANALYZE SELECT E.id, E.update_date, E.synopsis AS ADVISORY_SYNOPSIS
FROM rhnErrata E,
(SELECT SNEC.errata_id
FROM rhnServerNeededCache SNEC
WHERE SNEC.server_id IN (SELECT server_id FROM rhnUserServerPerms USP WHERE USP.user_id = 13)
AND errata_id IS NOT NULL
GROUP BY SNEC.errata_id) X
WHERE E.id = X.errata_id
ORDER BY E.update_date DESC, E.id

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'rhn%' ORDER BY relname;

SELECT COUNT(*) FROM rhnErrata;
SELECT COUNT(*) FROM rhnServerNeededCache;
SELECT COUNT(*) FROM rhnUserServerPerms;

As the output might be verbose, please use pastebin or a similar service!

Regards,

[Morten A. Middelthon]

On 10. des. 2015 07:54, Silvio Moioli wrote:
<snip>

Thanks for taking time to look at this

Post by Silvio Moioli
EXPLAIN ANALYZE SELECT E.id, E.update_date, E.synopsis AS ADVISORY_SYNOPSIS
FROM rhnErrata E,
(SELECT SNEC.errata_id
FROM rhnServerNeededCache SNEC
WHERE SNEC.server_id IN (SELECT server_id FROM
rhnUserServerPerms USP WHERE USP.user_id = 13)
AND errata_id IS NOT NULL
GROUP BY SNEC.errata_id) X
WHERE E.id = X.errata_id
ORDER BY E.update_date DESC, E.id

I saved the output from this query here:

http://flipp.net/spacewalk.out1.txt

Post by Silvio Moioli
SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname
LIKE 'rhn%' ORDER BY relname;

Output here: http://flipp.net/spacewalk.out2.txt

Post by Silvio Moioli
SELECT COUNT(*) FROM rhnErrata;
SELECT COUNT(*) FROM rhnServerNeededCache;
SELECT COUNT(*) FROM rhnUserServerPerms;

Output here: http://flipp.net/spacewalk.out3.txt

with regards,

[Morten A. Middelthon]

Just a follow-up to this issue. This week Spacewalk has been performing
quite nicely. Even the errata-page on the web GUI is reasonably fast to
load. Could this perhaps be because I turned on auto-vacuuming in
postgres.conf? Other than that we haven't changed any of the setup since
the problems started

with regards,