List:General Discussion« Previous MessageNext Message »
From:nigel wood Date:July 3 2009 10:33am
Subject:Re: Best approach for DB-based event logging?
View as plain text  
Marcus Bointon wrote:

"For the most part this is write-only and is only ever read very rarely, 
but when I do, it will be to retrieve the details of a single user, and 
all I need is the whole history, not individual events."

For your stated requirements the filesystem is probably most efficient.  
It does sound odd from a data retention/archiving and  data retrieval 
times point of view though.  Will the earliest log data be held on line 
and constantly available literally forever?  Will you still want to pull 
back the whole list when a user's history hits 50mb? Would this data 
pass over a network?

Personally I'd use the database and I really wouldn't worry about the 
number of rows. Properly normalised hundreds millions of rows aren't a 
problem until you cant hold indexes in memory, them its time to shard. 
It looks like the logical shard point for http://www.smartmessages.net/ 
is customer.

I'd use bulk inserts where approprate in applications adding lots of 
events and I wouldn't store the text I didn't need. In log messages most 
of the text is the same. I'd store just the needed to generate the 
message and perhaps the templated text of the log message itsself.

I'd use mutiple 'events' tables and tie them together with temporary 
tables and SQL or if efficient enough a view. The first table is written 
to and probably innodb or something else with row level locking and 
foreign keys. The other tables would be 1:N MyISAM compressed read only 
tables (which do support indexes) covered by a merge table. The copy 
from the innodb to new MyISAM tables would be automated as would the 
modification of the merge table. At some point old events could be 
removed by removing the table from the merge table and simply dropping it.


Here;s a rough table stucture. The  indexes in events tables would be  
TargetId. But problably TargetId+EventDate probably eventId+event date 
as you found more uses/added paging.


User/Actor -------> CurrentEvents Innodb<------- EventType
   TargetID            EventId                        EventTypeId
  Username           TargetId                      LogMesssageText 
(optional)
                              TargetEventType
                              EventData             
                              EventDate

                
user/actor-------> ArchivedEventsNNN (MyISAM compressed) <---EventType
                              EventId
                              TargetId
                              TargetEventType                            
                              EventData    
                              EventDate
     
User/Actor -------> MergedHistoricalEvents <--- EventType
                               ***Merge of ArchivedEventsN to M

For maximum speed or If you need to use the log messages in numerous 
languages add a tiny template parser to replace EventData into the 
logMessageText at high speed using a native function . lots of 
application level things can build the messages from string + data of 
course.

http://dev.mysql.com/doc/refman/5.1/en/adding-native-function.html

Just my brainstorm and untried so I'd appreciate other folks thoughts on 
the suggestion but it may be of some use.

Nigel
Thread
Best approach for DB-based event logging?Marcus Bointon1 Jul
  • Re: Best approach for DB-based event logging?Johan De Meersman3 Jul
    • Re: Best approach for DB-based event logging?Marcus Bointon3 Jul
      • Re: Best approach for DB-based event logging?Johan De Meersman3 Jul
  • Re: Best approach for DB-based event logging?nigel wood3 Jul
    • Re: Best approach for DB-based event logging?nigel wood3 Jul
      • RE: Best approach for DB-based event logging?Martin Gainty3 Jul