Showing entries 1 to 10 of 1147
10 Older Entries »
Displaying posts with tag: innodb (reset)
Preview of the upcoming new features in MySQL 8.0.17

Note: I am no longer working for Oracle/MySQL. This post is based on public information.

The MySQL 8.0.17 release is around the corner. Let's take a look at the new features in the upcoming release.

InnoDB redo log archive

InnoDB added new parameter “--innodb-redo-log-archive-dirs” in 8.0.17, according to the doc page. The implementation of both page archive and redo log archive has been in the code for a while, under arch directory. This feature can be used by backup and database clone.


Multi-Valued Indexes

As of MySQL 8.0.17, InnoDB supports multi-valued indexes. A multi-valued index is a secondary index defined on a column that stores an array of values. A “normal” index has one index record for each data record (1:1). A multi-valued index …

[Read more]
InnoDB ALTER TABLE ADD INDEX and INSERT performance

In my previous blog post, I explained the internals of the sorted index build process. The blog ended with saying “there is one disadvantage.”

Beginning in MySQL 5.6, many DDLs including ALTER TABLE ADD INDEX became “ONLINE”. Meaning, when the ALTER is in progress, there can be concurrent SELECTS and DMLs. See the MySQL documentation for online DDL. From the documentation, we can see that ALTER TABLE ADD INDEX DDL permits concurrent DML.

The main disadvantage with Sorted Index Builds introduced in 5.7 is the reduced insert performance when ALTER is in progress. In this blog, we specifically talk about single thread insert performance on a table with ALTER ADD INDEX in progress.

If the table …

[Read more]
Indexes bad practices

When creating indexes, some common mistakes should be avoided.

The post Indexes bad practices appeared first on Federico Razzoli.

MySQL Support Engineer's Chronicles, Issue #10

As promised, I am trying to write one blog post in this series per week. So, even though writing about InnoDB row formats took a lot of time and efforts this weekend, I still plan to summarize my findings, questions, discussions, bugs and links I've collected over this week.

I've shared two links this week on Facebook that got a lot of comments (unlike links to my typical blog posts). The first one was to Marko Mäkelä's blog post at MariaDB.com, "

[Read more]
On Importing InnoDB Tablespaces and Row Formats

Let me start with a short summary and then proceed with a long story, code snippets, hexdumps, links and awk functions converted from the source code of MariaDB server. This blog post can be summarized as follows:

  • One can find row_format used to create table explicitly in the .frm file (or the outputs of SHOW CREATE TABLE or SHOW TABLE STATUS). Internals manual may help to find out where is it stored and source code reading helps to find the way to interpret the values.
  • For InnoDB tables created without specifying the row_format explicitly neither logical backup nor .frm file itself contains the information about the row format used. There are 4 of them (Redundant, Compact, Dynamic and Compressed). The one used implicitly is defined by current value of the …
[Read more]
Primary Key in InnoDB: how they work and best practices

How primary key are implemented in InnoDB, and how to build fast primary keys.

The post Primary Key in InnoDB: how they work and best practices appeared first on Federico Razzoli.

Innotop – A Monitoring tool for MySQL

Monitoring MySQL server has never been an easy task. Monitoring also needs to go through many Complex and difficult queries to get the details.

All these problems can be overcome by an excellent command line monitoring tool  called “Innotop”. Innotop comes with many features and different types of modes/options, which helps to monitor different aspects of MySQL  (InnoDB) performance and also helps database administrator to find out what’s wrong going with MySQL server. Innotop helps in monitoring user statistics, mysql replication status,query list, InnoDB I/O informations etc. Another important thing about innotop is it refreshes the data continuously , so we can view realtime statistics.

Innotop is one of the designed based on top utility for linux.

[Read more]
MySQL Support Engineer's Chronicles, Issue #9

My previous post from this series was published more than 1.5 years ago. I had never planned to stop writing about my everyday work on a regular basis, but sometimes it's not easy to pick up something really interesting for wider MySQL audience and when in doubts I always prefer to write about MySQL bugs...

In any case, any long way starts from the first step, so I decided to write one post in this series per week and try to summarize in it whatever findings, questions, discussions, bugs and links I've collected over the week. My work experience differs week after week, so some of these posts may be boring or less useful, but I still want to try to create them on a regular basis.

I was working on (upcoming) blog post (inspired by one customer issue) on …

[Read more]
Hot Backup For MyRocks(Rocksdb) using Percona Xtrabackup

Xtrabackup now supports Hotbackup for Myrocks!! yes you heard me right, this is one of the most awaited features with xtrabackup. With the latest release of percona xtrabackup 8.0.6 this is enabled and is supported only for Percona Server version 8.0.15-6 or higher, you can see detailed released notes here.

Myrocks is getting much of the attention now because of its much improved write capabilities and compression. We have also planned to have detailed blog on Myrocks features and limitations.

We shall proceed to test the backup and restore of Myrocks

Environment:

OS : Debian GNU/Linux 9 …
[Read more]
MySQL InnoDB Sorted Index Builds

It’s not essential to understand how MySQL® and Percona Server for MySQL build indexes. However, if you have an understanding of the processing, it could help when you want to reserve an appropriate amount of space for data inserts. From MySQL 5.7, developers changed the way they built secondary indexes for InnoDB, applying a bottom-up rather than the top-down approach used in earlier releases. In this post, I’ll walk through an example to show how an InnoDB index is built. At the end, I’ll explain how you can use this understanding to set an appropriate value for innodb_fill_factor.

Index building process

To build an index on a table with existing data, there are the following phases in InnoDB

  1. Read phase (read from clustered index and build secondary index entries)
  2. Merge sort phase
[Read more]
Showing entries 1 to 10 of 1147
10 Older Entries »