Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(This is somewhat a follow up on my last question)

I am using MySQL 5.5 with innodb.

My Log table consists of two columns. One for user number, and the other for a string consisting of IP, request method, request body, and so on. The two columns use no index.

Usually the content of the logs are below 200 letters. However, there are special cases that the content go up to more than 5000 words.

I've thought of three ways to log long contents. First is to split the content into 200 words and put it in several rows. Second is to set the data type to TEXT or VARCHAR(10000). Third is to make a new table only for storing long contents.

What is the normal approach to storing logs?

share|improve this question
    
"make a new table only for storing long contents." -- I'd do that –  Phil Dec 9 '13 at 15:06
add comment

2 Answers 2

up vote 5 down vote accepted

I would not use any of those options: I would properly split out each part of the log entry, putting each in its own column of appropriate maximum length. This is more work of course as you need to break the data down for insert if you are getting it as a single string (it'll also consume a bit more disk space, probably) but it will greatly increase the analysis you can do on the logs later.

Also define useful indexes. At very least something on that ordering column and/or if there is a date+time in those logs there should be an index covering that.

If you are just storing logs as blocks of text per line then there is very little point pushing them into the database: just keep them in their original flat files where you can use grep and other such tools. If you want to take advantage of the database's features at all, create a table optimised for making best use of the DB instead of optimising solely for easiest insert convenience.

You could also break down the consistent parts of query strings, form parts, and cookies (I'm assuming these are HTTP(S) logs) into their own sub-tables, but unlike separating basic log variables that is probably going to be considerable overkill.

share|improve this answer
1  
I like this approach better, but it would involve writing code to split all the existing log entries to conform to this more structured solution. It would be good if the OP plans to query all of these specific fields, but it's not clear if the OP actually wants to do this, or if they're just using the table as a slightly more sophisticated version of a text-file log, in which case they might not want to go with the extra effort. –  FrustratedWithFormsDesigner Dec 9 '13 at 16:05
add comment

If you split the long entries into multiple records, you'll need to start adding sequencing information so you can reproduce the "single" entry as a single log item. Changing the datatype of your main log column would work, but if the vast majority of log entries won't need it, then I wouldn't go that route. Your third option of moving the very long log entries to a separate table (and you'd have to have a long_log_entry FK to it) sounds like the most reasonable: minimal changes to your main table, minimal application logic changes (no need for entry splitting/sequencing), and all the extra data is separate in its own table.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.