The first release candidate of MySQL 8.0 is here, and I'm happy to share few performance stories about. This article will be about the "most simple" one -- our in-memory Read-Only performance ;-))
However, the used test workload was here for double reasons :

• 1) validate MySQL 8.0 performance
• 2) fully evaluate the "New" Sysbench developed by Alex (https://github.com/akopytov/sysbench)

Going ahead to the second point, the main worry about New Sysbench was about its LUA overhead (the previous version 0.5 was running slower than the old one 0.4 due LUA) -- a long story short, I can confirm now that the New Sysbench is running as fast as the oldest "most lightweight" Sysbench binary I have in use ! so, KUDOS Alex !!! ;-)) …

Comparing Oracle MySQL Group Replication and Galera Cluster through a probability perpective seems quite interesting. At commit time both use a group certification process that requires network round trips. The required time for these network roundtrips is what will mainly determined the cost of a transaction. Let us try to compute an estimate of the ...continue reading "Probability perspective on MySQL Group replication and Galera Cluster"

Want to learn or refresh yourself on MySQL? MySQL Marinate is the FREE virtual self-study group is for you!

MySQL Marinate quick links if you know what it is all about.

This is for beginners – If you have no experience with MySQL, or if you are a developer that wants to learn how to administer MySQL, or an administrator that wants to learn how to query MySQL, this course is what you want. If you are not a beginner, you will likely still learn some nuances, and it will be easy and fast to do. If you have absolutely zero experience with MySQL, this is perfect for you. The first few chapters walk you through getting and installing MySQL, so all you need is a computer and the book.

The format of a virtual self-study group is as follows:
Each participant acquires the same textbook ( …

The website or mobile app is the storefront for participating in the modern digital era. It’s your portal for inviting users to come and survey your products and services. Much attention focuses on front-end development; this is where the HMTL5, CSS, and JavaScript are coded to develop the landing page that everyone sees when they visit your site.

But the real magic happens on the backend. This is the ecosystem that really powers your website. One writer has articulated this point very nicely as follows:

The technology and programming that “power” a site—what your end user doesn’t see but what makes the site run—is called the back end. Consisting of the server, the database, and the server-side applications, it’s the behind-the-scenes functionality—the brain of a site. …

We all know that if we add a MySQL index to speed up a read, we end up making writes slower. How often do we do the analysis to look at how much more work is done?

Recently, a developer came to me and wanted to add an index to a very large table (hundreds of gigabytes) to speed up a query. We did some testing on a moderately used server:

Set long_query_time to 0 and turn slow query logging on
Turn slow query logging off after 30 minutes.

Add the index (was on a single field)

Repeat the slow query logging for 30 minutes at a similar time frame (in our case, we did middle of the day usage on a Tuesday and Wednesday, when the database is heavily used).

Then I looked at the write analysis – there were no DELETEs, no UPDATEs that updated the indexed field, and no UPDATEs that used the indexed field in the filtering. There were only INSERTs, and with the help of pt-query-digest, here’s what I found: …

Duration: 30 min

Level: Intermediate

In this tutorial we are going to install and experiment with Dynimize using MySQL running the Sysbench OLTP benchmark. We also play around with the Linux perf command, top and vmstat. This tutorial assumes that you have MySQL and the Linux perf tool installed, and that there are no other CPU intensive workloads on the system other than those being tested. In order for the Linux perf tool to report CPU event counts, this tutorial should be completed on either a bare metal Linux server, or if using a virtual machine guest then virtual PMU support must be enabled by the hypervisor.

The initial part of this tutorial is meant to illustrate how to determine if there is potential for Dynimize to speedup a mysql (or any other program) workload, by checking to …

Queries with LIMITs and OFFSETs are common in application that require pagination and in some cases might work well for a while.
In many cases though, they become slow and painful once the OFFSET has a high value.

Why OFFSET is so slow?

Well, in most cases, low offset queries are not slow. The problem starts with high OFFSET values.
If your query is using the following limit clause: “LIMIT 50000, 20”, it’s actually requesting the database to go through 50,020 rows and throw away the first 50,000. This action can have a high cost an impact response time.

You may ask yourself “who the heck is going to skip to page 50,000 in my application?”.
Let’s list few possible use cases:

• Your favorite search engine (Google / Bing / Yahoo / DuckDuckGo / whatever) is about to index your ecommerce website. You have about 100,000 pages in that website. How will your application react when the …

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.

…