List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:December 7 2000 8:51am
Subject:MySQL protocol efficiency
View as plain text  

>>>>> "bruce" == bruce  <bruce@stripped> writes:

bruce> Hi,
bruce> 	I imagine this has been asked before, but I can't find any reference to
bruce> it, so here goes.

bruce> I've been looking at the MySQL protocol, mainly because I would like to find
bruce> a way to make the JDBC driver work better.  In my investigation, I noticed
bruce> the following things about the protocol used between the client and server:

bruce> * It uses a 'Packet' system, despite the fact that communications are 
bruce>   occuring over a reliable socket.  There seems to be no point to this.
bruce>   It also introduces an extra copy/write cycle for the data.

The packet system is required for the client to know how many bytes to
read to get the whole answer.  The packets also makes it very easy to
find errors in the server/client protocol (and even in the TCP/IP
connection).  Having packets makes it also very easy to use
compression and encryption on the protocol. Just the above makes it
well worth the extra copy cycle.

bruce> * All results are sent as strings, even integers and doubles.  This leads
bruce>   to unnecessary bytes being sent down the channel (2-15 bytes being sent
bruce>   rather than 4 or 8), and the unnecessary overhead of formatting the 
bruce>   string on the server, and parsing it back in on the client. 

The extra bytes is not a major overhead as most results are small. The
formatting of numbers is however a major overhead that we want to get
rid off!

bruce> It would seem to me that fixing these two issues would make the protocol
bruce> faster.  I agree there are endian issues involved with sending binary data,
bruce> but there are standard C macros to convert to and from big/small endian

We plan to change everything to be little-endian as we already have macros in
MySQL to easily fix this.

bruce> Changing a protocol is never easy, but the gains available here seem 
bruce> worthwhile to me.  Are there any plans to addess these issues?

The reason for using the current protocol where originally to make it
easy to port clients from mSQL to MySQL (and vice versa).

To maintain the compatibility to old clients, we plan to solve this
approximately the following way:

- Extending the MySQL interface so that the user can bind variables to
  the parameters and results.
- When a uses uses the new bind function, we will tell the server this
  by using COM_BINARY_QUERY instead of COM_QUERY.
- The server will now send the data binary and the client will store
  it in binary format.
- When the client executes mysql_fetch_data(), we will then copy
  the data to the bound variables.
- We will also support binding of strings to pointers, to avoid
  having to copy the data.

Now we only need someone to implement the above :)

MySQL protocol efficiencybruce7 Dec
  • MySQL protocol efficiencyMichael Widenius7 Dec
    • Re: MySQL protocol efficiencybruce7 Dec
      • Re: MySQL protocol efficiencyMichael Widenius7 Dec
        • Re: MySQL protocol efficiencySasha Pachev7 Dec