MARKS TUTORIAL

In this article we will look into a very important concept of distributed computing and that is we need to prevent happening of transaction on failed master in a HA cluster of MySQL, being other nodes has elected a new master (earlier slave) due to Network partitioning. Here we will assume the setup of HA cluster as follows: Client/s first connect to Service Discovery module to know the current Master , they cache the information for a specific interval. They then talk to the Master to proceed their transaction/s. The transaction are then replicated to connected replicas. The client who has the Master information will cache it and do not change it until told to do so. We will tell you, what will happen if we allow transactions on a failed master, using a real world use case, and so you will know why it is important to prevent it. The setup is as follows:  3 Clients. 1 Service Discovery Module. 3 MySQL server, 1 Master/Primary and 2 Replicas/Secondary. Please see the diagram repr...

In this article I have tried to explain how flow of information is seen from a resource point of view. I know this is very basic, but remember good knowledge of basic things help you understand more complex things easily. This knowledge will also help you to create or understand monitoring graph provided by various applications like Percona Monitoring and Management. PS: I am not in their marketing team, I just have done hands on it and I love it that's why mentioned here. 😄 I believe the image is self explanatory but defining the terms here for completeness. CPU No Description. Memory Pages read from disk and written to Memory. Pages read from Memory and written to disk. Disk Pages read from Disk and written to memory. Pages read from memory and written to Disk. Network Interface When information is send to the Network interface, it is called Request/Bytes Read/Inbound and when it is retrieved from Network Interface then it is called Response/Bytes Written/Outbound.

This article defines a method for extracting schema from a particular MySQL database, without dumping the data from the tables. This procedure can be helpful in cases of migration where you want schema but not the data of target database. For example, checking if Application DB for version X can be migrated to Application DB version Y. Dump Table Structure mysqldump -h localhost -u root -proot@123 -P3306 --single-transaction --skip-comments --no-data --skip-routines --skip-triggers --skip-events --add-drop-database --set-gtid-purged=OFF --hex-blob --databases emsent_dplicense | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*PROCEDURE/PROCEDURE/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*FUNCTION/FUNCTION/' > ems52_schema_tables.sql  Dump Routines, Triggers and Events Structure mysqldump -h localhost -u root -proot@123 -P3306 --single-transaction --skip-comments --no-create-db --no-data --no-create-info --routines --triggers --events --...

In this article I will show case a deadlock example encountered in a API call in our application. Quick Links 1. Deadlock Output 2. Understanding MySQL deadlock 3. How to avoid a MySQL deadlock NOTE:  The blog I referred is: How to deal with MySQL deadlocks A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. In a transaction system, deadlocks are a fact of life and not completely avoidable. InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. It uses a metric to pick the easiest transaction to rollback. Though an occasional deadlock is not something to worry about, frequent occurrences call for attention. Before MySQL 5.6, only the latest deadlock can be reviewed using SHOW ENGINE INNODB STATUS command. But with Percona Toolkit’s pt-deadlock-logger you can have deadlock information retrieved from SHOW EN...

There’s no common blueprint for database management system design. Every database is built slightly differently, and component boundaries are somewhat hard to see and define. Even if these boundaries exist on paper (e.g., in project documentation), in code seemingly independent components may be coupled because of performance optimizations, handling edge cases, or architectural decisions. Hence in this article I will be defining a common architecture (component + interfaces between them) of DBMS, that I believe every DB vendor thrive for. Quick Links 1. Architecture 2. Components and their Interaction 2.1. Transport Subsystem 2.2. Query Processor 2.3. Execution Engine 2.4. Storage Engine DBMS Architecture Database management systems use a client/server model, where database system instances (nodes) take the role of servers, and application instances take the role of client...

Quick Links 1. Error 2. Explanation 3. Solution One day I was restoring the mysqldump, shared by one of my team mate, into my local machine. I was using HeidiSQL as the client, but believes that same has happened if I were using command line utility. All was going well, the dump is being restored. But after a while, as the database is being restored, I got the following error: Error SQL Error (2006): MySQL server has gone away Explanation Now, let us see why we got this error. Since it happens due to the MySQL configuration system variable max_allowed_packet setting , hence the timing of getting the error will depend upon when you hit that limit in your database restore. max_allowed_packet  is basically the maximum size (in bytes) of one packet or any generated/intermediate string exchanged between client and MySQL server. Straight from the documentation A communication packet is a single SQL stat...