Showing entries 1 to 6
Displaying posts with tag: MySQL DBA Insights (reset)
MySQL Clone Plugin Speed Test

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 …

[Read more]
MySQL load data infile made faster .

Loading any large file into MySQL server using the LOAD DATA INFILE is a time consuming process , because it is single threaded and it is a single transaction too. But with modern hardwares system resource is not a bottle neck. At Mydbops we focus on improving the efficiency of process as we value performance more. MySQL introduced the parallel load data operations in its latest minor release MySQL 8.0.17 . I had the curiosity to test this feature and wanted to know, how it can improve the data loading comparing to the existing single threaded method  . Through this blog I am going to  compare the both methods .

Remember you can use the parallel data loading utility only via MySQL Shell .

Internal Work Flow :

This section describes the …

[Read more]
Get the most IOPS out of your physical volumes using LVM.

Hope everyone aware about known about LVM(Logical Volume Manager) an extremely useful tool for handling the storage at various levels. LVM basically functions by layering abstractions on top of physical storage devices as mentioned below in the illustration.

Below is a simple diagrammatic expression of LVM

         sda1  sdb1   (PV:s on partitions or whole disks)
           \    /
            \  /
          Vgmysql      (VG)
           / | \
         /   |   \
      data  log  tmp  (LV:s)
       |     |    |
      xfs  ext4  xfs  (filesystems)

IOPS is an extremely important resource, when it comes to storage it defines the performance of disk. Let’s not forget PIOPS(Provisioned IOPS) one of the major selling points for AWS and other cloud vendors for production machines …

[Read more]
MySQL Functional Index and use cases.

MySQL has introduced the concept of functional index in MySQL 8.0.13. It is one of the much needed feature for query optimisation , we have seen about histogram in my last blog. Let us explore the functional index and its use cases.

For the below explanation, I have used a production scenario which has 16 core cpu, 32GB RAM and with MySQL version 8.0.16(Latest at the time of writing).

MySQL do support indexing on columns or prefixes of column values (length).


mysql>show create table app_user\G
*************************** 1. row ***************************
Table: app_user
Create Table: CREATE TABLE `app_user` (
`ad_id` int(11) DEFAULT NULL,
`source` varchar(32) DEFAULT NULL,
`medium` varchar(32) DEFAULT NULL,
`campaign` varchar(32) DEFAULT NULL,
`timestamp` …
[Read more]
TaskMax limit affects MySQL connections

Recently we had been bitten by a Systemd limitation at the “Tasks” created per-unit ie., process. This includes both the kernel threads and user-space threads, with each thread counting individually.

Am writing this blog as a reference for someone who might come across this limitation.

We have been actively working on migration DB instances, from one DC to the newly built DC .The instances on the newer DC were provisioned with the latest hardware and latest Debian OS. Below is the detailed spec of the system.

RAM             : 244G
Core             : 44Core
HardDisk.   : SSD
IOPS             : 120K
OS         …

[Read more]
Histogram​ in MySQL 8.0

MySQL 8.0 introduces many new features. We will have a look at the exciting histogram  feature in MySQL 8.0


What is Histogram?

In General, a histogram is an accurate representation of the distribution of numerical data. In MySQL, It will be useful to collect data distribution for a specific column.

What problem it solves?

In general DB Optimizer gives us the best execution plan, But the stats make the execution plan better and better .The data distribution of values in columns can make good impact in optimiser in case of column with less distinct values.

We will see an example of how it helps optimizer in some cases. 

I have used a production case. MySQL version is 8.0.15 installed in ubuntu 18.04 (32GB RAM,8 core) with optimal configuration. Let us try to optimise a …

[Read more]
Showing entries 1 to 6