MySQL Replication: Statement based Replication vs Row based Replication

Before discussing the replication formats in MySQL it is necessary to discuss how replication works in MySQL. Why is replication required? The answer lies in my earlier post in which I described the process of scaling MySQL for reads (Scale-Out MySQL). Lets proceed by discussing the replication process. Replication process consists of the following steps:

1. MySQL Master writes any changes that occur to the 'binlog'. binlog is a log that contains any updates/inserts/deletes made on the master MySQL.
2. Slave's I/O thread reads the binlog from the master and writes the events in its 'relaylog'.
3. The MySQL thread reads the events from the relaylog and applies those events to the slave.
4. Steps 1 till 3 are repeated so the slave is synchronized with the master all the time.

Below is the diagram that will aid you in understanding the process of replication in MySQL.

Now that you are aware to some extent about the process of replication, lets dive deep into the replication formats. By replication format I mean the format in which events are recorded in master's binlog. There are three types of replication formats:

1. SBR or Statement based Replication
   In this format of replication, the MySQL master, records the events as SQL statements into the binlog. The statements are picked up by the MySQL slaves and replayed in the same way as they are played at master
2. RBR or Row based Replication
   In this format of replication, the MySQL master, records the events as actual rows that indicate how the rows are changed at the master.
3. Mixed Mode Replication
   This format of replication is a mix of RBR and SBR. MySQL switches the format in real-time depending on the type of event.

Of course there are pros and cons of each format, I will primarily address the RBR and SBR. I will discuss the pros and cons of each replication format type.

Statement based Replication

Pros:

1. As in case of statement based replication the events are logged as SQL statements and not in form of row changes, hence the log files are much much smaller and utilize less storage space.
2. The log file contains all the SQL statements and hence can be utilized for analysis, audit or restoring from backup.
3. In terms of bandwidth, statement based replication is much more efficient because its the queries that are transferred to slave and not the actual row updates.

Cons:

1. Statements that possess non-deterministic properties are difficult to replicate using this format.
2. Statements that use stored procedures or user defined functions (UDF) are considered to be nondeterministic statements and hence are difficult to replicate using SBR.
3. DELETE and UPDATE statements that use LIMIT without using ORDER BY are considered to be nondeterministic and hence cannot be replicated using SBR.
4. UPDATE statements with a WHERE clause that doesn't utilize the index require more locks in SBR then in RBR.
5. INSERT statements that use auto-increment, block other non-conflicting INSERT statements. This is applicable to InnoDB engine. Its a very important point to consider as this point effects concurrancy.
6. More locks are required on the slave for INSERT/UPDATE/DELETE
7. Complex statements that are evaluated and executed on the master, need to go through the same process on the slave before applying changes to the slave.
8. Table definations should be identical on the master and slave.

Row based Replication

Pros:

1. Its the safest form of replication though not as old as statement based replication.
2. Fewer row locks are required for INSERT statements with auto-increment.
3. Fewer locks are required for INSERT/UPDATE/DELETE on the slave when compared to statement based replication.
4. Fewer locks are required when UPDATE and DELETE statement's WHERE clause does not make use of the index.

Cons:

1. Contrary to statement based replication, row based replication records changes to each row. The most probable disadvantage from this behavior is the size of the binlog. If a SQL statement effects 100 rows then in SBR just one query is logged whereas in RBR changes to 100 rows are recorded. This behaviour makes binlogs in RBR much much larger then SBR.
2. As in case of SBR, the SQL queries are logged and hence they can be reused and read for audit. In RBR, there is no way to figure out what statements were executed on the master and recieved on the slave. However what rows has been changed or inserted, can be decoded.
3. There is no mechanism available in RBR to ensure that the binlog in MySQL master was processed without any problem at the slave.

After comparison of both the formats, I personally believe that Row based Replication in MySQL is the way to go because:

• Its the safest form of replication
• It is safer when it comes to replicating triggers and stored procedures.
• It requires fewer locks and hence its much faster and achieves high concurrancy.
• Regarding the drawback of having large binlog, I would say that not all applications may issue updates that effect thousands of rows. Comparing to the other advantages that row based replication provides and an informed guess I would say that this disadvantage may be ignored.

