'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”

How to reload/refresh the privilege or the grants tables

# mysqladmin -u root -ptmppassword reload;

Refresh command will flush all the tables and close/open log files.

# mysqladmin -u root -ptmppassword refresh

What is the current status of MySQL server?

# mysqladmin -u root -ptmppassword status 

Uptime: 9267148 

Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067 

Flush tables: 1 Open tables: 64 Queries per second avg: 0.25

The status command displays the following information:

• Uptime: Uptime of the mysql server in seconds
• Threads: Total number of clients connected to the server.
• Questions: Total number of queries the server has executed since the startup.
• Slow queries: Total number of queries whose execution time waas more than long_query_time variable’s value.
• Opens: Total number of tables opened by the server.
• Flush tables: How many times the tables were flushed.
• Open tables: Total number of open tables in the database.

How to display all the running process/queries in the mysql database

# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

You can use this command effectively to debug any performance issue and identify the query that is causing problems, by running the command automatically every 1 second as shown below.

# mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

How to display all MySQL server system variables and the values?

# mysqladmin  -u root -p******** variables
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| auto_increment_increment        | 1                               |
| basedir                         | /                               |
| big_tables                      | OFF                             |
| binlog_format                   | MIXED                           |
| bulk_insert_buffer_size         | 8388608                         |
| character_set_client            | latin1                          |
| character_set_database          | latin1                          |
| character_set_filesystem        | binary                          |

skip.....

| time_format                     | %H:%i:%s                        |
| time_zone                       | SYSTEM                          |
| timed_mutexes                   | OFF                             |
| tmpdir                          | /tmp                            |
| tx_isolation                    | REPEATABLE-READ                 |
| unique_checks                   | ON                              |
| updatable_views_with_limit      | YES                             |
| version                         | 5.1.25-rc-community             |
| version_comment                 | MySQL Community Server (GPL)    |
| version_compile_machine         | i686                            |
| version_compile_os              | redhat-linux-gnu                |
| wait_timeout                    | 28800                           |
+---------------------------------+---------------------------------+

How do I find out what version of MySQL I am running?

Apart from giving the ‘Server version’, this command also displays the current status of the mysql server.

# mysqladmin -u root -ptmppassword version
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:                 107 days 6 hours 11 min 44 sec

Threads: 1  Questions: 231976  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

How to check whether MySQL Server is up and running

# mysqladmin -u root -p ping 

Enter password:

 mysqld is alive

How to change the MySQL root user password

mysqladmin -u root -p OLDPASSWORD password NEWPASSWORD

MySQL for Beginners

A MySQL database server contains many databases (or schemas). Each database consists of one or more tables. A table is made up of columns (or fields) and rows (records).

The SQL keywords and commands are NOT case-sensitive. For clarity, they are shown in uppercase. The names or identifiers (database names, table names, column names, etc.) are case-sensitive in some systems, but not in other systems. Hence, it is best to treat identifiers as case-sensitive.

You can use SHOW DATABASES to list all the existing databases in the server.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

The databases "mysql", "information_schema" and  "performance_schema" are system 

databases used internally by MySQL. A "test" database is  provided during 

installation for your testing. 

Creating and Deleting a Database - CREATE DATABASE and DROP DATABASE

You can create a new database using SQL command "CREATE DATABASE databaseName"; and delete a database using "DROP DATABASE databaseName". You could optionally apply condition "IF EXISTS" or "IF NOT EXISTS" to these commands. For example,

mysql> CREATE DATABASE south;
Query OK, 1 row affected (0.03 sec)
   
mysql> DROP DATABASE south;
Query OK, 0 rows affected (0.11 sec)
   
mysql> CREATE DATABASE IF NOT EXISTS south;
Query OK, 1 row affected (0.01 sec)
   
mysql> DROP DATABASE IF EXISTS south;
Query OK, 0 rows affected (0.00 sec)

IMPORTANT: Use SQL DROP (and DELETE) commands with extreme care, as the deleted entities are irrecoverable. THERE IS NO UNDO!!!

 

Install mod_rpaf with apache-2.4

Apache-2.4 has own module mod_remoteip

"This module is used to treat the useragent which initiated the request as the originating useragent as identified by httpd for the purposes of authorization and logging, even where that useragent is behind a load balancer, front end server, or proxy server."

you may use it with nginx such:

....
    RemoteIPHeader X-Forwarded-For
....

but I found 2 issues for me:

1. I must change LogFormat ( replace %h with %a) to show original IP in logs
2.  /server-status/ page show nginx ip address instead original

So, i deside to use mod_rpaf. Defaut veriosn does not compile with aapche-2.4.
To solve the issue:

 replace  “remote_” with “client_” in “mod_rpaf-2.0.c” 

Linux TOP Command

Top command options:

Line 1: Gives System present time, up time of the machine, number of users logged in, Load average on system at 1, 5, 15 min interval. Please ignore the values which mention load average as 5,10 and 15 mins.

Line 2: Gives total number of process on the machine, number of running process, number of sleeping process, number of stopped process, number of Zombie process.

Line 3: Gives you CPU details

Line 4 &  5: Gives RAM and SWAP details.

Line 6: To execute top command shortcuts(See below for the list of top command shortcuts ).

From Line 7: dynamically displayed top process results.

 top commands shortcuts:

The Runlevels in Linux

Many Windows users are familiar with different modes of execution of their operating system:
a. Safe mode
b. Command prompt only
c. Safe mode with network support
d. Safe mode with vga

And other such options that are presented during the boot-up process. Similarly, Linux has different modes of executing as well, which are known as runlevels. But unlike Windows, we can change runlevels on a fly.

Definition for runlevel : The term runlevel refers to a mode of operation in one of the computer operating system that implement Unix System V-style initialization. This system replaces the traditional /etc/rc and /etc/rc.local scripts used in BSD UNIX. Conventionally, seven runlevels exist, numbered from zero to six, plus an alias runlevel named ‘S’ and/or ‘s’.

Runlevels control services started by the initialization process. The number of runlevels and services started on those runlevels varies with Linux distributions. Information about the runlevels of a particular distribution is listed in file /etc/inittab.

The contents of the file include the following:

D	Name	Description
0	Halt	Shuts down the system.
1	Single-user Mode	Mode for administrative tasks.
2	Multi-user Mode	Does not configure network interfaces and does not export networks services.
3	Multi-user Mode with Networking	Starts the system normally.
4	Not used/User-definable	For special purposes.
5	Start the system normally with appropriate display manager. ( with GUI )	As runlevel 3 + display manager.
6	Reboot	Reboots the system.