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 the following options to skip steps 2. through 4. and thus also avoid the locking steps: --only-innodb - Back up only InnoDB data and log files. All .frm files and files … [Read more...] about How MySQL Enterprise Backup (MEB) uses locking while making a backup
MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
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 data is copied. FLUSH TABLES WITH READ LOCK is executed. All other files are copied and other tasks requiring the lock are performed. See … [Read more...] about MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
SQL script to find tables that are fragmented
This post intends to provide an SQL script to find tables that are fragmented (i.e. Data is much lower than High Water Mark) so that we can target those segments (tables) for recreation. pre-requisites Requires dba privileges as the script is to be run as the owner SYS or SYSTEM. It Will not work on Compressed tables, and may return negative numbers. Do an Analyze with compute statistics on all tables present in the Users schema. The SQL script The script is as shown below: REM This is … [Read more...] about SQL script to find tables that are fragmented
TRUNCATE TABLE not releasing space from tablespace
When we delete the rows from the table using the DELETE command, the extents that are allocated to the table will not be released and the table still holds them, whereas when using TRUNCATE with DROP STORAGE clause (which is the default), it will release the space back to the tablespace. But we see cases that event truncating won't release the space back to the tablespace. The answer is simple, When a truncate is issued on a table, Oracle deallocates all space used by the removed rows … [Read more...] about TRUNCATE TABLE not releasing space from tablespace
How to reclaim entire space of an oracle database table with “Truncate Table” statement
Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing … [Read more...] about How to reclaim entire space of an oracle database table with “Truncate Table” statement
Oracle SQL Script to Report Tablespace Free and Fragmentation
This script displays tablespace free space and fragmentation for each tablespace. It prints the total size, the amount of space available, and a summary of free space fragmentation in that tablespace. This can serve as an early warning system to prevent database space allocation crises. Note that no attempt is made to coalesce adjacent fragments. It is thus possible sometimes to obtain an extent larger than the size reported as the Largest Free Frag. Since a large space-consuming … [Read more...] about Oracle SQL Script to Report Tablespace Free and Fragmentation
Truncate Table Statement: REUSE STORAGE VS DROP STORAGE
Question: If we want to truncate any large table and reclaim space, which is the truncate command out of below 3 which can be used? TRUNCATE TABLE table_name; OR TRUNCATE TABLE table_name DROP STORAGE; OR TRUNCATE TABLE table_name REUSE STORAGE; REUSE STORAGE VS DROP STORAGE Here is the difference between drop and reuse storage: DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of … [Read more...] about Truncate Table Statement: REUSE STORAGE VS DROP STORAGE
How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
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 this: +--------------------------+----------------------------+ | Variable_name | Value … [Read more...] about How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
“Connection reset by peer” – error while ssh into a CentOS/RHEL system with a specific user only
The Problem When trying to open an ssh connection to a system with a specific account, it failed with 'Connection reset by peer'. Other users can successfully connect with ssh to this system. Below is an example showing the failed login to node [NODE2] with the account 'oracle'. [oracle@NODE1]$ ssh oracle@[NODE2] oracle@[NODE2]'s password: Read from remote host [NODE2]: Connection reset by peer Connection to [NODE2] closed. [oracle@NODE1]$ ssh root@[NODE2] root@[NODE2]'s … [Read more...] about “Connection reset by peer” – error while ssh into a CentOS/RHEL system with a specific user only
MySQL: how to figure out which session holds which table level or global read locks
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. Please look at the example below. mysql> use performance_schema ... snip ... mysql> update performance_schema.setup_instruments set enabled='yes', timed='yes' where name = … [Read more...] about MySQL: how to figure out which session holds which table level or global read locks