MySQL is simpler than the Oracle example  because MySQL only has Nested Loops and doesn’t have Hash Join.

Here is the query, like on the Oracle example from the last blog post

select max(t1.data) 
from  t1, t2  
where t1.id = t2.id 
and t1.clus  = 1
;

So there are only 4 examples below which all do NL joins

1. full table scan on T1 and T2
   1. 7.83 secs
2. index on T1 predicate filter column
   1. 7.39 secs
3. index on T2 join column
   1. 0.49 secs
4. index on both T2 join column and T1 predicate filter column
   1. 0.06 secs

There isn’t an idea of “explain analyze” on MySQL until MySQL 8.0.18 and I did my testing on …

A few days ago one of our intern @mydbops reached me with a SQL query. The query scans only a row according to the execution plan. But query does not seems optimally performing.

Below is the SQL query and its explain plan. ( MySQL 5.7 )

select username, role from user_roles where username= '9977223389' ORDER BY role_id DESC LIMIT 1;

Execution plan and table structure

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_roles
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

show create table user_roles\G
*************************** 1. row ***************************
       Table: …

Writing MySQL Proxy in GO for self-learning: Part 1 — TCP Proxy

Weekend. Quarantine. If you bored like me, and want to learn something new, you can join my journey of writing yet another MySQL Proxy Server.

In the past, I had a crazy idea of writing MySQL Proxy Server. The first reason was to understand the MySQL Communication Protocol to let me write more efficient programs. The second one was to learn more about low-level network programming concepts in general. Finally, I have time at least to start… and you can join me!

The real usage of MySQL Proxy can vary:

• Testing and benchmarking of the infrastructure
• Testing your applications for dealing with MySQL network failures and delays
• Wrap the MySQL usage with own business layer
• Load balancing
• Multiplexing

Prelude

I’m not sure if I can …

There are many users of MySQL in Cloud environment regions. It’s ideal that those MySQL instances are implemented with only private-ips in the respective Subnet. No public-ips. For that matter, all cloud infrastructure resources are ideally placed behind networks that provision only private-ips.  This limits any front-end attack surface for an application stack.  Certainly the up coming MySQL… Read More »

As a MySQL database administrator, we all using the MySQL client program for communicate to MySQL Server . Maximum the client program is used to execute the SQL’s, monitor the traffic and modify the variables . MySQL client program has some good features which can helps to make our work easier . In this blog I am going to explain some MySQL client program features which impressed me .

• Execute the OS commands inside the MySQL client program
• Create / Execute the SQL file inside the MySQL client program
• Query output with XML and HTML languages
• MySQL client program for learning MySQL commands .
• Pager with MySQL client program
• Safe UPDATE / DELETE with MySQL client program
• Define the maximum number of rows in result set ( SELECT’s )

Execute the OS commands inside the MySQL client program :

Yes, It is possible to execute …

Tweet

I have over the last few years been fortunate to have two books published through Apress, Pro MySQL NDB Cluster which I wrote together with Mikiya Okuno and MySQL Connector/Python Revealed. With the release of MySQL 8 around a year ago, I started to think of how many changes there has been in the last few MySQL versions. Since MySQL 5.6 was released as GA in early 2013, some of the major features related to performance tuning includes the Performance Schema which was greatly changed in 5.6, histograms, EXPLAIN ANALYZE, hash …

If you are going to migrate your MySQL workloads to GCP’s managed database service CloudSQL, then you have to keep these points in mind. We have done a lot of CloudSQL migrations. But sometimes it’s not smooth as we thought. Generally, people don’t even think that these thinks will make the replication failure. I listing 3 things that ate our brain and time while migrating to CloudSQL.

1. Server character set:

CloudSQL by default using utf8 as the server character set. But it is customizable, we can change it any time. But still, it’ll mess up your application later. We had a MySQL server on a VM where the server’s character set was latin1. We dump the database and restore it to CloudSQL. While launching the CloudSQL we didn’t set up any Database flags. So the data restore with utf8 character set.

Before Migration

mysql> SHOW SESSION …

If you are going to migrate your MySQL workloads to GCP’s managed database service CloudSQL, then you have to keep these points in mind. We have done a lot of CloudSQL migrations. But sometimes it’s not smooth as we thought. Generally, people don’t even think that these thinks will make the replication failure. I listing 3 things that ate our brain and time while migrating to CloudSQL.

1. Server character set:

CloudSQL by default using utf8 as the server character set. But it is customizable, we can change it any time. But still, it’ll mess up your application later. We had a MySQL server on a VM where the server’s character set was latin1. We dump the database and restore it to CloudSQL. While launching the CloudSQL we didn’t set up any Database flags. So the data restore with utf8 character set.

Before Migration

mysql> SHOW SESSION …

Percona is happy to announce the experimental release of Percona XtraDB Cluster 8.0. This is a major step for tuning Percona XtraDB Cluster to be more cloud- and user-friendly. This is the second experimental release that combines the updated and feature-rich Galera 4, with substantial improvements made by our development team.

Improvements and New Features in Percona XtraDB Cluster

Galera 4, included in Percona XtraDB Cluster 8.0, has many new features. Here is a list of the most essential improvements:

• Streaming replication to support large transactions
• The synchronization functions allow action coordination (wsrep_last_seen_gtid, wsrep_last_written_gtid, wsrep_sync_wait_upto_gtid)
• More granular and improved error logging. wsrep_debug is …

Today we will see how to install WordPress in Oracle Cloud (OCI).

We will use Oracle Cloud’s Free Trier and deploy 2 compute instances:

• the webserver (apache, PHP and WordPress)
• the database server (MySQL 8.0)

We will use one public IP to reach our site and our two servers will communicate using their own private network:

Deploying 2 compute instances

Let’s start by creating 2 compute instances:

We will use the Oracle Linux 7.7 on Micro Instances:

As I am in Belgium, I use the Frankfurt datacenter.

When our instances are deployed, we can find the public IP of one of them. This is the one we will use as webserver (you can free the public IP on the other one, the MySQL one):

At the creation, we entered a ssh key to access …