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…)

记得在周会上的时候，某个ORACLE DBA说select不属于DML。当时就觉得很奇怪，在大学的时候学习《数据库原理》，我明明记得select是属于DML的。难道ORACLE连数据库的基本概念都想改？好奇之下，问了好几个ORACLE DBA，他们都认为select不属于DML，其中考过OCP的DBA对此印象特别深刻。于是让他们把文档发给我验证下。 (more…)