A couple of weeks ago, one of our customers reached us asking about the WARNING messages in their MySQL error log. After a while, there were a few more requests from some other customers asking whether to worry about these messages or not. In this post, I am going to write about the condition at which this WARNING message is written into the log and will explain some of the fundamentals behind the scene.

Look at the warningmber message which appears in the MySQL error log. It says it’s difficult to find a free block in the buffer pool and searched through the pool in a loop for 336 times. This is something weird to imagine; why would it have to go in a loop so many times? Let’s try to understand this.

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (336 search iterations)! 0 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version …

This presentation covers about the basics of Group replication in MySQL 8.0. Group replication world on the basis of Multi Paxos ( a variant of it ). The author covers the leader election algorithm , switching the primary node and replication modes in this presentation.

Group Replication in MySQL 8.0 ( A Walk Through ) from Mydbops

Like to have this high availability MySQL solution configured on your environment. Mydbops Consultants can provide the best solution with right cost for performance.

MySQL has come up with window function in latest GA MySQL 8.0 . It is a major leap in SQL for MySQL. This presentation provides an overview to window function in MySQL 8.0.

Window functions in MySQL 8.0 from Mydbops Window Function in MySQL 8.0

Data security plays a major role in current age. Privacy matters a lot. Data masking is one of the Key Features when comes to security.

• In MySQL community versions if you want to mask your data, You can go with a Maxscale load balancer.
• They introduced a new masking filter on the Maxscale 2.1 version.
• My colleague Prasanth written about column-level data masking. For reference click here Column Level Data Masking.
• In MySQL 8.0 Enterprise they have introduced a new security feature called data masking.
• This feature was introduced in MySQL 8.0.13 Enterprise Edition and it provides data masking and de-identification capabilities.

What is Masking ?

• Transformation of existing data to mask it and remove …

What You May Not Know About Random Number Generation in Sysbench

Sysbench is a well known and largely used tool to perform benchmarking. Originally written by Peter Zaitsev in early 2000, it has become a de facto standard when performing testing and benchmarking. Nowadays it is maintained by Alexey Kopytov and can be found in Github at https://github.com/akopytov/sysbench.

What I have noticed though, is that while widely-used, some aspects of sysbench are not really familiar to many. For instance, the easy way to expand/modify the MySQL tests is using the lua extension, or the embedded way it handles the random number generation. 

Why This Article? 

I wrote this article with the intent to show how easy it can be to customize sysbench to make it what you need. There are many different ways to extend sysbench use, and one of these is …

This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of  3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.

We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.

In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.

Going forward will look into the detailed analysis made to tackle the issue of OOM.

DB Environment:-

• Service – Group Replication Cluster
• Cluster Nodes – 3
• GR mode – Single Primary …

Sometimes, you may experience “ERROR 1034: Incorrect key file” while running the ALTER TABLE or CREATE INDEX command:

mysql> alter table ontime add key(FlightDate);
ERROR 1034 (HY000): Incorrect key file for table 'ontime'; try to repair it

As the error message mentions key file, it is reasonable to assume we’re dealing with the MyISAM storage engine (the legacy storage engine which used to have such a thing), but no, we can clearly see this table is InnoDB!

When the error message in MySQL is confusing or otherwise unhelpful, it is a good idea to check the MySQL error log:

2019-02-24T02:02:26.100600Z 9 [Warning] [MY-012637] [InnoDB] 1048576 bytes should have been written. Only 696320 bytes written. Retrying for the remaining bytes.
2019-02-24T02:02:26.100884Z 9 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2019-02-24T02:02:26.100894Z 9 [ERROR] [MY-012639] [InnoDB] Write to file (merge) …

In this post I’ll expand on the subject of my MySQL pre-FOSDEM talk: what dbadmins need to know and do, when upgrading from MySQL 5.7 to 8.0.

I’ve already published two posts on two specific issues; in this article, I’ll give the complete picture.

As usual, I’ll use this post to introduce tooling concepts that may be useful in generic system administration.

The presentation code is hosted on a GitHub repository (including the …

As a part of Mydbops Consulting we have a below problem statement from one of our client.

“We have a high powered server for reporting which in turn powers our internal dashboard for viewing the logistics status.Even with a high end hardware, we had a heavy CPU usage and which in turn triggers spikes in replication lag and slowness. Below is the hardware configuration.“

OS : Debian 9 (Stretch)

CPU : 40

RAM : 220G (Usable)

Disk : 3T SSD with 80K sustained IOPS.

MySQL : 5.6.43-84.3-log Percona Server (GPL)

Datasize : 2.2TB

Below is the graph on CPU utilisation from Grafana.

Since the work load is purely reporting(OLAP) we could observe a similar type of queries with different ranges. Below is the Execution plan of the query. It is a join query over 6 tables.

Explain Plan:

 …

A few days ago I wrote about how grossly outdated statistics returned through MySQL’s Information_Schema can be. In that post, Øystein Grøvlen suggested taking a look at mysql.innodb_table_stats and mysql.innodb_index_stats as a better source of information. Let’s do just that!

Let’s start with the good news. Unlike MySQL Data Dictionary Tables (mysql.table_stats, etc),  mysql.innodb_table_stats and mysql.innodb_index_stats can be queried by the user. They also contain a lot of interesting statistics. Before we get to that though, let’s examine where those tables come from in more detail.

As the manual tells us, these tables are storing …