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

Yeah, I am gonna do a good thing for all MySQL DBAs.

Here  is a  configuration example for  multi MySQL instances running on Redhat or CentOS 6:

[mysqld_safe]
pid-file=/mysql/myPORT/run/mysqld.pid
[mysql]
port=PORT
prompt=\\u@\\d \\r:\\m:\\s>
default-character-set=gbk

[client]
port=PORT
socket=/mysql/myPORT/run/mysql.sock

[mysqld]
#dir
basedir=/mysql/myPORT
datadir=/mysql/myPORT/data
tmpdir=/mysql/myPORT/tmp
lc_messages_dir=/usr/share
log-error=/mysql/myPORT/log/alert.log
slow_query_log_file=/mysql/myPORT/log/slow.log
socket=/mysql/myPORT/run/mysql.sock

#innodb
innodb_data_home_dir=/mysql/myPORT/log/iblog
innodb_log_group_home_dir=/mysql/myPORT/log/iblog
innodb_buffer_pool_size=INNODB_BUFFER_POOL_SIZE
innodb_buffer_pool_instances=8
innodb_log_files_in_group=4
innodb_log_file_size=1G
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=1
innodb_additional_mem_pool_size=20M
innodb_max_dirty_pages_pct=60
innodb_io_capacity=INNODB_IO_CAPACITY
innodb_thread_concurrency=THREADBY4
innodb_read_io_threads=THREAD
innodb_write_io_threads=THREAD
innodb_open_files=60000
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_flush_neighbor_pages=0
innodb_change_buffering=inserts
innodb_adaptive_flushing=1
innodb_adaptive_flushing_method=keep_average
innodb_adaptive_hash_index_partitions=8
innodb_old_blocks_time=1000
innodb_fast_checksum=1
innodb_stats_on_metadata=0
innodb_lazy_drop_table=1
innodb_read_ahead="linear"
innodb_use_native_aio=1
innodb_lock_wait_timeout=5
innodb_rollback_on_timeout=0
#innodb_strict_mode=1
transaction-isolation=READ-COMMITTED

#myisam
key_buffer=64M
myisam_sort_buffer_size=64M
concurrent_insert=2
delayed_insert_timeout=300

#replication
master-info-file=/mysql/myPORT/log/master.info
relay-log=/mysql/myPORT/log/relaylog
relay_log_info_file=/mysql/myPORT/log/relay-log.info
slave_load_tmpdir=/mysql/myPORT/tmp
slave_type_conversions="ALL_NON_LOSSY"
slave_net_timeout=4
skip-slave-start

#binlog
log-bin=/mysql/myPORT/log/binlog
server_id=1153668
binlog_cache_size=32K
max_binlog_cache_size=2G
max_binlog_size=500M
binlog-format=ROW
sync_binlog=1
log-slave-updates
expire_logs_days=7

#server
default-storage-engine=INNODB
character-set-server=gbk
lower_case_table_names=1
skip-external-locking
open_files_limit=65536
safe-user-create
local-infile=0
#sqlmod="STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE"
performance_schema=0
log_slow_admin_statements=1
log_slow_verbosity=full
log_warnings=1
long_query_time=1
slow_query_log=1
query_cache_type=0
query_cache_limit=1M
query_cache_min_res_unit=1K
table_definition_cache=65536
table_cache=65536
thread_stack=512K
thread_cache_size=256
read_rnd_buffer_size=128K
sort_buffer_size=256K
join_buffer_size=128K
read_buffer_size=128K
port=PORT
skip-name-resolve
skip-ssl
max_connections=8192
max_user_connections=8000
max_connect_errors=65536
max_allowed_packet=128M
max_long_data_size=128M
connect_timeout=8
net_read_timeout=30
net_write_timeout=60
back_log=1024

It’s quite funny because I have never seen MySQL like this.

I set up a MySQL replication several months ago, and we don’t use these production servers yet. So you can imagine how surprised I was when I saw  the CPU usage. Load is minor but MySQL uses 100% CPU in top.

top - 08:50:55 up 114 days,  5:46,  1 user,  load average: 0.00, 0.00, 0.00
Tasks: 140 total,   1 running, 139 sleeping,   0 stopped,   0 zombie
Cpu(s): 13.7%us,  0.5%sy,  0.0%ni, 85.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  35128056k total,  6323556k used, 28804500k free,   351320k buffers
Swap:        0k total,        0k used,        0k free,  2927496k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                       
28879 mysql     20   0 29.9g 2.2g 5732 S 105.0  6.5 117807:11 mysqld

I tried to restart one of the MySQL, but it didn’t help. After I restarted one of the servers, things changed.

top - 08:55:10 up  7:09,  2 users,  load average: 0.00, 0.00, 0.00
Tasks: 142 total,   1 running, 141 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.3%us,  0.4%sy,  0.0%ni, 99.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  35128056k total,  3169936k used, 31958120k free,    86580k buffers
Swap:        0k total,        0k used,        0k free,   297368k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                       
 3382 mysql     20   0 29.0g 2.1g 5188 S  0.0  6.4   0:22.37 mysqld

(more…)