Phenomenon of read-only queries performance
We are evaluating Galera cluster with MariaDB and find out interesting phenomenon which we can not explain. We are using standard sysbench compex-OLAP, 10 million records. Performance of read-only queries is about 3k TPS and is quite stable. But then we run for a while read-write version and after it once again read-only version, then in this case performance starts to grow from 4k till 10k TPS. 10K TPS is reached after about 30 seconds of running benchmark and stays at this level. But if we stop benchmark and restart it, then once again speed falls down to 3k TPS.
Data is located in temporary file system, so disk is not involved at all. We used recommended configuration settings for MariaDB and Galera. Also the same behavior is observed with standalone mysql without Galera: performance of read-only queries varies from 3k to 6k TPS (but high values are smaller than with Galera, although no load balancing was performed).
Is there any explanation of such behavior and where we can look trying to understand the reason? Why performance of standalone mysql can be slower than on Galera cluster? How we can tune it?
Answer Answered by Nirbhay Choubey in this comment.
The performance gain in the first scenario on switch from R/W to R/O could be due to usual database "warm-up".
When compared to standalone server, galera node (assuming that the test was run against a galera cluster with a single node) should perform as good as standalone if not worse. So, I suspect the two servers were run with different configurations. One option would be to compare servers' system and status variables using SHOW VARIABLES & SHOW STATUS respectively.
