perfparse-users

[Perfparse-users] Tables cleanup

[Ciro I. <cy...@gm...>]

Hi, i'm running 106.1, and i have a doubt, i don't use the daemon to
submit the results, instead i run the DB insert with every check. I
would like to now if it's posible to launch the table cleanup while
nagios is inserting data or should i stop nagios the whole time the
procedure takes?

Atte
Ciro

Re: [Perfparse-users] Tables cleanup

[Florian G. <fl...@bi...>]

I think it depends on how long the cleanup lasts. I'm not sure, but i
think mysql uses table locks and data inserts hang until the table is
unlocked. So if the cleanup takes hours you might have a problem with
nagios creating hundreds of processes waiting to insert data. You can
also let nagios write the data in a file meanwhile and make a script to
feed the data later to perfparse(d).

Flo

Ciro Iriarte wrote:
> Hi, i'm running 106.1, and i have a doubt, i don't use the daemon to
> submit the results, instead i run the DB insert with every check. I
> would like to now if it's posible to launch the table cleanup while
> nagios is inserting data or should i stop nagios the whole time the
> procedure takes?
> 
> Atte
> Ciro
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Perfparse-users mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perfparse-users
> 

Re: [Perfparse-users] Tables cleanup

[Ciro I. <cy...@gm...>]

2007/1/18, Florian Gleixner <fl...@bi...>:
>
> I think it depends on how long the cleanup lasts. I'm not sure, but i
> think mysql uses table locks and data inserts hang until the table is
> unlocked. So if the cleanup takes hours you might have a problem with
> nagios creating hundreds of processes waiting to insert data. You can
> also let nagios write the data in a file meanwhile and make a script to
> feed the data later to perfparse(d).
>
> Flo
>
> Ciro Iriarte wrote:
> > Hi, i'm running 106.1, and i have a doubt, i don't use the daemon to
> > submit the results, instead i run the DB insert with every check. I
> > would like to now if it's posible to launch the table cleanup while
> > nagios is inserting data or should i stop nagios the whole time the
> > procedure takes?
> >
> > Atte
> > Ciro
> >
PHP:
Ok, i tried the PHP interfase to set the deletion policies,  i click
on the link but it doesn't load a new page.

CGI:
I can access the CGI page and set the Raw Default Policy, although i
got this error sometimes:
---------------
An error occured with the SQL:

  "UPDATE perfdata_service SET raw_delete_policy_name = 'Raw Default'
WHERE raw_delete_policy_type = 'template' AND raw_delete_policy_name
IS NULL"

Failure Message:

  "Deadlock found when trying to get lock; try restarting transaction"
---------------
I don't have luck when trying to change the policy deletion per host,
i select the drop down boxes, and submit changes, but after reloading
the page, data isn't changed.

Then i try to run the db-purge, but all i get is this:

ope2:~ # /usr/sbin/perfparse-db-purge -r
+-----------------------------------------------------------------------------+
| There are no Plugins, Unique Key or Metrics marked for deletion today.      |
+---------------------------------------------------+--------------+----------+
+---------------------------------------------------+--------------+----------+


Am i doing something wrong?

Ciro

Re: [Perfparse-users] Tables cleanup

[Florian G. <fl...@bi...>]

Hi,

The PHP interface will have full functionality in the next version of
Perfparse. At 106.1 this part simply was not finished.
With the cgi interface the menu "View / Edit Hosts Deletion Policy"
works for me.

Try to set one policy there and rerun the purge to see if it works.

Flo

