LOGGING(File logging vs database logging)

So,1st OF ALL what is  logging?

Every system will require a logging system (unless it is a crappy system). There is always a need to have a quick, secure and reliable log to store these information for any further investigation.

Lemme give u a real life example of when are logs useful.
Currently i am engaged in a hudge job portal project.Whenever any jobseeker purchases any service say executive resume or resume critique then immediately his  transaction record is stored in a log.By logging you can track all details of any person who purchases some product.So,In large systems,it becomes really handy to track if some problem occurs unexpectedly.So log is nothing but a proof that transaction has occured which is stored in db in form of table record.Now say for example someday your project head or some investigating authority comes and asks you to show all the transactions since past 4 years with date and time references then you can immediately refer your transaction logs.That’s it!!!!!!!!!!!

“Keeping and maintaining data logs is a corporate best practice and, in many cases, when you consider regulation and industry standards, it’s the law. Still, few companies take advantage of the benefits of log maintenance when it comes to detecting and responding to data breaches.”

Two basic types of logging 

File Logging vs database logging.We need to look at three important things to consider which media to log our details. These are performance, security and reliability.

Log Performance

Performance, performance performance! This is something we all want to know about. Whether file base log or database log is better? We will be looking at long run where our log gets really huge! Delay and performance problem might arise and which media will be more resistant against such problem.

Another good thing to consider between these two media is the extra cost of HTTP requestcomparing to a read and write and the problem of delay arise from huge size.

Log Security

Another thing that every hacker will be interested with is the log file. Valuable information is being stored in our log file and it is necessary to consider how secure can either media gives us.
Log file may even carry sensitive details of our customers which was log by our program. Hence, considering the security risk of having plain text and a database is important to prevent security hole in our system environment.
Each media will have its own way to further secure its media but which is better?

Log Reliability

Why we bother to have a log file if it is unreliable.
This is necessary for a system that is required to keep track of a system that handle important transaction. An unreliable log might miss a log due to various reason such as manual query termination, file lock, database down during logging etc.
It is necessary to have all our log in order to capture important incidents.

Other log criteria

Scalability and flexibility is another thing some of you might want to mention. Migration of server and ease of searching etc. is also points that is important for us to consider as a log that cannot find its detail is consider a useless log.

 Database Logging(salient features)

1)Performance wise, database might be slower when log amount is small. But once the log amount became a huge amount, database based logging might really be much faster.
Also,searching and sorting results are the biggest advantages of db logging .

2) The problem is that it will fight with other urgent query which has higher priority to be executed and table locking. This is usually resolved by using MySQL Insert Delay operation.

Now what is mysql  Insert Delay operation-

a)Many Web sites write data to a database. Usually, the data absolutely must be properly saved, so the default way of adding records (using an SQL “INSERT” statement) ensures that the data is permanently stored on the server’s disks. Doing that takes a relatively long time in computer terms — it’s much slower than most things computers do.
b)In some cases, you might be storing data that’s not quite so important. And if it means your application can run much faster, you might be willing to risk a very small chance of data loss. That’s where MySQL’s “INSERT DELAYED” statement, which works with MyISAM table types (but not InnoDB tables), can be useful. (Tables are created as type MyISAM by default, so most tables are eligible to benefit from this tip.)
c)Adding the word “DELAYED” to your statement tells MySQL to remember the data to be added and return immediately to your application. MySQL will then write the data as soon as the database isn’t busy. This lets your insertion happen (effectively) immediately, and reduces the load on the database (and server). Using this technique can give your application a huge performance gain.
d)Since the record is not written immediately, there is a very small chance that the data will be lost before it’s written to the disk. However, the odds of this happening are very small. It would only happen if MySQL crashed before it had a moment of idle time to write out the record, if the server lost power, or if some similar unexpected event happened.
Another problem can be latency!!!!!!!!!!!!!!
3) In term of reliability, using insert delay will risk the chances of our log getting lost especially if the system is a very active one. In a very busy system every few millisecond time interval there will be additional query that makes the database super busy until the insert delay log are pile up and have to wait till the database is quiet to be active. Hence, any accident such my sql die or forcefully terminated, the log query are gone. Furthermore, additional overhead to delay such insert will degrade MySQL performance by a little.

File Logging(salient features)

Fundamentally, logging data to a file consists of three steps:1. Open the target file (or create it if it doesn’t already exist);

2. Append your data to the end of the file;

3. Close the file.

function logToFile($filename, $msg)
// open file
$fd = fopen($filename, "a");
// write string
fwrite($fd, $msg . "\n");
// close file
$v = "Mary had a little lamb";
if (!is_numeric($v)) { logToFile("my.log", "Non-numeric variable
encountered"); }
$a = array("chocolate", "strawberry", "peach");
if (!in_array('fish', $a)) { logToFile("my.log", "No fish available"); }
$conn = @mysql_connect("localhost", "joe", "pass");
if (!$conn)
logToFile("my.log", "Could not connect to database");
die("Could not connect to database");

1) the greatest advantage is its simplicity, the worst problem of file based logging 
is searching.But usually this can be overcome with some formatting and regular expression.

2)Performance wise, it should be directly opposite a database logging where smaller size 
will be better and larger it gets worst.

3)file based logging doesn’t required a call to the database. Hence, everything is done by 
the server scripting language you are using and operation is complete regardless of 
whether the connection is down(as long as the request pass from client to server 
is complete). 

4)The other more critical part to choose file based logging is the problem of file locking 
where only one person is allowed to open the log file at one time. Hence, in a active system 
this might really post a big problem where logging is done intensively .

Why I prefer log files instead of databases

1) Space is not that cheap when using Virtual private servers. Recovering space on live database systems is often a huge hastle and you might have to shut down services while recovering space. If your logs is so important that you have to keep them for years then this is a real problem. Remember that most databases does not recover space when you delete data as it simply re-uses the space – not much help if you are actually running out of space.
I researched on the line highlighted in red-:What does it mean??
Well,say you have total 5gb space utilized by your db.Now u r running out of space.You delete some rows or empty the table(you cant drop or truncate it in real time scenarios,assuming you have a optimized database)Now what do you think…will space become < 5gb…No,not at all,Mysql will send the block of freed space to Freelist(then concepts of PCTUSED and PCTFREE)will come into picture…which you can research yourself!!!!!!Freelist is table specific(ie it will not free db space instead it frees table space means that it will free the table space for furthur updates,insert commands on the same table)…the database space will only be freed if u use TRUNCATE AND DROP commands.
You can go more and more deep on this topic…:)
2) If you access the logs fequently and you have to pull daily reports from a database with one huge log table and millions and millions of records then you will impact the performance of your database services while querying the data from the database.(Reference::http://stackoverflow.com/questions/209497/using-a-sql-server-for-application-logging-pros-cons)
3) Log files can be created and older logs archived daily. Depending on the type of logs massive amounts of space can be recovered by archiving logs. We save around 6x the space when we compress our logs and in most cases you’ll probably save much more.
4) Individual smaller log files can be compressed and transferred easily without impacting the server. Previously we had logs ranging in the 100’s of GB’s worth of data in a database. Moving such large databases between servers becomes a major hassle, especially due to the fact that you have to shut down the database server while doing so. What I’m saying is that maintenance becomes a real pain the day you have to start moving large databases around.
5) Log files only pose problems if you don’t structure your logs properly. You may have to use additional tools and you may even have to develop your own to help process them, but in the end it will be worth it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s