MariaDB – How to Resolve Slow/Lagging Replication

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!