MySQL: REDO Logs and UNDO Logs

Most of the DBAs like me, haven't cared about the them..! But, they are the Unsung heroes of MySQL..!!

Yes, UNDO and REDO logs are the 2 different kind of logs; each with it's own purpose and you shouldn't get confused between them ..!!

As the name indicates, REDO logs are for Re-doing things..! The transactions which were already executed, but not committed to disk due to some DB crash/power off..! So, the REDO log records every transaction, holds until it gets committed and if needed, it will be used for crash recovery.

By default, there will be 2 REDO logs, named, ib_logfile0 and iblogfile1. As of version 5.6 or later, we can have up to 100 REDO logs using the System Variable  innodb_log_files_in_group . Since, these logs are overwritten as soon as all the files are full, it is advised to have more files or files with larger size. By default, each log file will be 5 MB and the size can be set using the mysql variable  innodb_log_file_size.

The maximum allowed combined size of all the files is 512 GB (innodb_log_file_size * innodb_log_files_in_group <= 512 GB).

The REDO log files location can be set using the variable innodb_log_group_home_dir , if not specified, default directory will be used.

Let's talk about UNDO logs..:

This log stores copy of data that is being modified by any current transaction. So that, at the same time if any other transaction queries for the original data, this log will serve the purpose..!!These logs are also called Rollback Segments.

The path of the UNDO log table space can be set by the variable innodb_undo_directory.

In the earlier versions, these logs were invisible..!! As, UNDO logs were the part of System table space, i.e., ibdata1 and ibdata2. But, as of 5.6 and later, you can create multiple and separate UNDO log files(table space files).

Use variable innodb_undo_tablespaces  to set the number of table space files. Max value is 126 and minimum 0. The Undo logs set by the variable innodb_undo_logs will be divided between the set table spaces. Maximum you can create 128 undo logs and it is the default value.

MONGODB Behaviour with NFS Storage

Just wanted to share a finding which I faced during mongoDB start up with NFS storage.

SCENARIO:

One of secondary went out of sync (Oplog window crossed at primary side). Copied the latest and good snapshot-backup of primary at secondary side.

When trying to start the instance at secondary side encountered with below Error:

ERROR:

2018-07-17T14:27:23.226Z I STORAGE [initandlisten] The size storer reports that the oplog contains 56474469 records totaling to 21717749153 bytes

2018-07-17T14:27:23.230Z I STORAGE [initandlisten] Sampling from the oplog between Jul 17 06:12:35:1cf7 and Jul 17 13:14:06:1313 to determine where to place markers for truncation

2018-07-17T14:27:23.230Z I STORAGE [initandlisten] Taking 1011 samples and assuming that each section of oplog contains approximately 558429 records totaling to 214748737 bytes

2018-07-17T14:27:23.769Z I STORAGE [initandlisten] Failed to get enough random samples, falling back to scanning the oplog

INDICATIONS:

Whenever I am starting using command line option /u01/mongodb/3.2/bin/mongod -f /u01/mongodb/3.2/etc/lmrevents/27029/mongod.conf & . It will get stuck at message “about to fork child process, waiting until server is ready for connections”.

And will not show the message “child process started successfully, parent exiting”. During this if you do ps against the same process you will notice three process would be running (Parent process along with child process) and it will end up with message “ERROR: child process failed, exited with error number 51” after long time (for us 15 minute)



mongodb 21038 19297 0 16:42 pts/0 00:00:00 /u01/mongodb/3.2/bin/mongod -f /u01/mongodb/3.2/etc/lmrevents/27029/mongod.conf --- PARENT PROCESS

mongodb 21039 21038 0 16:42 ? 00:00:00 /u01/mongodb/3.2/bin/mongod -f /u01/mongodb/3.2/etc/lmrevents/27029/mongod.conf --- CHILD PROCESS

mongodb 21040 21039 2 16:42 ? 00:00:00 /u01/mongodb/3.2/bin/mongod -f /u01/mongodb/3.2/etc/lmrevents/27029/mongod.conf --- CHILD PROCESS

WHY AND HOW IT IS HAPPENING:

