MySQL Storage Engine

MySQL Storage Engine:

Storage Engine is a software component of DBMS that take care of how  data is stored and retrieved. MYSQL supports various types of storage engines each having its own features, strengths and trade-offs.

The default storage engine used by MYSQL prior to version 5.5 was MyISAM. Since MYSQL 5.5 the default storage engine used is INNODB. Selecting appropriate storage engine can make a significant impact on an application in terms of performance. 

To check the list of storage engines supported by your MYSQL database, execute below statement:

show engines;

To check storage engine used by specific application database, execute following statement:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'espritkm'

To change the default storage engine used by MYSQL set the default-storage-engine variable in my.cnf conf file of MySQL.

To change the default storage engine used by current mysql session, execute

SET storage_engine = MYISAM

We can also specify the default storage engine when creating table:

Create table test (  

  `id` int NOT NULL AUTO_INCREMENT,

  `data` varchar(100),

  primary key (`id`)

) ENGINE = INNODB;

To alter storage engine used by existing table:

Alter table test ENGINE = MYISAM;

But we should be very careful while switching to other storage engine type because switching to storage engine that does not support the same index, field types or sizes may lead to data loss. 

Before selecting storage engine for our database table, we should know the core functionality provided by each engine. We can bifurcate the core functionality into four parameters:

• 1. Data type support:
• 2. Transaction support (ACID compliant)
• 3.Types of indexes support
• 4 Type of locking support

MySQL supports several storage engines. The standard storage engine supported by MySQL are:

• InnoDB
• MEMORY ( formerly known as HEAP)
• MyISAM
• BDB (BerkeleyDB)
• MERGE
• EXAMPLE
• ARCHIVE
• CSV
• BLACKHOLE

InnoDB:

• Has transaction support. It is ACID compliant storage engine.
• Provides row level locking thus supports parallel insert/update operations on the same table.
• Only storage engine that provides foreign key constraint support. This ensures data integrity.
• InnoDB supports crash recovery. If we plug out the box, and then reboot, when MySQL starts up, it will rollback all standing and open transactions that were interrupted by the power failure
• Provides caching for data in memory as well as on disk, which provides large increase in performance gain.
• Support for FULLTEXT indexes is available in MySQL 5.6.4 and higher version.
• InnoDB tables arrange table data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups.

Use-case:

•  InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
•  Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

MyISAM:

• MyISAM is based on ISAM (Indexed Sequential Access Method), an indexing algorithm developed by IBM that allows retrieving information from large sets of data in a fast way. 
• MyISAM is best choice for tables that have very less insert/update rate but very high select rate. It is very fast storage engine that provides best reading speed of all storage engines available in MySQL.
• Uses table level locking thus limits multi-user concurrency
• No transaction support
• Does not have support  for data integrity i.e. no foreign key constraint supported
• The max number of rows supported for MyISAM is ~4.29E+09 with up to 64 indexes per table. Fields of TEXT/BLOB type can also be fully indexed for cases such as searching

Use-case:

• Read-only tables.  Can be used for tables that are never or rarely modified.
• Replication configuration. In replication environment we can make an InnoDB based Master database which is used for writing and processing data and MyISAM-based Slave database which is used for reading.

MEMORY:

• It stores table content in memory.  Data gets lost when server is restarted. But the table definition persist as it stores table definition on disk. To populate a memory table when the MySQL server starts, we can use --init-file option. For example, you can put statements such as INSERT INTO ... SELECT or LOAD DATA INFILE into this file to load the table from a persistent data source.
• Fast data retrieval as it stores data in memory.
• Provides table-level locking.
• No Support for transaction.
• Supports  both HASH and BTREE indexes. By default it uses HASH index.
• Memory table does not support BLOB and TEXT column types
• Memory is not reclaimed if individual rows are deleted from a Memory table. Memory  is reclaimed only when the entire table is deleted. Memory that was previously used by rows that have been deleted will be re-used for new rows only within the same table. To free up the memory used by rows that have been deleted, use ALTER TABLE ENGINE=MEMORY to force a table rebuild.
• The data is lost when the database is restarted.

Use-case:

• Can be used when temporary tables are required and fast data access is needed. It is ideal for creating temporary tables or for quick lookups.
• It can also be used to access information on the fly basis for better processing. Can also be used to store session or caching data.

MERGE:

