Analysis of MySQL Network Communication Protocol (MySQL 5.7)

MySQL's network communication protocol consists of several layers, starting with the MySQL packet protocol at the top and ending with the socket transmission at the bottom. The article focuses on MySQL 5.7 and provides code excerpts to illustrate the implementation.

Session Creation

The main thread listens for client connections and creates a Per_thread_connection_handler for each, which spawns a user thread whose entry function is handle_connection . In MySQL 5.7 the initialization of THD , NET , and VIO is moved from the main thread to the newly created user thread, improving short‑connection performance.

Key call stack for creating a session:

mysqld_main → connection_event_loop → listen_for_connection_event → Connection_handler_manager::process_new_connection → Per_thread_connection_handler::add_connection → handle_connection

Inside handle_connection the following steps occur:

handle_connection → init_new_thd → Channel_info_local_socket::create_thd → create_and_init_vio → Protocol_classic::init_net → my_net_init → vio_fastsend (sets IP_TOS and TCP_NODELAY) → Global_THD_manager::add_thd → thd_prepare_connection → login_connection → check_connection → vio_keepalive → acl_authenticate → prepare_new_connection_state

NET/VIO Layer

The my_net_write function copies data to the NET buffer; if the length exceeds MAX_PACKET_LENGTH (4 MiB‑1) the packet is split, each packet header containing a 3‑byte length field and a 1‑byte packet number ( net->pkt_nr ). The buffer is flushed via net_flush when full.

When compression is enabled an extra 3‑byte header ( COMP_HEADER_SIZE ) is added, and net->compress_pkt_nr is incremented for each compressed packet.

Data is finally sent using net_write_raw_loop , which respects vio->write_timeout (controlled by net_write_timeout ) and may retry on EINTR or block on EWOULDBLOCK/EAGAIN until the timeout expires.

VIO encapsulates socket operations for various connection types (TCP/IP, SSL, named pipe, shared memory).

Result Set Transmission

MySQL uses two main protocol classes: Protocol_binary for prepared statements and Protocol_classic for ordinary queries. The article walks through a simple SELECT example, showing how metadata (column definitions) is sent before row data, and how the EOF packet marks the end of the result.

Metadata fields include identifier, database name, table name, column name, charset, length, type, flags, etc., which can dominate network traffic for simple queries.

Metadata Overhead Test

A benchmark using mysqlslap on a table with 40 columns demonstrates that reducing metadata (using options like METADATA_IGNORE ) dramatically lowers network traffic and increases TPS, confirming that metadata can be a significant bottleneck.

Test command:

mysqlslap --no-defaults -uxx --create-schema=test -h$host -P $port --number-of-queries=1000000000 --concurrency=100 --query='SELECT * FROM test.test_meta_impact where abcdefg1 = 2'

Results (TPS and Net send):

METADATA_FULL: 3.48 w TPS, 113 M

METADATA_REAL_COLUMN: 7.2 w TPS, 111 M

METADATA_FAKE_COLUMN: 9.2 w TPS, 116 M

METADATA_NULL_COLUMN: 9.6 w TPS, 115 M

METADATA_IGNORE: 13.8 w TPS, 30 M

The data shows that metadata overhead can heavily affect both network bandwidth and throughput in extreme scenarios.

Source: Database Kernel Monthly, July 2016.