Ciro Iriarte wrote:
> 2007/1/18, Florian Gleixner <fl...@bi...>:
>> I think it depends on how long the cleanup lasts. I'm not sure, but i
>> think mysql uses table locks and data inserts hang until the table is
>> unlocked. So if the cleanup takes hours you might have a problem with
>> nagios creating hundreds of processes waiting to insert data. You can
>> also let nagios write the data in a file meanwhile and make a script to
>> feed the data later to perfparse(d).
>>
>> Flo
>>
>> Ciro Iriarte wrote:
>>> Hi, i'm running 106.1, and i have a doubt, i don't use the daemon to
>>> submit the results, instead i run the DB insert with every check. I
>>> would like to now if it's posible to launch the table cleanup while
>>> nagios is inserting data or should i stop nagios the whole time the
>>> procedure takes?
>>>
>>> Atte
>>> Ciro
>>>
> PHP:
> Ok, i tried the PHP interfase to set the deletion policies,  i click
> on the link but it doesn't load a new page.
> 
> CGI:
> I can access the CGI page and set the Raw Default Policy, although i
> got this error sometimes:
> ---------------
> An error occured with the SQL:
> 
>   "UPDATE perfdata_service SET raw_delete_policy_name = 'Raw Default'
> WHERE raw_delete_policy_type = 'template' AND raw_delete_policy_name
> IS NULL"
> 
> Failure Message:
> 
>   "Deadlock found when trying to get lock; try restarting transaction"
> ---------------
> I don't have luck when trying to change the policy deletion per host,
> i select the drop down boxes, and submit changes, but after reloading
> the page, data isn't changed.
> 
> Then i try to run the db-purge, but all i get is this:
> 
> ope2:~ # /usr/sbin/perfparse-db-purge -r
> +-----------------------------------------------------------------------------+
> | There are no Plugins, Unique Key or Metrics marked for deletion today.      |
> +---------------------------------------------------+--------------+----------+
> +---------------------------------------------------+--------------+----------+
> 
> 
> Am i doing something wrong?
> 
> Ciro
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Perfparse-users mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perfparse-users
> 

Re: [Perfparse-users] Tables cleanup

[Ciro I. <cy...@gm...>]

2007/1/21, Florian Gleixner <fl...@bi...>:
> Hi,
>
> The PHP interface will have full functionality in the next version of
> Perfparse. At 106.1 this part simply was not finished.
> With the cgi interface the menu "View / Edit Hosts Deletion Policy"
> works for me.
>
> Try to set one policy there and rerun the purge to see if it works.
>
> Flo
Ok, that works, i had to disable the inserts made by nagios because i
was getting many timeouts from runnig perfparse-log2mysql.

After 3 hours of execution i get:

+-----------------------------------------------------------------------------+
| There are no Plugins, Unique Key or Metrics marked for deletion today.      |
+---------------------------------------------------+--------------+----------+
|  Raw Data Delete Policy                                                     |
+---------------------------------------------------+--------------+----------+
| Unique Key                                        | Policy       | Deleted  |
+---------------------------------------------------+--------------+----------+
| as400, CPU                                        | To   30 days |   177425 |
| as400, PING                                       | To   30 days |    36000 |
| as400, RAM                                        | To   30 days |   173613 |
| as400, Storage                                    | To   30 days |
An error occured with the SQL:

  "DELETE FROM perfdata_service_raw WHERE host_name = 'as400' AND
service_description = 'Storage' AND ctime < NOW() - INTERVAL 30 DAY"

Failure Message:

  "The total number of locks exceeds the lock table size"

Can the --no_transactions flag help with this?, in which case do i
need to use it?, did anybody seen this error?

Ciro

Re: [Perfparse-users] Tables cleanup

[Florian G. <fl...@bi...>]

Hi,

the --no_transactions switch was made for people that have no innodb 
table type. You can try. If you disable inserts, transactions are not 
needed. If you did not cleanup your tables for a long time (or never) i 
would set the deletion policies only for one host, run a purge, add 
another host, run purge ...
If you cannot clean a host at once try to make your delete policy longer 
(100 days or more) first. I had these problems first too, but since i 
clean every hour, problems are gone.

Flo

Ciro Iriarte schrieb:
> 2007/1/21, Florian Gleixner <fl...@bi...>:
>> Hi,
>>
>> The PHP interface will have full functionality in the next version of
>> Perfparse. At 106.1 this part simply was not finished.
>> With the cgi interface the menu "View / Edit Hosts Deletion Policy"
>> works for me.
>>
>> Try to set one policy there and rerun the purge to see if it works.
>>
>> Flo
> Ok, that works, i had to disable the inserts made by nagios because i
> was getting many timeouts from runnig perfparse-log2mysql.
> 
> After 3 hours of execution i get:
> 
> +-----------------------------------------------------------------------------+
> | There are no Plugins, Unique Key or Metrics marked for deletion today.      |
> +---------------------------------------------------+--------------+----------+
> |  Raw Data Delete Policy                                                     |
> +---------------------------------------------------+--------------+----------+
> | Unique Key                                        | Policy       | Deleted  |
> +---------------------------------------------------+--------------+----------+
> | as400, CPU                                        | To   30 days |   177425 |
> | as400, PING                                       | To   30 days |    36000 |
> | as400, RAM                                        | To   30 days |   173613 |
> | as400, Storage                                    | To   30 days |
> An error occured with the SQL:
> 
>   "DELETE FROM perfdata_service_raw WHERE host_name = 'as400' AND
> service_description = 'Storage' AND ctime < NOW() - INTERVAL 30 DAY"
> 
> Failure Message:
> 
>   "The total number of locks exceeds the lock table size"
> 
> Can the --no_transactions flag help with this?, in which case do i
> need to use it?, did anybody seen this error?
> 
> Ciro
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Perfparse-users mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perfparse-users
> 