In version 3.1.8 and later mongo added a new feature: Keep "milestones" against the oplog to efficiently remove the old records using WT_SESSION::truncate() when the collection grows beyond its desired maximum size

On startup WiredTiger now needs to read values from the oplog to figure out where to place the milestones. This process is fine if all of the data is stored locally on disk. The problem is when you are using external storage (For example: Amozon EBS and Network File Storage: NFS , for us it is NFS) and the sampling and truncation of oplog never completed.

In case of EBS: sampling from cold EBS is taking somewhere between 20 and 30 minutes leading to incredibly slow startup times or error message.

In case of NFS: Network latency is the culprit and encountering the error.

HOW TO RESOLVE THIS:

MongoDB has taken it feature request and brought it in version 3.4.

feature request: so that WiredTigerKVEngine::initRsOplogBackgroundThread() skips starting a thread and thus skips sampling the oplog when the undocumented --queryableBackupMode option is specified.
So till the version 3.2 you have to wait for no network latency or hot EBS to start it again or get rid of Network storage.
It is highly not recommended to use remote storage for MongoDB. 

References : SERVER-23935

Power of gdb

When one wants to script automated replication chain building, certain things are quite annoying, like immutable replication configuration variables. For example, at certain moments log_slave_updates is more than needed, and that's what the server says:

mysql> show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> set global log_slave_updates=1;
ERROR 1238 (HY000): Variable 'log_slave_updates' is a read only variable

How gdb can help you to solve MySQL problems

What you can do with gdb

• Check stack traces (and variables), per thread: thread apply all bt [full]
• Print variables, up to complex one:
• thread 1
• print do_command::thd->query_string.string.str
• Set new values for variables (global and per thread, even those formally read-only in MySQL while it’s running):
• set max_connections=5000
• set opt_log_slave_updates=1
• Call functions (that may do complex changes):
• call rpl_filter->add_do_db(strdup("hehehe"))
• Set breakpoints and watchpoints
• Work interactively or use gdb as a command line utility (-batch)
• Use macros, Python scripting, more…
• All these may not work, fail, hang, crash, produce obscure errors…
• You have to read and understand the source code

Too many connections? No problem!

We can increasing max_connections in MySQL without logging into MySQL instance
Is there any way we can increase max_connections variable in MySQL without logging into MySQL, mostly in situations wherein max_connections are exhausted ?

Many times a DBA needs to debug what's going withing MySQL instance in case its max out, below command can come for rescue

