• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

mysql

What is the meaning of the TRX_TABLES_LOCKED column in the information_schema.INNODB_TRX MySQL table

by admin

While the column mention “tables”, it does not literally mean table locks, but rather the number of tables in which the transaction holds one or more InnoDB row locks. From the MySQL Reference Manual: TRX_TABLES_LOCKED: Number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table […]

Filed Under: mysql

What are the various types of locking used in the MySQL Server

by admin

MySQL uses locking at several levels of which table level locks and row level locks are the two most often encountered lock types. For each lock type, the lock held can in general be either a shared lock or an exclusive lock. Shared allows multiple processes to read the same data, but writes will block. […]

Filed Under: mysql

How MySQL Enterprise Backup (MEB) uses locking while making a backup

by admin

MySQL Enterprise Backup performs the backup in several steps: First the InnoDB data is copied. FLUSH TABLES WITH READ LOCK is executed. All other files are copied and other tasks requiring the lock are performed. See also below. The lock is released. If you only need to backup InnoDB tables, you can use one of […]

Filed Under: mysql

MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?

by admin

MySQL 8.0.11 supports lock instance for backup but MySQL Enterprise Backup (MEB) not support that still uses FTWRL (FLUSH TABLES WITH READ LOCK). Support for avoiding FLUSH TABLES WITH READ LOCK for InnoDB tables was added in MySQL Enterprise Backup 8.0.16. Before 8.0.16, MySQL Enterprise Backup performs the backup in several steps: First the InnoDB […]

Filed Under: mysql

How to set the default character set in MySQL and how to propagate it in a master-master replication scenario

by admin

In this post, we will see how to change the default character set from the default latin1_swedish_c to utf8_general_ci (or any other) and how to propagate charset changes in a master-master replication environment. 1. First determine the current default values, by issuing the MySQL command: mysql> show variables like ‘%character_set_%’; This will return something like […]

Filed Under: mysql, MySQL Cluster

MySQL: how to figure out which session holds which table level or global read locks

by admin

Question: How to Retrieve Table Level or Global Read Lock Status from Running MySQL Server? Prior to MySQL 5.7, it is not possible to figure out table level or global read locks held by each sessions. As of MySQL 5.7, a new performance schema table, metadata_locks is added. This performance schema can achieve the goal. […]

Filed Under: mysql

Recommended Configuration of the MySQL Performance Schema

by admin

In this post, we will see how to configure the Performance Schema and avoid it becomes a performance bottleneck. The default settings are a good starting point. Those have been extensively tested to ensure that in the vast majority of cases, they will not cause a severe overhead. It can be worth considering to enable […]

Filed Under: mysql

MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running

by admin

The preferred solution depends on the version of MySQL. If you are using MySQL 5.7 or later with metadata lock instrumentation enabled in the Performance Schema (available in MySQL 5.7 and later), it is possible to get the information directly from the performance_schema.metadata_locks table. Otherwise, there is no direct way to get the information. To […]

Filed Under: mysql

MySQL: How to kill a Long Running Query using max_execution_time

by admin

In this post, we will learn about what options are available if you need to automatically kill long-running queries. MySQL 5.7 and Later For SELECT Statement Starting from MySQL 5.7.4 there is support for automatic timeouts of read-only SELECT statements (the following discussion assumes 5.7.8 or later as the feature had changes to the option/hint […]

Filed Under: mysql

How to install and configure MySQL sys schema

by admin

This is general guidance for DBAs who are looking to use the MySQL sys schema. Introduction The MySQL sys schema is a collection of views, stored procedures, and stored functions to help MySQL database administrators get insight into what MySQL is doing and to help use the Performance Schema. However while the sys schema primarily […]

Filed Under: mysql

« Previous Page
Next Page »

Primary Sidebar

Recent Posts

  • ncat Command Examples in Linux
  • ncat: command not found
  • nautilus Command Examples in Linux
  • namei: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright