MySQL “No space left on device from storage engine”

We have planned for archiving the data to improve the DB performance and to reclaim the space. We were evaluating Compression in InnoDB and TokuDB. To find out the best compression method. We started benchmarking the compression ratio between InnoDB and TokuDB. Everything goes well for some time, but after a few hours got an … Continue reading MySQL “No space left on device from storage engine”

Handling case sensitive column in pt-archiver

To copy the data of the particular column of the table to another table/server, We have an option to export the data as CSV and import the data back to a different table. But when the table size is large and we need to copy the data only for the required data to the target … Continue reading Handling case sensitive column in pt-archiver

Troubleshooting an unique key addition during pt-online-schema-change

We all tried various alternative methods for modifying the table structure, but pt-online-schema-change (pt-osc) is the most convenient and preferred method for performing the alter online. It has more granular control too. But it may lead to data loss if proper precautionary steps are not taken care of. In this blog, we are going to … Continue reading Troubleshooting an unique key addition during pt-online-schema-change

MySQL ERROR Log Table Explained

Over the decades we have been reading the MySQL error log from the server system file, if there are any issues in MySQL or any unknown restart happened , generally we look at the mysql error log. By default MySQL error log can be found in the default path /var/log/mysqld.log , or it can be … Continue reading MySQL ERROR Log Table Explained

XtraDB Cluster troubleshooting a rejected node.

PXC(Percona XtraDB Cluster), aka Galera cluster, is one of my favorite clustering technology for MySQL that comes with real Multi-master capabilities, Write-set based nearly real-time replication and many more features. I could proudly say that Mydbops is one of the very few companies who have been supporting mission-critical clusters and preaching PXC ie., Galera cluster … Continue reading XtraDB Cluster troubleshooting a rejected node.

Row scanned equals to 1, Is the query is optimally tuned ?

A few days ago one of our intern @mydbops reached me with a SQL query. The query scans only a row according to the execution plan. But query does not seems optimally performing. Below is the SQL query and its explain plan. ( MySQL 5.7 ) select username, role from user_roles where username= '9977223389' ORDER … Continue reading Row scanned equals to 1, Is the query is optimally tuned ?

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 … Continue reading MySQL Group Replication and its Memory consumption (troubleshooting).

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) \ … Continue reading Get the most IOPS out of your physical volumes using LVM.

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 … Continue reading TaskMax limit affects MySQL connections

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 … Continue reading Innotop – A Monitoring tool for MySQL