Re: [Spacewalk-list] Spacewalk - PostgreSQL High I/O

[Allan Moraes]

Thank you for the tips,

In this case there is available 6GB of memory and the high I/O occur at the
postgres disk. Other disks, the I/O is normal. The system not is using swap
and there is 3GB of swap.

I will separate the postgre and apply your tips.

2016-10-10 21:58 GMT-03:00 Matt Moldvan :

> We have about 6,000 systems to manage and it was unusable otherwise... I
> had way too much trouble trying to get OSAD to work through proxies and F5
> load balancers, so I had to end up pointing them all to two masters that
> are still using the same Postgres database VM.  I was also toying with
> having the database be the back end for OSAD, so with that in mind the
> number of concurrent clients would often reach higher than usual
> numbers...  I tried a lot of different things to get Spacewalk stable,
> usable, and have proper failover, so I don't know that any of my
> recommendations or environment specific settings might be a silver bullet
> for anyone else, but it can't hurt to try, and learn in the process.
>
> On Mon, Oct 10, 2016 at 6:23 PM Paul Robert Marino 
> wrote:
>
>> tuning for 5000 clients is nuts that would hurt your performance
>> try running pgtune for about 50 to maybe 500 clients max, but I try
>> the lower setting first.
>> Now lets talk about the high IO that usually happens when you don't
>> have enough working memory in PostgreSQL's configuration. When that
>> happens PostgreSQL creates temp files that are slow and do a lot of
>> write IO during read operations because it will have to swap the data
>> out to the temp files, note setting the number of connections too high
>> would exacerbate that issue f its the root cause.
>> By the way I managed up to 400 with spacewalk and never had to disable
>> the snapshots.
>>
>>
>> On Mon, Oct 10, 2016 at 4:48 PM, Matt Moldvan  wrote:
>> > I had similar issues and ended up first breaking out the database to
>> it's
>> > own VM, then increasing the Postgres debug logs.  I saw that there were
>> a
>> > large number of operations running against the snapshot tables, with
>> locks
>> > and so on being set for a long period of time.  In /etc/rhn/rhn.conf,
>> try
>> > disabling snapshots with:
>> >
>> > enable_snapshots = 0
>> >
>> > I also did quite a bit of Postgres tuning using pgtune, for 5,000
>> clients or
>> > so:
>> > pgtune -i data/postgresql.conf  -o ./data/postgresql.conf.new -c 5000
>> >
>> > Another thing that may help is installing pgbadger to analyze your
>> Postgres
>> > logs... it has some nice visualizations of the types of queries and
>> tables
>> > involved, which may point you in the right direction if snapshots
>> aren't the
>> > reason for the high utilization.
>> > https://github.com/dalibo/pgbadger
>> >
>> > Hope that helps.
>> >
>> > On Mon, Oct 10, 2016 at 4:06 PM Konstantin Raskoshnyi <
>> konra...@gmail.com>
>> > wrote:
>> >>
>> >> Because all your systems request information from SP, and default
>> >> installation doesn't make any sense if you have more that 50 machines,
>> so
>> >> you need to tyne postgres, tomcat & linux itself
>> >>
>> >> On Mon, Oct 10, 2016 at 12:34 PM, Allan Moraes <
>> al...@allanmoraes.com.br>
>> >> wrote:
>> >>>
>> >>> Hi
>> >>> In my CentOS 7 server, is installed the spacewalk 2.4 and PostgreSQL
>> from
>> >>> default installation. Via iotop, my postgresql write a lot of
>> informations,
>> >>> during all day. Why this occur?
>> >>>
>> >>> ___
>> >>> Spacewalk-list mailing list
>> >>> Spacewalk-list@redhat.com
>> >>> https://www.redhat.com/mailman/listinfo/spacewalk-list
>> >>
>> >>
>> >> ___
>> >> Spacewalk-list mailing list
>> >> Spacewalk-list@redhat.com
>> >> https://www.redhat.com/mailman/listinfo/spacewalk-list
>> >
>> >
>> > ___
>> > Spacewalk-list mailing list
>> > Spacewalk-list@redhat.com
>> > https://www.redhat.com/mailman/listinfo/spacewalk-list
>>
>> ___
>> Spacewalk-list mailing list
>> Spacewalk-list@redhat.com
>> https://www.redhat.com/mailman/listinfo/spacewalk-list
>>
>
> ___
> Spacewalk-list mailing list
> Spacewalk-list@redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>



