[Spacewalk-list] PostgreSQL performance problems

[Jan Dobes]

On 8.12.2015 11:01 Morten Middelthon wrote:
> 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 A. Middelthon
> Last Friday
> System Administration and Development
> +47 907 83 708
> mm at lastfriday.com
>

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,
-- 
Jan Dobes
Satellite Engineering, Red Hat