gdb -p $(cat <PATH OF PID FILE> -ex "set max_connections=1500" –batch

Improving Performance with MySQL Index Column

In addition to creating new indexes to improve performance, you can improve database performance with additional schema optimizations. These optimizations include using specific data types and/or column types. The benefit is a smaller disk footprint producing less disk I/O and results in more index data being packed in available system memory.

Data Types

Several data types can be replaced or modified with little or no impact to an existing schema.

BIGINT vs. INT

When a primary key is defined as a BIGINT AUTO_INCREMENT data type, there is generally no requirement why this datatype is required. An INT UNSIGNED AUTO_INCREMENT datatype is capable of supporting a maximum value of 4.3 billion. If the table holds more than 4.3 billion rows, other architecture considerations are generally necessary before this requirement.

The impact of modifying a BIGINT data type to an INT data type is a 50 percent reduction in per row size of the primary key from 8 bytes to 4 bytes. The impact is also not just for a primary key; all foreign keys that are defined as BIGINT can now be modified to INT. This savings can significantly reduce the space required for indexes in a heavily normalized database.
For a more detailed explanation, see
http://ronaldbradford.com/blog/bigint-v-int-is-there-a-big-deal-2008-07-18/

TIP

The change of BIGINT to INT UNSIGNED for an AUTO_INCREMENT column can be one of the best immediate schema improvements for a database server with limited memory and a heavily normalized data model.

DATETIME vs. TIMESTAMP

Is the requirement for recording a date/time value an epoch value—that is, the number of seconds since 1/1/1970? If the value being stored is only an epoch value, then a TIMESTAMP column supports all necessary values. A DATETIME column supports all possible date/time values. A DATETIME data type is 8 bytes, and a TIMESTAMP data type is 4 bytes.

The disadvantage of using a TIMESTAMP is the implied DEFAULT 0, which is incompatible with any related SQL_MODE settings that disable zero date settings. The TIMESTAMP data type also does not support a NULL value. This column type is ideal for creating the date and time of a created or an update value for a row when a value always exists.

ENUM

MySQL provides the ENUM data type, which is ideal for static code values. For example, when recording the values for gender, you could define a column as either of the following.

gender1     VARCHAR(6) NOT NULL
gender2     ENUM (‘Male’,’Female’) NOT NULL

There are three benefits of using the ENUM data type:

• An ENUM column provides additional data integrity with an implied check constraint.
• An ENUM column uses only 1 byte for up to 255 distinct values.
• The values of an ENUM column are better for readability. For example, if you have a status field, the use of an ENUM is compact in data size and provides a meaningful description of the column value.

     status1  CHAR(1) NOT NULL DEFAULT ‘N’,  // ‘N’,’A’,’X’
     status2  ENUM (‘New’,’Active’,’Archived’) NOT NULL DEFAULT ‘New’

Historically, the impact of using an ENUM was the database dependence of changing the range of values when a new value was required. This was an ALTER DDL statement, which is a blocking statement. Starting with MySQL 5.1, adding a value to an ENUM column is very fast and unrelated to the size of the table.

NULL vs. NOT NULL

Unless you are sure that a column can contain an unknown value (a NULL), it is best to define this column as NOT NULL. Frameworks, for example Ruby on Rails, are notorious for not defining mandatory columns as NOT NULL. When the column is defined within an index, there is an improved size reduction and simplification of index processing, as no additional NULL conditions are required. In addition, having NOT NULL places an extra integrity constraint on the data in the column, ensuring all rows have a value for the column.

Implied Conversions

When you are choosing an indexed data type for table joins, it is important that the data type is identified. Implied type conversion can be an unnecessary overhead. For numeric integer columns, ensure that SIGNED and UNSIGNED is consistent. For a variable data type, the added complexity includes the character set and collation. When you are defining indexed columns for table join conditions, ensure that these match. A common problem is an implied conversion between LATIN1 and UTF8 character sets.

Global Spare or Dedicated Spare?

Global hot spare:
                              A global hot spare is not assigned to a specific logical drive, it will protect any redundant logical drive on the controller. (RAID 0 logical drives and simple volumes are non-redundant and are not protected by hot spares.) You can designate a global hot spare before or after you build logical drives on a controller.

Dedicated hot spare: 
                                   A dedicated hot spare is assigned to one or more specific logical drives and will only protect those logical drives. A dedicated hot spare that is assigned to protect more than one logical drive is called a pool spare. You must create the logical drive before you can assign a dedicated hot spare to protect it. 

Clearing disk space when a file is deleted but still consuming space

[root@test01 /]# lsof  | grep deleted

Once you are sure of the culprit file, specifically grep that in lsof.

[root@test01 /]# lsof  | grep deleted | grep rabbit@test01.log-20151108
beam.smp   43563      root   10w      REG              253,0 29471244165          1371295 /var/log/rabbitmq/rabbit@test01.log-20151108 (deleted)

Find the entry in /proc/<pid>/fd/ that corresponds to the filehandle (for above case:)

[root@test01 /]# ll /proc/43563/fd/10

Now, just cat /dev/null into the fd:

[root@test01 /]# cat /dev/null > /proc/43563/fd/10

And we are good to go :-) 

Advantages and Disadvantages of Percona Xtrabackup, Installation and Steps to take DB backup through innobackupex tool

PERCONA XTRABACKUP:-

           This post is about advantages, disadvantages, Installation and DB backup steps of percona xtrabackup/innobackupex and I will let you know about innobackupex tool which is written in Perl,  It enables more functionality by integrating xtrabackup and other functions such as file copying and streaming, It perform point in time backup for Innodb, MyISAM , Archive engines. 

  I have picked a DB which size is 28GB to test the same. I got below results: 

  Backup Time Taken with innobackupex: 5 minutes
  Backup Size : - 5.8 GB

and below are the findings (Advantages and Disadvantages):-

