To control optimizer behaviour, we can enable/disable specific optimization via optimizer_switch system variable. The optimizer_switch variable can be changed at runtime, and has global and session values.
Execute the command below to see the current set of optimizer flags.`
SELECT @@optimizer_switch \G
To change the value of optimizer_switch, use the following syntax.
SET [GLOBAL|SESSION] optimizer_switch='cmd[,cmd]...';
Syntax
Description
default
Reset all optimizations to their default values.
optimization_name=default
Set the specified optimization to its default value.
optimization_name=on
Enable the specified optimization.
optimization_name=off
Disable the specified optimization.
There is no need to list all flags – only those that are specified in the command will be affected.
seconds_behind_master takes the difference between the timestamp on the Replica minus the timestamp of the event that the SQL_THREAD is processing that (timestamp) that was on the master.
DEMO
Execute the script below in Master Server.
create database if not exists sbm_db;
USE `sbm_db`;
DROP procedure IF EXISTS `gendata`;
DELIMITER $$
CREATE PROCEDURE gendata (in loopLimit int)
BEGIN
declare c int;
set c = 0;
label: LOOP
insert into tbl (fld)
values (FLOOR(1 + (RAND() * 60000)));
set c = c + 1;
if c > loopLimit then
leave label;
end if;
end LOOP label;
END$$
DELIMITER ;
drop table if exists tbl;
create table tbl (
tblId int not null primary key auto_increment,
fld varchar(255),
updatedAt timestamp not null default current_timestamp
);
You might be wondering, why seconds behind master is 93 when there were only 5 seconds of worth of data?
How seconds master works is it takes the difference between the timestamp on the replica minus the timestamp of the event that the sql thread is processing (The timestamp that is on the master).
mysqlbinlog --base64-output="decode-rows" --verbose mysqldb2-relay-bin.000006 | less
# at 2512613
#210522 1:07:50 server id 1 end_log_pos 2512485 GTID last_committed=9012 sequence_number=9013 rbr_only=yes original_committed_timestamp=1621660070 immediate_commit_timestamp=1621660070915181 transaction_length=279
mysql> select 1621660163 - 1621660070;
+-------------------------+
| 1621660163 - 1621660070 |
+-------------------------+
| 93 |
+-------------------------+
To improve performance, each concurrent session in MariaDB/MySQL independently access the same table. In other words, many clients could be executing queries for a given table simultaneously. This is also because MySQL/MariaDB is multithreaded. This, however, uses additional memory, but normally increases performance.
table_open_cache indicates the maximum number of tables the server can keep open in any one table cache instance.
View the current setting of table_open_cache by running the command below, or by checking the value in my.cnf
select @@table_open_cache;
There is a variable called “Open_tables”, which indicates the number of table-opening operations since the server started. This is the variable that you can check to determine whether your table cache is too small.
SHOW GLOBAL STATUS LIKE 'Opened_tables';
If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table_open_cache value at server startup.
FLUSH TABLES is to force all tables to be closed to ensure that if someone adds a new table outside of MySQL, all threads will start using the new table.
Table Definition Cache
This parameter shows the number of table definitions (SHOW CREATE TABLE \G) that can be stored. This is to speed up opening of tables and only one entry per table. You should consider increasing this parameter if you have large number of tables (>400) in your DB instance.
Values between 400 and 2000 are good for table_definition_cache. Do take note that a bigger table definition cache also means that your database uses more RAM.
To check table definition cache value, run the command below.
select @@table_definition_cache;
Add this parameter in your configuration file as follows:
This parameter enables compression on master/slave protocol. If both master and slave supports it, this parameter will instruct MariaDB/MysQL to use compression.
This parameter is not enabled by default.
It can be enabled in your my.cnf file (ensure to restart the DB service after enabling it to take effect)
Replication was single threaded in MariaDB 5.5 and MySQL 5.5. The previous transaction must commit on the slave before the next transaction can start.
A single I/O thread works to replicate events from the Master’s binlog to the relay log in the Slave.
On the Slave, the SQL Thread will apply those events, one after the other.
With that said, as you can imagine, replication can sometimes lag. Being single threaded, it is challenging to keep the Master and Slave in sync.
Enable Parallel Slave
To enable, indicate slave-parallel-threads=# in your my.cnf.
Configure the number (#) of worker threads to apply events in parallel for all your slaves.
If you indicate a value of zero, then that means no worker threads are created.
The value should be at least twice the number of multi-source master connections utilized.
You do not have to restart mysqld when you configure slave-parallel-threads=#, because it is a dynamic variable. However, all slaves connections must be stopped when modifying the value.
Here is a link that shows the performance improvement when using parallel replication.
Waiting for prior transaction to start commit before starting next transaction
The previous batch of transactions that committed together on the master has to complete first
Waiting for prior transaction to commit
Transaction has been executed by the worker thread
Parallel Slave Queue Size
SQL thread will read ahead in the relay logs when parallel replication is used. This will queue events in memory while looking for opportunities for executing events in parallel. The system variable that sets a limit for how much memory it will use for this is slave_parallel_max_queued
In order for the SQL thread to read far enough ahead in the binary log to exploit all possible paralellism, the slave_parallel_threads system variable should be set large enough.
To prevent limit throughput, the slave_parallel_max_queued system variable could be set relatively high. It should just be set low enough that total allocation of the parallel slave queue will not cause the server to run out of memory.
Slave Parallel Mode
There are 2 options for Slave Parallel Mode: In-Order and Out-of-Order
In-order runs transactions in parallel, but instructs the commit steps of the transactions in the precise same sequence as on the master.
Out-of-order will have the ability to execute and commit transactions in a different order on the slave than primarily on the master. The application must be tolerant to viewing updates occur in random order.
In this blog post, we are going to discuss MariaDB crash issues due to out-of-memory (OOM), and how to troubleshoot them.
When you get this error, it clearly indicates that the server is running out of memory. In other words, the allocated memory to MariaDB is not enough to handle the process requirements.
If memory is insufficient, then we just have to increase RAM, right? Well, this is certainly one solution, but it is the easiest way out. Simply adding RAM is neither the ideal solution nor a cheaper way. A memory-intensive query or process can eventually eat up the added memory, and can lead to errors in a short span of time.
Common Causes of Out-Of-Memory Issues
Non-optimal tables and queries
MySQL Server Configuration
Memory-intensive processes
Poor hardware resources
Insufficient RAM
Lack of disk space
Here are the ways to troubleshoot
Check Linux OS and Config
Check /var/log/messages and /var/log/syslog. Try to find an entry where it says something like “OOM killer killed MySQL”. Once you find it, the surrounding details will give you clues.
Check available RAM.
free -g
cat /proc/meminfo
Issue “top” command to see what applications are consuming RAM.
Run vmstat 5 5 to view if the system is swapping and if it is reading/writing via virtual memory.
Check MariaDB. Look for possible MariaDB memory leaks
These are the places where MariaDB allocates most of the memory:
Table cache
InnoDB (execute show engine innodb status, and look for buffer_pool and associated caches in the buffer pool section.
Identify if there are temporary tables in RAM (select * from information_schema.tables where engine=’MEMORY’;)
Prepared statements, when it is not removed from the set of resources (show global status like ‘Com_prepare_sql’; show global status like ‘Com_dealloc_sql’)
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.
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.
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.
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.
