Recently, I found myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one of the table, we were talking of 5 TB of data, 23B rows. Not small… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take [...]
A (prototype) lower impact slow query log
September 20, 2012 By 12 Comments
Yesterday, over at my personal blog, I blogged about the impact of the MySQL slow query log. Since we’re working on Percona Server 5.6, I did wonder if this was a good opportunity to re-examine how we could provide slow query log type functionality to our users. The slow query log code inside the MySQL [...]
Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
As an instructor with Percona I’m sometimes asked about the differences between the READ COMMITTED and REPEATABLE READ transaction isolation levels. There are a few differences between READ-COMMITTED and REPEATABLE-READ, and they are all related to locking.
Impact of memory allocators on MySQL performance
July 5, 2012 By 11 Comments
MySQL server intensively uses dynamic memory allocation so a good choice of memory allocator is quite important for the proper utilization of CPU/RAM resources. Efficient memory allocator should help to improve scalability, increase throughput and keep memory footprint under the control. In this post I’m going to check impact of several memory allocators on the [...]
Comparing Percona XtraDB Cluster with Semi-Sync replication Cross-WAN
June 14, 2012 By 7 Comments
I have a customer who is considering Percona XtraDB Cluster (PXC) in a two colo WAN environment. They wanted me to do a test comparing PXC against semi-synchronous replication to see how they stack up against each other. Test Environment The test environment included AWS EC2 nodes in US-East and US-West (Oregon). The ping RTT latency [...]
thread_concurrency doesn’t do what you expect
June 4, 2012 By 3 Comments
Over the last months I’ve seen lots of customers trying to tune the thread concurrency inside MySQL with the variable thread_concurrency. Our advice is: stop wasting your time, it does nothing on GNU/Linux Some of the biggest GNU/Linux distributions includes the variable thread_concurrency in their my.cnf file by default. One example is Debian and its [...]
Load management Techniques for MySQL
May 6, 2012 By 3 Comments
One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to degrade. The first thing you need [...]
InnoDB’s gap locks
March 27, 2012 By 15 Comments
One of the most important features of InnoDB is the row level locking. This feature provides better concurrency under heavy write load but needs additional precautions to avoid phantom reads and to get a consistent Statement based replication. To accomplish that, row level locking databases also acquire gap locks. What is a Phantom Read A [...]
Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
So lets say you upgraded from MySQL 5.1 to Percona Server 5.5 and instead of expected performance improvement you see your performance being worse. What should you do ? First if you followed MySQL upgrade best practices such as testing your workload with pt-upgrade the chances of this happening are rather slim. But lets assume [...]
Percona XtraDB Cluster Feature 2: Multi-Master replication
January 19, 2012 By 22 Comments
This is about the second great feature – Multi-Master replication, what you get with Percona XtraDB Cluster. It is recommended you get familiar with general architecture of the cluster, described on the previous post. By Multi-Master I mean the ability to write to any node in your cluster and do not worry that eventually you [...]
