InnoDB Vs MyIsam

So,many of us who use MySQL are familiar with these terms.Only familiar!!!!!!…right??

Have you ever wondered about what these terms actually mean and difference b/w them?No issues..”Genesis” is all about unlocking such issues….So letz start…

Image

These 2 are types of storage engines.So 1st question

What is a Storage Engine?

  1. The storage-engine is what will store, handle, and retrieve information for a particular table.
  2. MySQL can use several different formats for storing data on disk, these are called storage engines or table types. phpMyAdmin allows a user to change their storage engine for a particular table through the operations tab.These formats means sometimes you want temporary data(hash based),sometimes you want row level locking,page-level locking,or u want your data should be stored remotely etc
  3. You can select the storage engine to use on a server, database and even table basis, providing you with the maximum amount of flexibility when it comes to choosing how your information is stored, how it is indexed and what combination of performance and functionality you want to use with your data.

The figure below is self explanatory and simply explains position of storage engine in mysql architecture!!!!

Image

4.The interface between the second and third layers is a single API not specific to any given storage engine. This API is made up of roughly 20 low-level functions that perform operations such as “begin a transaction” or “fetch the row that has this primary key” and so on. The storage engines don’t deal with SQL or communicate with each other; they simply respond to requests from the higher levels within MySQL.

Now letz start with MyISAM(salient features)

  1. ISAM(Indexed Sequential Access Method) .It was the only storage engine available until MySQL 3.23, when the improved MyISAM engine was introduced as the default. ISAM is deprecated.
  2. Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.
  3. Text/Blob fields are able to be fully-indexed which is of great importance to search functions
  4. it lacks transaction capabilities.
  5. uses table-level locking.
  6. With table-level locking, a database with a high number of row inserts or updates becomes a performance bottleneck as the table is locked while data is added. Luckily this limitation also works well within the restrictions of a non-transaction database.

InnoDB(salient features)

  1. Relatively new,so ppl are still unsure about itz performance and reliability.
  2. transaction-safe meaning data-integrity is maintained throughout the entire query process.
  3. InnoDB  provides row-locking, meaning while one query is busy updating or inserting a row, another query can update a different row at the same time. These features increase multi-user concurrency and performance.
  4. Because of its row-locking feature InnoDB is said to thrive in high load environments. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

InnoDB  vs MyISAM

1) InnoDB maintains excellent data integrity at the cost of storage engine housekeeping, If you value your data, you can easily live with that.
MyISAM, being free of such overhead, can be used in a concentrated way to perform lightning-fast SELECTs at the cost of data loss in the event of a crash, especially if tables are set with ROW_FORMAT=DYNAMIC (default).

You should store all static data to MyISAM that is 98% SELECT and 1% UD (Update,Delete) and 1% INSERTs to rows that need not be updated.

You should store all transactional data, whose data integrity matter from an application standpoint, to InnoDB. You can harness both storage engines well in any application when using them together for the right reasons.

2)Innodb supports transactions, MyISAM does not

3)Row-level locking and MVCC in inno db while table level locking in MyISAM

There are many more differences which even I could not understand…If any one finds some more difference plz do leave a comment.But the differences should be in layman terms.

you can refer the following sources:

1)http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/notsowellknown-differences-between-myisam-and-innodb-mysql-server-storage-engines.php

2)http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

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.

<?php
function logToFile($filename, $msg)
{ 
// open file
$fd = fopen($filename, "a");
// write string
fwrite($fd, $msg . "\n");
// close file
fclose($fd);
}
$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 .
 Source:http://hungred.com/useful-information/best-way-log-details-database-or-file-log/

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.