Friday, 25 July 2014

Cost of enabling general log in MySQL

At some point of time every DBA must have come across a situation where he has to enable the general log to debug an issue or even to capture all the queries hitting the DB. In such situations what most DBA’s do is inform the stakeholders that he is  enabling the general log and this might cause a drop in performance and in turn the stakeholders ask a question like
“What will the percentage of performance drop?  Can you give us a number?”

The easy answer is “It is difficult to provide a number”.
 I had wanted to address this, but never really made an effort until I saw the below blog post “PERFORMANCE_SCHEMA VS SLOW QUERY LOG”

In that particular blog Peter explains why he chose to use slow query log to capture slow queries instead of using the PERFORMANCE SCHEMA. At some point he talks about the overhead of enabling the PERFORMANCE SCHEMA. He used sysbench tool to calculate the overhead and provides us an approximate value. I wanted to use the same technique to calculate the overhead of general log
I did some benchmarks that gave me some interesting numbers.

The machine I used is a dedicated box with 12 cores. I am mentioning it here because it plays an important role in my benchmarks.

sysbench --test=oltp --oltp-test-mode=simple  --num-threads=8  --max-requests=0 --max-time=100  --mysql-db=test  --db-driver=mysql   --mysql-user=root --oltp-table-size=1000000 --mysql-password=’’ run

I did a series of benchmarks by gradually increasing the number of threads from 4 until 25

From the chart it is evident that increasing the number of threads increases the “queries per second” until the number of threads match the number of CPU cores. The machine has 12 cores and it is obvious that increasing the number of threads beyond 12 has little effect on “Queries per sec”.
Now the overhead

With number of threads =4 the overhead was around 14%. Increasing the number of threads gradually increases the overhead up to 42% and stays around 42 regardless of increasing the number of threads above 12(which is equal to the number of CPU cores).
But this doesn’t mean that enabling general log results in an overhead of 40% unless the system is very busy serving many parallel threads at the same instance.
This number could also vary depending on the Disk IO. I am interested to see the results of this benchmark on different hardware environments.  I would be happy if somebody could do the same benchmark on their machines and share the results here