Advantages:- 
1) It supports completely non-blocking backups of InnoDB/XtraDB (Which support Transactions) storage engines.
2) It supports on fly compression which help to cater disk space with (--stream=xbstream).
3) We can take incremental backup as well with this. This can be done because each InnoDB page has a log sequence number, LSN, which acts as a version number of the entire database. Every time the database is modified, this number gets incremented. An incremental backup copies all pages since a specific LSN.
4) We can take partial backup as well which means that we can take backup only for some specific tables. But for this there must be innodb_file_per_table enabled 
5) Use --slave-info when we are backing up from backup slave server because it will provide binlog file name and position of the master server. It also writes this information to the xtrabackup_slave_info file.

How To Use HAProxy For MySQL Load Balancing

HAProxy is an open source software which can load balance HTTP and TCP servers. All your MySQL servers have to be configured to perform Master-Master replication as load balancing involves both reading and writing to all the backends.

HAproxy over MySQL

Prepare MySQL Servers

We need to prepare the MySQL servers by creating one additional user for HAProxy. This user will be used by HAProxy to check the status of a server and health of server.

mysqlchk

We need to create a script which detects the MySQL replication role on the database node as per below:

• if master (SHOW SLAVE HOSTS > 1 AND read_only = OFF),
• return 'MySQL master is running.'
• if slave (Slave_IO_Running = Yes AND Slave_SQL_Running = Yes AND (Seconds_Behind_Master = 0 OR Seconds_Behind_Master < SLAVE_LAG_LIMIT))
• Set limit of SLAVE_LAG_LIMIT which will be bear able for your environment.
• return 'MySQL slave is running. (slave lag: 0)'
• else
• return 'MySQL is *down*'

How to use

• Create a script in any path like I created at '/opt/mysqlcheck' and make it executable. 

#!/bin/bash

SLAVE_LAG_LIMIT=5

MYSQL_HOST="127.0.0.1"

MYSQL_PORT="3306"

MYSQL_USERNAME='USERNAME'

MYSQL_PASSWORD='PASSWORD'

MYSQL_BIN='/usr/bin/mysql'

MYSQL_OPTS="-q -A --connect-timeout=10"

TMP_FILE="/tmp/mysqlchk.$$.out"

ERR_FILE="/tmp/mysqlchk.$$.err"

FORCE_FAIL="/tmp/proxyoff"

preflight_check()

{

    for I in "$TMP_FILE" "$ERR_FILE"; do

        if [ -f "$I" ]; then

            if [ ! -w $I ]; then

                echo -e "HTTP/1.1 503 Service Unavailable\r\n"

                echo -e "Content-Type: Content-Type: text/plain\r\n"

                echo -e "\r\n"

                echo -e "Cannot write to $I\r\n"

                echo -e "\r\n"

                exit 1

            fi

        fi

    done

}

return_ok()

{

    echo -e "HTTP/1.1 200 OK\r\n"

    echo -e "Content-Type: text/html\r\n"

    echo -e "Content-Length: 43\r\n"

    echo -e "\r\n"

    if [ $role == "master" ]; then

        echo -e "<html><body>MySQL master is running.</body></html>\r\n"

    elif [ $role == "slave" ]; then

        echo -e "<html><body>MySQL slave is running. (Slave lag: $SLAVE_LAG)</body></html>\r\n"

    else

        echo -e "<html><body>MySQL is running.</body></html>\r\n"

    fi

    echo -e "\r\n"

  #  rm $ERR_FILE $TMP_FILE

    exit 0

}

return_fail()

{

    echo -e "HTTP/1.1 503 Service Unavailable\r\n"

    echo -e "Content-Type: text/html\r\n"

    echo -e "Content-Length: 42\r\n"

    echo -e "\r\n"

    echo -e "<html><body>MySQL is *down*.</body></html>\r\n"

    echo -e "\r\n"

    exit 1

}

preflight_check

if [ -f "$FORCE_FAIL" ]; then

        echo "$FORCE_FAIL found" > $ERR_FILE

        return_fail

fi

CMDLINE="$MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"

SLAVE_IO=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Replica_IO_Running |  tail -1 | awk {'print $2'})

