Emergency

Impact of logging on MySQL’s performance


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

Introduction
When people think about Percona’s microslow patch immediately a question arises how much logging impacts on performance. When we do performance audit often we log every query to find not only slow queries. A query may take less than a second to execute, but a huge number of such queries may significantly load a server. On one hand logging causes sequential writes which can’t impair performance much, on other hand when every query is logged there is a plenty of write operations and obviously performance suffers. Let’s investigate how much.

I took DBT2, an OSDL’s implementation of TPC-C.
Hardware used
The benchmark was run on a DELL server running CentOS release 4.7 (Final)
There are four CPUs Intel(R) Xeon(R) CPU 5150 @ 2.66GHz, 32GB RAM. There are 8 disks in RAID10(a mirror of 4+4 striped disks).
Software
It was used MySQL 5.0.75-percona-b11 on CentOS release 4.7
MySQL setting
There were two cases considered CPU- and IO-bound.
Each case had three options:

  • logging turned off;
  • logging queries which take more than a second to execute;
  • logging every query;

MySQL was run with default settings except following:

Depending on workload different InnoDB buffer was used.
In CPU-bound case

In IO-bound case

DBT2 settings
For CPU-bound case number of warehouses was 10(1.31GiB). In case of IO-bound load – 100 warehouses which is 10GiB in terms of database size.
The test was run with 1, 20 and 100 database connections
Results
To reduce random error the test was run 3 times per each parameter set.
The metric of a DBT2 test is NOTPM (New Order Transaction per Minute) – the more the better.

CPU-bound case – 10 warehouses

Database size 1.31 GiB
# of connectionsNo logging, NOTPMLogging queries >1 SEC, NOTPMratio 1 sec / no_loggingLogging all queries, NOTPMRatio all_logging / no_logging
1960796321.0084340.88
2027612277201.00221050.80
10011704117411.00109560.94


We see here that logging all queries decreases MySQL’s performance on 6-20% depending on a number of connections to a database.
It should be noted during the test it was executed roughly 20-25k queries per second. If all queries are logged – a slow log is populated at rate about 10MB/sec. This is the highest rate observed.
IO-bound case – 100 warehouses

Database size 10GiB
# of connectionsNo logging, NOTPMLogging queries > 1 sec, NOTPMRatio no_logging / 1 sec_loggingLogging all, NOTPMRatio no_logging / all_logging
1225 ± 9211 ± 30.94213 ± 90.95
20767 ± 41730 ± 350.95751 ± 330.98
100746 ± 54731 ± 120.98703 ± 360.94


In this case every test was run 5 times and random measurement error was calculated. As it is seen from the chart above the performance almost doesn’t depend on logging – the difference doesn’t exceed the measurement error.

The query rate in this case is about 1000 per second.

Logging to /dev/null
It is interesting to know how much from performance degradation caused by the microslow patch itself. Let’s do the same tests but logging to /dev/null.

CPU-bound case – 10 warehouses, Database size: 1.31 GiB
# of connectionsNo logging, NOTPMLogging all queries, NOTPMRatio all_logging /no_logging
1951289430.94
2027675258690.93
10011609112360.97


Conclusion
From the all tests above there are two conclusions can be made:

  1. It is safe to log slow queries with execution time bigger than a second without worry about performance impact in case of CPU-bound workload. The performance impact is negligibly small in IO-bound workload even if all queries are logged.
  2. In general logging all queries can hurt MySQL and you should consider the load while using it, especially in CPU-bound case.
Share Button
PREVIOUS POST
NEXT POST


Aleksandr Kuzminsky

Aleksandr is a consultant and data recovery specialist. He is a former Percona employee.



Categories:
Benchmarks


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *