MySQL and MongoDB DBA

Have you ever wondered how much network traffic was caused by a query with a  specific data set? In this post, I would like to share some research I did for stress testing network. During the whole test, I used mysqlslap to simulate a kv query. it’s like the following SQL:

SELECT UUID() as data1, UUID() as data2, UUID() as data3, UUID() as data4, 
UUID() as data5, UUID() as data6, UUID() as data7, UUID() as data8, 
UUID() as data9, UUID() as data10, UUID() as data11, UUID() as data12, 
UUID() as data13, UUID() as data14, UUID() as data15, UUID() as data16,
UUID() as data17, UUID() as data18, UUID() as data19, UUID() as data20,
UUID() as data21, UUID() as data22, UUID() as data23, UUID() as data24,
UUID() as data25, UUID() as data26, UUID() as data27, UUID() as data28,
UUID() as data29, UUID() as data30, UUID() as data31, UUID() as data32

It has 32 fileds, and every one of them is 36 bytes long, which makes the whole record 1152 bytes.

MySQL can hardly utilize  all the cpu and network traffic, so I started four MySQL instances on one machine and four clients remotely. After warm-up period, I captured realtime network traffic with ‘sar -n DEV’:
QPS for each instance are 12823, 12936, 13153 and 13123. Basicly 2370 byte per query.

We all know its original size is just 1152 bytes, so that’s a surprisingly expensive query! Let’s dig a little deeper to see what the hell happens inside. Read chapter ”Client/Server Communication” of “Understanding MySQL Internals” carefully, the knowledge there is still updated (although it’s 6 years old already). Usually ppl are too lazy to read it by themselves, so I summarized it a little.

There are four kinds of packets sent from server to client:

1. OK Packet
2. Error Packet
3. EOF Packet
4. Result Set Packets (NOTICE: plural)

Result Set Packets contain field definition sequence of packets and data packets.

After server receives a query, it responses client with result Set packets as following sequence:

1. A packet with the body consisting of the standard field-length specifier sequence
2. A group of field description packets, one for each field, in the field order of the result set
3. EOF packet
4. Row data packets, one packet per row
5. EOF packet

Before finding out what’s inside every packet, we need to learn an important definition called “Data Field” first. Basicly, every field in MySQL packet is abstracted as ”Data Field”, and the length of “Data Field” is like the following table:

Length of Actual Data (N Byte)	Length specifier sequence	Length of Data Field
N < 251 Byte	1 Byte	(1 + N) Byte
N < 65536 Byte	2 Byte	(2 + N) Byte
N < 16777216 Byte	3 Byte	(3 + N) Byte
N >= 16777216 Byte	8 Byte	(8 + N) Byte

According to the table we define a function called “length_of_data_filed”:

Function length_of_data_filed(data)
{
  If (length(data) < 251)
    return (length(data)+1);
  else if (length(data) < 65536)
    return (length(data)+2);
  else if (length(data) < 16777216)
    return (length(data)+3);
  else
    return (length(data)+4);
}

Here comes the length of packets:

1. Standard Field-length Specifier Sequence Packet, 1 byte
2. Field Description Packet, 17 + length_of_data_filed(db_name) + length_of_data_filed(table_name) + length_of_data_filed(alias_table_name) + length_of_data_filed(column_name) + length_of_data_filed(alias_column_name) + length_of_data_filed(default_value)
3. Row Data Packet, length_of_data_filed(field1) + length_of_data_filed(field2) + … + length_of_data_filed(fieldn)
4. EOF Packet, 5 bytes

(more…)

Let’s assume that you have a lot of machines running multiple MySQL instances. Is it a little troublesome to remember all the server_id in case of having a server_id confliction when we build a MySQL cluster? Which makes it even worse is that some of us have several IDC in different areas. Can we have an easy way to generate globally unique server_id based on IP address and port?

I am sorry to tell you, that’s not possible without limitation. So the problem becomes how can we generate server_id  most uniquely.
server_id is a 32-bit int in MySQL, and IP address is represented as a 32-bit int in C too. To record port information into a 32-bit int, we need to ignore some bits from the head of IP address which are always the same to us. Here is my way to do it in Linux:

