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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
