We are moving a database from a single Percona master/slave setup to a dual-data center, PXC setup. I did some benchmarks today that seem strange. They seem to indicate that read performance on a local PXC node is as bad as (or worse) than reading between data centers, which doesn’t make sense to me.
Here’s my methodology:
I took four read queries from the slow-query log on a a Percona MySQL server and wrote a script that would clear the query cache and run all four queries 10 times in a loop. So the loop is toreset the query cache, run four long queries, loop again–10 times.
First I tried the script in our first data center talking to a Percona MySQL Master in the same data center. over a LAN. The times were:
Then I copied the scripts to our other data center and ran them again, still pointing to that same Percona MySQL master in the first data center. The purpose here was to measure the effect of network latency on read operations across the internet. The times were:
So we can see that network latency adds about 10-11 seconds to my loop on a READ operation.
I have a PXC cluster set up with two nodes in each data center. All four nodes are configured exactly the same as the Percona MySQL server, with the same data set. I ran my scripts locally against a PXC node in the same data center. This would be comparble to the first test above, since the data set is local to the PXC node. I would have expected the results to be the same as those of the first test case above, because PXC keeps an entire copy of the data locally, and these were only read operations.
Instead I saw:
The performance was worse than reading between data centers.
Any ideas on why?
Both servers (the PXC cluster and the Percona) have /tmp/mysqltmp mounted as RAM drives to speed tmp table operations.
Software on the PXC node was:
On the MySQL Master:
Any thoughts or insight would be helpful.