#!/bin/bash

hostip=`hostname -i`
a=`echo $hostip|cut -d\. -f1`
b=`echo $hostip|cut -d\. -f2`
c=`echo $hostip|cut -d\. -f3`
d=`echo $hostip|cut -d\. -f4`
port=65 #Change this in your code

echo "${a}.${b}.${c}.${d}"
serverid=`expr \( ${a} \* 256 \* 256 \* 256 + ${b} \* 256 \* 256 + ${c} \* 256 + ${d} \)`
echo ${serverid}

serverid=$((${serverid} << 6))
serverid=`expr ${serverid} % 4294967296`
echo ${serverid}

serverid=`expr ${serverid} + \( ${port} % 64 \)`
echo ${serverid} #Yeah, here we go

It is widely known that MySQL now supports two kinds of replication: statement based replication (SBR) and row based replication (RBR). And the corresponding logging methods used in replication are called statement based logging (SBL) and row based logging (RBL). In this post, I am going to find out a few more details inside of different logging methods.

Before that, make sure you have read the basics in MySQL official document. Please keep in mind that binlog is used for recovering data and replication is nothing but an online recovery process. That will make it easier to understand.
1. Binary Logging Formats
2. Advantages and Disadvantages of Statement-Based and Row-Based Replication

OK, let’s begin our journey to binlog’s design. The first question, what will you do if you want to save all the data changes in log?
The easiest way may be to record every commands related to data changes into log. That is SBL and here is a log example for insertion:

# at 463
#120618 6:01:57 server id 1 end_log_pos 577 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1339999317/*!*/;
insert into test(idx,data) values (2,3),(4,5),(6,7)

SBL seems to be good enough until we find some records are not completely same after recovery. Why is datetime filed different from it was before? We are talking about nondeterministic functions here. Try to imagine the followings:
1. Insert one record with one datetime filed by execute “insert into test values (sysdate())”
2. Data in MySQL will be “2012-06-16 08:31:47″  and the command in binlog (SBL format) will be “insert into test values (sysdate())”
3. One month later, recover data from binlog. Then we will find that record’s value change to “2012-07-16 08:31:47″

(more…)

之前介绍了MySQL如何避免使用swap的四个方法。这里需要补充一下原理和实现机制，对于Linux api不感兴趣的同学可以直接跳过。

一、操作系统设置swap的目的
程序运行的一个必要条件就是足够的内存，而内存往往是系统里面比较紧张的一种资源。为了满足更多程序的要求，操作系统虚拟了一部分内存地址，并将之映射到swap上。对于程序来说，它只知道操作系统给自己分配了内存地址，但并不清楚这些内存地址到底映射到物理内存还是swap。
物理内存和swap在功能上是一样的，只是因为物理存储元件的不同（内存和磁盘），性能上有很大的差别。操作系统会根据程序使用内存的特点进行换入和换出，尽可能地把物理内存留给最需要它的程序。但是这种调度是按照预先设定的某种规则的，并不能完全符合程序的需要。一些特殊的程序（比如MySQL）希望自己的数据永远寄存在物理内存里，以便提供更高的性能。于是操作系统就设置了几个api，以便为调用者提供“特殊服务”。

二、Linux提供的几个api
1、mlockall()和munlockall()
这一对函数，可以让调用者的地址空间常驻物理内存，也可以在需要的时候将此特权取消。mlockall()的flag位可以是MCL_CURRENT和MCL_FUTURE的任意组合，分别代表了“保持已分配的地址空间常驻物理内存”和“保持未来分配的地址空间常驻物理内存”。对于Linux来说，这对函数是非常霸道的，只有root用户才有权限调用。

2、shmget()和shmat()
这一对函数，可以向操作系统申请使用大页内存（Large Page）。大页内存的特点是预分配和永驻物理内存，因为使用了共享内存段的方式，page table有可能会比传统的小页分配方式更小。对于多进程共享内存的程序（比如ORACLE），大页内存能够节省很多page table开销；而对于MySQL来说，性能和资源开销都没有显著变化，好处就在于减少了内存地址被映射到swap上的可能。至于为什么是减少，而不是完全避免，之后再讲解。
(more…)