In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.

Introduction

Starting in version 5.7.17-29.20 of Percona XtraDB Cluster significantly improved performance. Depending on the workload, the increase in throughput is in the range of 3-10x. (More details here). These optimization fixes also helped improve IST (Incremental State Transfer) performance. This blog is aimed at studying the IST impact.

IST

IST stands for incremental state transfer. When a node of the cluster leaves the cluster for a short period of time and then rejoins the cluster it needs to catch-up with cluster state. As part of this sync …

Does it matter how many indexes I create?

A general rule of thumb is that the more indexes you have on a table, the slower INSERT, UPDATE, and DELETE operations will be.

Indexes in MySQL (or any database for that matter) are not static. Every time we update the table (for example, using an INSERT query), the relevant indexes are updated by MySQL. Otherwise, they will be useless in the next search query that will need them.

Therefore, adding indexes shouldn’t be taken lightly, as it’s actually a performance trade off which must be balanced properly. The more indexes you add on a set of columns / table, the slower INSERT, UPDATE and DELETE statements will be. On the other hand, search operations will be optimized using those indexes.

This article will not describe the methodologies of choosing the correct indexes, but will teach you how to find and remove redundant indexes from your MySQL database.

How …

Next up in the "quick look" series is a discussion of connection management best practices and the memory usage implications of idle connections in Aurora and MySQL. I'll also throw in some notes on how to configure your connection pools to avoid unpleasant surprises.

Thread handling in Aurora vs MySQL Community MySQL editions use "one-thread-per-connection" approach to thread handling. It means that each individual user connection receives a dedicated OS thread within the mysqld process. This comes with issues, such as: 

• Relatively high memory usage with large number of user connections, even if the connections are completely idle.
• Higher internal server contention and context switching overhead when working with thousands of user connections.

To avoid such issues, some servers support a thread pool approach. Examples include Percona Server and Amazon Aurora.

…

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 …