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!
