In MySQL 8.0.16 the optimizer has improved again! Comparisons of columns of numeric types with constant values are checked and folded or removed for invalid or out-of-rage values. The goal is to speed up query execution.
6 Older Entries »
Since MySQL 5.7 one can put indexes on expressions, aka functional indexes, using generated columns. Basically you first need to use the generated column to define the functional expression, then indexed this column.
Quite useful when dealing with JSON functions, you can find an example here and the documentation there.
Starting with MySQL 8.0.13 we have now an easiest way to create functional indexes (or functional key parts as mentioned in the documentation) \o/
Let’s see how with a quick practical example.
MySQL User camp is one of the prestigious MySQL meetup happening in India for the past 6 years. Mydbops DBA’s PonSuresh and Vignesh has presented about the “JSON functions and their improvements in MySQL 8.0” at MySQL User Camp Bangalore on 13-02-2019.
JSON has been more improved a lot in MySQL 8.0 and improvements in LOB storage of MySQL boost it performance further. This presentation covers the JSON performance enhancements in MySQL 8.0 with its basic functions.
Partitioning is a process in which a single larger table is split into several smaller tables (physically) and still considered as a single table.It is generally a good idea for the tables whose size is in a few 100 GB’s.
While performing select,update,delete operations in a partitioned tables, we can notice a better performance in queries .This simple process of optimization is called partition pruning in which we can avoid scanning the certain partitions which does not have any matching values based on partition key.
The following example will explain you in detail about the partition pruning
Here I have used an Amazon linux 2 machine
Hardware Info 1 core CPU 1 GB RAM 20 GB Disk (SSD) maximum of 3000 IOPS
MySQL 5.7 is installed in this machine and two tables (one is not partitioned and other is partitioned) with 1.7 million records of same data is loaded in using …[Read more]
During our recent consulting with one of our client, We came across an interesting issue on RDS. The baseline is that “Low IO size on your RDS instance can affect your DB performance”. Yes, It’s IO size, Not IOPS.
We had our production systems running on RDS MySQL with a single master, 3 replicas. All instances are of same type db.m4.4xlarge with same parameter group configuration and the disk size is 1.5 TB. According to the AWS user guide, each of these instances can support up to 4500 (sustained IOPS) guaranteed IOPS.
Find below the Write IOPS graph for all the instances.
It’s understood that Write IOPS / pattern on Master can vary when compared with Slave, due to a lot of factors like binlog row format, log writing etc. But it has to be almost similar for all the slaves given that it …[Read more]
MySQL 8.0 was released with awesome features. One of its most prominent features is CTE (Common Table Expression).
The Common Table Expression can be used to construct complex queries in a more readable manner. In this blog, I have described how the CTE works with some useful examples.
1.1 What is CTE?
A Common Table Expression (CTE) is the result set of the query, which exists temporarily and uses only within the context of a larger query.
The CTE provides better readability and performance in comparison with a derived table.
In a normal query the temporary result set that exists only within the execution scope of a single SQL statement.
select …[Read more]
Mydbops team has presented at 15th edition Open source India Conference happened at Bangalore on 12-10-2018. Manosh malai ( Senior Devops/ DB Consultant ) and Kabilesh P R ( Co-Founder Mydbops ) has made presentation on “Evolution of DBA in the Cloud Era“. They have shared the value addition that DBA’s can bring into any organisation who have adopted to cloud ( DBaaS ). They have shared a case study of their consulting experience too.
MySQL is the most widely used Relational Database Management System in the open source world. MySQL stepped into the NoSQL world by introducing the JSON Data Type in MySQL 5.7 release. In this blog post I am going to explain one of the major advantage of optimisation made in JSON Replication .
This was done from the MySQL 8.0.3 release.
What happened before 8.0.3 ?
Before MySQL 8.0.3, Whatever changes (Updates) made in JSON document, it will log as a full document in binary log & replicate the same into slave. The JSON data is stored as a blob internally. Below is an example of how it is logged as full document in binary log ?
Server version - 5.7.22-log MySQL Community Server (GPL) My Binlog …[Read more]
MySQL 8.0 has a rich set of features. One of the feature which interests DBA’s more is invisible index
What is an index in MySQL ?
- The indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.
What is invisible index ?
- The invisible index is ability to mark an index as unavailable for use by optimizer.
- In MySQL 5.7 and other previous versions the indexes are visible by a default.
- To control the index visibility for a new index ,use a visible or invisible key words as a part of the index creation.
How to add a invisible index on existing table ?
Syntax :…[Read more]
The SQL language has one great advantage over procedural, object oriented, and “ordinary” functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser can easily transform one SQL expression into another, equivalent SQL expression, which might be faster to execute.
How does this work?
Expression Tree Equivalence
Here’s an introduction to equivalent expression trees from my SQL Masterclass: Let’s assume we want to evaluate the following expression:
A + (A - B)
Now in maths, it can be proven trivially that by the laws of associativity, the above expression …[Read more]
6 Older Entries »