Database Administration | SQL Conjuror

MariaDB/MySQL – InnoDB_flush_log_at_trx_commit

By Enrique 30/12/2020 31/12/2020 MariaDB, MySQL, Database Administration, Performance Optimization

In this blog post, we are going to talk about the variable InnoDB_flush_log_at_trx_commit. We are going to discuss what each value that we can assign to this variable means, and how it can affect performance and durability.

Innodb_flush_log_at_trx_commit controls the durability in ACID compliance

A – atomicity

C – consistency

I – isolation

D – durability

The possible values for this variable is 0, 1, and 2.

The specific path of the query goes to the innodb buffer pool, then log buffer (redo logs), then OS buffer, then finally to log file.

When innodb flush log at transaction commit is set to 0, your write goes thru memory, thru the buffer pool into the log buffer. That write then flushes from the log buffer to the log file on disk for every 1 second or when the OS flushes.

If this variable is set to 1, which is maximum durability. Your write goes to the log buffer, but the commit of the file ensures that it is written all the way on disk. This value will have a bit of performance hit compared to value 0.

If the value is set to 2, the write goes to the log buffer, but the file will be committed all the way to the OS buffer. And then the OS will flush to disk roughly every 1 second.

Advantages and Disadvantages

0 – when the database crashes, the log buffer within memory will get loss, and there is a possibility of losing those transaction. This setting is for performance, but not for durability.

1 – every write will surely be written to the redo log on disk. You will not lose that write regardless of the crash.

2 – You will lose only about 25% performance as compared to 1. If the DB crashes, the file is still written to disk cache, and then written to disk later. But if the DB server itself crashes, that DB server’s disk buffer may lose its data. This can be prevented though if we have battery backup or SAN.

MariaDB/MySQL – Difference Between Undo and Redo

By Enrique 30/12/2020 30/12/2020 MariaDB, MySQL, Database Administration

Undo log is used to keep track of changes performed by active transactions and roll them backup if necessary. It is physically stored in the system table spce and, optionally, in other tablespaces.

Redo log tracks data of the requested data changes and is used to recover tables after a crash. It is physically stored in dedicated files.

Redo and Undo logs are both used during crash recovery.

MariaDB – How to Resolve Slow/Lagging Replication

By Enrique 28/12/2020 30/12/2020 MariaDB, MySQL, Database Administration, Troubleshooting

In our environment where we have thousands of MariaDB servers, 95% of the reason why replication is slow or lagging is because there are many tables that do not have primary key.

Below is an example of what we can see in show processlist when the SQL Thread is lagging because many tables do not have primary keys.

Below is a script to check tables that do not have a primary key.

SELECT tables.table_schema, 
       tables.table_name, 
       tables.table_rows 
FROM   information_schema.tables 
       LEFT JOIN (SELECT table_schema, 
                         table_name 
                  FROM   information_schema.statistics 
                  GROUP  BY table_schema, 
                            table_name, 
                            index_name 
                  HAVING Sum(CASE 
                               WHEN non_unique = 0 
                                    AND nullable != 'YES' THEN 1 
                               ELSE 0 
                             end) = Count(*)) puks 
              ON tables.table_schema = puks.table_schema 
                 AND tables.table_name = puks.table_name 
WHERE  puks.table_name IS NULL 
       AND tables.table_schema NOT IN ( 'mysql', 'information_schema', 
                                        'performance_schema' 
                                        , 'sys' ) 
       AND tables.table_type = 'BASE TABLE' 
       AND engine = 'InnoDB';

Using the script above, I counted how many tables do not have primary key, and found out that there are 64 tables.

Having a primary key should be the norm/best practice in terms of designing schema anyway.

We have to ensure all our tables have primary key. This will guarantee that all rows are unique, and it will make the SQL thread locate rows to delete (or update) easily.

If there is no way to logically add a natural primary key for the table, a potential solution is to add an auto-increment unsigned integer column as the primary key.

Cheers!

MariaDB / MySQL – Doublewrite Buffer

By Enrique 19/12/2020 02/06/2021 MariaDB, MySQL, Database Administration

The Doublewrite Buffer provides a copy of a page needed to recover from a page corruption. This kind of corruption can happen when there is a power failure while InnoDB is writing a page to disk. InnoDB can track down the corruption from the mismatch of the page checksum while reading that page.

InnoDB writes a page to the doublewrite buffer first whenever it flushes page to disk. InnoDB will write the page to the final destination only when the buffer is safely flushed to disk. When recovering, InnoDB scans the double write buffer and for each valid page in the buffer, and checks if the page in the data file is valid too.