The choice of which format to choose depends on the application and requirements. I have not touched mixed format. My guess is that mixed format may bring in the best of both world. I will be discussing mixed format replication in detail, in my upcoming post.

Memcached and MySQL (Part II – Memcached + MySQL)

In part one of this blog (you can see it here) I gave a detailed overview of what Memcached really is. In this part I will address the usage of Memcached to alleviate the load from MySQL. In a scalable + high traffic application, database will prove to be a bottleneck due to the limitations of disk read/write rate, which is slow as compared to reading from memory. As Memcached is a memory-based distributed object storage cache, we can utilize the power of Memcached. Will avoiding database a good optimization? Yes it is. This is where Memcached will come into play, avoiding the requests to hit MySQL. Well not in all cases but yes to a considerable extent. Having Memcache installation on the same server as MySQL and using it will help the data source to perform much better. When I say Memcache it means that I am refering to only one instance of Memcache and not a distributed version (in the latter case its Memcached). Having Memcache on the same server as MySQL means that memory will be distributed between Memcache and MySQL which is not what I prefer. I would prefer a separate server dedicated to Memcache so MySQL can utilize the memory and definitely get more of it. Below is the architecture that I am considering:

As you can see that we have a MySQL server and a Memcache Server. We generally have queries that are either fetching data from database or adding/deleting/updating data in the database. For each type we can follow a particular sequence:

Read Queries:

1. Check Memcached if the data set is available

2. If its available then congratulations you just avoided a hit to the database

3. If its not, too bad. Get the result set from the database

4. You want to avoid hitting the database again, so set the data set in Memcache

Write Queries:

1. Write data to the database

2. If the write was a success, then either drop the data set from the Memcache or update the Memcache with current information

Of course this is a very naive concept when it comes to the practical implementation. Practically things are very much different and a bit complicated when it comes to the usage of Memcached. Additionally there can be different levels of caching in an application which are constructed overtime in the life cycle of an application and are based on the needs. Also we can increase the Memcache servers to develop a Memcached cluster to cater the needs of ever increasing data. Now we can clearly see that the performance of the application will increase greatly by reducing the load on the database, using an in-memory key value storage.

Memcached and MySQL (Part I - Memcached)

We all know that disk I/O is expensive then memory and we also know that data in memory is volatile but on disk is non-volatile. Talking about relational database storage, data is stored on disk which means that it is non-volatile but the retrieval and storage is slower then memory. On the contrary, if we store data in memory the retrieval and storage is super fast but the data is volatile and thus prone to loss. The question is whether we can get the best of both worlds. The question will be anwered ahead. Lets first see what Memcached is. Below is a point-wise explanation of Memcached (will try to cover as much as I can)

Memcached in a nutshell

1. Memcached is a distributed in-memory object caching system. It's distributed, which means that Memcached does not represent a single server but can span hundreds of computers. Its in-memory, which means that all the objects are stored in memory (RAM). Memcached is a distributed version of Memcache.

2. It's an in-memory key/value store, where data/object can be stored using the key as an identifier of the data/object.

3. Data is in-memory and is therefore volatile. Its good as a cache but not good for data that needs to be persisted and the loss of which might not be good for the application or the users.

4. All operations in the Memcached take constant time and hence their complexity is O(1). The basic operations of Memcached are add, set, get, multi-get, delete, replace (Note: a set after a set on the same key is considered to be an update).

5. All items in Memcached have expiration time. An expiration time of zero '0' means that the item will never expire (here never means 30 days of expiration time). If the expiry time is greater then 30, it will be treated as a UNIX timestamp.

6. Memcached does not have a garbage collection mechanism. You need to either explicitely delete the item, get an item that is already expired, or wait for Memcached to run out of alloted memory. In short, Memcached memory reclaiming is lazy, which is logical keeping in mind the complexity and processing involved in garbage collection.

7. Memcached reclaims the memory using the following mechanism:

   1. If an item is requested, Memcached checks it's expiry time. If the item is expired, it returns a negative response and reclaims the memory by freeing its memory.

   2. If Memcached is unable to accommodate any new items, it starts to free the memory of LRU (least recently used) items in order to accommodate new items.

