At work, replication

is a central feature in our MySQL Standard Architecture.

But until MySQL 5.6, replication was strictly sequential: Even if transactions happened in parallel on a primary, they would be downloaded to the replica by the IO_THREAD into the relay log. From there, a single SQL_THREAD would apply them, one after the other in strict binlog order. That can lead to Replication Delay.

We had a monitor for that, courtesy of Dennis Kaarsemaker

. That code looked at the time consumption in the SQL_THREAD, and counted the percentage of idle time over time, visualizing it in Graphite or Grafana.

This was the amount of runway we had. If the write-load to a specific replication hierarchy threatened to …

As a developer using Python, I want to be able to hand a list to an SQL statement with a WHERE id IN (…) clause, and it should do the right thing.

Well, that is not how it started, because it was asked on the internal no-work-channel, so it kind of escalated more.

A question

The original question was:

Dev> Why is it 2021, and SQL prepared statements still can’t deal with IN? Or have I missed some exciting development?

After a quick detour through Java (which we won’t discuss any further in this article), we established that this was a Python problem in this particular instance. And we touched on several other interesting things on our way.

But first, the solution:

#! /usr/bin/env python3

import click
import MySQLdb
import MySQLdb.cursors

class …

As a developer using Python, I want to be able to hand a list to an SQL statement with a WHERE id IN (…) clause, and it should do the right thing.

Well, that is not how it started, because it was asked on the internal no-work-channel, so it kind of escalated more.

A question

The original question was:

Dev> Why is it 2021, and SQL prepared statements still can’t deal with IN? Or have I missed some exciting development?

After a quick detour through Java (which we won’t discuss any further in this article), we established that this was a Python problem in this particular instance. And we touched on several other interesting things on our way.

But first, the solution:

#! /usr/bin/env python3

import click
import MySQLdb
import MySQLdb.cursors

class DebugCursor(MySQLdb.cursors.DictCursor):
    def _query(self, q):
        print(f"Debug: {q}") …

     Histograms were introduced with MySQL 8.0 and are a valuable way of speeding up queries.  The MySQL optimizer assumes that data in a column has evenly distributed values. Even distribution of data probably does not reflect much of the data sitting right now in your database.  

    The optimizer wants to find the most efficient way to return the data requested in a query.  If it has poor information on that data, then the optimizer will make a 'guesstimate' that will will result in a query plan that will not perform well.  But if the optimizer has good information, in this case provided by a histogram, then it can produce a better query plan.

    In the following example a able is filled with data that is not evenly distributed.  In the histogram image following, the data is represented in what looks like a rollercoaster side view. 
…

I use CodeIgniter 4 a great deal for learning, personal projects, and application development. In this post, I cover 2 different methods you can use and retrieve the MySQL Last Insert ID value after executing an INSERT statement in CodeIgniter. Continue reading for more information…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Model Setup

Starting out, I use the CLI Generator feature and create a Model by executing this code in the terminal in the project root:

php spark make:model user --suffix

I have removed much of the …

Recently, AWS presented its own CPU on ARM architecture for server solutions.

It was Graviton. As a result, they update some lines of their EC2 instances with new postfix “g” (e.g. m6g.small, r5g.nano, etc.). In their review and presentation, AWS showed impressive results that it is faster in some benchmarks up to 20 percent. On the other hand, some reviewers said that Graviton does not show any significant results and, in some cases, showed fewer performance results than Intel.

We decided to investigate it and do our research regarding Graviton performance, comparing it with other CPUs (Intel and AMD) directly for MySQL.

Disclaimer

1. The test is designed to be CPU bound only, so we will use a read-only test and make sure there is no I/O activity during the test.
2. Tests were run  on m5.* (Intel) , m5a.* (AMD),  m6g.*(Graviton) EC2 instances in the US-EAST-1 region. (List of EC2 see …

Why

Marco, why did you write this long article?

Yes, it is long, and I know most of the people will not read it in full, but my hope is that at least someone will, and I count on them to make the wave of sanity. 

Why I wrote it is simple. We write articles to share something we discover, or to share new approaches or as in this case to try to demystify and put in the right perspective the “last shining thing” that will save the world.  

The “last shining thing” is the use of containers for relational databases management systems (RDBMS) and all the attached solutions like Kubernetes or similar. 

Why is this a problem? The use of containers for RDBMS is not really a problem per se, but it had become a problem because it was not correctly contextualized and even more important, the model that should be used to properly design the solutions, was not reviewed and modified in …

Why

Marco, why did you write this long article?

Yes, it is long, and I know most of the people will not read it in full, but my hope is that at least someone will, and I count on them to make the wave of sanity. 

Why I wrote it is simple. We write articles to share something we discover, or to share new approaches or as in this case to try to demystify and put in the right perspective the “last shining thing” that will save the world.  

The “last shining thing” is the use of containers for relational databases management systems (RDBMS) and all the attached solutions like Kubernetes or similar. 

Why is this a problem? The use of containers for RDBMS is not really a problem per se, but it had become a problem because it was not correctly contextualized and even more important, the model that should be used to properly design the solutions, was not reviewed and modified in …

ProxySQL 2.3.0 was recently released and when I was reading the release notes, I was really impressed with the Group Replication enhancements and features. I thought of experimenting with those things and was interested to write a blog about them. Here, I have focused on the following two topics:

• When the replication lag threshold is reached, ProxySQL will move the server to SHUNNED state, instead of moving them to OFFLINE hostgroup. When shunning a server, it will be performed gracefully and not immediately drop all backend connections.
• The servers can be taken to maintenance through ProxySQL using “OFFLINE_SOFT”.

Test Environment

To test this, I have configured a three-node GR cluster (gr1,gr2,gr3) in my local environment. I have configured a single primary cluster (1 writer, 2 readers).

mysql> select member_host,member_state,member_role,member_version from …

When the time comes that you no longer have a need for specific data, use DELETE to remove it completely from your table. CodeIgniter 4 Models have a delete() method that is super-easy to use. Continue reading for more information…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

CodeIgniter 4 CRUD Series with MySQL

I have written a now complete 4-part series over on my Medium account about CodeIgniter 4 Model CRUD operations. Feel free and visit any of the previous posts in the series to get up to speed:

• …