SLAVE_SQL=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Replica_SQL_Running | head -1 | awk {'print $2'})

echo $CMDLINE

if [[ "${SLAVE_IO}" == "Yes" ]] && [[ "${SLAVE_SQL}" == "Yes" ]]; then

    role='slave'

    SLAVE_LAG=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Seconds_Behind_Source | tail -1 | awk {'print $2'})

    if [[ $SLAVE_LAG = 0 ]]; then

        return_ok

    elif [ $SLAVE_LAG -lt $SLAVE_LAG_LIMIT ] ; then

        return_ok

    fi

else

    role='master'

    READ_ONLY=$($CMDLINE 'SHOW GLOBAL VARIABLES LIKE "read_only"' --vertical 2>/dev/null | tail -1 | awk {'print $2'})

echo "$READ_ONLY"

    [[ "${READ_ONLY}" == "OFF" ]] && return_ok

fi

return_fail

• Create a service in /etc/xinetd.d/ and accordingly change /etc/service with port number and service name.

service mysqlchk

{

        flags           = REUSE

        socket_type     = stream

        port            = 9300

        wait            = no

        server          = /root/mysqlchk

        user            =  mysql

        log_on_failure  += USERID

        disable         = no

        only_from       = 0.0.0.0/0 # recommended to put the IPs that need

                                    # to connect exclusively (security purposes)

        per_source      = UNLIMITED # Recently added (May 20, 2010)

}

• restart xinetd service 
• Install HAProxy on the your selected node.
• Update require information (SLAVE_LAG_LIMIT is in seconds):
• SLAVE_LAG_LIMIT=5
• MYSQL_HOST="localhost"
• MYSQL_PORT="3306"
• MYSQL_USERNAME='root'
• MYSQL_PASSWORD='password'
• You are good to go. Now ensure in HAProxy you have 2 listeners (3307 for write, 3308 for read) and use option tcp-check & tcp-check expect to distinguish master/slave similar to example below:

Enable Slow Query Logging in mysql 5.6

This tutorial will show you the settings you need to make in order to enable slow query logging

slow_query_log = 1  <0|1>
log-queries-not-using-indexes <if you want to log all queries that don't use indexes>
long_query_time=1   <Set this to the number of seconds a query must take to be considered "slow">
log-slow-queries=/var/log/mysql/log-slow-queries.log <Log file path>

'XR’ (Crossroads) Load Balancer for Web Servers on RHEL/CentOS

Crossroads is a service independent, open source load balance and fail-over utility for Linux and TCP based services. It can be used for HTTP, HTTPS, SSH, SMTP and DNS etc. It is also a multi-threaded utility which consumes only one memory space which leads to increase the performance when balancing load.

Let’s have a look at how XR works. We can locate XR between network clients and a nest of servers which dispatches client requests to the servers balancing the load.

If a server is down, XR forwards next client request to the next server in line, so client feels no down time. Have a look at the below diagram to understand what kind of a situation we are going to handle with XR.

MySQL Data Types

in this tutorial, you will learn about MySQL data types and how to use them effectively in the MySQL database design.
Database table contains multiple columns with specific data types such as numeric or string. MySQL provides more data types other than just numeric or string. Each data type in MySQL can be determined by the following characteristics:

• Kind of values it can represent.
• The space that takes up and whether the values are fixed-length or variable-length.
• Does the values of the data type can be indexed.
• How MySQL compares the value of a specific data type.

Numeric Data Types

You can find all SQL standard numeric types in MySQL including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addition, MySQL also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except the BIT type. The following table shows you the summary of numeric types in MySQL:

Numeric Types	Description
TINYINT	A very small integer
SMALLINT	A small integer
MEDIUMINT	A medium-sized integer
INT	A standard integer
BIGINT	A large integer
DECIMAL	A fixed-point number
FLOAT	A single-precision floating-point number
DOUBLE	A double-precision floating-point number
BIT	A bit field

String Data Types

In MySQL, string can hold anything from plain text to binary data such as images and files. String can be compared and searched based on pattern matching by using the LIKE operator or regular expression. The following table shows you the string data types in MySQL:

