Tag: Performance_Schema

Feed: Planet MySQL; Author: MySQL Performance Blog; Disclaimer: the following script only works for Percona Server for MySQL 5.7, and relies on enabling performance schema (PS) instrumentation which can add overhead on high concurrent systems, and is not intended for continuous production usage as it’s a POC (proof of concept).IntroductionIn Percona Support, we frequently receive tickets related to deadlocks and even though the deadlock concept is simple, troubleshooting might not be in all cases. As explained in How to Deal with MySQL Deadlocks, a deadlock occurs when two or more transactions mutually hold and request for locks, creating a cycle of dependencies ... Read More

Feed: Planet MySQL; Author: Kristian Köhntopp; Since we now know how to look at the state of locking in a live database, let’s look at what happens when we run a normal insert or update and an insert or update with foreign key relationships defined, and compare.We will be using the tables and structures from our previous examples, a simple 1:n relationship between a and b: CREATE TABLE a ( a_id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (a_id) ); INSERT INTO a VALUES (10), (20), (30), (40); CREATE TABLE b ( b_id int NOT NULL AUTO_INCREMENT, a_id int NOT NULL, ... Read More

Feed: Planet MySQL; Author: Kristian Köhntopp; Support Channel. “Hi, I am getting deadlocks in the database and they occur when I have to rollback the transactions but if we don’t have to roll back all transactions get executed.” Wait, what? After some back and forth it becomes clear that the Dev experiences deadlocks and has data:mysql> pager less mysql> show engine innodb statusG ... MySQL thread id 142531, OS thread handle 139990258222848, query id 4799571 somehost.somedomain someuser update INSERT into sometable (identifier_id, currency, balance ) VALUES ('d4e84cb1-4d56-4d67-9d16-1d548fd26b55', 'EUR', '0') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 3523 page ... Read More

Feed: Planet MySQL; Author: Kristian Köhntopp; In a previous article we wrote data to the database using atomic update statements, and then using transactions with SELECT ... FOR UPDATE. In this article we will look at what happens when we continue doing this, in a more complicated way. Source code for this article is also available on github.com.A simple row lock But first let’s do things manually: We create a table kris with an integer primary key column and a secondary unindexed data column. We are filling it with some records with gaps between the primary keys. We then START ... Read More

Feed: Planet MySQL; Author: Frederic Descamps; I wanted to be brave and I installed MySQL Group Replication manually…. it was painful ! Then I realized that managing those servers and especially deal with MySQL Routers was even more painful ! What are my options now ? Is there a solution or do I need to restart from scratch ? Asking the answer is already answering it… and once again MySQL Shell at the rescue. MySQL Group Replication I’ve configured everything manually. I also loaded group_replication and clone plugins and finally after having bootstrapped my Group here is what I have: ... Read More

Feed: Planet MySQL; Author: RoseHosting; In this tutorial, we will show you how to create a backup of MySQL databases on an Ubuntu 20.04 VPS, and create a backup of the entire /var/lib/mysql directory.We will create the backup of the databases using MySQL’s command, aptly named mysqldump. After that we will then show you how to perform a backup of the /var/lib/mysql directory where MySQL have been located. Performing regular backups of your database and database server is vital to protecting the data that you have on your server. This way, in case something goes wrong on your Ubuntu 20.04 ... Read More

Feed: Planet MySQL; Author: MySQL High Availability; MySQL Group Replication allows you to create an highly-available replication group of servers with minimum effort. It provides automated mechanisms to detect and respond to failures in the members of the group. The response depends on the characteristics of each failure and it is configurable. In order to decrease the need for manual user intervention whenever there is a temporary network partition or a server slowdown in MySQL 8.0.21 we have changed the default values of two system variables: group_replication_member_expel_timeout group_replication_autorejoin_triesIn this blog post we will review MySQL Group Replication’s mechanisms to detect ... Read More

Feed: Planet MySQL; Author: MySQL High Availability; The Automatic Schema Synchronization mechanism, detailed in a two part series: part 1 and part 2, automatically detects and synchronizes mismatches in metadata between NDB Dictionary and MySQL Server’s Data Dictionary. Information about the mechanism is exposed through different MySQL Server status variables. These variables deal with the count of metadata objects in different states of detection and synchronization. Users had to delve into the error log for granular details about the objects. This has been improved in NDB Cluster 8.0.21 by the introduction of two Performance Schema tables: ndb_sync_pending_objects and ndb_sync_excluded_objects. These ... Read More

Feed: Planet MySQL; Author: InsideMySQL.com; Dear MySQL users, MySQL Shell 8.0.20 is a maintenance release of MySQL Shell 8.0 Series (a component of the MySQL Server). The MySQL Shell is provided under Oracle’s dual-license. MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Shell 8.0.20. MySQL Shell is an interactive JavaScript, Python and SQL console interface, supporting development and administration for the MySQL Server. It provides APIs implemented in JavaScript and Python that enable you to work with MySQL InnoDB cluster and use MySQL as a document store. The AdminAPI enables ... Read More

Feed: Planet MySQL; Author: Keith Hollman; Just recently, I have been asked to look into what a Disaster Recovery site for InnoDB Cluster would look like. If you’re reading this, then I assume you’re familiar with what MySQL InnoDB Cluster is, and how it is configured, components, etc. Reminder: InnoDB Cluster (Group Replication, Shell & Router) in version 8.0 has had serious improvements from 5.7. Please try it out. So, given that, and given that we want to consider how best to fulfill the need, i.e. create a DR site for our InnoDB Cluster, let’s get started. Basically I’ll be ... Read More