Welcome, Guest. Please login or register.

Author Topic: cyclic DB clean up  (Read 11766 times)

dobst

  • Full Member
  • ***
  • Posts: 144
    • View Profile
cyclic DB clean up
« on: February 28, 2013, 01:54:52 PM »
Actually our db grows very fast especially the tables events and iftrack. iftrack gets bigger because several mac addresses are togglein between two interfaces, seems to be a load balancing or redundancy mechanism.

Is there a cyclic clean up which deletes unnessesary entries from the db to prevent garbage? How long are entries kept in the db?


solution: attached script


new in 17.07.2013:
  • resolve dependencies for old devices and delete them (configs, links, logs, modules, monitoring, etc.)
  • delete files of old devices, such as configs and rrds
« Last Edit: July 17, 2013, 04:45:37 PM by dobst »

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2532
    • View Profile
    • NeDi
Re: cyclic DB clean up
« Reply #1 on: March 04, 2013, 11:06:09 PM »
It's not automated yet. However in System-Export you can select a query from the bottom of the export-selectbox to trim those tables. I know it's well hidden, but at least something...
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

dobst

  • Full Member
  • ***
  • Posts: 144
    • View Profile
Re: cyclic DB clean up
« Reply #2 on: March 22, 2013, 05:21:21 PM »
Hi there,

I wrote a script to automate these steps. You can use it once or as a cronjob. Currently it does the followting steps:

deleting old events (1/7)
deleting old interface tracking information (2/7)
deleting interface tracking information of toggling interfaces (3/7)
deleting old ip tracking information (4/7)
deleting old devices (5/7)
deleting logs (6/7)
optimizing tables (7/7)

Parameters are fetched from the nedi config file.

12.06.2013:
New version as download available below.
Fixes: Script adapted to new db scheme of v1.0.8
« Last Edit: June 12, 2013, 03:06:00 PM by dobst »

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2532
    • View Profile
    • NeDi
Re: cyclic DB clean up
« Reply #3 on: March 24, 2013, 01:08:02 AM »
Cool, I'll add it to the archive, if that's ok with you...tx!
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

dobst

  • Full Member
  • ***
  • Posts: 144
    • View Profile
Re: cyclic DB clean up
« Reply #4 on: March 24, 2013, 07:07:26 PM »
Sure, you can add it. It's the first version, so any suggestions and improvments are welcome.

dobst

  • Full Member
  • ***
  • Posts: 144
    • View Profile
Re: cyclic DB clean up
« Reply #5 on: April 04, 2013, 09:10:10 AM »
I added a new version with some bugfixes and improvements. See 3rd post.
« Last Edit: April 04, 2013, 09:17:57 AM by dobst »

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2532
    • View Profile
    • NeDi
Re: cyclic DB clean up
« Reply #6 on: April 04, 2013, 11:01:35 PM »
tx, added your name too :)
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

mduling

  • Jr. Member
  • **
  • Posts: 94
    • View Profile
Re: cyclic DB clean up
« Reply #7 on: April 11, 2013, 07:46:22 PM »
Does this script need to be updated for 1.08?  I'm running the latest RC and it is giving me this:

optimizing tables (7/8)
+-----------------+----------+----------+-----------------------------------+
| Table           | Op       | Msg_type | Msg_text                          |
+-----------------+----------+----------+-----------------------------------+
| nedi.chat       | optimize | status   | Table is already up to date       |
| nedi.configs    | optimize | status   | Table is already up to date       |
| nedi.devdel     | optimize | Error    | Table 'nedi.devdel' doesn't exist |
| nedi.devdel     | optimize | status   | Operation failed                  |


Also, I was getting this message until I inserted a space between & and >>

./nedi_db_maintenance.sh: line 20: syntax error near unexpected token `>'
./nedi_db_maintenance.sh: line 20: `      mysql --host="$dbhost" --user="$dbuser" --password="$dbpass" "$dbname" --ssl -B -e "$sql" &>> $logfile'

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2532
    • View Profile
    • NeDi
Re: cyclic DB clean up
« Reply #8 on: April 11, 2013, 09:00:30 PM »
Indeed devdel doesn't exist anymore and I've also fixed the space. Thanks!
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

dobst

  • Full Member
  • ***
  • Posts: 144
    • View Profile
Re: cyclic DB clean up
« Reply #9 on: April 11, 2013, 10:01:32 PM »
Sorry for that. I tested the script against the current version v1.07. When we've set up our server to SLES 11 (OpenSuse at the moment) I will move to 1.08 and fix this. But I didn' get the space-error, maybe a different version of bash? Thanks for reporting.
« Last Edit: April 11, 2013, 10:04:02 PM by dobst »

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2532
    • View Profile
    • NeDi
Re: cyclic DB clean up
« Reply #10 on: April 11, 2013, 10:27:24 PM »
Pity, NeDiO doesn't have bash. Add it with pkg_add, if you get this error on OBSD:
/var/nedi/contrib/nedi_db_maintenance.sh[85]: syntax error: `(' unexpected

With bash I get this one, but all looks good, if I enter FLUSH LOGS;RESET MASTER; on the SQL prompt:
ERROR 1227 (42000) at line 2: Access denied; you need the RELOAD privilege for this operation

But it actually looks really good! I'll probably just have to add it :) Again, many thanks for that dobst!
« Last Edit: April 11, 2013, 11:45:44 PM by rickli »
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

dobst

  • Full Member
  • ***
  • Posts: 144
    • View Profile
Re: cyclic DB clean up
« Reply #11 on: April 17, 2013, 10:36:30 PM »
if I enter FLUSH LOGS;RESET MASTER; on the SQL prompt:
ERROR 1227 (42000) at line 2: Access denied; you need the RELOAD privilege for this operation

That's because of missing rights. Give nedi these rights:

Code: [Select]
GRANT RELOAD ON *.* TO 'nedi'@'localhost';

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2532
    • View Profile
    • NeDi
Re: cyclic DB clean up
« Reply #12 on: April 17, 2013, 11:19:29 PM »
I can see that :) I'm wondering though, why it works when issuing those commands manually?
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

dobst

  • Full Member
  • ***
  • Posts: 144
    • View Profile
Re: cyclic DB clean up
« Reply #13 on: April 18, 2013, 12:18:55 PM »
Maybe you're using diffrent users? When you start mysql from cli as root you've got diffrent rights as the nedi db user, so you need to give nedi the rights you have as root user. To avoid such mistakes.

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2532
    • View Profile
    • NeDi
Re: cyclic DB clean up
« Reply #14 on: April 20, 2013, 12:42:16 AM »
Umpf, thought I was using the nedi user :)

However, I'm just trying to make everything a bit more secure and I'm not sure I want leave *.* access to the nedi user. A init has to be done with root as well and so this would be more consistent here too...
« Last Edit: April 20, 2013, 01:07:34 AM by rickli »
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo