Tuesday, 17 January 2017

How to change InnoDB log file size in MySQL?

The InnoDB log file is the transactional log, it captures all the transactions. The transaction log size is a very important factor in determining the performance of a database engine. The variable innodb_log_file_size represents the transactional log size.  This log size can be changed as follows:

Set the required log file size in the MySQL configuration file.

[mysqld]
innodb_log_file_size=xG
Perform the clean shutdown. This can be verified with the help of MySQL error log. A clean shutdown is also known as slow shutdown. This process will perform a full purge of InnoDB buffer pool. The value of the innodb_fast_shutdown variable indicates the shutdown type. Slow shutdown can be performed by setting up as follows. 

SET GLOBAL innodb_fast_shutdown = 0;
or

[mysqld]
innodb_fast_shutdown = 0
Take a backup of the existing InnoDB log files - just a precaution.

Remove the existing InnoDB log files. By default 2 InnoDB log files in the group, it is named as ib_logfile0, ib_logfile1.

rm /var/lib/mysql/ib_logfile*
Start the MySQL service. 

sudo service mysqld start

When the server is starting, new log files are created with the same name. This can be verified by checking the error log.  

tail  /var/lib/mysql/mysqld.err



Note: If the existing files not removed, the server will not start and it will write the error statement on the error log.

InnoDB: Error: log file ./ib_logfile0 is of different size 0 xxxxxxxxxxx bytes
InnoDB: than specified in the .cnf file 0 xxxxxxxxxx bytes! 


Why innodb_log_file_size is important?


As we have seen, the InnoDB log file is an InnoDB transaction log, to understand the importance of the InnoDB transaction log, we need to know the UNDO and REDO operations. Before that, let us understand how MySQL works when a query executes:

When a write operation is happening on MySQL, it is not directly written to the disk. The MySQL simultaneously write into InnoDB buffer pool and InnoDB transaction log. All the modifications are happening on the memory (InnoDB buffer pool). The modified pages on memory (InnoDB buffer pool) are called as Dirty pages. Once the changes are completed, the dirty pages are flushed to disk, this is called the COMMIT. So only after flushed to disk it is called committed data.

UNDO operation:


Assume that, data is being modified on the memory (InnoDB buffer pool), during this modification, if there is a ROLLBACK statement executed, the InnoDB pages should be rolled back to the original state and modified pages roll back from the ROLLBACK segment and should be removed from the InnoDB pages on buffer pool. In simple words undo operation is called as rolling back a transaction.

REDO operation:


Assume that, the modification is there in memory, the unexpected server crashed, without the transaction log, whatever there in memory gone forever. Since modified data is committed in InnoDB buffer pool(memory) but not in a disk. In order to avoid that, while restarting the server, it needs to recover the uncommitted transaction from the transaction log, The MySQL will match the sequential number of the disk and the transactional log sequence number and start to recover. In simple words, redo operation is called as replaying a committed transaction.

So what is the importance of the InnoDB log file size? Here size matters!!!.

If log file size is small: few transactions are captured in the transaction log, so it means, faster recovery but slow writes. More IOPS on a single log file.






If log file size is large: more transactions are captured in the transaction log, so it means, faster writes but slow recovery. Less IOPS on a single log file.

Finally, having huge log size will be a problem but small size also will be a problem. So what is the best size for InnoDB transaction log? From my understanding, there is no right exact value, but we have a formula, which is accepted by most of the database administrators. Here goes the rule of thumb:

InnoDB log file size: Total size of the redo logs fits in 1 hour worth of writes during peak traffic. 

How much InnoDB is writing per hour?


This can be calculated by using the following statement:

mysql> pager grep seq;
mysql> show engine innodb status \G select sleep(60); show engine innodb status \G






This will give you the Log Sequence Number with one minute gap. By using this LSN we can calculate the how much writes InnoDB performs in an hour. The following sample output will give you a good idea of this:

mysql> pager grep seq;
PAGER set to 'grep seq' 
mysql> show engine innodb status \G select sleep(60); show engine innodb status \G 
Log sequence number 392311720379
1 row in set (0.17 sec) 
1 row in set (1 min 0.00 sec) 
Log sequence number 392323861206
1 row in set (0.09 sec)
mysql> select (392323861206 - 392311720379)*60/1024/1024;
+--------------------------------------------+
| (392323861206 - 392311720379)*60/1024/1024 |
+--------------------------------------------+
|                               694.70369339 |
+--------------------------------------------+
1 row in set (0.00 sec)
Here, on my testing server, the InnoDB writes around 694 MB per hour. So as per my default configuration, I have 2 transaction log files, unless I change. this can be verified by running the following statement:

 mysql> show variables like '%innodb_log_files_in_group%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
1 row in set (0.01 sec)
It means, my log file should be around 350 MB. I suggest, try this on your test environment and check the performance before implement on the production environment. Always remember to take a backup of the existing files before removing from the system. If you feel something missing or if you want to share something on the innodb_log_file_size please mention in the comment section. Please click on FOLLOW button on the right side to get more posts on database administration.

Sunday, 8 January 2017

How to allocate innodb_buffer_pool_size in MySQL?

