Not the answer you need?
Register and ask your own question!

Using MySQL for logging views

mrdillingermrdillinger EntrantCurrent User Role Beginner

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.



  • psongpsong Contributor Inactive User Role Beginner

    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.