String Types	Description
CHAR	A fixed-length non-binary (character) string
VARCHAR	A variable-length non-binary string
BINARY	A fixed-length binary string
VARBINARY	A variable-length binary string
TINYBLOB	A very small BLOB (binary large object)
BLOB	A small BLOB
MEDIUMBLOB	A medium-sized BLOB
LONGBLOB	A large BLOB
TINYTEXT	A very small non-binary string
TEXT	A small non-binary string
MEDIUMTEXT	A medium-sized non-binary string
LONGTEXT	A large non-binary string
ENUM	An enumeration; each column value may be assigned one enumeration member
SET	A set; each column value may be assigned zero or more set members

Understanding MySQL Table Types, or Storage Engines

In this tutorial, you will learn various MySQL table types, or storage engines. It is essential to understand the features of each table type in MySQL so that you can use them effectively to maximize the performance of your databases.
MySQL provides various storage engines for its tables as below:

• MyISAM
• InnoDB
• MERGE
• MEMORY (HEAP)
• ARCHIVE
• CSV
• FEDERATED

Each storage engine has its own advantages and disadvantages. It is crucial to understand each storage engine features and choose the most appropriate one for your tables to maximize the performance of the database. In the following sections we will discuss about each storage engine and its features so that you can decide which one to use.

MyISAM

MyISAM extends the former ISAM storage engine. The MyISAM tables are optimized for compression an speed. MyISAM tables are also portable between platforms and OSes.
The size of MyISAM table can be up to 256TB, which is huge. In addition, MyISAM tables can be compressed into read-only tables to save space. At startup, MySQL checks MyISAM tables for corruption and even repair them in case of errors. The MyISAM tables are not transaction-safe.
Before MySQL version 5.5, MyISAM is the default storage engine when you create a table without explicitly specify the storage engine. From version 5.5, MySQL uses InnoDB as the default storage engine.

InnoDB

The InnoDB tables fully support ACID-compliant and transactions. They are also very optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-and forward operations. The size of the InnoDB table can be up to 64TB.
Like MyISAM, the InnoDB tables are portable between different platforms and OSes. MySQL also checks and repair InnoDB tables, if necessary, at startup.

MERGE

A MERGE table is a virtual table that combines multiple MyISAM tables, which has similar structure, into one table. The MERGE storage engine is also known as the MRG_MyISAM engine. The MERGE table does not have its own indexes; it uses indexes of the component tables instead.
Using MERGE table, you can speed up performance in joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables. If you use DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying tables will not be affected.

Memory

The memory tables are stored in memory and used hash indexes so that they are faster than MyISAM tables. The lifetime of the data of the memory tables depends on the up time of the database server. The memory storage engine is formerly known as HEAP.

Archive

The archive storage engine allows you to store a large number of records, which for archiving purpose, into a compressed format to save disk space. The archive storage engine compresses a record when it is inserted and decompress it using zlib library as it is read.
The archive tables only allow INSERT and SELECT commands. The archive tables do not support indexes, so reading records requires a full table scanning.

CSV

The CSV storage engine stores data in comma-separated values file format. A CSV table brings a convenient way to migrate data into non-SQL applications such as spreadsheet software.
CSV table does not support NULL data type and read operation requires a full table scan.

FEDERATED

The FEDERATED storage engine allows you to manage data from a remote MySQL server without using cluster or replication technology. The local federated table stores no data. When you query data from a local federated table, the data is pull automatically from the remote federated tables.

Secrets of Redo Logging in InnoDB

Why InnoDB engine need Redo Log Files ?

InnoDB is a general-purpose storage engine that balances high reliability and high performance. It is a transactional storage engine and is fully ACID compliant, as would be expected from any relational database. The durability guarantee provided by InnoDB is made possible by the redo logs.

How it is generated  ?

InnoDB (.ibd files) are considered to be a sequence of equal sized pages. These pages are uniquely identified within the InnoDB system using the space_id, page_no combination. If we want to read or modify any page, it needs to be loaded into memory. So there are two copies of the pages - on disk and in memory.

