Impact of logging on MySQL’s performanceAleksandr Kuzminsky
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.
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).
It was used MySQL 5.0.75-percona-b11 on CentOS release 4.7
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
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
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
|# of connections||No logging, NOTPM||Logging queries >1 SEC, NOTPM||ratio 1 sec / no_logging||Logging all queries, NOTPM||Ratio all_logging / no_logging|
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
|# of connections||No logging, NOTPM||Logging queries > 1 sec, NOTPM||Ratio no_logging / 1 sec_logging||Logging all, NOTPM||Ratio no_logging / all_logging|
|1||225 Â± 9||211 Â± 3||0.94||213 Â± 9||0.95|
|20||767 Â± 41||730 Â± 35||0.95||751 Â± 33||0.98|
|100||746 Â± 54||731 Â± 12||0.98||703 Â± 36||0.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.
|# of connections||No logging, NOTPM||Logging all queries, NOTPM||Ratio all_logging /no_logging|
- 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.
- In general logging all queries can hurt MySQL and you should consider the load while using it, especially in CPU-bound case.