Re: [Perfparse-users] Tables cleanup

[Ciro I. <cy...@gm...>]

2007/1/22, Florian Gleixner <fl...@bi...>:
> Hi,
>
> the --no_transactions switch was made for people that have no innodb
> table type. You can try. If you disable inserts, transactions are not
> needed. If you did not cleanup your tables for a long time (or never) i
> would set the deletion policies only for one host, run a purge, add
> another host, run purge ...
> If you cannot clean a host at once try to make your delete policy longer
> (100 days or more) first. I had these problems first too, but since i
> clean every hour, problems are gone.
>
> Flo
Ok, got it running with --no_transactions, but it has been running for
3 days!!, this can't be right.... The DB runs on Xeon servers (HP
Proliant), maybe there's a place to tweak the process?, maybe mysql?,
it's going, and going, and going, and going.....

Re: [Perfparse-users] Tables cleanup

[Florian G. <fl...@bi...>]

Ciro Iriarte wrote:
> 2007/1/22, Florian Gleixner <fl...@bi...>:
>> Hi,
>>
>> the --no_transactions switch was made for people that have no innodb
>> table type. You can try. If you disable inserts, transactions are not
>> needed. If you did not cleanup your tables for a long time (or never) i
>> would set the deletion policies only for one host, run a purge, add
>> another host, run purge ...
>> If you cannot clean a host at once try to make your delete policy longer
>> (100 days or more) first. I had these problems first too, but since i
>> clean every hour, problems are gone.
>>
>> Flo
> Ok, got it running with --no_transactions, but it has been running for
> 3 days!!, this can't be right.... The DB runs on Xeon servers (HP
> Proliant), maybe there's a place to tweak the process?, maybe mysql?,
> it's going, and going, and going, and going.....
> 

The bigger the database the longer a cleanup takes. You could make it
"incremental" if you first use large values for the deletion policies
(example 100 days) and then reduce this value at every cleanup step.
Database server tuning can help - but in this case large delete
operations will take some time.
Another idea could be to make it the other way round: If you want to
delete 90% and keep 10% of the data, then it could be better to copy the
data you want to keep in a new database. This requires some sql
knowledge and some understanding of the perfparse table schema.

Flo

Re: [Perfparse-users] Tables cleanup

[Ciro I. <cy...@gm...>]

2007/1/28, Florian Gleixner <fl...@bi...>:
> Ciro Iriarte wrote:
> > 2007/1/22, Florian Gleixner <fl...@bi...>:
> >> Hi,
> >>
> >> the --no_transactions switch was made for people that have no innodb
> >> table type. You can try. If you disable inserts, transactions are not
> >> needed. If you did not cleanup your tables for a long time (or never) i
> >> would set the deletion policies only for one host, run a purge, add
> >> another host, run purge ...
> >> If you cannot clean a host at once try to make your delete policy longer
> >> (100 days or more) first. I had these problems first too, but since i
> >> clean every hour, problems are gone.
> >>
> >> Flo
> > Ok, got it running with --no_transactions, but it has been running for
> > 3 days!!, this can't be right.... The DB runs on Xeon servers (HP
> > Proliant), maybe there's a place to tweak the process?, maybe mysql?,
> > it's going, and going, and going, and going.....
> >
>
> The bigger the database the longer a cleanup takes. You could make it
> "incremental" if you first use large values for the deletion policies
> (example 100 days) and then reduce this value at every cleanup step.
> Database server tuning can help - but in this case large delete
> operations will take some time.
> Another idea could be to make it the other way round: If you want to
> delete 90% and keep 10% of the data, then it could be better to copy the
> data you want to keep in a new database. This requires some sql
> knowledge and some understanding of the perfparse table schema.
>
> Flo
>
Well, after 2 weeks running i had to kill the process, i will take the
incremental approach and if that fails i'll try to extract only the
needed data and probably skip the RAW data insertion completely in the
future (didn't find good use for it yet)

Thanks a lot

Ciro