-
The Agony of Big Transactions in the MySQL Binlog
Databases fail in interesting ways. About a week ago I was called in on a support case for Tungsten Enterprise. We were getting failures in which the master MySQL 5.1.50 server would run out of connections, block applications for 5 to 10 minutes and eventually trigger automated failover to a slave. Running out of database connections is a classic symptom of blocking on a shared resource inside the server. The blocked transactions hold onto their connections, which are quickly exhausted if new transactions constantly arrive from applications. So where was the hold-up?Our first clue was to notice that Tungsten Replicator was processing a huge transaction at the time of one of the failovers. To find out more I dug into the MySQL binlog using the handy mysqlbinlog utility and looked at transactions committed around the time of the failure. The following pattern quickly became apparent across hundreds of transactions. The timestamps show that the commit occurred over 8 minutes after the first statement. #120201 11:57:59 server id 313819 end_log_pos 215822862 Query thread_id=55165463 exec_time=0 error_code=0SET TIMESTAMP=1328119079/*!*/;BEGIN/*!*/;# at 215822862#120201 11:49:23 server id 313819 end_log_pos 215823092 Query thread_id=55165463 exec_time=1 error_code=0SET TIMESTAMP=1328118563/*!*/;(SQL statement)/*!*/;# at 215823092#120201 11:57:59 server id 313819 end_log_pos 215823119 Xid = 32444278496COMMIT/*!*/;The root cause turned out to be simple. The large transaction we found in the Tungsten log resulted in over 1.3Gb of data in the MySQL binlog. Mysqld took several minutes to write this into the log. Meanwhile, since transactions apply serially into the binlog, every other transaction had to wait instead of just committing and releasing its connection. New transactions quickly consumed the spare connections up to the max_connections limit. Problem explained. The MySQL binlog is a wonderful thing but the fact that it is a serial resource has important consequences for busy servers that use replication. First, if you run out of connections, look at the binlog. Tungsten makes large transactions fairly easy to detect because they are fragmented in the replicator log. We noticed a transaction with 1300 fragments, which tipped us off to the problem. However, you can also find the problem by looking at the binlog directly as I did above. Or you can use any of several tools to analyze the binlog and look for periods of time with no transactions committed. Second, big transactions are a problem for MySQL. Admin operations or application bulk loads through web APIs can easily create huge numbers of updates. Break them up into pieces and do a chunk at a time. This is also better for slaves, which may end up lagging badly when large transactions reach them. Increasing max_connections to a high number is incidentally not a solution. It just makes problems with transactions less visible and will not help anyway if you have high transaction throughput. Third, if you have updates or deletes that affect many rows, use statement replication. Row replication is a great thing. We use it regularly in Tungsten deployments, especially for heterogeneous replication, e.g., from MySQL to Oracle. However, we have also seen customers write 5Gb or more into the binlog without even realizing it. Statement replication can write as little as a single statement. This flexibility is one of the outstanding features of MySQL replication.A possible fourth suggestion is to put the binlog on faster storage. However, it is unclear whether that would help--the binlogs in this case were already on fast, separate disk storage with large caches and sync_binlog=0. It seems MySQL writes large transactions rather slowly into the binlog for reasons that may not have much to do with storage speed. I hope to understand why that is at some later time. Perhaps somebody reading this article can suggest a reason.
-
Managing MySQL with Percona Toolkit by Frédéric Descamps
Frédéric Descamps of Percona.
Percona Toolkit is Maatkit & Aspersa combined. Opensource and the tools are very useful for a DBA.
You need Perl, DBI, DBD::mysql, Term::ReadKey. Most tools are written in Perl, and whatever is in Bash is being re-written in Perl. There is also a tarball or RPM or DEB packages.
Know your environment. The hardware & OS are crucial for you to know. How much memory/CPU do you use? Do you use swap? Is this a physical/virtual machine? Do you have free space? What kind of RAID controller? Volumes? Disk? What about the network interfaces? What IO schedulers are used? Which filesystem is the data stored on? To answer all that, just use pt-summary.
Know your MySQL environment. Version? Build? How many databases? Where is the data directory? What about replication? What are key InnoDB settings? Storage engine in use? Index type? Foreign keys? Full text indexes? To answer all this and more use pt-mysql-summary.
pt-slave-find shows you the topology and replication hierarchy of your MySQL replication instances. An inventory of replicas!
Where is my disk I/O going? Use pt-diskstats which is an improved iostat. There is pt-ioprofile but it can be dangerous in production.
Now its time to get more intimate with your database. Let’s try to find the answer to these questions: how are the indexes used? Are there duplicate keys? Which queries are eating most of the resources? You can use pt-duplicate-key-checker to check for duplicate/redundant indexes or foreign keys. pt-index-usage can tell you which indexes are unused. If you think you have bad SQL, check out pt-query-advisor.
You can use pt-query-digest to analyze the slow query log and show a profile of the workload. You mostly use this with slow query logs & tcpdump’s. Be careful when you have dropped packets — results may tend to be fake then!
After all this, its time to maintain your environment.
pt-deadlock-logger checks InnoDB status to log MySQL deadlock information. It needs to run continually to capture things.
pt-fk-error-logger extracts and logs MySQL foreign key errors.
pt-online-schema-change to alter tables. It makes a “shadow copy” and swaps them. Extremely useful for large, long-running ALTER. Facebook uses the same technique.
Validate your upgrades as upgrades are the leading cause of downtime. Are queries using different indexes? Is query execution plan different? New errors? See pt-upgrade for this. Best to run this on a third machine (i.e. the old machine and a new machine to see how it goes).
Verify replication integrity – pt-table-checksum. Perform an online replication consistency check or checksum MySQL tables efficiently on one or many servers. Use it routinely (mandatory for 95% of MySQL users). Put it in a weekly crontab. Repair differences with pt-table-sync.
Repair out-of-sync replicas – pt-table-sync
Measure delay acfurately – pt-heartbeat
Deliberately delay replication – pt-slave-delay
Watch & restart MySQL replication after errors – pt-slave-restart
When there are problems, get the symptoms when it hurts. Look at pt-stalk (wait for a condition to occur them begin collecting data – eg. everytime the threads go over 2,000 you have a problem, so it collects stuff – it calls pt-collect), pt-collect (collect information from a server for some period of time), and pt-sift.
pt-mext looks at many samples of MySQL SHOW GLOBAL STATUS side-by-side. Default STATUS shows counter since the MySQL instances started. It is very helpful to see a delta of recent activity.
The future: pt-query-digest will do query reviews; pt-stalk will do “magical fault detection algorithm”. Its all opensource and its all on Launchpad at lp:percona-toolkit.
Related posts:Practical MySQL Indexing guidelines by Stéphane Combaudon
Replication features of 2011 by Sergey Petrunia
MySQL synchronous replication in practice with Galera by Oli Sennhauser
-
Replication features of 2011 by Sergey Petrunia
Sergey Petrunia of the MariaDB project & Monty Program.
MySQL 5.5 GA at the end of 2010. MariaDB 5.3 RC towards the end of 2011 (beta in June 2011).
MySQL 5.5 is merged to Percona Server 5.5 which included semi-sync replication, slave fsync options, atuomatic relay log recovery, RBR slave type conversions (question if this is useful or not), individual log flushing (very useful, but not many using), replication heartbeat, SHOW RELAYLOG EVENTS. About 2/3rds of the audience use MySQL 5.5 in production, with only 2 people using semi-sync replication.
MariaDB 5.3 brings replication features brings group commit in the binary log, which is merged into Percona Server 5.5. Checksums for binlog events which is merged from MySQL 5.6. Sergey goes in-depth about the group commit for the binary log. To find out a little more about MariaDB replication changes, see Replication in the Knowledgebase.
There are several implementations of group commit. Facebook started it, followed by MariaDB & Oracle. Percona 5.5 is GA so the feature is there, its not in MySQL 5.6 (yet?), and MariaDB 5.3 is where its at. Seems like the MariaDB implementation is the best so far – refer to the Facebook benchmark performed by Mark Callaghan.
Annotated RBR poses a compatibility problem. MariaDB 5.3 has annotate_rows, while MySQL 5.6 has rows_query event. They are different events. So you cannot have a MariaDB 5.3 master and a MySQL 5.6 slave at this moment. So MySQL 5.6 will have a flag to mark “ignorable” binlog events which will be merged into MariaDB and this will make binary logs compatible again.
There is now also optimized RBR for tables with no primary key.
MySQL 5.6 also has crash-safe slave (replication information stored in tables). Crash-safe master (binary log recovery if the server starts & sees the binary log is corrupted). Parallel event execution is something that is new in MySQL 5.6 which is the most important feature for Sergey.
Pre-heating: There is mk-slave-prefetch (famous quote: “Please don’t use mk-slave-prefetch on #MySQL unless you are Facebook.”). There is replication booster by Yoshinori Matsunobu. There is a Python version of mk-slave-prefetch that Facebook uses.
Related posts:MariaDB 5.3 query optimizer by Sergey Petrunia
Where is MariaDB today?
Building simple & complex replication clusters with Tungsten Replicator by Giuseppe Maxia
-
MySQL Creatively in a Sandbox by Giuseppe Maxia
Giuseppe Maxia of Continuent and long time creator of MySQL Sandbox.
Only works on Unix-like servers. Works with MySQL, Percona & MariaDB servers. MySQL server has the data directory, the port and the socket – you can’t share these.
To use it: make_sandbox foo.tar.gz. Then just do ./use.
$SANDBOX_HOME is ~/sandboxes. You can also create ~/opt/mysql/ and if you have MySQL 5.0.91 binary in that directory, you can just do “sb 5.1.91″.
Sandbox has features to start replication systems as well. You can have varying master/slave setups with varying versions as well (good idea to test from MySQL -> MariaDB master->slave for migration).
You can now also play with tungsten-sandbox, which is a great way to start playing with Tungsten Replicator (see documentation and tungsten-toolbox). There is apparently also a MySQL Cluster sandbox tool that someone is working on.
Related posts:Building simple & complex replication clusters with Tungsten Replicator by Giuseppe Maxia
MySQL HA reloaded by Ivan Zoratti
MySQL synchronous replication in practice with Galera by Oli Sennhauser
-
Optimizing your InnoDB buffer pool usage by Steve Hardy
Steve Hardy of Zarafa.
Work that has been done to make Zarafa better. Why do you optimise your buffer pool? To decrease your I/O load. How can you do it? Buy more RAM, page compression, less (smaller) data, rearrange data.
MariaDB or Percona Server allows you to inspect your buffer pool (unsure if this is now available in MySQL 5.6). Giuseppe in the audience says this is available in MySQL 5.6, but Steve used this on MariaDB 5.2.
Strategies to fix it: Make records smaller. Remove indexes if you can use others almost as efficiently. Make records that are accessed around the same time have a higher chance of being on the same page. Use page compression. Buy more RAM. Try Batched Key Access (BKA) in MariaDB 5.3+.
Best to view the presentation since there are specific examples that speak about how Zarafa solves their problems like a user trying to sort their email, etc.
Related posts:Batched Key Access Join Previews available
MariaDB 5.3 query optimizer by Sergey Petrunia
MariaDB/MySQL users in Paris & Brussels
|
News Feeds 
