We are all familiar with “.frm” files since the earliest days of MySQL, The community has been continuously requesting for replacement of file-system based metadata for several good reasons, So with MySQL 8.0 “.frm” files are gone for ever, Going forward MySQL stores table metadata in the data dictionary tables which uses InnoDB storage engine. This blog is about MySQL 8.0 data dictionary and how it creates value for MySQL going forward:

How file based metadata management used to work in the past (before MySQL 8.0) ? 

• Every table in MySQL will have corresponding .frm file, This .frm file stores information like column names and data-types in the binary format, In addition to the .frm file, there are .trn, .trg and .par files to support triggers, trigger namespace and partitioning .

What are major bottlenecks faced due to the usage of file based metadata management …

In this blog post, we have multiple OLTP performance benchmarking scenarios using sysbench 1.0.14 on InnoDB and MyRocks. InnoDB and MyRocks (RocksDB with MySQL) are definitely not to supplement each other, They actually compliment well with respective advantages, Let me quickly explain how InnoDB and MyRocks can benefit you when used wisely, Again this blog post is not to show who (InnoDB or MyRocks) is better ? We regularly benchmark both of these storage engines before recommending to our customers on what is best suited for their database infrastructure operations ? so we would like to share our thoughts on this post.

How InnoDB and MyRocks are different ?

• MyRocks supports only READ-COMMITTED isolation level, There is no REPEATABLE-READ isolation level like InnoDB so no gap locking like InnoDB, We have written detailed blog on InnoDB transaction isolation levels …

If you attended my latest Troubleshooting MySQL Concurrency Issues with Load Testing Tools webinar you learned how I exploit benchmarking tools to replicate locking issues and crashes. But I told you nothing about proper use of these tools: for reliable benchmarks. I did not tell you that for a reason… I am just a Support Engineer and not a benchmarking expert.

And I have a dream. I want to invite world famous MySQL benchmarking experts for a Percona webinar and ask them for their secrets. I have a pretty long list of my own questions, but in order to make 1-hour webinar productive, we need to concentrate on just a few of the hottest ones. Therefore I need your help.

Please add your questions into the comments field of this blog post. Then we will choose the most important and/or most popular of …

In this blog, I will provide answers to the Q & A for the Troubleshooting MySQL Concurrency Issues with Load Testing Tools webinar.

First, I want to thank everybody for attending my May 23, 2018, webinar on troubleshooting tools for MySQL. The recording and slides for the webinar are available here. Below is the list of your questions that I was unable to answer fully during the webinar.

Q: What do you recommend for benchmarking NDB cluster? Which should be used and how?

A: The issue with benchmarking NDB cluster is not the tool choice, …

We manage several hundreds of MySQL servers, We carefully benchmark and build custom database infrastructure operations for performance, scalability, availability and reliability … But What if we have provision for auto sizing of MySQL system variables innodb_buffer_pool_size, innodb_log_file_sizeand innodb_flush_method ? Actually, These are top 3 system variables we consider tuning for MySQL performance and when we first read about this feature, we got super excited so did some research and decided to write this post:

What was our first reaction, when we first read about innodb_dedicated_server ?

Wow, That will be awesome … Indeed, When we manage several hundreds of MySQL instances, This feature will really improve efficiency and DBA Ops. governance.

Now, Let us explain what we have found:

How does innodb_dedicated_server system variable in MySQL 8.0 size the following …

The UTF-8 is a variable-length encoding.  In the case of UTF-8, it means that storing one code point requires one to four bytes. But, In MySQL’s encoding called “utf8” only stores a maximum of three bytes per code point. In the modern web / mobile applications, we have to support for storing not only language characters but also symbols and emojis, Let me show you below some very weird issues faced using MySQL “utf8” :

mysql> SET NAMES utf8; # just to emphasize that the connection charset is set to `utf8`
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE custfeeds.reactions SET reacted = 'super like ' WHERE id = 13015;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SELECT reactions FROM custfeeds.reactions WHERE id = 13015;
+-------------+
| reactions   |
+-------------+
| super liked |
+-------------+
1 row in set (0.00 sec)


mysql> SHOW WARNINGS; …

This post is about a very simple approach / step-by-step InnoDB log (aka transaction logs)resize, We don’t do this activity regularly but when we have to resize InnoDB log files, there will be a MySQL downtime. This post will be a like a checklist for anyone who want to resize InnoDB log files without any mistakes, We made this task in multiple steps so that you can follow much better:

Step 1 – Check existing logs and their size:

[root@localhost ~]# lsof -c mysqld | grep ib_logfile
mysqld  1018 mysql    5uW     REG              253,0  50331648   180228 /var/lib/mysql/ib_logfile0
mysqld  1018 mysql   11uW     REG              253,0  50331648   180229 /var/lib/mysql/ib_logfile1

Step 2 – Shutdown MySQL

[root@localhost ~]# systemctl stop mysqld 
[root@localhost ~]# systemctl status mysqld 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; …

In this post we will discus about the different ways we can purge binary logs safely in MySQL, We recommend you to confirm before purging the binary logs from the master, all logs were applied to the slaves to avoid halting them. The following error is usual when binary log is purged before being applied on slave:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not open log file’

How can we safely purge MySQL binary log files ? 

1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.
2. Get the binary log files details on the master with SHOW BINARY LOGS.
3. Check for the earliest log file among all the slaves, This is the target file. If all the slaves are up to date, this is the last log file on the list.
4. Make a backup of all log files you are about to delete (We insist …

MySQL 8 supports invisible indexes, This allows you to on-demand enable/disable indexes from being used by MySQL optimizer. Now please don’t get confused with “disabled indexes“, “invisible indexes are not disabled indexes, MYISAM supports disabled indexes, ” , The disabled indexes halt maintenance of an index. Invisible indexes are a new feature in MySQL 8.0 , which mark an index unavailable for use by the optimizer. That means, Index will still be maintained and keep up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses a FORCE INDEX hint) .

Why we really love invisible indexes in MySQL 8.0?

• You want to make only one query to use that index, In this case “invisible index” is a great option
• On-demand indexing, You will have index (up-to-date with data) but you can make it visible or …

In transaction processing systems we have to guarantee the transaction committed will be durable permanently. For example, In an online commerce platform, The completed transaction will remain safe even if the system crashes. This can be achieved by flushing the transactional log records to non-volatile storage devices before acknowledging the commit. MySQL guarantees maximum durability of transaction by optimally setting following system variables :

innodb_doublewrite (enabled by default) 

InnoDB stores all data twice, first to doublewrite buffer (storage area in system tablespace to write pages that are flushed from InnoDB buffer pool, before written in data file). If ever operating system / storage / mysqld process crash during the middle of page write, InnoDB can still find a durable copy of the page from doublewrite buffer for recovery. Though data is written twice,  the doublewrite buffer …