8. Memcached servers are isolated, which means that one server is unaware of the presence of another server. Where to route the request is the responsibility of the Memcached client library.

9. Generally you do not need authentication mechanism for Memcached and previously it was not even supported. Now if the client supports, SASL authentication can be used. Generally Memcached infrastructure is in a closed internal network and hence having authentication and other security measures may complicate and introduce unwanted latency to an otherwise simple concept.

10. Memcached has a client part and the server part. The client part is responsible for routing the request to an appropriate Memcached server in the Memcached server cluster, managing connection and handling failures. The server part is responsible for request processing and reclaiming memory.

11. You can cache objects, queries, data-set and anything sensible in the Memcached. Just remember its a cache and not a persistent storage.

12. There is no replication or a fail-over mechanism in Memcached.

13. Compression and Serielization of cache objects should be investigated when selecting an appropriate client for Memcached. Also connection handling mechanism should be carefully read in order to avoid connection leakages which will render the Memcached server useless.

14. Hashing Algorithm depend on the clients. Generally 'Consistent Hashing' algorithm is implemented by the clients. This algorithm devises a strategy to distribute the keys across several Memcached servers evenly but the biggest advantage comes in when new servers are added to the Memcached cluster. This algorithm minimizes the number of re-hashed keys whenever a new server is added in comparison to the normal hashing algorithms where re-hashing is considerable.

Below is a diagram that shows the client part and server part of Memcached (in a Memcached cluster of two Memcached servers):

The next part will discuss how we can use Memcached to allieviate the load from the database server, which was the actual motive of this post.

MySQL DATETIME vs TIMESTAMP

A well-designed application has many traits. Good schema design is one of them. Choosing the right data type serves as a contributor towards a good schema design. The selection of a data type depends on various questions like what is the range of values that the column will hold?, the precision required by the column?, and if any special behavior is required. Choosing an optimal data type may save space on disk and memory, and may save CPU cycles. Generally such consideration are trivial when we talk about smaller applications but are very important for large scale applications. MySQL offers two alternatives for storing date time values namely DATETIME and TIMESTAMP. Which one is the best is not a good question but which one to use and what are the characteristics of each one is a better question and knowing the answer to the latter question will help us selecting the best option.

Storage Format

When using DATETIME, MySQL stores date time information as an integer in the format of YYYYMMDDHHMMSS. When displaying this data MySQL formats this storage representation into an ANSI standard representation which is YYYY-MM-DD HH:MM:DD. On the other hand, MySQL just stores the number of seconds elapsed since January 1, 1970 12:00 AM when it comes to storing date time information as TIMESTAMP.

Range

DATETIME can hold date time information from the year 1001 to the year 9999 whereas TIMESTAMP can hold date time information from the year 1970 to the year 2038.

Storage

DATETIME uses 8 bytes of storage whereas TIMESTAMP uses 4 bytes of storage, almost half of what DATETIME uses. So if TIMESTAMP is feasible for usage, you shouldn't mind using it.

Timezone Dependence

Storage and display of date time information when using DATETIME is independent of the timezone whereas in the case of TIMESTAMP, the date time information displayed is dependent on the time zone.

Special Characteristics of TIMESTAMP

TIMESTAMP type has some characteristics that are absent in DATETIME type. Below are the characteristics:

• The display of date time when using TIMESTAMP type is dependent on the time zone. This means that MySQL will return the date time information after converting it into an appropriate time zone. By default the time zone is the server's time and can be changed on a per connection basis.

• By default, if you insert a row in the table without specifying the value of the timestamp column, MySQL will set the time of that column to be the current time. Similar is the case when a row is updated, MySQL will update the TIMESTAMP column with the current time if the date is not specified in the update query. Obviously we can change the default behaviour of the TIMESTAMP data type.

Hopefully this information will help us distinguish the two data types and to choose the right data type for the right purpose.

Scale-Out MySQL