Provides functionality to combine many identical MyISAM tables into one logical table. Here identical means that the tables in collection should have identical column and index information.

Use-case:

Good for data warehousing environments.

CSV:

• CSV storage engine does not store data in binary format, its stores data in CSV format (comma separated values). This creates limitation in storing data. It is not an efficient storage type to store large volume of data, although supports BLOB data type.
• Provides table level locking
• No indexing support
• No transaction support

Use-case:

As it stores data in CSV format, it is effective and easiest method for data exchange. CSV files generated can be easily imported to many different software packages. Best use-case is when we want data to be transfer to spreadsheet for later use.

FEDERATED :

FEDERATED storage engine  provides the functionality to access remote MySQL database without using replication or cluster technology. It offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically.

Use-case:

Good for distributed environments.

Limitation:

• Remote server to be accessed must be MySQL server.
• The remote table must exist before we try to access remote table through Federated table.
• No support for transaction.
• FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE and indexes. It does not have support for DROP, ALTER statement.
•  FEDERATED storage engine does not work on query cache.

ARCHIVE:

The ARCHIVE storage engine is used to store large volume of data as a small footprint. It stores the data in compressed form. It support only select and insert operation. It has no support for indexes.

Use-case:

As it stores data in compressed form and does not allow to modify data, it can be used to store log data or sales transaction data or old invoice data or any information that is not in active use.  

BLACKHOLE:

BLACKHOLE engine does not actually store any data. Although it allows us to create tables and indexes, all SQL statements that would add or update information to the database are executed without writing any data. It seems like of no use, but it does allow us to test database structures and play with table definitions without actually creating any data. Most importantly, if statement based binary logging is enabled, SQL statements executed on BLACKHOLE databases are written to the binary log and therefore they can be replicated to slave databases. This can be useful as a repeater or filter mechanism.

Use-case:

Consider your application requires slave-side filtering rules, but transferring all binary log data to the slave first results in too much network traffic. In such a scenario, it is possible to set up on the master host a “dummy” slave process whose default storage engine is BLACKHOLE.

The master writes to its binary log. The “dummy” mysqld process acts as a slave, applying the desired combination of replicate-do-* and replicate-ignore-* rules, and writes a new, filtered binary log of its own. This filtered log is provided to the slave.

The dummy process does not actually store any data, so there is little processing overhead incurred by running the additional mysqld process on the replication master host. This type of set up can be repeated with additional replication slaves.

Conclusion:

Apart from the storage engines mentioned above, MySQL also provide functionality to develop custom storage engine and integrate it in MySQL server with the help of  Pluggable Storage Engine Architecture. No storage engine is ideal for all use-cases. Each have its own pros and cons. If we want data to be secured, then InnoDB is the way to go. If we want fast data retrieval  then we must go for MyISAM. Thus selection of storage engine depends on application requirement.

Reference URL:

http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html

Create CSS3 Gradients

What is Gradients
Gradient is a smooth transition from one color to another. Its a color technique where one color gradually fades into another color. Several color combinations can be applied to a single element. In most of the popular sites logos, menus, buttons are created just by using color variations in subtle, very creative way. Gradient enhance the look of a web page and makes the web page look more richer. In older versions of browsers gradient was produced by using images given in a fallback attribute. But now gradient are supported by almost all the latest version of browsers and you can create gradient using CSS3 in your own style. 


Types of Gradient:

• Linear Gradient
• Radial Gradient

Linear Gradient
The most common type of gradient you will see is a linear gradient of two colors or multiple colors. This means that the gradient will move in a straight line changing gradually from the first color to the second along that line. 
Linear Gradient looks like:

