In several RDBMS databases, including MySQL, subqueries are often one of the causes for performance issues. Therefore, we have an incentive to avoid them whenever we can and to find alternative ways to implement our requirements.

One of the most popular uses for subselects in SQL is when one needs to fetch the first, last, maximum or minimum row for each group in a table. For example, how would you implement an SQL query that should fetch the employees with the maximum salary for each department from the employees table? Actually, fetching the salary itself is pretty easy, but it becomes more complicated when you want to fetch the employee name (the row data) along with the maximum salary.

Let’s look at the table:

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.

We have mentioned ClickHouse in some recent posts (ClickHouse: New Open Source Columnar Database, Column Store Database Benchmarks: MariaDB ColumnStore vs. Clickhouse vs. Apache Spark), where it showed excellent results. ClickHouse by itself seems to be event-oriented RDBMS, as its name suggests (clicks). Its primary purpose, using Yandex Metrica (the system similar to Google Analytics), also points to an event-based nature. We also can see there is …

In May, I presented two talks – one called “Are you getting the best out of your indexes?” and “Optimizing Queries Using EXPLAIN”. I now have slides and video for both of them.

The first talk about indexing should probably be titled “Why is MySQL doing this?!!?!!?” It gives insight into why the MySQL optimizer chooses indexes that you do not expect; especially when it does not use an index you expect it to.

The talk has something for everyone – for beginners it explains B-trees and how they work, and for the more seasoned DBA it explains concepts like average value group size, and how the optimizer uses those concepts applied to metadata to make decisions.

Slides are at http://technocation.org/files/doc/2017_05_MySQLindexes.pdf.
Click the slide image below to go to the video at …

In this part of the "quick look" series, I evaluate the performance of DDL operations in Amazon Aurora and vanilla MySQL. I'll use a few examples to demonstrate how fast the DDLs run and what impact they can have on regular workload. I'll also take the Aurora's Fast DDL feature for a spin.

Introduction DDL operations are a well-known source of pain in MySQL-based environments. They are not very fast, they're not fully online (non-blocking), and they're difficult to run on top of regular workload without serious performance degradation. MySQL has not seen much improvement in this area for quite some time, which is we have tools such as pt-online-schema-change, now considered to be an industry standard rather than a workaround.
Amazon Aurora tries to shake things up a little bit with the Fast DDL feature, which (as of this writing) allows you to add a nullable column at the end of a table nearly instantaneously. You can …

In this blog, we will provide answers to the Q & A for the MySQL In the Cloud: Migration, Best Practices, High Availability, Scaling webinar.

First, we want to thank everybody for attending the June 7, 2017 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that we were unable to answer during the webinar:

How does Percona XtraDB cluster work with AWS for MySQL clustering?

…

New MariaDB/MySQL storage engines such as MyRocks and TokuDB have renewed interest in using engines other than InnoDB. This is great, but also presents new challenges. In this article, I will describe work that I am currently finishing, and which addresses one such challenge.

For example, the left bar in the figure shows what happens to MyRocks replication performance when used with a default install where the replication state table uses InnoDB. The middle bar shows the performance improvement from my patch.

Current MariaDB and MySQL replication uses tables to transactionally record the replication state (eg mysql.gtid_slave_pos). When non-InnoDB storage engines are introduced the question becomes: What engine should be used for the replication table? Any choice will penalise other engines heavily by injecting a cross-engine transaction with every replicated change. Unless all tables can be migrated to the …

As promised, here is the follow up of the MySQL 8.0-dev Progress teaser ;-)
so, yes, we expect to see the most hot contentions gone, as it's seen from the following graph : Observations :

• the graph is representing the spin waits / spin rounds events happening during the same Sysbench Update-NoKEY workload
• the load is progressing from 8 concurrent users to 16, 32, .. 512
• 3 engines are tested one after one : MySQL 5.7, MySQL 8.0 (current DMR), MySQL 8.0-dev (current prototype)
• first all 3 engines are running on 22cores-HT only (1 CPU socket)
• then, the second time : on 44cores-HT (2 CPU sockets)
• and the most important thing to retain about this graph is that on 8.0-dev we see all main hot contentions gone ;-)

Read more... (4 …

MySQL 8.0.1 introduces new features and improvements to replication. The next sections summarize some related to performance.

1. WL#9556 – Writeset-based dependency tracking on the master

Transaction writesets can now be used to evaluate which transactions are independent and can be executed in parallel on the binary log applier on the asynchronous slaves.…

Query Reviews part 2: pt-query-digest

One task that can really help reduce future problems is to do a periodic query review. I’ve been using pt-query-digest to do this since 2010 (back when it was part of Ma’atkit, mk-query-digest!), and while I have presented the idea several times at conferences, I have never blogged about it.

I am going to share a secret with you – I blog not just to share information with YOU, but to share information with ME. Future me. This comes up because I am working on a query review at Salesforce.com for one of our busiest shards. I went to go look up a blog post on using pt-query-digest, because it’s a handy cheat sheet….and….I never did it. So, here goes!

This is the first blog post in what will …

Starting on MySQL 8.0.1, a very updated replica server will be more efficient (and probably faster) than previous MySQL versions because of improvements in the relationship between the replication threads. Preliminary testing showed a benefit of up to 65% on Sysbench Update Index.…