As a web applications grows, they face high peak times and a burst of requests that they have to handle. On an application stack, software components can be scaled with ease by just adding new application servers. The hardest part is to scale the data layer. One question that comes into our mind is to why scaling the database is necessary and what matters most? The database or the application. The answer to this is that the stand alone database will at some point become a bottle neck for the application. If the application is capable of servicing 800 requests per second then because of the database, the application will be able to serve far lesser requests then it is capable of. In order to explain the bottleneck lets analyse the architecture of MySQL:

Figure taken from:

http://dev.mysql.com/doc/refman/5.1/en/pluggable-storage-overview.html

If you analyse the lowest level of this architecture, you will see a bottleneck. Yes you are right, its the file system or the disk on which the data is eventually stored. No matter how fast and efficient the data retrieval and storing algorithms are, there will always be a limitation of the disk in short the requests are I/O bound.

Now that we are aware of the bottleneck that effects the application's performance, we have to come out with different solutions in order to improve the response/serving time of the data layer. There are different architectures that provide additional performance. Lets first look at the traditional application architecture:

The above architectures are traditional architectures, that almost every application may have, or at-least most. As we can see in figure 2, the application layer is scaled to two app servers which improves the performance a little bit, but the problem is the data layer. Of course, scaling the application layer will definitely have some issues but those issues are out of scope for this blog. So lets start by talking about scaling the database. Yes by scaling I mean increasing the number of database servers to distribute the load between them.

Concepts:

1. A query is either a read or a write query. I call them passive queries or active queries. Queries that change the state of the database are considered to be active queries (insert, update, & delete) whereas the queries that do not change the state of the database are passive queries (select).
2. Master Server: A server that serves as the primary data source for the application. This server has the most updated state of the data.
3. Slave Server: A server that serves as a backup data source for the master server and shares the load with the master server. The state of the data in this server is 'eventually consistent'.

Improvement 1:
This improvement introduces one or more slaves for a master in the architecture. The active queries are executed at the master and the updates are reflected on the slaves after a slight delay. The passive queries may be executed on any of the servers including the master server. This approach reduces the load on master server as now the load is shared between more then one server. Below is the architecture:

As we can clearly see that the slaves share the burden of the reads on the master. We can add some more slaves in order to improve performance further.

Improvement 2:
So in order to increase the performance we keep on increasing the slaves and wait for the magic. But soon we observe that the spell proves to be ineffective and the master server is lagging. The reason for this lag are the slaves. MySQL master slave configuration requires master to record active queries in a log. The slaves, in order to update the data connect to master, access the log, get all the new updates in it and apply the updates to the slave data. This way the slaves are synchronized with the master. So many slaves trying to synchronize themselves with the master server by making connection with the master and accessing the log to get the updates, makes the master lag. Having this problem, we can utilize the 'blackhole' engine to solve the master lag issue.
So instead of Slaves connecting directly to the master, we introduce a dummy master that acts as a slave to master, getting all the changes in the binlog of the master and recording it in its binlog. The only difference between master and the dummy master is the table type. All the table in the dummy master use black hole engine. It means that the actual data is not stored and the queries are not performed on the dummy master but rather just logged. The slaves can connect to the dummy master just like in Improvement 1 and get synchronized. Below is how the architecture should look like:

As we can now see that all the slaves do not connect to master but rather they connect to the dummy master and the dummy master connects to the master. We can add some more dummy master in order to accommodate more slaves.

Few Notes:

1. In this post I only talked about Scaling Reads and not Scaling Writes.
2. There is a single point of failure and yes that's the master. You need to have a replicated secondary master so in case the primary master fails, the secondary master takes its role.
3. You cannot add up infinite slaves to the master or black hole master.
4. There is always a replication lag in slaves and that should be considered.
5. If the master is write-heavy, expect a bit more delay in replication.

In this blog, I went through the conventional application architecture, then devised two scaleable architectures for MySQL that we can use for high performance demanding applications. My next posts will be:

• Statement-based and Row-based Replication
• MySQL + MemcacheD
• Setting up MySQL Master/Slave replication
• Setting up MySQL Master/Master replication

Hello World

Hello World

This is my first blog post. I will share with you all, my experiences, my work and my opinions. Topics will not only be technical but they will be from my experiences both technical and non-technical. Opinions and appreciations are welcome

Happy Reading!!!!!