1. Any changes to a page is first done to the in-memory copy of the page. The page that is modified in memory and not yet flushed to disk is marked as the dirty page in memory.
2. An associated redo log is generated in memory, in the local mini transaction (mtr) buffer. This will then be transferred to the global in-memory redo log buffer.
3. The redo log record is written from the redo log buffer in memory to the redo log file on disk. This is subsequently flushed. These two steps are considered separate - writing to the redo log file and flushing the redo log file to the disk. This is to account for the file buffering done by the operating system.
4. The dirty page is flushed from memory to disk at some later point of time as part of the checkpointing operation.The order of these steps are important. The redo log record of a change must be flushed to disk before flushing the corresponding dirty page to the disk. This is the concept of write-ahead logging (WAL).

How it works ?

By default, InnoDB creates two redo log files (or just log files) ib_logfile0 and ib_logfile1 within the data directory of MySQL. In MySQL versions 5.6.8 and above, the default size of each redo log file is 48 MB each. This can be configured by the user by making use of innodb_log_file_size server option. The number of log files is controlled byinnodb_log_files_in_group server option.
A log group consists of a number of log files, each of same size. As of MySQL 5.6, InnoDB supports only one log group. So I'll not discuss this further.
The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the last redo log file. Once the last redo log file has been written, then redo logs are again written from the first redo log file.
The log files are viewed as a sequence of blocks called "log blocks" whose size is given by OS_FILE_LOG_BLOCK_SIZE which is equal to 512 bytes. Each log file has a header whose size is given by LOG_FILE_HDR_SIZE, which is defined as 4*OS_FILE_LOG_BLOCK_SIZE.

Terminology 

Redo Log File Header

Each redo log file contains a header occupying four log blocks with the following information:
• The first 4 bytes contain the log group number to which the log file belongs.
• The next 8 bytes contain the lsn of the start of data in this log file.
• First checkpoint field located in the beginning of the second log block.
• Second checkpoint field located in the beginning of the fourth log block.

Log Blocks 

A redo log file can be viewed as a sequence of log blocks. All log blocks, except the ones belonging to the log file header, contain a header and a footer.
The log block header contains the following information:
• The log block number. This field is of 4 bytes.
• Number of bytes of log written to this block. This field is of 2 bytes.
• Offset of the first start of an mtr log record group in this log block or 0 if none
• The checkpoint number to which this log block belongs
The log block trailer contains checksum of the log block contents.

Log Sequence Number (LSN)

It’s a most talked terminology while among Database Admins :) The LSN is an offset into the redo log file. Within InnoDB the log sequence number is represented by the type lsn_t, which is an 8-byte unsigned integer. The LSN is what that links the dirty page, the redo log record, and the redo log file. Each redo log record when it is copied to the in-memory log buffer, it gets an associated LSN. When each database page is modified, redo log records are generated. So each database page is also associated to an LSN. The page lsn is stored in a header for each page. The page lsn gives the lsn upto which the redo log file must be flushed before flushing the page.

Global In-memory Redo Log Buffer

The in-memory redo log buffer is global and all redo logs generated by user transactions will be written into this buffer. The size of this buffer is configurable and is given by the innodb_log_buffer_size. The default size of this redo log buffer is 8MB.

• When it comes into picture ?
• Running transaction is modifying database contents
• This log buffer will be written or flushed to the log file, either when the transaction commits
• When the log buffer gets full

Mini transaction (mtr)

A mini transaction (mtr) must be used to generate all the redo log records. A mini transaction contains a local buffer (called the mini transaction buffer) into which the generated redo log records will be stored. If we need to generate a group of redo log records such that either all make it to the redo log file or none makes it, then we need to put them in a single mini transaction. Apart from the redo log records, the mini transaction also maintains a list of pages that has been modified (dirty pages).

Redo Log Record Types

When we modify a database page, a redo log record is generated. This redo log record either contains what information has been changed in the page (physical redo log) or how to perform the change again (logical redo log). InnoDB uses a combination of physical redo logs and logical redo logs.

Life cycle of a redo log record

The life cycle of a redo log record is as follows:

