One of our customers recently had a Corrupted Index that affected the health of his database and caused latency for his customers. To identify and handle a corrupted index in MySQL, here are some common FAQs:

What is a MySQL corrupted index?

A MySQL corrupted index refers to a situation where the index data within a MySQL database becomes inconsistent or damaged, resulting in incorrect or incomplete query results. It can occur due to various reasons, such as hardware failures, software bugs, improper shutdowns, or disk errors.

What are the common symptoms of a MySQL corrupted index?

When a MySQL index is corrupted, you may experience several symptoms, such as slow query performance, incorrect or missing query results, frequent crashes or errors during database operations, or unusually high CPU or disk usage.

What are the common error messages related to MYSQL Index Corruption?

You can receive …

The challenge If you have a large MySQL table and you're trying to add a new column and index it, you may have ran into this error:

Creating index 'Name' required more than 'innodb_online_alter_log_max_size' bytes 
of modification log. Please try again.

So how can you address it?

The solution

By default, recent MySQL versions will execute the ALTER statement with the INPLACE flag (unless it's eligible for INSTANT alter, though that's not always the case). this means that the database is using a temporary log (size of innodb_online_alter_log_max_size) which is by default 128MB in most recent versions, to keep track of DML changes happening during the ALTER command. So if the database is executing an UPDATE/INSERT/DELETE during the ALTER, it will keep track of those changes in the temp file, and then after the ALTER is done, it will apply those changes to the table as well.
So you have several options we can …

At some points, many of our customers need to handle insertions of large data sets and run into slow insert statements. This article will try to give some guidance on how to speed up slow INSERT SQL queries.

The following recommendations may help optimize your data loading operations:

1. Remove existing indexes - Inserting data to a MySQL table will slow down once you add more and more indexes. Therefore, if you're loading data to a new table, it's best to load it to a table without any indexes, and only then create the indexes, once the data was loaded.

   When you're inserting records, the database needs to update the indexes on every insert, which is costly in terms of performance. It's much faster to insert all records without indexing them, and then create the indexes once for the entire table.

…

So you thought your database is in good shape, but suddenly your CPU usage spiked and reached 100% at 2AM. How would you go about tracking the root cause and fix it?

1. Locate the root cause

If you are using CloudWatch or any infrastructure monitoring solution, you can start your investigation there.
To understand what happened in your database, you should enable the slow query log feature on your database. This will capture all the slow log queries into a log, based on a threshold that you define.
If you need, we created a step-by-step guide on how to enable the slow query log.

Once you enabled it, you can use slowquerylog.com to …

In this post, we will quickly demonstrate how to enable Slow Query Log on Google Cloud SQL for MySQL.

1. Open Cloud SQL Dashboard
2. Click Edit on your Database
3. For Cloud SQL MySQL instances, add these 3 flags (a.k.a. configuration parameters) to your instance:
   • log_output - file / table / none
     We recommend to choose file, which will help you to quickly visualize and analyze your slow query using EverSQL
   • slow_query_log - On/Off
   • long_query_time - number, in seconds.
     For example, 1 means that the log will capture all queries that are longer than 1 …

Right-sizing database instances is the process of adjusting your database instances’ size to match the workload generated by the application. In most cases, the incentive to right-size your database instances will be to lower the cloud infrastructure’s costs, ideally without compromising on performance.

In this post we’ll guide you through how to analyze the instance’s used resources, in an effort to identify opportunities to save on your RDS costs.

How to identify memory related down-sizing opportunities

MySQL has a lot of “moving parts” which may contribute to the amount of memory it needs to operate efficiently. Just to list a few of the most impactful factors: fixed-size buffers (query cache, innodb buffer pool size), the database’s workload (query connections, query buffers), replication internals (replication connections, binary log caches) and more.

When looking for down-sizing opportunities, …

From time to time, you might notice that your MySQL database suddenly under-performs. To quickly locate the root cause, one might start digging into MySQL slow query logs, trying to locate the query(ies) taking up the server's resources.

Those slow log files contain lots of metrics for each query execution, which can be overwhelming, especially if you have a busy application with lots of queries constantly being executed.

There are several command line tools out there, doing a fantastic job in analyzing those slow log files and summarizing them (examples: pt-query-digest, mysqldumpslow), so if you never used them, you should definitely check them out.

If you're looking for a more visual way to look at those slow logs, another option …

I recently stumbled upon a very interesting post by Lukas Eder, where he describes 10 query transformations which do not depend on the database’s cost model. He posted it a couple of years ago, though when I read it, I assumed some portions of it may still be relevant today.

In the original post, several databases were tested to see if their internal optimizer will be able to automatically re-write the SQL queries and optimize them. In those tests, MySQL under-performed in several of the use cases (the tested version was MySQL 8.0.2, which was released on 2017-07-17).

Seeing those results, and given the previous evaluation was done almost two years ago, I thought that now can be a good chance to re-evaluate a few of those tests with the latest MySQL 8.0.16 (released on 2019-04-25), and demonstrate …