Posts

Showing posts from 2019

MySQL Memory Calculator

Image
MySQL Memory Calculator

Check out the “MySQL Memory Calculator” added to my blog page, which will be helpful to calculate MySQL memory usage during tuning of MySQL memory parameters. https://www.abhinavbit.com/p/mysql-memory-calculator.html




Photo by Kelly Sikkema on Unsplash

SLEEP thread causing "Waiting for table metadata lock"

Image
SLEEP thread causing "Waiting for table metadata lock"
We might have faced some scenarios once our db connection threads are getting locked and we try to find the culprit thread id which is locking the thread, to kill and release the connections. 
Here I am going to focus on one of the scenarios when db connection threads are locked at state "waiting for table metadata lock" and when we see the processlist, we can't find any thread which is running and using the same table, i.e., all threads are in SLEEP state except the locked threads.


Such as : 
mysql [localhost:5726] {msandbox} (world) > show processlist\G *************************** 1. row ***************************            Id: 5          User: rsandbox          Host: localhost:60624            db: NULL       Command: Binlog Dump          Time: 111519         State: Master has sent all binlog to slave; waiting for more updates          Info: NULL     Rows_sent: 0 Rows_examined: 0 *************************** 2. ro…

Quickly configure replication using DBdeployer [SandBox]

Image
Quickly configure replication using DBdeployer [SandBox]
We might have different scenarios when we need a quick setup of replication either between the same version of MySQL (Like 8.0 --> 8.0) or between the different version of MySQL (Like 5.7 --> 8.0) to perform some testings. 

Here in this blog post, I will explain how we can create our replication lab setup quickly using the virtual machine and DBdeployer tool. 

Let's see, how to create replication between the same version and different version of MySQL using DBdeployer step by step. 

Create CentOS VMPlease find my this blog post link where you will get instruction about, how you can create CentOS virtual machine using the vagrant. Install DBdeployer tool -- Run below command to install the latest DBdeployer package.

[root@centos7-test-vm ~]# yum -y install wget [root@centos7-test-vm ~]# VERSION=1.33.0 [root@centos7-test-vm ~]# OS=linux [root@centos7-test-vm ~]# origin=https://github.com/datacharmer/dbdeployer/releases/downl…

MySQL 8.0 : Error Logging

Image
MySQL 8.0 : Error Logging
Here I would like to explain, How MySQL 8.0 server can be configured to log diagnosis messages inside the error log file. 
Generally, the error log contains: A record for mysqld service startup and shutdown.Diagnostic messages such as errors, warnings, and notes during mysqld server startup, running or shut down. A stack trace if mysqld exits abnormally. mysqld_safe writes once it finds mysqld exists abnormally and mysqld_safe invoke/start mysqld.  Error Log Component Configuration MySQL 8.0 uses the MySQL error log component architecture that performs log event filtering and writing. These error log components can be configured using the system variable to achieve the desired logging results inside the error log file.

We have mainly two types of error log components: filter & sink.

Error Log Filter Components --->  Filter components, mainly process log events, to add, delete or modify event fields or to delete events entirely. Error Log Sink Components