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:
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.
ConclusionEmbedded 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.