MySQL 8.0 Performance Benchmarking on Amazon EC2 The scope of performance benchmarking

The core objective of this benchmarking exercise is to measure MySQL 8.0 performance, This include INSERTs , SELECTs and complex transaction processing (both INSERTs and SELECTs) without any tuning of MySQL 8 instance’s my.cnf. We agree tuning my.cnf will greatly improve performance but in this activity we wanted to benchmark MySQL 8 transaction processing capabilities and technically in MinervaDB we measure performance by Response Time and believe you can build high performance MySQL applications by writing optimal SQL. We have used Sysbench (https://github.com/MinervaDB/MinervaDB-Sysbench release 1.0.20) for this benchmarking activity. This is not a paid / sponsored benchmarking effort by any of the software or hardware vendors, We will remain …

Recently one of our customers wanted us to benchmark InnoDB, TokuDB and RocksDB on Intel(R) Xeon(R) Gold 6140 CPU (with 72 CPUs),  nvme SSD (7 TB) and  530 GB RAM for performance. We have used Ubuntu xenial 16.04.4, Percona Server 5.7 (included storage engines- InnoDB/XtraDB, TokuDB and RocksDB) and  Sysbench 1.0.15 with custom Lua scripts for this exercise, This benchmarking exercise included bulk INSERTS, WRITES, READS and READS-WRITES. We have tried our best to capture maximum information about the hardware infrastructure and copied / shared scripts we have used for benchmarking. This is not a paid / sponsored benchmarking effort by any of the software or hardware vendors, We will remain forever an vendor neutral and independent web-scale database infrastructure operations company with core expertise in performance, scalability, high availability and database reliability engineering. This benchmarking is …

When you are building Database Infrastructure for an data sensitive business (like financial services, digital commerce, advertising media solutions, healthcare etc. ) governed by compliance and policies, You are expected to maintain the audit log of the transactions to investigate, if you ever suspect something unacceptable (i.e., user updating / deleting data) happening to your database . MariaDB provides Audit Plugin (MariaDB started including by default the Audit Plugin from versions 10.0.10 and 5.5.37, and it can be installed in any version from MariaDB 5.5.20.) to log the server activity, Although the MariaDB Audit Plugin has some unique features available only for MariaDB, it can be used also with MySQL. MariaDB Audit Plugin log the details like who connected to server (i.e., username and host), what queries were executed, the tables accessed and server variables changed. This information is retained in a rotating log file or sent to …

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 …

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; …

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 …