Showing entries 1 to 6
Displaying posts with tag: DBA Insights (reset)
Integrating MySQL tools with Systemd Service

In my day to day tasks as a DB Engineer at Mydbops we have been using multiple MySQL tools for multiple use cases to ensure an optimal performance and availability for servers managed by our Remote DBA Team.

A tool like pt-online-schema can be used for any DDL changes ( Overview to DDL algorithm ), if any tool which needs to scheduled for longer period we tend to use screen or cron.

Some of the problems we face when we demonise the process or use screen for running processes.

  • The daemon process gets killed when the server reboot happens. …
[Read more]
Presentation : Group Replication in MySQL 8.0

This presentation covers about the basics of Group replication in MySQL 8.0. Group replication world on the basis of Multi Paxos ( a variant of it ). The author covers the leader election algorithm , switching the primary node and replication modes in this presentation.

Group Replication in MySQL 8.0 ( A Walk Through ) from Mydbops

Like to have this high availability MySQL solution configured on your environment. Mydbops Consultants can provide the best solution with right cost for performance.

Security: Data Masking in MySQL 8.0 server

Data security plays a major role in current age. Privacy matters a lot. Data masking is one of the Key Features when comes to security.

  • In MySQL community versions if you want to mask your data, You can go with a Maxscale load balancer.
  • They introduced a new masking filter on the Maxscale 2.1 version.
  • My colleague Prasanth written about column-level data masking. For reference click here Column Level Data Masking.
  • In MySQL 8.0 Enterprise they have introduced a new security feature called data masking.
  • This feature was introduced in MySQL 8.0.13 Enterprise Edition and it provides data masking and de-identification capabilities.

What is Masking ?

  • Transformation of existing data to mask it and remove …
[Read more]
MySQL Group Replication and its Memory consumption (troubleshooting).

This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of  3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.

We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.

In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.

Going forward will look into the detailed analysis made to tackle the issue of OOM.

DB Environment:-

  • Service – Group Replication Cluster
  • Cluster Nodes – 3
  • GR mode – Single Primary …
[Read more]
3 Step Migration of MySQL data to Clickhouse for faster analytics.

Recently one of our client approach Mydbops with Query slowness on a MySQL environment . They deployed the new code for generate the huge reports for the year end analytics data . After the deployment the queries were extremely slow and they struggled lot , then they approached us for the solution. After the analysis, their OLAP database as expected it was IO bound with 100% disk IOPS utilised during the report generation. So, the queries were starving for the Disk IO slows the process .

Problem statement :

  • Reports are majorly focused on two larger log tables ( emp_Report_model , emp_details ) .
  • The report generator (procedure) is using the count(*) statement to stimulate the aggregated data on each call. It is required for their business purpose .
  • Count(*) is terribly slow in MySQL ( Using MySQL 5.7 ) as it …
[Read more]
Troubleshooting an OLAP system on InnoDB

As a part of Mydbops Consulting we have a below problem statement from one of our client.

We have a high powered server for reporting which in turn powers our internal dashboard for viewing the logistics status.Even with a high end hardware, we had a heavy CPU usage and which in turn triggers spikes in replication lag and slowness. Below is the hardware configuration.

OS : Debian 9 (Stretch)
CPU : 40
RAM : 220G (Usable)
Disk : 3T SSD with 80K sustained IOPS.
MySQL : 5.6.43-84.3-log Percona Server (GPL)
Datasize : 2.2TB

Below is the graph on CPU utilisation from Grafana.

Since the work load is purely reporting(OLAP) we could observe a similar type of queries with different ranges. Below is the Execution plan of the query. It is a join query over 6 tables.

Explain Plan:

[Read more]
Showing entries 1 to 6