-- 

Atenciosamente...

*Allan Moraes*
*- Linux Consulting at Venda e Cia *
*- Founder and Editor at MySQL Box *
*- Linux System Administrador and DBA MySQL at Umbler
*

*Cel: (51) 81885480*
*E-mail: al...@mysqlbox.com.br *
*Skype: al...@allanmoraes.com.br *
___
Spacewalk-list mailing list
Spacewalk-list@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list

Re: [Spacewalk-list] Unable to sync Red Hat errata incorrect metadata

[Morten Middelthon]

On 10 Oct 2016, at 17:33, Jan Dobes  wrote:
> 
> On 10.10.2016 11:43 Morten A. Middelthon wrote:
>> 
>> Has anyone gone through the trouble of applying the patch on Spacewalk
>> 2.2? My Spacewalk server is unfortunately running RHEL 5, which means
>> I'm can't upgrade past spacewalk v2.2 :(
>> My python skills are very limited, but I'm willing to give it a go if
>> noone else has
>> 
> 
> There isn't big difference in Spacewalk 2.2, does this work?
> 
> http://pastebin.com/HGk7R6AN
> 
> -- 
> Jan Dobes
> Satellite Engineering, Red Hat

Yes! Thank you very much my good man. This has solved weeks of headache for my 
part.

with regards,

Morten


___
Spacewalk-list mailing list
Spacewalk-list@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list

Re: [Spacewalk-list] Spacewalk - PostgreSQL High I/O

[Matt Moldvan]

Yeah, it was quite a trial to get it to scale to that degree, so I tried a
lot of different things.  I increased the max_fds in the jabber configs and
the ulimits for the jabber user and it's pretty stable now, though even the
back end Python code wasn't written for having multiple osa dispatchers
(one overwrites the other with respect to the database password in the
rhnpushdispatcher table in the main database).

As for proxies, we have 4 in each datacenter but even that was bringing
down the database due to what I realized later was OSAD restarting on all
systems when Puppet would run in the morning.  That coupled with the
database locks that were being set on the snapshot tables, because
Spacewalk for some reason thought the base channels were updated daily,
made the UI completely unresponsive and unusable by our operations folks
trying to run the patching for hundreds of systems at a time.

As for the proxies, my main struggle was putting them behind F5 local and
global load balancers.  I signed the SSL certs with the name of the global
WIP, and unfortunately the way the jabber S2S component works, it was
trying to use that same name for all the proxies.  I realized later I was
trying to fit a square peg into a round hole and fixed up the Puppet
module, disabled snapshots, and disabled Jabber on the proxies and pointed
the clients at the masters, instead.  That was after months of frustration
and a growing chorus of complaints on the issues with the UI on the masters.

Anyway, having the proxies helped reduce the load significantly on the
masters, as they still function with the other components like Apache httpd
and Squid for caching the RPMs.

On Tue, Oct 11, 2016 at 5:02 PM Paul Robert Marino 
wrote:

> Matt,
> you ave a lot of clients so those numbers start to make sense to
> increase the database connections.
> also OSAD isn't meant to scale that high in fact you should run out of
> file handles for it before it even gets to that many clients.
> further more I hope you are using spacewalk proxies if not you may
> find they help you a lot.
>
> As to the original poster look at this page
> https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
> specifically work_mem I would be willing to bet that is your issue
> because it isn't enough for Spacewalk by default.
> the easiest way to confirm it is to look in the postgresql base data
> directory /var/lib/pgsql/data by default and look for a directory
> called pgsql_tmp if there are any files in that directory then you
> know its swaping.
> further more you can look for any queries that have been running for a
> long time and run explain analyze on them.
> I normally do not suggest this site or any ones like it because they
> often give very wrong answers but in this case its not a bad answer
>
> http://dba.stackexchange.com/questions/112079/slow-query-performance-due-to-temporary-file
> I just don't suggest going strait to 64M
>
>
>
> On Tue, Oct 11, 2016 at 8:37 AM, Allan Moraes 
> wrote:
> > Thank you for the tips,
> >
> > In this case there is available 6GB of memory and the high I/O occur at
> the
> > postgres disk. Other disks, the I/O is normal. The system not is using
> swap
> > and there is 3GB of swap.
> >
> > I will separate the postgre and apply your tips.
> >
> > 2016-10-10 21:58 GMT-03:00 Matt Moldvan :
> >>
> >> We have about 6,000 systems to manage and it was unusable otherwise... I
> >> had way too much trouble trying to get OSAD to work through proxies and
> F5
> >> load balancers, so I had to end up pointing them all to two masters
> that are
> >> still using the same Postgres database VM.  I was also toying with
> having
> >> the database be the back end for OSAD, so with that in mind the number
> of
> >> concurrent clients would often reach higher than usual numbers...  I
> tried a
> >> lot of different things to get Spacewalk stable, usable, and have proper
> >> failover, so I don't know that any of my recommendations or environment
> >> specific settings might be a silver bullet for anyone else, but it can't
> >> hurt to try, and learn in the process.
> >>
> >> On Mon, Oct 10, 2016 at 6:23 PM Paul Robert Marino  >
> >> wrote:
> >>>
> >>> tuning for 5000 clients is nuts that would hurt your performance
> >>> try running pgtune for about 50 to maybe 500 clients max, but I try
> >>> the lower setting first.
> >>> Now lets talk about the high IO that usually happens when you don't
> >>> have enough working memory in PostgreSQL's configuration. When that
> >>> happens PostgreSQL creates temp files that are slow and do a lot of
> >>> write IO during read operations because it will have to swap the data
> >>> out to the temp files, note setting the number of connections too high
> >>> would exacerbate that issue f its the root cause.
> >>> By the way I managed up to 400 with spacewalk and never had to disable
> >>> the snapshots.
> 

Re: [Spacewalk-list] Spacewalk - PostgreSQL High I/O

[Paul Robert Marino]

Matt
You may consider using multiple PostgreSQL replica servers in HOT mode
servers with pgpool in front of them it may help but a little. but
because of te way spacewalk does its queries I doubt it will help much
as the safties in pgpool will think just about every query may write
and send it to the master any way.
A better but more maintenece intensive option you may want to consider
is table partitioning to alleviate the locks
https://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
https://wiki.postgresql.org/wiki/Table_partitioning
this is a chopped down version of what the commercial data warehouse
versions of PostgreSQL do,
its a little bit of a pain but it works well for alleviating locking
issues. I've used it with multi TB databases in the past with great
success but it takes a lot of planning and testing to get it right.

On Tue, Oct 11, 2016 at 10:04 PM, Matt Moldvan  wrote:
> Yeah, it was quite a trial to get it to scale to that degree, so I tried a
> lot of different things.  I increased the max_fds in the jabber configs and
> the ulimits for the jabber user and it's pretty stable now, though even the
> back end Python code wasn't written for having multiple osa dispatchers (one
> overwrites the other with respect to the database password in the
> rhnpushdispatcher table in the main database).
>
> As for proxies, we have 4 in each datacenter but even that was bringing down
> the database due to what I realized later was OSAD restarting on all systems
> when Puppet would run in the morning.  That coupled with the database locks
> that were being set on the snapshot tables, because Spacewalk for some
> reason thought the base channels were updated daily, made the UI completely
> unresponsive and unusable by our operations folks trying to run the patching
> for hundreds of systems at a time.
>
> As for the proxies, my main struggle was putting them behind F5 local and
> global load balancers.  I signed the SSL certs with the name of the global
> WIP, and unfortunately the way the jabber S2S component works, it was trying
> to use that same name for all the proxies.  I realized later I was trying to
> fit a square peg into a round hole and fixed up the Puppet module, disabled
> snapshots, and disabled Jabber on the proxies and pointed the clients at the
> masters, instead.  That was after months of frustration and a growing chorus
> of complaints on the issues with the UI on the masters.
>
> Anyway, having the proxies helped reduce the load significantly on the
> masters, as they still function with the other components like Apache httpd
> and Squid for caching the RPMs.
>
> On Tue, Oct 11, 2016 at 5:02 PM Paul Robert Marino 
> wrote:
>>
>> Matt,
>> you ave a lot of clients so those numbers start to make sense to
>> increase the database connections.
>> also OSAD isn't meant to scale that high in fact you should run out of
>> file handles for it before it even gets to that many clients.
>> further more I hope you are using spacewalk proxies if not you may
>> find they help you a lot.
>>
>> As to the original poster look at this page
>> https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
>> specifically work_mem I would be willing to bet that is your issue
>> because it isn't enough for Spacewalk by default.
>> the easiest way to confirm it is to look in the postgresql base data
>> directory /var/lib/pgsql/data by default and look for a directory
>> called pgsql_tmp if there are any files in that directory then you
>> know its swaping.
>> further more you can look for any queries that have been running for a
>> long time and run explain analyze on them.
>> I normally do not suggest this site or any ones like it because they
>> often give very wrong answers but in this case its not a bad answer
>>
>> http://dba.stackexchange.com/questions/112079/slow-query-performance-due-to-temporary-file
>> I just don't suggest going strait to 64M
>>
>>
>>
>> On Tue, Oct 11, 2016 at 8:37 AM, Allan Moraes 
>> wrote:
>> > Thank you for the tips,
>> >
>> > In this case there is available 6GB of memory and the high I/O occur at
>> > the
>> > postgres disk. Other disks, the I/O is normal. The system not is using
>> > swap
>> > and there is 3GB of swap.
>> >
>> > I will separate the postgre and apply your tips.
>> >
>> > 2016-10-10 21:58 GMT-03:00 Matt Moldvan :
>> >>
>> >> We have about 6,000 systems to manage and it was unusable otherwise...
>> >> I
>> >> had way too much trouble trying to get OSAD to work through proxies and
>> >> F5
>> >> load balancers, so I had to end up pointing them all to two masters
>> >> that are
>> >> still using the same Postgres database VM.  I was also toying with
>> >> having
>> >> the database be the back end for OSAD, so with that in mind the number
>> >> of
>> >> concurrent clients would often reach higher than usual numbers...  I
>> >> tried a
>> >> lot of different things 

