[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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgresql.conf
Type: application/octet-stream
Size: 17441 bytes
Desc: not available
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20151208/022672b8/attachment.obj>
-------------- next part --------------

Morten A. Middelthon
Last Friday
System Administration and Development
+47 907 83 708
mm at lastfriday.com