Using MySQL for logging views

MySQL & MariaDB Other MySQL® Questions
1

Hello–

I have always parsed logs to get views for particular pages, however I am wondering if there is a good design for storing data in a MySQL table for later use in analytics?

My thought is to have one table which has optimal performance for write actions (logging) and another table (or set of tables) to query against for analytic purposes. Ideally the second set would have indexes.

In particular, does have any suggestions for the following?

  1. Best engine for frequent writes (for logging obviously)?
  2. Is my assumption of having one table which has no indexes (for faster inserts) then another table (with indexes for queries) even a good design?
  3. If point two is a good design, what is the best mechanism for migrating records? I assume that replication here would not really work if one table has indexes and another does not…but I am not a pro when it comes to replication.

I have many more questions to follow up these, however these three are my main questions after combing the Internet (and this site) for a few days.

Thanks,
Ed

2

Ed,

Store the data in a relational database like MySQL and use SQL to query it could be convenient. The original logs don’t have to be directed(or duplicated) into a table, unless you have a good reason for that. The output from the parser is the data of interest for querying and aggregation, and need to go into a schema.

For storage engine, InnoDB and MyISAM are the two primary choices. InnoDB is ACID compliant, supports transactions, foreign keys, and locks in row level, etc, while MyISAM doesn’t support transaction, foreign key, on the other hand, avoids overhead from those. MyISAM has table level locking and allows much less concurrency. So, it depends on your usage pattern. If you’d be the only user, and you only read the data, MyISAM could be enough.