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!
4 comments:
I think shared memory should not exist anymore.
The protocol does not have a way to detect dead connections (I believe you might have mixed that with wait_timeoutm, I think timeouts should be taken care of),
so any terminated client program, that is terminated before doing an orderly connection close results in connection leak.
Also mentioning "8 threads" in conjuction with embedded sounds odd. Embedded is not able to handle more than one connection and it is unlikely that it has changed in the last time. Can you explain what 8 threads are doing with embedded?
Wlad, multiple threads seem to work just fine with embedded.
But, I'm not using InnoDB at all, I think that was the limitation.
my.ini:
[libmysqld_server]
sync-frm=0
log-error=error.log
myisam-recover=force
datadir=./data
lc-messages-dir=./share/
lc-messages=en_US
default-storage-engine=myisam
skip-innodb
skip-grant-tables
skip-name-resolve
skip-slave-start
loose-stored_program_cache=10
loose-metadata_locks_cache_size=10
lock_wait_timeout=1
max_error_count=0
symbolic_links=0
See http://mysqlserverteam.com/improving-the-performance-of-mysql-on-windows/
Post a Comment