The latest from the MySQL community

In the previous article I've intentionally skipped the topic related to Binlog impact on MySQL 8.0 Performance, because it's not a short story, nor a simple one..
In fact, for most of people Binlog in MySQL is generally representing and additional overhead, and historically it was true. Since MySQL 5.6 there is Binlog Group Commit (BGC) feature available, and it was rather doing well, decreasing the gap between "binlog=OFF" and "binlog=ON sync_bin=1". However, storage vendors are making flash drives more and more better from year to year.. And when we delivered MySQL 5.7 the scope of Binlog impact moved with code and flash improvements -- the main impact was no more coming from the I/O operations …

This post is following previously published OLTP_RO results for MySQL 8.0 ( latin1 and utf8mb4 charsets), and now is focusing on Sysbench RW workloads, particularly "mixed" OLTP_RW and Update-NoKey :

• OLTP_RW : while this workload has writes, it's mainly driven by reads (OLTP_RO + 2 updates + delete + insert)
• Update-NoKey : aggressively bombarding UPDATE queries (but with no changes on indexed columns)

The same 2S Skylake server was used as in previous tests :
Server configuration :

• OS : Oracle Linux 7.4
• CPU : 48cores-HT Intel Skylake 2.7Ghz (2CPU sockets (2S), Intel(R) Xeon(R) Platinum 8168 CPU)
…

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 …

I want to take a closer look at MySQL performance with binary logs enabled on different filesystems, especially as MySQL 8.0 comes with binary logs enabled by default.

As part of my benchmarks of the MyRocks storage engine, I’ve noticed an unusual variance in throughput for the InnoDB storage engine, even though we spent a lot of time making it as stable as possible in Percona Server for MySQL. In the end, the culprit was enabled binary logs. There is also always the question, “If there is a problem with EXT4, does XFS perform differently?” To answer that, I will repeat the same benchmark on the EXT4 and XFS filesystems.

You can find our previous experiments with binary logs here: …

One of the big features of MySQL 8.0 is geography support. MySQL now has a catalog of spatial reference systems (SRSs), of which almost 500 are geographic. Most functions also support geographic computations. What about indexes?

MySQL 8.0 comes with InnoDB spatial indexes for geographic data.…

Percona announces the GA release of Percona Server for MySQL 5.7.21-21 on on April 24, 2018. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

This version of Percona Server for MySQL 5.7.21 includes three new encryption features – Vault keyring plug-in, encryption for InnoDB general tablespaces, and encryption for binary log files.

These new capabilities, which allow companies to immediately …

MySQL 8.0 has a rich set of features. One of the feature which interests DBA’s more is invisible index

What is an index in MySQL ?

• The indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

What is invisible index ?

• The invisible index is ability to mark an index as unavailable for use by optimizer.
• In MySQL 5.7 and other previous versions the indexes are visible by a default.
• To control the index visibility for a new index ,use a  visible or invisible key words as a part of the index creation.

How to add a invisible index on existing table ?

Syntax :

It is a great today for whole MySQL community: MySQL 8.0 was just released as GA!

Geir Høydalsvik has a great summary in his “What’s New in MySQL 8.0” blog post. You can find additional information about MySQL 8.0 Replication and MySQL 8.0 Document Store that is also worth reading.

If you can’t wait to upgrade to MySQL 8.0, please make sure to read the  …

Due to continuous development and improvement, Percona Server for MySQL incorporates a number of improvements related to binary log handling and replication. This results in replication specifics, distinguishing it from MySQL Server.

Temporary tables and mixed logging format Summary of the fix:

As soon as some statement involving temporary tables was met when using a mixed binlog format, MySQL switched to row-based logging for all statements until the end of the session (or until all temporary tables used in the session were dropped). This is inconvenient when you have long-lasting connections, including replication-related ones. Percona Server for MySQL fixes the situation by switching between …

To change the number or the size of your InnoDB redo log files, please follow the steps below:

Step 1: Stop the MySQL server and confirm that it has shutdown without any errors

Step 2: Edit my.cnf to configure innodb_log_file_size (log file size) and innodb_log_files_in_group (number of log files)

Step 3: Restart MySQL server

If ever InnoDB finds innodb_log_file_size differs from the redo log files, It writes a log checkpoint, closes and removes the old log files, create again new log files at requested size and opens the new log files.

The post How to change the number or size of InnoDB Redo Log Files ? appeared first on MySQL Consulting, …