Syntax to define Linear gradient
      linear-gradient(pos, #AAA B, #XXX Y);
Where:
pos = the position of  first colour, giving direction to the gradient
#AAA = primary colour
B = where the fade begins (%)
#XXX = secondary colour
Y = where the fade begins (%)
Position can be projected in following ways

• Horizontal
• vertical
• Upper side diagonal
• Lower side diagonal

Setting Horizontal Gradient:

linear-gradient(left, # F4305E  0%, # F4305E  100%);

In above example transition of color(fading) will start from left side to right side horizontally.

linear-gradient(right, #F4305E 0%, #EFACBD 100%);

In above example transition of color will start from lright side to left side horizontally. 

Now to set it as the background of a DIV you have to write

div {

background: linear-gradient(left, #F4305E  0%, #EFACBD 100%)

}

Div with above gradient applied will look as shown in below image:

Browser Specific syntax for CSS3 gradient:
To make your gradient to work cross-browser, you need to use browser specific syntax for most browsers and filter for Internet Explorer 9 and lower (actually 2 filters). 


For Internet Explorer
IE 5.5–7 
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#F4305E ', endColorstr='# EFACBD ', GradientType=1);

 IE 8–9 
-ms-filter: "progid:DXImageTransform.Microsoft.gradient(startColorstr='#F4305E', endColorstr='# EFACBD  ', GradientType=1)";

 IE 10 +
-ms-linear-gradient(left, # F4305E  0%, # EFACBD   100%);


For Mozilla: -moz- prefix is added
-moz-linear-gradient(left, #F4305E 0%, #EFACBD 100%);


For Chrome/ Safari: -webkit- prefix is added
-webkit-linear-gradient(left, #F4305E 0%, #EFACBD 100%);


For Opera 11.1+ : -o- prefix is added
-0-linear-gradient(left, #F4305E 0%, #EFACBD 100%);


Setting Vertical Gradient:
To give vertical gradient pos parameter in syntax mentioned above will take value top if we want to have color transition from top end or to have color transition from bottom end, pos will take value bottom
Example
For Fire fox:

background: -moz-linear-gradient(top,  #66dd04 0%, #84bc56 49%, #c5e0af 100%);

For Chrome 10+, Safari 5.1+:

background: -webkit-linear-gradient(top,  #66dd04 0%,#84bc56 49%,#c5e0af 100%);

For Opera 11.10+

background: -o-linear-gradient(top,  #66dd04 0%,#84bc56 49%,#c5e0af 100%); 

For IE 10+:

background: -ms-linear-gradient(top,  #66dd04 0%,#84bc56 49%,#c5e0af 100%); 

For IE-9

filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#66dd04', endColorstr='#c5e0af',GradientType=0 );

In above example I have used three colors to make gradient. We can use any number of color and can set their fading %  accordingly.
So the gradient made above will look like:

Setting Diagonally Gradient:


Up side Diagonal
To give up side diagonal gradient pos parameter in syntax mentioned above will take value 45deg 

For Fire fox:

background: -moz-linear-gradient(45deg,  #66dd04 0%, #84bc56 49%, #c5e0af 100%);

For Chrome 10+, Safari 5.1+:

background: -webkit-linear-gradient(45deg, #66dd04 0%,#84bc56 49%,#c5e0af 100%);

For Opera 11.10+:

background: -o-linear-gradient(45deg,  #66dd04 0%,#84bc56 49%,#c5e0af 100%); 

For IE 10+:

background: -ms-linear-gradient(45deg,  #66dd04 0%,#84bc56 49%,#c5e0af 100%); 

Upside diagonal Gradient looks like:

Down side Diagonal Gradient
To give upside diagonal gradient pos parameter in syntax mentioned above will take value -45deg 


For Fire fox:
background: -moz-linear-gradient(-45deg,  #66dd04 0%, #84bc56 49%, #c5e0af 100%);

For Chrome 10+, Safari 5.1+:

background: -webkit-linear-gradient(-45deg, #66dd04 0%,#84bc56 49%,#c5e0af 100%);

For Opera 11.10+:

background: -o-linear-gradient(-45deg,  #66dd04 0%,#84bc56 49%,#c5e0af 100%); 

For IE 10+:

background: -ms-linear-gradient(-45deg,  #66dd04 0%,#84bc56 49%,#c5e0af 100%); 

Down side diagonal Gradient looks like:

Radial gradients:

In Radial Gradient  color will start fading from center to outward direction.

Syntax to define radial Gradient:

For Fire fox:
background: -moz-radial-gradient(center, ellipse cover,  #66dd04 0%, #84bc56 49%, #c5e0af 100%);

For Chrome 10+, Safari 5.1+:

background: -webkit- radial -gradient(center, ellipse cover , #66dd04 0%,#84bc56 49%,#c5e0af 100%);

For Opera 11.10+

background: -o- radial -gradient( center, ellipse cover ,  #66dd04 0%,#84bc56 49%,#c5e0af 100%); 

For IE 10+:

background: -ms- radial -gradient( center, ellipse cover ,  #66dd04 0%,#84bc56 49%,#c5e0af 100%); 


Radial Gradient looks like:

This was all about to create gradient. You can add more beauty to gradient applied on any element by adding gradient effects on mouse hover and on mouse out.

Best Practices to Enhance Performance of Website

Performance of a website can be visualized in two ways:

• Server Performance
• Perceived Performance

Server Performance is concerned with the number of request a server can handle at a time and time taken to process request

Perceived Performance is concerned with speed of website from visitor(end user) perspective. Even if the server performance is high , site might appear slow to visitor because of poor client side performance and due to this visitor can distract away from your website. 

There are several areas in which performance can be improved. But as front end is most commonly accessible part of website by visitor so client side performance matters a lot. Thus for the user, it is the speed of Web page delivery that indicates the performance level, not how fast the application is executed on the server. In this blog I will throw light on ways to enhance client side performance of website.

Minimize HTTP Requests

Yahoo did experiments on improving performance of website and concluded that popular sites spend 5%-38% time downloading HTML document, remaining 65%-95% time is spend in making HTTP request to get the components(Images, Stylesheets, Scripts, Flash and more...) used in HTML web page. More component used, more will be the HTTP request, But in rich UI web pages, components used are more but you have several ways to minimize HTTP request while also maintaining rich UI design web pages.

Combining Script / Style sheet files into single file :

By combining all scripts file into a single script file, and similarly combining all CSS into a single style sheet you can reduce number of HTTP request.

CSS Sprites: 

You can reduce number of image requests and even over all size of image by combining all images into a single image(sprites) and can use background-image and background-position properties to display the desired image part. Creating sprites is hard, requires arcane knowledge and lots of hit and trial efforts. But no worries there are web services like SpriteMe available which can help you in creating sprites.

Image Map:

Image Maps are used to combine multiple images into a single image. The overall size is about the same, but reducing the number of HTTP requests speeds up the page. Image maps only work if the images are contiguous in the page, such as a navigation bar. Defining the coordinates of image maps can be hard and error prone. So this is rarely used technique.

Use Content Delivery Network

Most of the end user time is spend in loading component of web page like image, script, style sheet, flash etc. So to reduce loading time, it is possible to disperse contents. This can be achieved by CDN. CDN is a group of servers around the world, optimized for fast delivery of static files and positioned to be as close as possible to your site's visitors.

 Advantage of using CDN 

• CDN servers are faster and closer to your visitors. 
• They're also usually on another host like cdn.example.com for your site. This means your browser will open 5 requests to example.com and  5 requests to cdn.example.com at the same time. Allowing it to load even more at the same time. 

Some large Internet companies own their own CDN, but it's cost-effective to use a CDN service provider, such as  Akamai Technoligies , Edge Cast, Level 3 etc. For small companies and private web sites, the cost of a CDN service can be prohibitive, but if the target marget is large and global then CDN is necessary to achieve fast response times of website. 

Caching:

Web servers and browsers use a tool called a cache to help pages load more quickly. When browser cache is unable, the first time you load a web page, it is loaded straight from the server, but if later during the same session if you make request for the same page, then it is loaded from browser cache instead of making HTTP request for the same page. This loads the page quickly and reduces strain on server cache can be controlled by the user  at browser level and the Web developer at server level.

Three Ways to control Server Cache

• Via <meta> tags (<meta http-equiv="Expires"...>)
• Programmatically by setting HTTP headers (CGI scripts etc.)
• Through web server configuration files (httpd.conf)

Use HTTP Compression

Time taken to transfer HTTP request and response across depends on network bandwidth and network traffic. End user bandwidth and internet service provide are beyond control for development team. But still this response time can by reduced by compressing HTTP response.

 HTTP/1.1 web clients indicate support for compression with the Accept-Encoding header in the HTTP request.

      Accept-Encoding: gzip, deflate

If the web server sees this header in the request, it may compress the response using one of the methods listed by the client. The web server notifies the web client of this via the Content-Encoding header in the response.


 Content-Encoding: gzip

Gzipping reduces the response size by about 70%. In Apache, the module configuring gzip depends on  version: Apache 1.3 uses mod_gzip while Apache 2.x uses mod_deflate. There are known issues with browsers and proxies that may cause a mismatch in what the browser expects and what it receives with regard to compressed content.  But Apache modules help out by adding appropriate Vary response headers automatically.

Reduce DNS Lookups

The Domain Name System (DNS) maps hostnames to IP addresses. When you type in your domain name into your browser, a DNS resolver contacts the browser to return with the servers IP. Usually the browser takes some time to complete this process.  The browser can't download anything from this hostname until the DNS lookup is completed. Reducing the number of unique hostnames has the potential to reduce the amount of parallel downloading that takes place in the page. Avoiding DNS lookups reduces the response times.

Reduce  redirects

Ways to redirect user to different URL

• Server ways (301 and 302)

      HTTP/1.1 301 Moved Permanently
      Location: http://example.com/newuri
      Content-Type: text/html

• Meta refresh tag and Java scipt 

        <meta http-equiv="refresh" content="5; url=http://example.com/">

    

        <script type="text/javascript">

        window.location="http://www.google.com"

        </script>

Redirect is done to connect the old web page to a new URL. But reducing this to a minimum is the best practice to increase the performance of a web page.Redirects slow down the users experience because inserting a redirect between the user and the HTML document delays everything in the page since nothing in the page can be rendered and no components can start being downloaded until the HTML document has arrived.

One of the most wasteful redirects happens frequently and web developers are generally not aware of it. It occurs when a trailing slash (/) is missing from a URL that should otherwise have one. For example, going to http://example.com/documents results in a 301 response containing a redirect to http://example.com/documents/ (notice the added trailing slash). This is fixed in Apache by usingAlias or mod_rewrite, or the DirectorySlash directive if you're using Apache handlers.

Reduce the Number of DOM Elements

Less DOM elements means better page performance. It means better javascript performance, especially in older browsers.

Example: 

Use

<ui id="navigation">

//code goes here

</ui>

Instead of

<div id="navigation">

<ui>

//code goes here

</ui>

</div>

Use additional element only when required

Place Style sheet in Header

Style sheets to the document HEAD makes pages appear to be loading faster  because putting stylesheets in the HEAD allows the page to render progressively. This is especially important for pages with a lot of content and for users on slower Internet connections. The HTML specification clearly states that style sheets are to be included in the HEAD of the page, A, [LINK] may only appear in the HEAD section of a document.

Place Scripts at the Bottom

Scripts causes progressive rendering to stop for all content below the script until it is fully loaded. Moreover, while downloading a script, browser does not start any other component downloads, even on different hostnames. 

In some situations it's not easy to move scripts to the bottom. If, for example, the script uses document.write to insert part of the page's content, it can't be moved lower in the page. There might also be scoping issues. 

Alternative solution is to use deferred scripts. The DEFER attribute indicates that the script does not contain document.write, and is a clue to browsers that they can continue rendering.  If a script can be deferred, it can also be moved to the bottom of the page. That will make your web pages load faster.

Avoid CSS Expressions:

CSS Expression have become deprecated since IE8. 

Example of CSS Expression 

 The background color could be set to alternate every hour in below CSS expression

 background-color: expression( (new Date()).getHours()%2 ? "#B8D4FF":"#F08A00" );

In above example , the expression method accepts JavaScript expression. CSS property is set to the result of evaluating the JavaScript expression. The expression method is ignored by other browsers, so it is useful for setting properties in Internet Explorer needed to create a consistent experience across browsers.

One way to reduce the number of times your CSS expression is evaluated is to use one-time expressions, where the first time the expression is evaluated it sets the style property to an explicit value, which replaces the CSS expression. If the style property must be set dynamically throughout the life of the page, using event handlers instead of CSS expressions is an alternative approach. If you must use CSS expressions, remember that they may be evaluated thousands of times and could affect the performance of your page.

Simplify tables and avoid nested tables

If more tables /layout are nested,longer it will take for the Web browser to render the Web page.

Minify Javascipt and CSS

Minification is the practice of removing unnecessary characters from code to reduce its size, thus improving load times. When JS/CSS code is minified all comments are usually removed, as well as unneeded "white space" characters like space, new line, and tab. Various online tools are available to minify js ans css files.

Optimize Images

Optimizing images reduces the size of image without doing any harm to image quality. Optimized image takes less time to load on a web page. Various online tools are available to optimize images. One of them mostly used is Yahoo Smush.it


Make Ajax Cacheable