• The redo log record is first created by a mini transaction and stored in the mini transaction buffer. It has information necessary to redo the same operation again in the time of database recovery.
• When mtr_commit() is done, the redo log record is transferred to the global in-memory redo log buffer. If necessary, the redo log buffer will be flushed to the redo log file, to make space for the new redo log record.
• The redo log record has a specific lsn associated with it. This association is established during mtr_commit(), when the redo log record is transferred from mtr buffer to log buffer. Once the lsn is established for a redo log record, then its location in the redo log file is also established.
• The redo log record is then transferred from the log buffer to redo log file when it is written + flushed. This means that the redo log record is now durably stored on the disk.
• Each redo log record has an associated list of dirty pages. This relationship is established via LSN. A redo log record must be flushed to disk, before its associated dirty pages. A redo log record can be discarded only after all the associated dirty pages are flushed to the disk.
• A redo log record can be used to re-create its associated list of dirty pages. This happens during database recovery.

It should be helpful to understand concept of redo logging which makes InnoDB a dependent transaction safe engine to keep our data in safe hands .

Managing Crons In linux

Not a nightmare anymore ..

What is Cron ?
Cron is a time-based job scheduler in Unix-like computer operating systems. People who set up and maintain software environments use cron to schedule jobs (commands or shell scripts) to run periodically at fixed times, dates, or intervals.

With increasing servers, applications, crons (schedule jobs) it was becoming pain to manage and monitor all crons.
We thus built NCConfig – a tool to monitor all crons which includes their status - Success/Failure, executing time, output, etc

Few major problems which we used to face before building NCConfig

- Did my job actually run?
Generally all crons were used to be monitored via email now because the typical way to monitor cron jobs is via email it is often extremely hard to find out if a job has actually run successfully. Ideally your monitoring shouldn’t involve people being awake and willing to read automatically generated emails. Especially if the cron job is important.

- Email, email everywhere!
Cron used to email the output of a given job to the user that the job is running as. This can be very useful when some aspect of the job requires alerting the user.
The problem is that you very often do not want emails from cron jobs. And usually when they start they can flood your inbox very fast.

- Where is my cron running ?
With more than 100 serves and single applications running on multiple servers its sometime get difficault to find where actually is my cron running.

- Execution Statistics
You may see the cron job execution statistics (including the total number of successful executions,  total number of failed executions (TF) and number of consecutive failed executions (CF)) in the cron job list

How to start and stop MySQL replication on a slave server?

#mysqladmin  -u root -ptmppassword stop-slave
Slave stopped

# mysqladmin  -u root -ptmppassword start-slave

slave started

========================================================================

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2836691
Server version: 5.5.25 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> stop slave;
Query OK, 0 rows affected (14.39 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: master server
                  Master_User: user
                  Master_Port: mysql master port
                Connect_Retry: 60
              Master_Log_File: bin.00001
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.0001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.00001

            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0
1 row in set (0.48 sec)

How to combine multiple mysqladmin commands together

In the example below, you can combine process-list, status and version command to get all the output together as shown below.

# mysqladmin  -u root -ptmppassword process status version
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 43 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Uptime: 3135
Threads: 1  Questions: 80  Slow queries: 0  Opens: 15  Flush tables: 3
Open tables: 0  Queries per second avg: 0.25

mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 52 min 15 sec

You can also use the short form as shown below:

# mysqladmin  -u root -ptmppassword pro stat ver

Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.

# mysqladmin  -h serverIP -u root -ptmppassword pro stat ver

List of all mysqladmin flush commands.

# mysqladmin -u root -ptmppassword flush-hosts
# mysqladmin -u root -ptmppassword flush-logs
# mysqladmin -u root -ptmppassword flush-privileges
# mysqladmin -u root -ptmppassword flush-status
# mysqladmin -u root -ptmppassword flush-tables
# mysqladmin -u root -ptmppassword flush-threads

• flush-hosts: Flush all information in the host cache.
• flush-privileges: Reload the grant tables (same as reload).
• flush-status: Clear status variables.
• flush-threads: Flush the thread cache.

What is the safe method to shutdown the MySQL server

# mysqladmin -u root -ptmppassword shutdown

# mysql -u root -ptmppassword
ERROR 2002 (HY000): Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock'

Note: You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server. To start the server, execute “/etc/rc.d/init.d/mysql start”