Showing entries 1 to 10 of 78
10 Older Entries »
Displaying posts with tag: troubleshooting (reset)
Innotop – A Monitoring tool for MySQL

Monitoring MySQL server has never been an easy task. Monitoring also needs to go through many Complex and difficult queries to get the details.

All these problems can be overcome by an excellent command line monitoring tool  called “Innotop”. Innotop comes with many features and different types of modes/options, which helps to monitor different aspects of MySQL  (InnoDB) performance and also helps database administrator to find out what’s wrong going with MySQL server. Innotop helps in monitoring user statistics, mysql replication status,query list, InnoDB I/O informations etc. Another important thing about innotop is it refreshes the data continuously , so we can view realtime statistics.

Innotop is one of the designed based on top utility for linux.

[Read more]
Chose right SST method MariaDB Cluster 10.2

                In this blog post, I am going to explain an interesting issue which I faced in one of our client project . Few weeks back , I got an requirement from our support client to construct a new MariaDB Galera Cluster ( 10.2.21 ) and an async slave with GTID. The Complete requirement is as below.

  • Need to construct the Galera cluster with 37 GB of data ( Using a given MySQL dump file )
  • Have to configure an asynchronous slave under the 3 node Galera cluster
  • The asynchronous slave was proposed with GTID to ease master node switchover.

So, as per the requirement the architecture will be, 

 Architecture  –

How to achieve this ?

A high level steps

    • Install MariaDB 10.2.21 server on all four nodes ( 3 Galera node with wsrep_on = ON & 1 async slave with …
[Read more]
Impact of “tmpdir” change in MySQL replication

Recently we had encountered a strange issue with replication and temp directory(tmpdir) change while working for one major client.

All the servers under this were running with Percona flavor of MySQL versioned 5.6.38 hosted on a Debian 8(Jessie)

The MySQL architecture setup  is as follows one master with 5 direct slaves under it

Through this blog, we will take you through the issue we had faced and how we fixed ultimately.

Client Request:

There was a simple request from our client to add a column and index to a 16GB production table since the table had foreign keys, to avoid complications we decided to go with online DDL instead of pt-online-schema.

When we started to alter, it got failed due to insufficient space in “tmpdir”.MySQL by default would be using “/tmp” for temp table creating, sorting and other temp operation, Since we had only 5.7G left on the “/” …

[Read more]
How to Switch Replica Master of a non-GTID Slave in Percona Cluster ?

Introduction –

Recently i worked on a production issue for one of our client under support .They have a architecture of a three node Galera cluster with one asynchronous slave .

  • Node1 – 172.10.2.11
  • Node2 – 172.10.2.12
  • Node3 – 172.10.2.13
  • Replica – 172.10.2.14

Architecture –

The slave(replica) was configured with node3 as replica master. Unfortunately the node 3 was crashed with an OOM killer ,also server has a low gcache size, so when i am trying to start the node 3 , it went to SST . Here the data size was around 2.6 TB , in general for completion of whole SST and joining the node back to cluster will take around  approximately 12 hours.

As i told earlier, the replication slave was under …

[Read more]
Configuring efficient MySQL Logrotate

I am a Junior DBA at Mydbops. This is my first blog professionally, I would like to brief my encounter with Log-rotate in first few weeks of my work,  Hope it will help other beginners as well. This Blog will cover the following sections.

  • Introduction to Log-rotate

  • Issues Faced

  • Solutions (Fix for the above issues)

  • Best practices

    • How to configure the Log-rotate

    • Operation of Log-rotate

    • Files responsible for the Log_rotate utility.

1.0. Introduction to Log-rotate:

  • Log-rotate is a utility and …

[Read more]
Will IO Size Affect your RDS Performance?​

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]
The Difference Between Lock Wait Timeout And Deadlock

If you use ACID transactional databases, you’ve probably heard of lock wait timeouts and deadlocks. What are these? And how are they different?

It’s inevitable that many of us will come across these phenomena at some point. Most databases use locking strategies to make sure that data stays consistent when multiple users (or connections, or processes) are reading and modifying the data concurrently.  The locks ensure that only one operation can alter a specific portion of the data at a time as well as serializing changes to further mitigate these race conditions. Without this locking, confusing and incorrect behaviors can happen.

Lock wait timeouts and deadlocks both arise from certain locking mechanisms. A lock wait timeout results when one user gets a lock on some data and holds it while another user …

[Read more]
Database Objects migration to RDS/ Aurora (AWS)

The world of application and its related services are migrating more towards cloud, because of availability, Elasticity, Manageability etc. While moving the entire stack we need to be very cautious while migrating the database part.

Migration of DB servers is not a simple lift and shift operation, Rather it would require a proper planning and more cautious in maintaining data consistency with existing DB server and cloud server by means of native replication or by using any third party tools.

The best way to migrate the existing MySQL database to RDS, in my opinion, is by using “logical backup“. Some of the logical backup tools as below,

Mysqldump — single threaded (widely used)

[Read more]
How NOT to Monitor Your Database

Do you have experience putting out backend database fires? What were some things you wished you had done differently? Proactive database monitoring is more cost efficient, manageable, and sanity-saving than reactive monitoring. We reviewed some of the most common mistakes - too many log messages, metric “melting pots,” retroactive changes, incomplete visibility, undefined KPIs - and put together an action plan on how to prevent them. From our experience, we've listed out the top 5 biggest (and preventable!) database monitoring pitfalls.

Log Levels

There never seem to be enough logging levels to capture the desired granularity and relevance of a log message accurately. Is it INFO, TRACE, or DEBUG? What if it’s DEBUG but it’s for a condition we should WARN about? Is there really a linear hierarchy here? If you’re like most people, you’ve seen …

[Read more]
How NOT to Monitor Your Database

Do you have experience putting out backend database fires? What were some things you wished you had done differently? Proactive database monitoring is more cost efficient, manageable, and sanity-saving than reactive monitoring. We reviewed some of the most common mistakes - too many log messages, metric “melting pots,” retroactive changes, incomplete visibility, undefined KPIs - and put together an action plan on how to prevent them. From our experience, we've listed out the top 5 biggest (and preventable!) database monitoring pitfalls.

Log Levels

There never seem to be enough logging levels to capture the desired granularity and relevance of a log message accurately. Is it INFO, TRACE, or DEBUG? What if it’s DEBUG but it’s for a condition we should WARN about? Is there really a linear hierarchy here? If you’re like most people, you’ve seen …

[Read more]
Showing entries 1 to 10 of 78
10 Older Entries »