prelude db maintenance

I know I have mailed the following below before, I got a response from someone that had to move the db as well.  I lost that email and forgot who it was.  I did shut down my sensors so no know information was coming in, which I think that is what the person did.  I am not having any luck.  I let the preludedb-admin command that I issued below run for 3 days and the db never changed size.



Anything I and doing wrong?  I am entering the innodb and key buffer size to the my.cnf file with the right syntax?  Is there tables I can truncate? Which ones do I leave alone so I don't clear out the sensors and the usernames or not cause damage to the system?



****************************************************



My Prelude deployment will consist of 14 sensors and one main correlator.  I only have 5 sensors up so far and it seems like the main correlator with Prewikka is bogging down.  When I look at the resources using "top" it seems MySQL is the one working really hard.



The database is 175 gig so far and I need a way to delete everything except for the last couple of days.  I issued both:



preludedb-admin delete heartbeat "type=mysql name=prelude user=prelude pass=xxxxxx" --criteria "heartbeat.create_time < 2010-11-15"

and:

preludedb-admin delete alert "type=mysql name=prelude user=prelude pass=xxxxxx" --criteria "alert.create_time < 2010-11-15"



Heartbeats complete and return me to a prompt just fine.  With alerts, at first, I was receiving the following error:







delete event failed: The total number of locks exceeds the lock table size.



Error at transaction 1000. Use --offset 1000 to resume operation.



18446744071566262268 'delete' events processed in 42.289796 seconds (0.000000 seconds/events - 436198464373120896.000000 delete/sec average).



18446744071566262268 events processed in 42.289796 seconds (0.000000 seconds/events - 436198464373120896.000000 events/sec average).







I then had a suggestion to make the following changes to the my.cnf file and did so:



key_buffer              = 20G  (70 % of ram)



innodb_buffer_pool_size = 20G  (70 % of ram)







I ran the preludedb-admin command again for the alerts and it never returned me to a prompt for 2 days, I finally killed it and the DB was unchanged in size.  I believe it was deleting alerts as I could see numbers decreasing on some alerts in prewikka.  I did not get the error again.







Any ideas on how to trim the db?  Am I doing something wrong with preludedb-admin?  Can I truncate the tables?  Which tables are safe to truncate without losing user logon info and my sensors?

_______________________________________________
Prelude-user site list
[email protected]
http://lists.prelude-technologies.com/mailman/listinfo/prelude-user