Friday, January 4, 2013

protocol speed comparison on windows

Comparison of Protocols


A while ago I wrote a small random function tester to fuzz test native functions such as linestring, polygon, astext, etc.  The queries it sends are generally small (100 bytes or less) and a totally CPU bound workload, since no data/tables are accessed.

As this was pretty much an open-ended test,  simply pumping random data into the functions, I had planned to let it run for a few days and see if any problems arose.

I benchmarked all the ways to connect on windows;  TCP/IP, named pipe, shared memory, and embedded server.

5.5.29 client and server are used here in all tests.  Roughly 345 million queries are sent via 8 threads.  Below is a graph to show the QPS of each protocol for the run:

Averages:



The QPS taken every minute is here.
As we see, libmysqld can do nearly 8x the throughput of tcp/ip in this test.  This matters, when you're running hundreds of billions of small fast queries.

Conclusion 

Embedded speed is clearly superior.  For this reason, I always try writing QA/testing code in C/C++ if I think it might need to be run billions of times.    But you lose ability to monitor the embedded server status from a mysql client, which is annoying.   However, distributing an embedded server application is far easier as it's self-contained. :)

Shared memory doesn't care to enforce wait_timeout, so you may want an idle-connection-killer script
looping in the background.  Also, shared memory connection isn't very stable at high concurrency.  This stability issue is already being dealt with so that's a plus.

Happy testing!