MySQL has multiple timeout variables these slides helps to give an overview of the different  timeout variables and their purposes briefly.

Generally TPC-C benchmark workload is considered as one of the #1 references for Database OLTP Performance. On the same time, for MySQL users it's often not something which is seen as "the most compelling" for performance evaluations.. -- well, when you're still fighting to scale with your own very simple queries, any good result on something more complex may only look as "fake" ;-)) So, since a long time Sysbench workloads remained (and will remain) as the main #1 "entry ticket" for MySQL evaluation -- the most simple to install, to use, and to point on some sensible issues (if any). Specially that since new Sysbench version 1.0 a lot of improvements were made in Sysbench code itself, it really scales now, has the lowest ever overhead, and also allowing you to add your own test scenario via extended LUA scripts (and again, with lowest ever overhead) -- so, anyone can easily add …

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

Historically, Random I/O Reads were always a major PITA for any OLTP workload.. If Random I/O Writes you could yet "delay" via controller's caches (or any kind of other battery-protected caches -- specially if Writes are coming in bursts), there is no way to "predict" I/O Reads if they are fully Random (so you cannot "cache" or "prefetch" them ahead and have to deliver the data directly from storage, read by read.. -- which is hitting a huge "rotation penalty" on HDD).
Indeed, things changed dramatically since arriving of Flash Storage. You don't need to spend any particular attention if your I/O Reads are Random or Sequential. However, you still need to keep in mind to not hit the overall throughout limit of your Flash Device. As the result, reading by smaller I/O blocks allowing you to do more I/O operations/sec than with bigger blocks. And what about InnoDB ? -- InnoDB is using by default 16KB page size (so by default all Random I/O …

Encryption is a very important form of security and It’s becoming a standard from a compliance perspective to ensure PCI, PII and HIPPA compliances. Encryption needs to be performed for Data at Rest, Data over Wire.

Data At Rest:

• Until 5.6, To encrypt the MySQL data files, Only disk level encryption possible (Gazzang, Luks)
• From 5.7.11 MySQL added a feature for tablespace encryption. Which encrypts only the …

Instant DDL has been one of the most requested InnoDB features for a very long time. With ever larger and rapidly growing datasets the ability to do DDL instantly is a must have feature in any web scale database.  Developers constantly need to add new columns to meet the constantly changing business requirements.  …

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