How to store IP address value in integer datatype column.

Most of the time, we use string datatype column like char or varchar to store ip address values. There is a way to store ip address value in numeric datatype column like unsigned interger. There is miscellaneous functions INET_ATON(), INET_NTOA() using which it's possible

INET_ATON() Return the numeric value of an IP address

INET_NTOA() Return the IP address from a numeric value

SELECT INET_ATON('192.168.1.5') AS IP;
+------------+
| IP         |
+------------+
| 3232235781 |
+------------+
1 row in set (0.00 sec)

The generated number is always in network byte order. For example
192x256^3 + 168x256^2 + 1x256^1 + 5

SELECT INET_NTOA('3232235781') AS IP;
+-------------+
| IP          |
+-------------+
| 192.168.1.5 |
+-------------+
1 row in set (0.00 sec)

MySQL root user

MySQL root user is meant for database administrators for administration task. Many times, I found it's being used as replication user and normal user for making connections to mysql database.

One of my clients did the same thing and later it was fixed.

110329 12:01:13 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.003726' at position 850699172, relay log './poxxxx-relay-bin.000821' position: 11845356
110329 12:01:13 [Note] Slave I/O thread: connected to master 'root@xxx.xx.xx.xxx:3306',  replication started in log 'mysql-bin.003726' at position 850699172

It can be problematic in many scenario and mysql server restart can be required. It leads to below errors and don't allow connections to mysql database.

Too many connections

TEST: Default Schema

MySQL and it's fork (Percona Server, MariaDB) contains default schema (database) test. Many times, i found it missing from clients mysql-server. It has it's own importance.

1. On master slave replication setup with below configuration parameters.

binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema

This makes the test schema useful for benchmarking purpose (sysbench, mybench, supersmack) without replicating the benchmark data on slave or new schema can be created if slave benchmark required.

2. On clients server we have to work under restricted conditions and access. In that senario, test schema can be very useful.

3. It's a workspace for users to try and test.

Building Report Server

Although, there are many ways to setup a reporting server depending on the requirements. Sharing one of those.

Reporting Server provides reports i.e. daily, weekly, monthlty and quarterly reports to the clients. Data flow from many different servers (services) to one system called as Report Server. Client might be using more than one services. Therefore, it is necessary to collect the whole data at one place, analyze and generate reports for clients and billing purpose. This makes the data huge in TBs. Many times this server is used for billing clients for the services provided by the company. This makes this server very very critical and the data should be accurate.

Setup process has been breakup into few steps and explained below.

1. Storage Engine Selection:

The first and the most important is storage engine selection. Since, the data can be used for billing purpose. Therefore, it should be accurate and loosing data by crash is not acceptable at all. Mostly select, insert and delete (data purging discussed below) is required in this kind of setup. No update is required. Below command should be used for inserting data.

LOAD DATA INFILE ...

Since, this command is 30% faster than normal inserts.

Concurrent operations (select, insert) is required. As, innodb uses MVCC to achieve high concurrency and it's crash safe. Therefore, I've used innodb storage engine and recommend other's to use for critical activity like billing. Inoodb is a great Storage Engine !!!

2. Table Design:

The table must hold, all the important columns required for all the reports. Some times, it can have 15-20 columns. Partitioning is one of the great feature of MySQL 5.1 It provides good performace with data management. Many types of partitioning is avaliable. It depends which one fits into your senario. I have used range based partitioning on date column.

CREATE TABLE mt      
(  c1 bigint unsigned not null,
c2 varchar(30),
c3 datetime,
.
.
c9 date default NULL) engine=innodb
partition by range (to_days(c9))
(PARTITION p0 VALUES LESS THAN (to_days('2011-01-01')),
PARTITION p1 VALUES LESS THAN (to_days('2011-01-05')),
PARTITION p2 VALUES LESS THAN (to_days('2011-01-10')),
PARTITION p3 VALUES LESS THAN (to_days('2011-01-15')),
PARTITION p2 VALUES LESS THAN (to_days('2011-01-20')),
.
.
PARTITION p25 VALUES LESS THAN (to_days('2011-03-25')),
PARTITION p26 VALUES LESS THAN (to_days('2011-03-30')),
);

3. Purging Data: In order to make query operation faster, Old data should be purged on a regular basis. Dropping partiton is the best and fastest way to purge old data. But, make sure that this operation is performed at off peak hours. Otherwise, it can block the whole table. While dropping partition from the top and adding partition from the bottom, we can maintain some specific duration data for eg 3 months.

In most cases alter table command rebuild the whole table. But, not in below cases.

ALTER TABLE mt DROP PARTITION ...
ALTER TABLE mt ADD PARTITION ...

The above task can be automated (details can be found in information_schema) using procedure and cronjob or mysql event.

4. Replication: Mysql doesn't support many to one replication. Therfore, script is required to transfer the data from many servers (services) to one servers (Report Server)

5: Intelligent Application: As, we know that count is slow in innodb. Therefore, application can be designed in such a way that report for more than a month can be scheduled. The result can be send to client using sms or email.

I've build a reporting server with 2TB+ data and still growing. The ratio of data and index is 6:4

