Planet MySQL

Planet MySQL -
  • How does the Replication Synchronization Checker Work? 
    We recently introduced 'mysqlrplsync' in MySQL Utilities release-1.4.2 RC. This new utility allows users to check the data consistency of an active replication system. In this blog we provide more details about how 'mysqlrplsync' works.In an active replication topology, slaves may be slightly behind the master in processing events. Depending on the workload and capabilities of each slave, transactions may be applied at different times. Should this occur and something untoward happen to one of the slaves (such as a user making a manual change directly on the slave), a synchronization process may be required to ensure that the slaves have the same data - to manually catch up all of the slaves that are behind the master.The strategy we choose was to build on the top of the replication process and makes use of GTIDs; it works independently of the binary log format (row, statement, or mixed) and does not create any new data to be replicated across the topology. The synchronization process, including the involved servers, is controlled by the utility and not by replication process itself.Synchronization AlgorithmThe synchronization algorithm was carefully designed to have minimal impact on the target systems allowing the consistency check between servers to be performed without affecting the rest of the replication topology. In a nutshell, the algorithm defines a synchronization point based on the GTIDs processed and waits for each slave to reach the defined synchronization point. It then computes checksums to compare the table data. More specifically, the synchronization algorithm performs the following for each table. More details of key steps are explained in the following paragraphs.1 - Compute the synchronization point     sync_point := compute_sync_point()2 - For each target slave set the synchronization point using the command     START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = sync_point3 - Compute the table checksum for the master (if comparing master and slaves)     CHECKSUM TABLE `target_table`4 - For each slave, compute the table checksum once the synchronization point is reached     a. - Wait for the SQL thread to stop     b. - CHECKSUM TABLE `target_table`     c. - START SLAVE SQL_THREAD5 - Compare checksums and report results.Note: The algorithm is slightly different if comparing master and slaves or only slaves. For simplicity, we abstract those implementation details since the basic steps are the same.After computing the synchronization point, the command is given to execute all transactions until the synchronization point is reached (step 2). Once it is reached, the SQL thread is stopped, the table checksum is computed, and the SQL thread restarted (Step 4). Those operations are executed concurrently on each slave.When comparing master and slaves, on the master the checksum of the target table is  computed immediately (step 3) after determining the synchronization point. Thus, the table is locked for a minimum amount of time.This algorithm does not perform any operation on any additional server other than the ones actually specified by the user.Other ConsiderationsIn order to minimize any performance impact of the algorithm on the target system, each checksum operation has an execution timeout that when reached will kill the operation and skip the check for the current table. The option is --checksum-timeout.Similarly, a timeout is also defined to wait for servers to catch up with the synchronization point. The option is --rpl-timeout.These timeout options allow users to adjust the time the utility runs when faced with large tables or servers under load. The user can then execute the utility at a later time for the skipped tables using higher timeout values.The flexibility to check different sets of servers separately allows users to distribute the load of the consistency check (i.e. only affecting different parts of the replication system at each time). For example, consider a heterogeneous system (where slaves have different performance characteristics) composed of one master 'M' and three slaves 'S1', 'S2' and 'S3'. To minimize the impact on the master, the user can run the utility first for the master 'M' and the fastest slave, 'S1', and then run it again only for the slaves 'S1', 'S2' and 'S3'. If no consistency issues are found in the first execution (M = S1), nor in the second execution (S1 = S2 = S3), then by transitivity and due to the inclusion of the same server 'S1' in both checks it can be said that there is no consistency issues in the all topology (M = S1 = S2 = S3) at the time the first check has started.Try it now! Your feedback is important for usMySQL Utilities release-1.4.2 RC is available for download from the following links.MySQL developers website: project:, Sharding, HA, Utilities Forum: documentation for MySQL Utilities can be obtained from the following link: Ideas:    - Community users: (MySQL Workbench: Utilities);    - Oracle customers: (Product = MySQL Workbench,      Component = WBUTILS);
  • Introduction to the Percona MySQL Audit Log Plugin 
    Percona has developed a MySQL Audit Log plugin that is now included in Percona Server since the recent 5.5 and 5.6 releases. This implementation is alternative to the MySQL Enterprise Audit Log Plugin: Percona re-implemented the Audit Plugin code as GPL as Oracle’s code was closed source. This post is a quick introduction to this plugin.Installation There are two ways to install the Percona MySQL Audit Plugin:INSTALL PLUGIN audit_log SONAME '';or in my.cnf[mysqld] plugin-load=""Verify installationmysql> SHOW PLUGINS\G ... *************************** 38. row ***************************   Name: audit_log Status: ACTIVE   Type: AUDIT Library: License: GPL 38 rows in set (0.00 sec)Configuration Let’s see variables provided by the Percona MySQL Audit Plugin:mysql> show global variables like 'audit%'; +--------------------------+--------------+ | Variable_name            | Value        | +--------------------------+--------------+ | audit_log_buffer_size    | 1048576      | | audit_log_file           | audit.log    | | audit_log_flush          | OFF          | | audit_log_format         | OLD          | | audit_log_policy         | ALL          | | audit_log_rotate_on_size | 0            | | audit_log_rotations      | 0            | | audit_log_strategy       | ASYNCHRONOUS | +--------------------------+--------------+ 7 rows in set (0.00 sec)The Percona MySQL Audit Plugin can log using the memory buffer to deliver better performance. Messages will be written into memory buffer first and then flushed to file in background. A certain amount of events can be lost in case of server crash or power outage. Another option is to log directly to file without using memory buffer. There is also an option to fsync every event.Set audit_log_strategy to control log flushing:ASYNCHRONOUS log using memory buffer, do not drop events if buffer is fullPERFORMANCE log using memory buffer, drop events if buffer is fullSEMISYNCHRONOUS log directly to file, do not fsync every eventSYNCHRONOUS log directly to file, fsync every eventaudit_log_buffer_size specifies the size of memory buffer, it makes sense only for ASYNCHRONOUS and PERFORMANCE strategy.Variable audit_log_file specifies the file to log into. It’s value can be path relative to datadir or absolute path.The Percona MySQL Audit Plugin can automatically rotate log file based on size. Set audit_log_rotate_size to enable this feature. File is rotated when log grew in size to specified amount of bytes. Set audit_log_rotations to limit the number of log files to keep.It is possible to log only logins or only queries by setting audit_log_policy value.Log file format Lets see how audit records look likeOLD format (audit_log_format = OLD):<AUDIT_RECORD  "NAME"="Connect"  "RECORD"="2_2014-04-21T12:34:32"  "TIMESTAMP"="2014-04-21T12:34:32 UTC"  "CONNECTION_ID"="1"  "STATUS"="0"  "USER"="root"  "PRIV_USER"="root"  "OS_LOGIN"=""  "PROXY_USER"=""  "HOST"="localhost"  "IP"=""  "DB"="" />NEW format (audit_log_format = NEW):<AUDIT_RECORD>  <NAME>Connect</NAME>  <RECORD>17481_2014-04-21T12:39:03</RECORD>  <TIMESTAMP>2014-04-21T12:39:05 UTC</TIMESTAMP>  <CONNECTION_ID>4</CONNECTION_ID>  <STATUS>0</STATUS>  <USER>root</USER>  <PRIV_USER>root</PRIV_USER>  <OS_LOGIN></OS_LOGIN>  <PROXY_USER></PROXY_USER>  <HOST>localhost</HOST>  <IP></IP>  <DB>test</DB> </AUDIT_RECORD>The difference is that the audit record in the OLD format was written as a single element with attributes, while in the NEW format it is written as a single element with sub-elements.A good idea of what each sub-element means can be found in Audit Plugin API documentation here: Lets compare performance of different audit_log_strategy modes. I used readonly sysbench on my laptop for it. Workload is CPU-bound with dataset fit in buffer pool and I set number of sysbench threads to the amount for which count of transactions per seconds is maximum.I got TPS drop for PERFORMANCE and ASYNCHRONOUS strategies around 7%, 9% for SEMISYNCHRONOUS and 98% for SYNCHRONOUS which shows that syncing every logged statement to disk is not the best thing for performance. Conclusion Of course any software has bugs and this plugin has plenty of them. Please give it a try and provide us your feedback. Report any issues here: post Introduction to the Percona MySQL Audit Log Plugin appeared first on MySQL Performance Blog.
  • MySQL & NoSQL – Best of Both Worlds. Upcoming webinar 
    On Thursday 22nd May I’ll be hosting a webinar explaining how you can get the best from the NoSQL world while still getting all of the benefits of a proven RDBMS. As always the webinar is free but please register here. There’s often a lot of excitement around NoSQL Data Stores with the promise of simple access patterns, flexible schemas, scalability and High Availability. The downside can come in the form of losing ACID transactions, consistency, flexible queries and data integrity checks. What if you could have the best of both worlds? This webinar shows how MySQL Cluster provides simultaneous SQL and native NoSQL access to your data, with a simple key-value API (Memcached), REST, JavaScript, Java or C++. You will hear how the MySQL Cluster architecture delivers in-memory real-time performance, 99.999% availability, on-line maintenance and linear, horizontal scalability through transparent auto-sharding. This is also an opportunity to pick the brains of the MySQL Cluster engineering team and get your technical questions answered. Times: Thu, May 22: 09:00 Pacific time (America) Thu, May 22: 10:00 Mountain time (America) Thu, May 22: 11:00 Central time (America) Thu, May 22: 12:00 Eastern time (America) Thu, May 22: 13:00 São Paulo time Thu, May 22: 16:00 UTC Thu, May 22: 17:00 Western European time Thu, May 22: 18:00 Central European time Thu, May 22: 19:00 Eastern European time Thu, May 22: 21:30 India, Sri Lanka Fri, May 23: 00:00 Singapore/Malaysia/Philippines time Fri, May 23: 00:00 China time Fri, May 23: 01:00 日本 Fri, May 23: 02:00 NSW, ACT, Victoria, Tasmania (Australia) Even if you can’t join the live webinar, it’s worth registering as you’ll be emailed a link to the replay as soon as it’s available.
  • Benchmark: SimpleHTTPServer vs pyclustercheck (twisted implementation) 
    Github user Arianlzt provided a python-twisted alternative version of pyclustercheck per discussion on issue 7.Due to sporadic performance issues noted with the original implementation in SimpleHTTPserver, the benchmarks which I’ve included as part of the project on github use mutli-mechanize library,cache time 1 sec2 x 100 thread pools60s ramp up time600s total durationtesting simulated node fail (always returns 503, rechecks mysql node on cache expiry)AMD FX(tm)-8350 Eight-Core ProcessorIntel 330 SSDlocal loop back test ( SimpleHTTPServer instance faired as follows: Right away we can see around 500TPS throughput, however as can be seen in both response time graphs there are “outlying” transactions, something is causing the response time to spike dramatically  SimpleHTTPServer, how does the twisted alternative compare? (note the benchmarks are from the current HEAD with features re-added to avoid regression, caching and ipv6 support)  Ouch! We appear to have taken a performance hit, at least in terms of TPS -19% rough estimates however compare the response time graphs to find a much more consistent plot, we had outliers hitting near  70s for SimpleHTTP server, we’re always under 1s within twisted.Great! So why isn’t this merged into the master branch as the main project and therfor bundled with Percona XtraDB Cluster (PXC)? The issue here is the required version of python-twisted; ipv6 support was introduced in issue 8 by user Nibler999 and to avoid regression I re-added support for ipv6 in this commit for twistedipv6 support for python-twisted is not in the version distributed to main server OS’s such asEL6 python-twisted 8.xUbuntu 10.04 LTS python-twisted 11.xWhat’s the issue here? Attempting to bind / listen to an ipv6 interface yields the following error: twisted.internet.error.CannotListenError: Couldn't listen on :::8000: [Errno -9] Address family for hostname not supported.Due to this regression (breaking of ipv6 support) the twisted version can not at this time be merged into master, the twisted version however as can be seen from above is much more consistent and if you have the “cycles” to implement it (e.g. install twisted from pypy via pip / easy_install to get >= 12.x) and test it’s a promising alternative.To illustrate this further the benchmark was made more gruling:5 x 100 thread pools60s ramp up600s total durationFirst the twisted results, note the initial spike is due to a local python issue where it locked up creating a new thread in multi-mechanize: Now the SimpleHTTPServer results: Oh dear, as the load increases clearly we get some stability issues inside SimpleHTTP server…Also worth noting is the timeoutstwisted: grep 'timed out' results.csv | wc -l == 0SimpleHTTPServer: grep 'timed out' results.csv | wc -l == 470 … in the case of increased load the twisted model performs far more consistently under the same test conditions when compared against SimpleHTTPServer. I include the multi-mechanize scripts as part of the project on GitHub – as such you can recreate these tests yourself and gauge the performance to see if twisted or SimpleHTTP suits your needs.The post Benchmark: SimpleHTTPServer vs pyclustercheck (twisted implementation) appeared first on MySQL Performance Blog.
  • Archival and Analytics - Importing MySQL data into Hadoop Cluster using Sqoop 
    May 16, 2014 By Severalnines We won’t bore you with buzzwords like volume, velocity and variety. This post is for MySQL users who want to get their hands dirty with Hadoop, so roll up your sleeves and prepare for work. Why would you ever want to move MySQL data into Hadoop? One good reason is archival and analytics. You might not want to delete old data, but rather move it into Hadoop and make it available for further analysis at a later stage.    In this post, we are going to deploy a Hadoop Cluster and export data in bulk from a Galera Cluster using Apache Sqoop. Sqoop is a well-proven approach for bulk data loading from a relational database into Hadoop File System. There is also Hadoop Applier available from MySQL labs, which works by retrieving INSERT queries from MySQL master binlog and writing them into a file in HDFS in real-time (yes, it applies INSERTs only).   We will use Apache Ambari to deploy Hadoop (HDP 2.1) on three servers. We have a clustered Wordpress site running on Galera, and for the purpose of this blog, we will export some user data to Hadoop for archiving purposes. The database name is wordpress, we will use Sqoop to import the data to a Hive table running on HDFS. The following diagram illustrates our setup: The ClusterControl node has been installed with an HAproxy instance to load balance Galera connections and listen on port 33306.   Prerequisites   All hosts are running CentOS 6.5 with firewall and SElinux turned off. All servers’ time are using NTP server and synced with each other. Hostname must be FQDN or define your hosts across all nodes in /etc/hosts file. Each host has been configured with the following host definitions: clustercontrol haproxy mysql mysql1 galera1 mysql2 galera2 mysql3 galera3 hadoop1 hadoop2 hadoop3   Create an SSH key and configure passwordless SSH on hadoop1 to other Hadoop nodes to automate the deployment by Ambari Server. In hadoop1, run following commands as root: $ ssh-keygen -t rsa # press Enter for all prompts $ ssh-copy-id -i ~/.ssh/id_rsa $ ssh-copy-id -i ~/.ssh/id_rsa $ ssh-copy-id -i ~/.ssh/id_rsa   read more