Cheers!

MariaDB – Streaming Backup Using mbstream When Provisioning a Slave

By Enrique 18/12/2020 18/12/2020 MariaDB, Database Administration

In this blog post, I will be showing you the commands that we can use to do streaming backup using mbstream, and how to redirect the stream to a slave , which can be useful especially when you have insufficient disk space in the Master server to hold multiple copies of backup images.

To redirect backup stream to a slave server, we use the socat utility.

Socat stands for Socket Cat. It is a relay for bidirectional data transfer between two independent data channels.

You can install socat using the command below

yum install -y socat

This is the first command that we will execute in Slave.

mkdir -p /mariadb/backup/rep/full_backup_`date +%Y%m%d`
BKPREPDIR=/mariadb/backup/rep/full_backup_`date +%Y%m%d`
socat -d -d TCP4-LISTEN:9999 STDOUT | ssh  mysql@<slave_ip>  \ 
"mbstream --directory=$BKPREPDIR -x"

The following is the breakdown of the command.

After the socat keyword, the next part of the command is the option part. The -d -d option prints fatal, error, warning, and notice messages.

Followed by the standard out stream keyword.

The next part of the command is the address type, option, and port.

Then after a pipe, the next command is the ssh connection to Slave. The port in ssh connection string is optional.

Now, Socat will listen on port 9999, and whatever it gets on port 9999, it will stream it to slave server, and to the directory that we specify.

When we execute this command in the slave server, this is what it should look like.

Below is the same command, but without the ssh connection port.

The following is the next command to be executed in the Master.

DB_ETC=/mariadb/bin/etc
USER=YourUser
PASS=YourPassword
CONNECTION_STRING="--user=$USER --password=$PASS"
DEFAULT_FILES=${DB_ETC}/my.cnf

mariabackup --defaults-file=${DEFAULT_FILES} ${CONNECTION_STRING} \ 
--backup --compress --stream=mbstream  --parallel=4 \
--compress-threads=12 | socat - TCP4:<slave_ip>:9999

The –compress-threads option defines the number of worker threads to use in compression. It can be used together with –parallel option. In the example above, we use –parallel=4, and –compress-threads=12. This means that it will create 4 I/O threads to read the data; then pipe it to 12 threads for compression.

When we execute the mariabackup command in the Master, below is what we will observe in slave. We will see that it is starting to do data transfer.

When the backup finishes, we will see that it exits with status 0.

Cheers!

MariaDB – Identify Blocking/Blocked Queries

By Enrique 08/07/2020 08/07/2020 MariaDB, Database Administration

Here is a SQL script that you can use to identify Blocking and Blocked Queries in MariaDB.

SELECT 
	r.trx_id AS waiting_trx_id,  
	r.trx_mysql_thread_id AS waiting_thread,  
	r.trx_query AS waiting_query, 
	b.trx_id AS blocking_trx_id, 
	b.trx_mysql_thread_id AS blocking_thread, 
	b.trx_query AS blocking_query 
FROM 
	information_schema.innodb_lock_waits w 
	INNER JOIN information_schema.innodb_trx b 
		ON b.trx_id = w.blocking_trx_id 
	INNER JOIN information_schema.innodb_trx AS r 
		ON r.trx_id = w.requesting_trx_id

Cheers!

SQL SERVER – Memory Settings Formula (Best Practice)

By Enrique 30/04/2020 01/05/2020 SQL SERVER, Database Administration

Here are the formulas for setting memory in SQL Server.

Single-instance dedicated Max server memory formula: physical memory – 4G (or 10% whichever is greater to leave some GBs for the OS).

Single-instance shared Max server memory formula: physical memory – 4G ( or 10% whichever is greater to leave some GBs for the OS ) – (memory for other services).

Use the sys.configurations view to query server configuration options including Max server memory(MB) and Min server memory (MB).

SELECT
	SERVERPROPERTY('InstanceName') AS myinstance,
	*
FROM	
	sys.configurations
WHERE name IN
	(
		'max server memory (MB)',
		'min server memory (MB)'
	)
GO

Use the sys.dm_os_sys_info view to query the total amount of physical memory in the server, number of schedulers, last server startup time, and if SQL Server runs in a virtual or physical environment

SELECT physical_memory_kb / 1024 AS Physical_Memory_MB
FROM sys.dm_os_sys_info

Use the sp_configure system stored procedure to set server configuration options to prevent potential UI data entry errors.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory (MB)', <Value>
GO
RECONFIGURE
GO

