Re: Prepared Statements, MySQL 4.1, and binary protocol
Posted by: Mark Matthews
Date: February 11, 2005 02:50PM

Jason Winnebeck wrote:
> I've been trying to find out some more information
> about this, and it is mostly for my curiousity and
> I cannot find it on the forums or in the manual
> more precisely than I've seen so far.
>
> I've heard that MySQL protocol normally sends all
> data across the wire (TCP in this case) in text
> encoding, and even does this when connecting to
> localhost.

Jason,

Correct.

> It always seemed to me that this is very
> inefficent, because if the client wants the data
> in binary form the server converts it to text,
> sends it across the socket and then the client has
> to parse it back. Also when sending over the
> network, text data is larger, so more bandwidth is
> used.

Yes, but it is drop-dead simple, which often wins the war (but not the battle), especially if you want other people to embrace your protocol (look at HTTP as an example).

>
> Some of our DB operations are CPU bound. What I'm
> wondering is if the binary protocol actually sends
> data across in binary form, which is faster to
> parse and more compact to send across the network,
> so for slow CPUs or slow connections, the binary
> form will result in a speedup.
>
> Is this true? If I understand correctly, the

It can be true if 1) most of your data is numeric or datetime in nature and 2) most of your SQL statements are 'static', and the varying parts are all parameters.

> binary protocol can only be used in C or JDBC when
> using the new prepared statement API? So if I

Correct.

> were to use prepared statements, MySQL 4.1.8 and
> Connector/J 3.1.6 then I will automagically get
> data across the network in binary form? I figure

Yes, but only non-string data (numbers/dates) are in 'binary form'.

> both server side preparation and binary-encoded
> results will speed things up when I make
> repetitive select queries. Is what I'm thinking
> correct? Would anyone like to make more of a
> comment about the implementation of the new
> prepared statements/binary encoding to satisfy my
> curiousity?
> So far from my benchmarking, 3.1 is faster than
> 3.0 in general but uses a lot more memory. MySQL

I'd be curious to see how you do your benchmarking..I've only noticed increased memory usage if the application itself is not good about closing statements and result sets when it's done with them (which is required by the JDBC spec, btw).

If you take a look at LoadStorePerfTest in the testsuite that comes with the driver, I've profiled that, and the only memory difference between it and 3.0, or 3.1 when not using server-side prepared statements is 19 more object instances, which are all related to the parameters in the prepared statements involved (which have 19 parameters all total).

> 4.1 seems to be slower than 4.0, but takes less
> memory (when not using server-side prep stmt).

4.1 server is also slower because all database metadata is in UTF-8, which is slower to compare than the 'latin1' encoding MySQL-4.0 uses.

-Mark

Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html

Options: ReplyQuote


Subject
Written By
Posted
Re: Prepared Statements, MySQL 4.1, and binary protocol
February 11, 2005 02:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.