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:
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|
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.