CODEWITHSUNDEEP
mariadbreplication
perry
perry

Reputation: 1

MariaDB update query taking a long time

I'm currently having some problems with our mysql replication. We're using a master-master setup for failover purposes.

The replication itself is working and I believe thats setup right. But we're having troubles with some queries that takes a cruciating time to execute.

Example:

| 166 | database | Connect | 35 | updating | update xx set xx = 'xx' where xx = 'xx' and xx = 'xx' | 0.000 |

These update queries are taking a 20-30+ seconds sometimes to complete and because of that the replication starts lagging behind and within a day, it will be behind for a couple of hours. Strange part is that it will eventually catchup with the other master.

The table is around ~100MM rows big and around 70GB large. On the master where the queries are executed they take less than a second.

Both configurations, mysql and server, are near identical and we tried optimizing the table and queries, but no luck so far.

Any recommendations we could try to solve this? Let me know if I can provide you with any more information.

Using:

MariaDB 10.1.35 - CentOS 7.5.1804

Upvotes: 0

Views: 1786

Answers (1)

The Impaler
The Impaler

Reputation: 48810

The key aspect of this is how many rows are you updating:

  • If the percentage is low (less than 5% of the rows) then an index can help.

  • Otherwise, if you are updating a large number of rows (greater than 5%), a full table scan will be optimal. If you have millions of rows this will be slow. Maybe partitioning the table could help, but I would say you have little chances of improving it.

I'm going to assume you are updating a small percentage of rows, so you can use an index. Look at the condition in the WHERE statement. If it looks like this:

WHERE col1 = 'xx' and col2 = 'yy'

Then, an index on those columns will make your query faster. Specifically:

create index ix1 on my_table (col1, col2);

Depending on the selectivity of your columns the flipped index could be faster:

create index ix2 on my_table (col2, col1);

You'll need to try which one is better for your specific case.

Upvotes: 1

Related Questions