Faulty Physical Ram

Some times it's very difficult to find out exact issue. Specially When it's related with hardware. Similar scenario, i faced with a client. I have been provided with a new box to setup mysql server. After setup mysql along with other application, mysql frequently goes down without any comment in mysql error log file. Spending few days verifying os, logs, mysql and later i found the culprit using memtester tool. Thanks to memtester tool.

[root@voice ~]# memtester 5 1
memtester version 4.1.2 (32-bit)
Copyright (C) 2009 Charles Cazabon.
Licensed under the GNU General Public License version 2 (only).

pagesize is 4096
pagesizemask is 0xfffff000
want 5MB (5242880 bytes)
got  5MB (5242880 bytes), trying mlock ...locked.
Loop 1/1:
Stuck Address       : ok      
Random Value        : ok
Compare XOR         : ok
Compare SUB         : ok
FAILURE: 0x7888cfc4 != 0xe088cfc4 at offset 0x00039fba.
Compare MUL         : FAILURE: 0x00000001 != 0x00000002 at offset 0x00039fba.
Compare DIV         : FAILURE: 0x7fff9d53 != 0x7fff9d52 at offset 0x00039fba.
Compare OR          : FAILURE: 0x77fb0403 != 0x77fb0402 at offset 0x00039fba.
Compare AND         :   Sequential Increment: ok
Solid Bits          : ok      
Block Sequential    : ok      
Checkerboard        : ok      
Bit Spread          : ok      
Bit Flip            : ok      
Walking Ones        : ok      
Walking Zeroes      : ok      

Done.
[root@voice ~]#

After replacing faulty ram with a new one. Things looks good and working fine.

[root@voice ~]# memtester 5 1
memtester version 4.1.2 (32-bit)
Copyright (C) 2009 Charles Cazabon.
Licensed under the GNU General Public License version 2 (only).

pagesize is 4096
pagesizemask is 0xfffff000
want 5MB (5242880 bytes)
got  5MB (5242880 bytes), trying mlock ...locked.
Loop 1/1:
Stuck Address       : ok      
Random Value        : ok
Compare XOR         : ok
Compare SUB         : ok
Compare MUL         : ok
Compare DIV         : ok
Compare OR          : ok
Compare AND         : ok
Sequential Increment: ok
Solid Bits          : ok      
Block Sequential    : ok      
Checkerboard        : ok      
Bit Spread          : ok      
Bit Flip            : ok      
Walking Ones        : ok      
Walking Zeroes      : ok      

Done.
[root@voice ~]#

Quick Table Query

Working with clients, sometime I have to deal wih more than 300
tables within a schema. In order to identify tables, views,
table size, alphabetical ordering of tables, engines etc.
Information Schema is the best place to look for detailed information.

Quick Alternatives:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.2.2-MariaDB-gamma Source distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use sakila;
Database changed
MariaDB [sakila]> show full tables;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| jk                         | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
24 rows in set (0.00 sec)

MariaDB [sakila]> show full tables where table_type='base table';
+------------------+------------+
| Tables_in_sakila | Table_type |
+------------------+------------+
| actor            | BASE TABLE |
| address          | BASE TABLE |
| category         | BASE TABLE |
| city             | BASE TABLE |
| country          | BASE TABLE |
| customer         | BASE TABLE |
| film             | BASE TABLE |
| film_actor       | BASE TABLE |
| film_category    | BASE TABLE |
| film_text        | BASE TABLE |
| inventory        | BASE TABLE |
| jk               | BASE TABLE |
| language         | BASE TABLE |
| payment          | BASE TABLE |
| rental           | BASE TABLE |
| staff            | BASE TABLE |
| store            | BASE TABLE |
+------------------+------------+
17 rows in set (0.01 sec)


MariaDB [sakila]> show full tables where table_type='view';
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor_info                 | VIEW       |
| customer_list              | VIEW       |
| film_list                  | VIEW       |
| nicer_but_slower_film_list | VIEW       |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff_list                 | VIEW       |
+----------------------------+------------+
7 rows in set (0.01 sec)

MariaDB [sakila]> show full tables where tables_in_sakila like 'film%';
+------------------+------------+
| Tables_in_sakila | Table_type |
+------------------+------------+
| film             | BASE TABLE |
| film_actor       | BASE TABLE |
| film_category    | BASE TABLE |
| film_list        | VIEW       |
| film_text        | BASE TABLE |
+------------------+------------+
5 rows in set (0.00 sec)

MariaDB [sakila]> show table status where name='actor' and engine='innodb' \G
*************************** 1. row ***************************
         Name: actor
       Engine: InnoDB
      Version: 10
   Row_format: Compact
         Rows: 200
Avg_row_length: 81
  Data_length: 16384
Max_data_length: 0
 Index_length: 16384
    Data_free: 5242880
Auto_increment: 201
  Create_time: 2010-09-29 19:55:46
  Update_time: NULL
   Check_time: NULL
    Collation: utf8_general_ci
     Checksum: NULL
Create_options:
      Comment:
1 row in set (0.00 sec)



This is my first post.

Hope you all will enjoy!!!