Access patterns intrigue me because it seems that everyone knows what they are and talks about them, but there’s also very little written about them—in MySQL literature, at least. That’s why I set out to enumerate a list of access patterns (specific to MySQL). Since there’s no apparent standard for access patterns, I cannot say how my list measures up, but after spending most my career with MySQL, I know this: it is necessary to consider these access patterns when evaluating and improving MySQL performance. Simply put: you cannot ignore how the application accesses MySQL.

Access patterns intrigue me because it seems that everyone knows what they are and talks about them, but there’s also very little written about them—in MySQL literature, at least. That’s why I set out to enumerate a list of access patterns (specific to MySQL). Since there’s no apparent standard for access patterns, I cannot say how my list measures up, but after spending most my career with MySQL, I know this: it is necessary to consider these access patterns when evaluating and improving MySQL performance. Simply put: you cannot ignore how the application accesses MySQL.

Access patterns intrigue me because it seems that everyone knows what they are and talks about them, but there’s also very little written about them—in MySQL literature, at least. That’s why I set out to enumerate a list of access patterns (specific to MySQL). Since there’s no apparent standard for access patterns, I cannot say how my list measures up, but after spending most my career with MySQL, I know this: it is necessary to consider these access patterns when evaluating and improving MySQL performance. Simply put: you cannot ignore how the application accesses MySQL.

Codership is pleased to announce a new release of Galera Manager. This is version 1.6.2 of Galera Manager GUI. Users will notice many usability improvements, and multiple fixes for issues filed at the galera-manager-support issue tracker.

The biggest user facing feature is now being able to deploy Galera Manager managed nodes on DigitalOcean, in addition to Amazon Web Services (AWS) Elastic Compute Cloud (EC2). Now with an access token, one can fully deploy a managed cluster from within Galera Manager. 3 node fully managed Galera Clusters on Digital Ocean in under 10 minutes? This is the promise and delivery of the latest Galera Manager.

From a security standpoint, SSL …

I really enjoyed to dig into the solution I described yesterday in this post, to generate table audit information using invisible columns and triggers.

In this post, I will focus only on the solution using a JSON column to store the audit information.

Yesterday, I wrote that it’s also possible to track all changes an not only the last one but also keep information about what changed.

I wanted to illustrate that with an example, let’s start with the output:

This is exactly what I was looking for !

And of course we can search in that audit information. For example let’s search for all records that have been modified and where the old or new name was/is frederic:

We can verify that indeed that record had frederic as initial value.

Triggers

Compare …

Abstract:

By diving into the details of our case study, we will explain how incorrect table statistics may lead the optimizer to choose a suboptimal execution plan. We will also go into how MySQL calculates the table statistics and the ways to correct the table statistics to prevent it from happening again.

Case study: Incorrect table statistics lead the optimizer to choose a poor execution plan.

A customer reported a drastic performance degradation of a query while there were no code changes and no configuration changes made. The data in this article has been edited for brevity and modified to mitigate the exposure of confidential information. The case has also been approved for publication by the customer.

We obtained the query execution plan, and got the results as shown below (execution plan #1):

mysql> explain 
-> SELECT count(con.id)          ,
->        MAX(DAYNAME(con.date)) ,
-> …

Today, somebody asked me how he could track the creation of a record (who created it and when) and who and when it was last modified.

Usually, this is performed in a different table, called audit table. In MySQL you can create an audit table and populate it using triggers.

But of course, this person had some constraints, otherwise it would have been too easy:

1. no other table could be used/created
2. the application should keep working without any changes

He was desperate for help…

MySQL 8.0 Invisible Columns

The answer is : Invisible Columns.

Let’s have a look at a simple table, containing an ID as primary key, a first name and a last name. And of course the application must work as expected… and unfortunately, it seems the application is …

In this blog post, you will learn how to create a MySQL table using the various tools MySQL Workbench provides without typing any SQL code. Continue reading and learn how…

Image by Larisa Koshkina from Pixabay 

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

No MySQL Code? …

Last month I performed a review of the Percona Operator for MySQL Server which is still Alpha.  That operator is based on Percona Server for MySQL and uses standard asynchronous replication, with the option to activate semi-synchronous replication to gain higher levels of data consistency between nodes. 

The whole solution is composed as:

Additionally, Orchestrator (https://github.com/openark/orchestrator) is used to manage the topology and the settings to enable on the replica nodes, the semi-synchronous flag if required. While we have not too much to say when using standard Asynchronous replication, I want to write a few words on the needs …

Last month I performed a review of the Percona Operator for MySQL Server (https://www.percona.com/doc/kubernetes-operator-for-mysql/ps/index.html) which is still Alpha.  That operator is based on Percona Server and uses standard asynchronous replication, with the option to activate semi-synchronous  replication to gain higher levels of data consistency between nodes. 

The whole solution is composed as:

Additionally, Orchestrator (https://github.com/openark/orchestrator) is used to manage the topology and the settings …