- 15 MySQL Client/Server Protocol
- 15.1 Overview
- 15.2 Connection Phase
- 15.3 Authentication Method
- 15.4 Compression
- 15.5 SSL
- 15.6 Text Protocol
- 15.7 Prepared Statements
- 15.8 Stored Procedures
- 15.9 Replication Protocol
- 15.10 Row Based Replication
- 15.11 Semi-Sync Replication
- 15.12 Protocol Examples
- 15.13 Source Code Locations
Compression
is its own protocol,
is transparent to the rest of the MySQL protocol and
compresses a stream of bytes (which may even be a part of several
Protocol::Packet).
It is enabled if
the server announces
CLIENT_COMPRESSin itsProtocol::Handshakeandthe client requests it too in its
Protocol::HandshakeResponsepacket andafter the server finishes Connection Phase with a
OK_Packet.
The compressed packet consists of a Compressed Packet header and a payload which is either a Compressed Payload or Uncompressed Payload.
The header looks like:
3 length of compressed payload 1 compressed sequence id 3 length of payload before compression
- length of compressed payload
raw packet length minus the size of the compressed packet header (7 bytes) itself.
- compressed sequence id
sequence id of the compressed packets, reset in the same way as the MySQL Packet, but incremented independently
- length of payload before compression
size of payload before it was compressed.
If the length of payload before compression is more than 0 the Compressed Packet header is followed by the compressed payload.
It uses the deflate algorithm as defined in RFC 1951 and implemented in zlib. The header of the compressed packet has the parameters of the uncompress() function in mind:
ZEXTERN int ZEXPORT uncompress OF((Bytef *dest, uLongf *destLen,
const Bytef *source, uLong sourceLen));
The payload can be anything from a piece of a MySQL Packet to several MySQL Packets. The client or server may bundle several MySQL packets, compress it and send it as one compressed packet.
A
COM_QUERY
for select
"012345678901234567890123456789012345" without
CLIENT_COMPRESS
has a payload length of 46 bytes looks
like:
2e 00 00 00 03 73 65 6c 65 63 74 20 22 30 31 32 .....select "012 33 34 35 36 37 38 39 30 31 32 33 34 35 36 37 38 3456789012345678 39 30 31 32 33 34 35 36 37 38 39 30 31 32 33 34 9012345678901234 35 22 5"
with
CLIENT_COMPRESS
the packet is:
22 00 00 00 32 00 00 78 9c d3 63 60 60 60 2e 4e "...2..x..c```.N cd 49 4d 2e 51 50 32 30 34 32 36 31 35 33 b7 b0 .IM.QP20426153.. c4 cd 52 02 00 0c d1 0a 6c ..R.....l
comp-length |
seq-id |
uncomp-len |
compressed payload |
|
|
|
compress( |
The compressed packet is 41 bytes long and splits into:
raw packet length -> 41 compressed payload length = 22 00 00 -> 34 (41 - 7) sequence id = 00 -> 0 uncompressed payload length = 32 00 00 -> 50
Executing SELECT repeat("a",
50) results in uncompressed
ProtocolText::Resultset
like:
01 00 00 01 01 25 00 00 02 03 64 65 66 00 00 00 .....%....def...
0f 72 65 70 65 61 74 28 22 61 22 2c 20 35 30 29 .repeat("a", 50)
00 0c 08 00 32 00 00 00 fd 01 00 1f 00 00 05 00 ....2...........
00 03 fe 00 00 02 00 33 00 00 04 32 61 61 61 61 .......3...2aaaa
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 aaaaaaaaaaaaaaaa
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 aaaaaaaaaaaaaaaa
61 61 61 61 61 61 61 61 61 61 61 61 61 61 05 00 aaaaaaaaaaaaaa..
00 05 fe 00 00 02 00 .......
which consists of 5
Protocol::Packet.
01 00 00 01 0125 00 00 02 03 64 65 66 00 00 00 0f 72 65 70 65 61 74 28 22 61 22 2c 20 35 30 29 00 0c 08 00 32 00 00 00 fd 01 00 1f 00 0005 00 00 03 fe 00 00 02 0033 00 00 04 32 61 61 61 61 ...05 00 00 05 fe 00 00 02 00
If compression is enabled a compressed packet containing the compressed version of all 5 packets is sent to the client:
4a 00 00 01 77 00 00 78 9c 63 64 60 60 64 54 65 J...w..x.cd``dTe 60 60 62 4e 49 4d 63 60 60 e0 2f 4a 2d 48 4d 2c ``bNIMc``./J-HM, d1 50 4a 54 d2 51 30 35 d0 64 e0 e1 60 30 02 8a .PJT.Q05.d..`0.. ff 65 64 90 67 60 60 65 60 60 fe 07 54 cc 60 cc .ed.g``e``..T.`. c0 c0 62 94 48 32 00 ea 67 05 eb 07 00 8d f9 1c ..b.H2..g....... 64 d
sending a MySQL Packet of the size
2^24-5 to
2^24-1 via compression
leads to at least one extra compressed packet.
If the uncompressed MySQL Packet is like:
fe ff ff 03 ... -- length = 2^24-2, sequence id = 3
compressing it would result in the length of payload before compression being:
length of mysql packet payload: 2^24-2 length of mysql packet header: 4 length of payload before compression: 2^24+2
which can not be represented in one compressed packet. Instead two or more packets have to be sent.
For small packets it may be to costly to compress the packet:
compressing the packet may lead to more data and sending the data uncompressed
CPU overhead may be not worth to compress the data
Usually payloads less than 50 bytes
(MIN_COMPRESS_LENGTH)
aren't compressed.
To send an uncompressed payload:
set length of payload before compression to 0
the compressed payload contains the uncompressed payload instead.
Sending a SELECT 1 query as
uncompressed payload
to the server looks like:
0d 00 00 00 00 00 00 09 00 00 00 03 53 45 4c 45 ............SELE 43 54 20 31 CT 1
decodes into:
raw packet length -> 20 compressed payload length = 0d 00 00 -> 13 (20 - 7) sequence id = 00 -> 0 uncompressed payload length = 00 00 00 -> uncompressed
... with the uncompressed payload starting right after the 7 byte header:
09 00 00 00 03 53 45 4c 45 43 54 20 31 -- SELECT 1
