Author: Rene Cannao

Mystery Solved: Replication lag in InnoDB

While running a backup with XtraBackup against a slave server we noticed that replication was lagging significantly. The root cause wasn’t clear, but we noticed that DML statements from replication were just hanging for a long time. Replication wasn’t always…

Read More >

Testing and Analyzing Performance with Benchmarks

Generic benchmark tools can be very useful for testing performance on your system. These benchmark tools normally have a set of predefined workloads, but often they don’t match your specific workload in useful ways. One of the best ways to…

Read More >

Performance Optimization for MyISAM Compressed Tables

Database performance optimization is a significant part of the ongoing service we provide for our clients. We recently found that a client had a query they performed regularly that was drastically slowing the system as a whole, and we investigated to see if…

Read More >

Master/Slave Replication In Limited Bandwidth Scenarios

Database replication is quite an important component of the database architectures of many of our clients. Managed properly, it offers real-time redundancy and fault-tolerance, and allows for increased scalability. When replication goes wrong, however, it can turn into a morass…

Read More >

Hashing Algorithm in MySQL PASSWORD()

Recently we had a question from a customer: what is the hashing algorithm implemented in PASSWORD() ? The manual doesn’t give a straight answer in any of these two pages: https://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password https://dev.mysql.com/doc/refman/5.1/en/password-hashing.html   It is enough to dig a bit…

Read More >

Change Views DEFINER without ALTER VIEW : how to fix thousands of views!

Recently I faced an interesting challenge: modify over 26k views on a single mysqld instance. Altering the view one by one was far from an easy task, so a I had to look for an efficient way to do it….

Read More >

Turning off the Query Cache works in Percona Server 5.1 , before MySQL Server 5.5

In a previous blog post I wrote that is not possible to completely turn off the Query Cache in MySQL before 5.5 . Although, it is possible to completely turn off the Query Cache with Percona Server 5.1 . MySQL bug #38551 reports…

Read More >

You can’t turn Query Cache Off before MySQL 5.5

… or at least, not completely! It often happens to see and hear of replication SQL thread almost always in state “invalidating query cache entries (table)” . The classic tip is “set global query_cache_size=0” . While sometime this works, others…

Read More >

Replication and EC2 gotcha

Tonight a client restarted some instances on EC2 and they got different IPs. This broke replication because the slaves were trying to connect to the masters using IPs that weren’t existing any more. To solve the task is easy: just…

Read More >