Re: Indexes in Prelude.* tables

Hi all,

So here're the numbers. The ouput from preludedb-admin with delete command was shown in my previous letter.

Before indexes have been created:
15000 'delete' events processed in 11719.978014 seconds (0.781332 seconds/events - 1.279866 delete/sec average).

After indexes have been created:
541410 'delete' events processed in 4968.052645 seconds (0.009176 seconds/events - 108.978314 delete/sec average).

As you all may see - almost 100 times faster. Indexes had been created with command:

CREATE INDEX _message_ident_index_<table> on Prelude.<table>(_message_ident);

I'm still not sure if this helps only in my case or may be helpful for others.

Best reagrds,
Alexander Afonyashin

----- Original Message -----
From: "Alexander Afonyashin" 
To: [email protected]
Subject: [prelude-devel] Indexes in Prelude.* tables
Date: Wed, 10 Sep 2008 10:22:13 +0300


Hi all,

I've encountered a very annoying problem with deleting alerts from 
Mysql-based Prelude db. It seems it may takes forever to delete 
alerts for particular user's successful logins if there're more 
than 2 million records in Prelude_Alert tables (~20 million rows in 
Prelude_Address table etc.). By examining slow-queries log of Mysql 
I've found that there're a lot of DELETE requests that look like:

DELETE FROM Prelude_Alertident WHERE _message_ident IN (3458579, 
3458582, 3458583, 3458584, 3458585, );
DELETE FROM Prelude_Node WHERE _message_ident IN (3458579, 3458582, 
3458583, 3458584, 3458585, );

There's no index on _message_ident field neither in these tables 
nor others. So it leads to full table scan afaik. Correct me if I'm 
wrong but adding index to table(s) like:

CREATE INDEX _message_ident_index ON _message_ident;

can greatly improve performance of DELETE operations in cases such mine.

Operation I'm performing:

$ preludedb-admin delete alert "type=mysql name=prelude user= 
pass=" --criteria "alert.target(0).user.user_id(0).name == 
'backup_user' && alert.assessment.impact.completion == 'succeeded'"

Best regards,
Alexander Afonyashin

[skipped]

-- 
Be Yourself @ mail.com!
Choose From 200+ Email Addresses
Get a Free Account at www.mail.com

_______________________________________________
Prelude-devel site list
[email protected]
http://lists.prelude-ids.org/mailman/listinfo/prelude-devel