Cheers!

SQL SERVER – Database Instant File Initialization

By Enrique 29/04/2020 30/04/2020 SQL SERVER, Database Administration

Instant file initialization is a setting that can make a positive impact at instance level on SQL Server I/O performance.

All I/O operations are done via the OS and the storage layer. For example:

Claiming disk space for creating a new database file
Autogrowing a database file
Restoring a database from backup

Whenever SQL Server needs to allocate space for these operations, it will first fill the space it needs with zeros. This zeroing phase, or zero initialization, can take a significant amount of time, depending on storage performance and size of the disk space to zeroed. Therefore, it can adversely impact file creation, autogrow, and database restore performance.

How to Turn-On Instant File Initialization

SQL Server doesn’t have a setting or checkbox to enable Instant File Initialization.

Instead, it detects whether or not the service account it’s running under has the Perform Volume Maintenance Tasks permission in the Windows Security Policy. You can find and edit this policy by running secpol.msc (Local Security Policy) in Windows.

Expand the Local Policies Folder.
Click on User Rights Assignment.
Go down to the “Perform Volume Maintenance Tasks” option and double click it.
Add your SQL Server Service account and click OK out of the dialog.

After granting this policy to the service account, you’ll need to restart the SQL Server service in order for the policy to take effect and for SQL Server to start using Initial File Initialization.

Cheers!

SQL SERVER – TSQL Script to Change the Compatibility Level of all Databases

By Enrique 25/04/2020 01/05/2020 Database Administration, T-SQL

I’ve just migrated 8 of my demo databases from SQL 2014 to SQL 2017, and I want to change the compatibility level (from 120 to 140) of all those databases via TSQL. Here is a script to do just that.

BEGIN
	SET NOCOUNT ON;

	DECLARE @rc INT = 0, @i INT = 1, @v_cmd NVARCHAR(MAX);

	DECLARE @cmds TABLE(RowNum INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Cmd NVARCHAR(MAX) NOT NULL);

	INSERT INTO @cmds
	SELECT 'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 140' FROM sys.databases 
	WHERE compatibility_level = 120

	SET @rc = @@ROWCOUNT;
	RAISERROR('The COMPATIBILITY_LEVEL of %d databases will be changed to 140', 10, 1, @rc) WITH NOWAIT;

	WHILE @i <= @rc
	BEGIN
		SELECT @v_cmd = cmd FROM @cmds WHERE RowNum = @i;

		PRINT @v_cmd;
		EXEC(@v_cmd);

		SET @i += 1;
	END;
END

Cheers!

SQL SERVER – How to Fix a Database in Recovery Pending State

By Enrique 23/04/2020 23/04/2020 SQL SERVER, Database Administration, Troubleshooting

A SQL database will be marked with different states if one or more of its core files are in a inconsistent state. The type of state will depend on how serious the damage is. Here are some of the states:

Online – occurs when one of the data files is damaged during an execution of a query or some other operation.
Suspect – occurs when a database cannot be recovered during startup
Recovery Pending – occurs when SQL Server knows that recovery of the database is to be done, but something is obstructing before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, because it has not started yet

Here a script to check the current state of all your databases in a SQL instance.

SELECT name, state_desc from sys.databases
GO

There are several reasons why a database is marked as ‘Recovery Pending‘

The database was not cleanly shutdown. There could be one or more transactions active at that time, resulting in the deletion of active transaction log file.
To overcome server performance issues, A user could have tried moving the log files to a new drive, but in the process, corrupted the files instead.
Insufficient memory or disk space that prevented database recovery from getting started.

Let’s Fix It

ALTER DATABASE TestDB SET EMERGENCY;
GO

ALTER DATABASE TestDB set single_user
GO

DBCC CHECKDB (OptimalTestDb, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO

ALTER DATABASE OptimalTestDb set multi_user
GO

Cheers!

Facebook

Google

Twitter

Category: Database Administration

MariaDB/MySQL – InnoDB_flush_log_at_trx_commit

MariaDB/MySQL – Difference Between Undo and Redo

MariaDB – How to Resolve Slow/Lagging Replication

MariaDB / MySQL – Doublewrite Buffer

MariaDB – Streaming Backup Using mbstream When Provisioning a Slave

MariaDB – Identify Blocking/Blocked Queries

SQL SERVER – Memory Settings Formula (Best Practice)

SQL SERVER – Database Instant File Initialization

SQL SERVER – TSQL Script to Change the Compatibility Level of all Databases

SQL SERVER – How to Fix a Database in Recovery Pending State

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud