In this post, we will explore one approach to MySQL high availability with ProxySQL, Consul and Orchestrator.

This is a follow up to my previous post about a similar architecture but using HAProxy instead. I’ve re-used some of the content from that post so that you don’t have to go read through that one, and have everything you need in here.

Let’s briefly go over each piece of the puzzle:

– ProxySQL is in charge of connecting the application to the appropriate backend (reader or writer).

It can be installed on each application server directly or we can have an intermediate connection layer with one or more ProxySQL servers. The former probably makes sense if you have a small number of application servers; as the number grows, the latter option becomes more attractive. Another scenario for the …

To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables:

mysql> create database mydata;
mysql> use mydata
mysql> create table table1 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));
mysql> create table table2 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));

Now, let’s create a user :

mysql> create user myuser identified by 'mypassword';

And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user:

• myrole1: user has access to both tables in their entirety, reads and writes
• myrole2: user has access only to `table2`, reads and writes
• myrole3: user has only access to the column `name`of `table1` and …

In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of  Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools.

Below tools are used for speed comparison of Backup and Recovery,

1. Clone-Plugin
2. Xtrabackup
3. mysqldump
4. mydumper with myloader
5. mysqlpump

Test …

For a long time, the only algorithm for executing a join in MySQL has been variations of the nested loop algorithm. With the release of MySQL 8.0.18, the server can now execute joins using hash join. This blog post will have a look at how it works, when it is used, and how it compares to the old join algorithms in MySQL in terms of performance.…

Facebook Twitter LinkedIn

Recently, at Percona Live Europe 2019, Dimitri Kravchuk from Oracle mentioned that he observed some unclear drop in performance for MySQL on an ext4 filesystem with the latest Linux kernels. I decided to check this case out on my side and found out that indeed, starting from linux kernel 4.9, there are some cases with notable (up to 2x) performance drops for ext4 filesystem in direct i/o mode.

So what’s wrong with ext4? It started in 2016 from the patch that was pushed to kernel 4.9: “ext4: Allow parallel DIO reads”. The purpose of that patch was to help to improve read scalability in direct i/o mode. However, along with improvements in pure read workloads, it also introduced regression in intense mixed random read/write scenarios. And it’s quite weird, but this issue had not been …

Discover the new Continuent Tungsten Replicator (AMI) – the most advanced & flexible replication engine for MySQL, MariaDB & Percona Server, including Amazon RDS MySQL and Amazon Aurora

We’re excited to announce the availability on the Amazon Marketplace of a new version of the Tungsten Replicator (AMI).

Tungsten Replicator (AMI) is a replication engine that provides high-performance and improved replication functionality over the native MySQL replication solution and provides the ability to apply real-time MySQL data feeds into a range of analytics and big data databases.

Tungsten Replicator (AMI) builds upon …

A user defined table and its corresponding  index data, in InnoDB, is  stored in files that have an extension .ibd. There are two types of tablespaces, general (or shared) tablespace and file-per-table.  For shared  tablespaces, data from many different  tables and their corresponding indexes may reside in a single .ibd…

Facebook Twitter LinkedIn

Introduction While developing a Spring Boot application is rather easy, tuning the performance of a Spring Boot application is a more challenging task, as, not only it requires you to understand how the Spring framework works behind the scenes, but you have to know what is the best way to use the underlying data access framework, like Hibernate for instance. In a previous article, I showed you how easily to optimize the performance of the Petclinic demo application. However, by default, the Petclinic Spring Boot application uses the in-memory HSQLDB database, which... Read More

The post Spring Boot performance tuning appeared first on Vlad Mihalcea.

Recently I was doing some small testing by using EC2 instances on AWS and I noticed the execution time and performance highly depend on which time of the day I am running my scripts. I was using t3.xlarge instance type as I didn’t need many CPUs and memory for my tests, but from time to time I planned to use all the resources for a short time (few minutes), and this is when I noticed the difference.

First, let’s see what AWS says about T3 instances:

T3 instances start in Unlimited mode by default, giving users the ability to sustain high CPU performance over any desired time frame while keeping cost as low as possible.

In theory, I should not have any issues or performance differences. I have also monitored the CPU credit balance and there was no correlation between the balance and the performance at all, and because these were unlimited instances the balance should not have any impact.

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and …