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

HandlerSocket is well known as a MySQL NoSQL plugin which provides user a much better performance without losing SQL features as some other NoSQL database do.
But honestly speaking, not all the fancy software would have a lot of users. Most of the time people just forward posts and blogs without trying to use the amazing thing first.
The problem HandlerSocket has is that we can merely find any blogs talking about how to tune or troubleshoot it. Based on my little experience of HandlerSocket, today I would like to share some problems we had encountered. And this post will be updated according to the future usage.

1. MySQL error code 167: Failed to set row auto increment value
This error is due to a HandlerSocket bug. If you can understand Chinese, please read this post written by Ruohui Huang.
The solution here is very easy: set innodb_autoinc_lock_mode to 0.

2. HNDSOCK failed to lock tables
If you try to use HandlerSocket API to update a read-only InnoDB table, this message will appear in the error log.

3. DDL timeout
It is very hard to do DDL on HandlerSocket enabled tables beacause it keeps tables opened for reuse. Even HandlerSocket in Percona server would close tables when traffics become small, there is definitely no parameter to control this behaviour. If you are suffering from this DDL problem, maybe you can try HandlerSocket in Spider server. There is a parameter named handlersocket_close_table_interval, hope it’s helpful to you.