Category: Performance

Performance

CPU Utilization is Not a Useful Metric

Once upon a time CPU utilization was quite a useful metric. Following are the output of several tools that provide CPU utilization metrics: top top reports a load of 1.66. Is this correct? No. The correct load number is probably closer to 2.4. # top -b -n 1| head -20 top – 11:27:45 up 151 …

Miscellaneous, Performance

Find evil developer habits with log_queries_not_using_indexes

Recently I switched on the MariaDB slow query logging flag log_queries_not_using_indexes just for curiosity on one of our customers systems: mariadb> SHOW GLOBAL VARIABLES LIKE ‘log_quer%’; +——————————-+——-+ | Variable_name | Value | +——————————-+——-+ | log_queries_not_using_indexes | OFF | +——————————-+——-+ mariadb> SET GLOBAL log_queries_not_using_indexes = ON; A tail -f on the MariaDB Slow Query Log caused …

Performance, Replication

Replication Performance Enhancements in MySQL 8

Although it feels like it was only yesterday that Oracle released version 5.7 of their acclaimed MySQL Community Server, version 8 is already available as a development milestone release (DMR). No, you didn’t sleep through a bunch of releases; MySQL is jumping several versions in its numbering due to 6.0 being dropped and 7.0 being …

Performance

20-30% Performance Hit from the Spectre Bug Fix on Ubuntu

  In this blog post, we’ll look at the performance hit from the Spectre bug fix on Ubuntu. Recently we measured the performance penalty from the Meltdown fix on Ubuntu servers. It turned out to be negligible. Today, Ubuntu made a Spectre bug fix on Ubuntu available, shipped in kernel 4.4.0-112. As with the Meltdown …

Miscellaneous, Performance, Security

Does the Meltdown Fix Affect Performance for MySQL on Bare Metal?

In this blog post, we’ll look at does the Meltdown fix affect performance for MySQL on bare metal servers. Since the news about the Meltdown bug, there were a lot of reports on the performance hit from proposed fixes. We have looked at how the fix affects MySQL (Percona Server for MySQL) under a sysbench workload. …

Clustering, Performance

MySQL InnoDB Cluster Performance Issue

Sometimes when you are using a MySQL InnoDB Cluster, you might encounter some performance issue because one node becomes dramatically slow. Why ? First of all, why ? A node can apply the transactions slower than the other nodes for many different reasons. The most frequents are for example, slower disks (remember, it’s advised to …

Performance

How to Get Optimizer Trace for a Query

While EXPLAIN shows the selected query plan for a query, optimizer trace will show you WHY the particular plan was selected. From the trace you will be able to see what alternative plans was considered, the estimated costs of different plans, and what decisions was made during query optimization. To turn on recording of optimizer …

Performance, Troubleshooting

Improving the Stability of MySQL Single-Threaded Benchmarks

I have for some years been running the queries of the DBT-3 benchmark, both to verify the effect of new query optimization features, and to detect any performance regressions that may have been introduced. However, I have had issues with getting stable results. While repeated runs of a query is very stable, I can get …

Performance, Troubleshooting

Improved Query Performance by Using Derived Table instead of IN-subquery

MySQL 5.6 introduced semi-join transformation for IN-subqueries. This opened up for several new ways to execute such subqueries; one was no longer tied to executing the subquery once for every row of the outer query. This dramatically improved the performance of many such queries. However, semi-join transformation does not apply to all types of queries. …