Re: [Spacewalk-list] Spacewalk - PostgreSQL High I/O

[Matt Moldvan]

Well for a smaller number of clients (<500-1,000) separating out the
database might be more work than it's worth (though good experience).
First step I would say is to take a look at increasing the logging in
Postgres to get an idea where the slowness is occurring.  Also, something
like atop or another monitoring solution can give you an idea of when the
spikes occur, maybe allowing you to correlate other system or maintenance
activities.

On Tue, Oct 11, 2016 at 8:37 AM Allan Moraes 
wrote:

> Thank you for the tips,
>
> In this case there is available 6GB of memory and the high I/O occur at
> the postgres disk. Other disks, the I/O is normal. The system not is using
> swap and there is 3GB of swap.
>
> I will separate the postgre and apply your tips.
>
> 2016-10-10 21:58 GMT-03:00 Matt Moldvan :
>
> We have about 6,000 systems to manage and it was unusable otherwise... I
> had way too much trouble trying to get OSAD to work through proxies and F5
> load balancers, so I had to end up pointing them all to two masters that
> are still using the same Postgres database VM.  I was also toying with
> having the database be the back end for OSAD, so with that in mind the
> number of concurrent clients would often reach higher than usual
> numbers...  I tried a lot of different things to get Spacewalk stable,
> usable, and have proper failover, so I don't know that any of my
> recommendations or environment specific settings might be a silver bullet
> for anyone else, but it can't hurt to try, and learn in the process.
>
> On Mon, Oct 10, 2016 at 6:23 PM Paul Robert Marino 
> wrote:
>
> tuning for 5000 clients is nuts that would hurt your performance
> try running pgtune for about 50 to maybe 500 clients max, but I try
> the lower setting first.
> Now lets talk about the high IO that usually happens when you don't
> have enough working memory in PostgreSQL's configuration. When that
> happens PostgreSQL creates temp files that are slow and do a lot of
> write IO during read operations because it will have to swap the data
> out to the temp files, note setting the number of connections too high
> would exacerbate that issue f its the root cause.
> By the way I managed up to 400 with spacewalk and never had to disable
> the snapshots.
>
>
> On Mon, Oct 10, 2016 at 4:48 PM, Matt Moldvan  wrote:
> > I had similar issues and ended up first breaking out the database to it's
> > own VM, then increasing the Postgres debug logs.  I saw that there were a
> > large number of operations running against the snapshot tables, with
> locks
> > and so on being set for a long period of time.  In /etc/rhn/rhn.conf, try
> > disabling snapshots with:
> >
> > enable_snapshots = 0
> >
> > I also did quite a bit of Postgres tuning using pgtune, for 5,000
> clients or
> > so:
> > pgtune -i data/postgresql.conf  -o ./data/postgresql.conf.new -c 5000
> >
> > Another thing that may help is installing pgbadger to analyze your
> Postgres
> > logs... it has some nice visualizations of the types of queries and
> tables
> > involved, which may point you in the right direction if snapshots aren't
> the
> > reason for the high utilization.
> > https://github.com/dalibo/pgbadger
> >
> > Hope that helps.
> >
> > On Mon, Oct 10, 2016 at 4:06 PM Konstantin Raskoshnyi <
> konra...@gmail.com>
> > wrote:
> >>
> >> Because all your systems request information from SP, and default
> >> installation doesn't make any sense if you have more that 50 machines,
> so
> >> you need to tyne postgres, tomcat & linux itself
> >>
> >> On Mon, Oct 10, 2016 at 12:34 PM, Allan Moraes <
> al...@allanmoraes.com.br>
> >> wrote:
> >>>
> >>> Hi
> >>> In my CentOS 7 server, is installed the spacewalk 2.4 and PostgreSQL
> from
> >>> default installation. Via iotop, my postgresql write a lot of
> informations,
> >>> during all day. Why this occur?
> >>>
> >>> ___
> >>> Spacewalk-list mailing list
> >>> Spacewalk-list@redhat.com
> >>> https://www.redhat.com/mailman/listinfo/spacewalk-list
> >>
> >>
> >> ___
> >> Spacewalk-list mailing list
> >> Spacewalk-list@redhat.com
> >> https://www.redhat.com/mailman/listinfo/spacewalk-list
> >
> >
> > ___
> > Spacewalk-list mailing list
> > Spacewalk-list@redhat.com
> > https://www.redhat.com/mailman/listinfo/spacewalk-list
>
> ___
> Spacewalk-list mailing list
> Spacewalk-list@redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
>
> ___
> Spacewalk-list mailing list
> Spacewalk-list@redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
>
>
>
> --
>
> Atenciosamente...
>
> *Allan Moraes*
> *- Linux Consulting at Venda e Cia *
> *- Founder and Editor at MySQL Box