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 …

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 …

All our customers are on Linux, They have multiple flavors of Linux actually – Ubuntu, CentOS, RedHat Linux, Oracle Linux, SUSE Linux etc. Though we are an full-service everything MySQL shop, Our consulting, support and managed services are never restricted to only MySQL Ops. , We are experts in Linux,  DevOps and Site Reliability Engineering (SRE). We have proven methods to deliver Linux performance audit / health check / diagnostics and recommendations. What are the tools we use for monitoring Linux ops. ? This post is about those tools we use regularly in MinervaDB for monitoring Linux operations:

How long Linux server is up and running ? 

[root@localhost ~]# uptime 
 12:32:56 up 1800 min,  83 users,  load average: 88.01, 88.52, 88.64
[root@localhost ~]#

Print all the processes running as root

[root@localhost ~]# ps -U root -u root 
  PID TTY          TIME CMD
    1 ? …

We often enable MySQL slow_query_log to proactively troubleshoot the performance, There is so lot of information with-in slow_query_log which is enough to address most common (even complex ones occasionally) MySQL performance bottlenecks. What if we haven’t planned for the log lifecycle management of slow query log ? Your log will really grow huge (we keep long_query_time as-low-as 1 second), So we have to plan for slow_query_log rotating to avoid MySQL outage due to “no space left in the disk” to grow further. There are two ways you can do log rotate, I have explained below:

1. Manual log rotating (We use it only when there is an emergency)
2. Using logrotate to automatic MySQL log rotation (We love it, MinervaDB is an big fan of MySQL automation & DevOps. but we also recommend to use it very carefully)

Step-by-step manual log rotating …