MySQL Database

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