The innodb_buffer_pool_size the most important variable for entire MySQL server. Last few years, you have seen the drastic improvements in the InnoDB storage engine features. After Oracle acquiring the MySQL, the importance of InnoDB storage engine grows multiple times over MyISAM. Even though MySQL supports multiple pluggable storage engines, one of the main reasons is its ACID compliance properties. Recent improvements in the MySQL 5.7 such as online innodb_buffer_pool_size configuration made it as a highly flexible and allow us to get the best performance without long downtime.

Tuesday, 25 October 2016

What are the features of MySQL 5.7?


InnoDB

Online buffer pool resize:

  • Configure innodb_buffer_pool_size offline (at startup) or online, while the server is running 
  • The operation is performed in chunks
  • Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option      
  • Buffer pool size always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
  • Default innodb_buffer_pool_chunk_size is 128M

Tuesday, 6 September 2016

MySQL Partitioning Example

This post is about partitioning a MySQL table by year by using range partition type. This post does not deal with the theories on partition, if you would like to understand the complete partitioning methods and types visit the official MySQL documentation. In this post we are directly focus on the implementation steps, assuming that we have knew the basics of MySQL partitioning types and its usages.


Tuesday, 24 May 2016

Tuesday, 19 April 2016

Understanding MySQL Architecture

The architecture of the world’s most popular open source database system is very important for the Information Technology people. There are many reasons for MySQL’s popularity around the world, but one of the main reasons is its architecture, while there are many big players such as Oracle, Microsoft SQL and DB2, MySQL’s architecture makes it as unique and preferred choice for most of the developers. In this article, we are going to discuss about of the internal architecture of the MySQL relational database management system. The article is for novice database administrators, database developers, software developers and those who are interested to work with MySQL database.

Sunday, 21 February 2016

[Solved] How to write a stored procedure in MySQL for insert?

Problem:

When multiple client applications such as web applications, desktop applications and mobile applications written in different languages like php, python, java, objective c, etc. need to perform the same database operation (insert), the same operations done by different client applications and client applications are directly accessing the database table using same SQL statement.

Solutions:

We are implementing stored procedure; here the client applications will simply call the defined stored procedures to perform the database operation (insert). In this post let us see about the syntax of the stored procedure and will see an example of insert operation using stored procedure using MySQL.

Friday, 22 January 2016

Thursday, 21 January 2016

[Solved] How to solve MySQL error code: 1062 duplicate entry?

Error Message:



Error Code: 1062. Duplicate entry ‘%s’ for key %d


Example:


Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’

Possible Reason:


Case 1: Duplicate value.


The data you are trying to insert is already present in the column primary key. The primary key column is unique and it will not accept the duplicate entry.

Tuesday, 19 January 2016

How to write SQL query?

We have discussed about the conceptual data model, logical data model, normalization and denormalization techniques and we discussed about installing and running up with MySQL community server.  What will be next? Once we build the data model and decided the database server, the next process will be start developing the physical database. What is needed to develop physical database? We need a common language to communicate with both the developer and database server. Here, comes the structure query language. So in this post we are going to see about the SQL (Structure Query Language). After reading this post you will be able to answer the following questions:

Friday, 15 January 2016

How to install MySQL server on windows?

Last week I got an assignment where I need to install MySQL server on client’s machine, after checking the system requirements, I came to know that, the machine contains the 64 bit windows operating system. So I thought to download MySQL community server for windows 64 bit operating system, but I found that the website provides only 32 bit installer for windows, and for 64 bit operating system, the website provides the zip file for download. My client needs to manage the MySQL products by himself; he was expecting software where he can manage all MySQL products at one place, so we need MySQL installer in that machine. So I decided to install MySQL server on windows machine using MySQL installer web community. In this post, let me go through the step by step guide to install MySQL server on windows machine.

Sunday, 10 January 2016

Database denormalization techniques

We have seen about the normalization and different levels of normalization in previous post. A normalized database design will store the information in more number smaller and separate tables. When you need to retrieve particular information from a normalized database you need to perform join operation, when you have more number of relationships among tables for particular information, you need to perform more join operation; it may further reduce the retrieval speed of the database. Here comes the denormalization, we are violating the normalization selectively in order to reduce the data retrieval time of a database. But at the same time over denormalization also leads to redundancy and inconsistent data, so it is data modeler’s response that, after denormalization the database should not become inconsistent.

Monday, 4 January 2016

What is database normalization?

Some years ago, when I was start working as junior database administrator, one of seniors given me a conceptual data model sheet and asked me to read the model and prepare a logical model from the sheet. I studied ER diagram (Entity-Relationship diagram) during my engineering course, so I must have some basic knowledge, but the given conceptual model is little different from what I studied (only the symbols are different). So I was searching in internet with queries, "what is logical data model?" and "how to convert a conceptual data model to a logical data model?". I found some pages related to what I was searching and I understood the concept, after a week somehow I submitted my newly created incorrect logical data model to my senior (it is a simple user management application).

Monday, 28 December 2015

Conceptual data model

Conceptual data model is a map of key concepts and rules binding these key concepts for a specific business, for a particular audience, that used for databases. Key concepts are both basic and critical to your business. Basic means this term is mentioned many times a day in your organisation in conversation among business people, such as CUSTOMER, EMPLOYEE, PRODUCT, etc.Critical means the business would be very different or non-existent without this key concept. For example, without the concept PRODUCT, your business may become non-existent.

Saturday, 26 December 2015