Percona XtraDB Cluster (PXC) was released over a year ago and since then there has been tremendous interest and adoption.  There’s plenty of talks that explain the fundamentals of PXC, but we’re starting to reach a threshold where it’s easier to find folks with PXC in production and such the need for more advanced talks has arisen.

As such, I wanted to shift gears from the standard introductory talk and focus instead more on some key questions/issues/pain-points for those with PXC in production already.   As such, I’m giving a webinar entitled Percona XtraDB Cluster Operations on June 26th 2013 from 10-11AM PDT.  Topics will include:

• Backups from the cluster
• Avoiding SST
• Flow Control
• What and How to Monitor
• Tuning best practices

This webinar is not meant to necessarily be exhaustive, but to cover key topics that administrators of PXC will commonly ask about.  You can register for the webinar here.

The post MySQL Webinar: Percona XtraDB Cluster Operations, June 26 appeared first on MySQL Performance Blog.

The aim of this post is to enumerate real-world usage of Percona XtraDB Cluster (PXC), and also to solicit use cases from the readers. One of the prominent usages in the production environment that we have come across (and our Percona consultants have assisted) is that of HP Cloud. There is a post about it here by Patrick Galbraith of HP. The post focuses on their deployment of PXC for HP Cloud DNS. The post focuses on the key aspects of synchronous replication setup with high-availability guarantees like split-brain immunity.

Nobody likes to debug async replication while its broken or do the master-master/master-slave switchover when master is dying/dead. Yes, there are wrappers/scripts around this to make life easier, however, wouldn’t it be nice if this was built into the system itself? PXC based on Galera strives to provide that. Scaling makes sense only when addition/removal of hosts from a cluster or a HA setup is simple and uncomplicated.

Their post focuses on following aspects:

• Initial setup
• Setup of other nodes with SST (Xtrabackup SST)
• Integration of chef with PXC
• Finally, integration of HAProxy as a loadbalancer.

To elucidate, their initial setup goes into bootstrapping the first node. Note that in the cloud environment other nodes are not known until they are brought up, hence bootstrapping with an empty gcomm:// is done for the first node by the chef. The second node is then added which SSTs with node1 (based on gcomm://node1 of node2) through Xtrabackup SST (state snapshot transfer). Node3 subsequently joins the cluster with node1 and node2 in its gcomm:// (since by this time node1, node2 are up). After this, a subsequent run of chef-client is done to update the cnf files with IP address of members (excluding itself). The rationale behind this is that when a node is restarted (and there are others when it comes up) it joins the cluster seamlessly. I would like to note here that we are adding a bootstrap parameter to PXC so that any latter modifications like these to cnf files are not required and preset it during cluster startup itself. The only caveat is that the node information – IP address or hostname – should be known in advance (the node itself needn’t be up), which may not be feasible in a cloud environment.

Next, the SST. Xtrabackup SST is used there. SST matters a lot because not only is it used during initial node setup but also it is required when a node has been down for a while and IST (incremental state transfer) is not feasible. It also helps when node data integrity is compromised. So, naturally duration of SST is paramount. We recommend Xtrabackup SST for its reduced locking period from its use (which means the donor is blocked for a shorter while). By using Xtrabackup for SST, you also get its benefits like compression, parallel streaming, encryption, compact backups which can be used for SST (Note, the wsrep_sst_xtrabackup in 5.5.30 can’t do those except parallel, the one in 5.5.31 will handle them all, also XB 2.1 is required for most).

Finally, the HAProxy. HAProxy is one of the loadbalancers recommended for use with PXC. The other one is glb. HAProxy is used with xinetd on the node along with a script which checks PXC for its sync status. As referenced in that post, you can refer a post by Peter Boros (“Percona XtraDB Cluster reference architecture with HaProxy“) for details. In their setup they have automated this with a HAProxy in each AZ (Availability Zone) for the API server. To add, we are looking at reducing the overhead here, through steps like replacing xinetd and clustercheck with a single serving process (we are adding one in 5.5.31), looking for optimizations with HAProxy to account for high connection rates, and using pacemaker with PXC. The goal is to reduce the overhead of status checks, mainly on the node. You can also look this PLMCE talk for HAProxy deployment strategies with PXC.

To conclude, it is interesting to note that they have been able to manage this with a small team. That strongly implies scalability of resources – you scale more with less, and that is how it should be. We would like to hear from you about your architectural setup around PXC – any challenges you faced (and horror stories if any), any special deployment methodologies you employed (Puppet, Chef, Salt, Ansible etc. ), and finally any suggestions.

The post Percona XtraDB Cluster (PXC) in the real world: Share your use cases! appeared first on MySQL Performance Blog.

If you attended last April’s Percona Live MySQL Conference and Expo in Santa Clara, you know it was a rare opportunity to learn from some of the most accomplished system architects and developers in the business. Now it’s time to give back.

The call for papers (CFP) is now open for Percona Live London, November 11-12 at the Millenium Gloucester Conference Center. Login or register as speaker and submit your proposals.

Your participation is an opportunity to make a difference. After all, the conference would be meaningless without great content. So step up and volunteer to present your unique insights into MySQL and MySQL-related technologies either as a keynote, tutorial or breakout session.

Tell us your war story: What have you experienced while actively using MySQL, Percona Server, MariaDB, Drizzle or (fill in the blank) in production? Are you managing multiple environments? Are you working with MySQL or its variants at the code level? Share your perspective about the internals or explain how specific features work in depth. These stories are gold! And you owe it to the MySQL community to share them.

Don’t worry about being fancy. Some of the most popular talks have been on simple topics done well. Many people come to the conferences to learn — and we need content for MySQL newcomers as well as the veterans.

We’re also looking for sponsors. This is the showcase event for Europe’s growing MySQL community of developers, business managers, technology evangelists, DBAs and entrepreneurs. Download the prospectus and take a closer look at the benefits of sponsorship.

Questions? Let me know in the comments section. I look forward to hearing from you!

The post Call for papers: Percona Live London appeared first on MySQL Performance Blog.

We’re less than a week away from Percona MySQL University at Portland, Oregon next Monday, June 17. The latest in a series of FREE one-day educational events, we are pleased to feature 10 technical talks by members of Team Percona as well as local members of the MySQL Community:

• Vadim Tkachenko, Percona co-Founder and CTO, will talk about Percona XtraDB Cluster, Percona Xtrabackup and Performance and Improvements and new Features in MySQL 5.6
• Garrick Peterson, a member of Percona’s RemoteDBA team, will talk about MHA for MySQL High Availability in the Cloud
• Matt Yonkovit, our VP of Consulting, will explain how to work with BigData effectively
• Justin Swanhart, our Senior MySQL Instructor, will continue the topic of BigData with a talk titled, “Conquering BigData with MySQL and Shard Query“
• Daniel Nichter, lead of the Percona Toolkit development team, will survey Percona Toolkit Command Line Tools – as well as go into more detail about using pt-table-checksum tool to verify replication integrity
• And finally, Andrew Ferlitsch will share with how MySQL can be used to provide access to government data.

The daylong event will be held at Portland State University’s Smith Memorial Student Union, located at 1825 SW Broadway, Suite 327/8/9 Portland, Oregon 97201. Afterward, we’ll have a networking reception at the famed Paddy’s Bar and Grill sponsored by Tag1 Consulting featuring great networking possibilities and free drinks for event attendees.

If you’re in the Portland area and work with MySQL, then this is an event you can’t afford to miss…   So register now!

Please also join the Portland MySQL Meetup group for more MySQL-focused events in Portland

If you love the ideal of Percona MySQL University and would like us to bring the event to your city, please let us know!

The post Percona MySQL University @Portland next Monday! appeared first on MySQL Performance Blog.

MySQL can be deployed in several ways, and that means you can choose a tailor-made path that best meets your needs. With simple services or development systems, many people are using a single server with some backups configured, and then simply take the downtime when a restore is needed.

As the application evolves, additional requirements will appear like hot backups, online schema changes, replication based high availability (which has some caveats). Also, because of asynchronous replication, you can end up having inconsistent data on the nodes and encounter replication errors, which you have to deal with.

Even later, when asynchronous or single-threaded replication is not enough, people can use Percona XtraDB Cluster, which has some of its own caveats, but you can also gain a lot from write set replication.

If the application gets really popular, sooner or later you have to think about write scalability, and implement sharding ultimately.

I will give a talk on this topic at next month’s RAMP conference in Budapest, Hungary. My presentation will provide an overview of some possible deployment and scaling scenarios, when it makes sense to use one or an other, and common pitfalls we typically see in our consulting practice. Please let me know if you plan on attending – and also feel free to post questions in advance below.

The post How people are using MySQL… from 1 user to 100 million (upcoming conference talk) appeared first on MySQL Performance Blog.

Percona Server for MySQL version 5.1.69-14.7

Percona is glad to announce the release of Percona Server 5.1.69-14.7 on June 10, 2013. A drop in replacement for MySQL, downloads are available  here and from the Percona Software Repositories. Based on MySQL 5.1.69, this release will include all the bug fixes in it. All of Percona’s software is open-source and free, all the details of the release can be found in the 5.1.69-14.7 milestone at Launchpad.

Bugs Fixed:

• In Ubuntu Precise libmysqlclient18 package was chosen from the distribution’s repository instead of Percona’s which could lead to package conflicts. Bug fixed #1174271.
• Fixed the RPM Percona-Server-shared-compat package naming issue that could lead to unresolved package dependencies when installing Percona Server 5.1. Bug fixed #893860.
• The log tracker thread was unaware of the situation when the oldest untracked log records are overwritten by the new log data. In some corner cases this could lead to assertion errors in the log parser or bad changed page data. Bug fixed #1108613.
• Percona Server wouldn’t start if the XtraDB changed page tracking was enabled and variable innodb_flush_method was set to ALL_O_DIRECT. Bug fixed #1131949.
• Fixed the RPM package dependencies for different major versions of Percona Server. Bug fixed #1167109.
• Fixed the CVE-2012-5627 vulnerability, where an unprivileged MySQL account owner could perform brute-force password guessing attack on other accounts efficiently. This bug fix comes originally from MariaDB (see MDEV-3915). Bug fixed #1172090.
• OpenSSL libraries were not found in 32-bit builds due to a typo. Bug fixed #1175447.
• Query to the INNODB_CHANGED_PAGES table would cause server to stop with an I/O error if a bitmap file in the middle of requested LSN range was missing. Bug fixed #1179974.
• Server would crash if an INNODB_CHANGED_PAGES query is issued that has an empty LSN range and thus does not need to read any bitmap files. Bug fixed #1184427.
• Incorrect schema definition for the User Statistics tables in INFORMATION_SCHEMA (CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS, THREAD_STATISTICS, and USER_STATISTICS) led to the maximum counter values being limited to 32-bit signed integers. Fixed so that these values can be 64-bit unsigned integers now. Bug fixed #714925.
• mysql_set_permission was failing on Debian due to missing libdbd-mysql-perl package. Fixed by adding the package dependency. Bug fixed #1003776.
• XtraDB changed page tracking used to hold the log system mutex for the log reads needlessly, potentially limiting performance on write-intensive workloads. Bug fixed #1171699.
• Missing path separator between the directory and file name components in a bitmap file name could stop the server starting if the innodb_data_home_dir variable didn’t have the path separator at the end. Bug fixed #1181887.
• A warning is now returned if a bitmap file I/O error occurs after an INNODB_CHANGED_PAGES query started returning data to indicate an incomplete result set. Bug fixed #1185040.
• Fixed the upstream bug #69379 which caused MySQL clients to return bogus error number for host-not-found errors on Ubuntu 13.04. Bug fixed #1186690.
• Under very rare circumstances, deleting a zero-size bitmap file at the right moment would make server stop with an I/O error if changed page tracking is enabled. Bug fixed #1184517.
• The INNODB_CHANGED_PAGES table couldn’t be queried if the log tracker wasn’t running. Bug fixed #1185304.

Other bug fixes: bug fixed #1174346, bug fixed #1160951, bug fixed #1079688, bug fixed #1132412, bug fixed #1153651.

Release notes for Percona Server for MySQL 5.1.69-17.4 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.1.69-14.7 now available: A drop in replacement for MySQL appeared first on MySQL Performance Blog.

Recently, I was working with a client that asked about using Percona Xtrabackup to take a snapshot of a particular MySQL schema and then reload it with a different schema name on the same server.  It caught me off guard because I’d never really thought about it – typically, I’ve used Xtrabackup simply to clone a server (for replication or migration) or migrate an existing schema to a new server.  However, given the import/export functionality of Xtrabackup combined with Percona Server (Exporting and Importing Tables), it did seem possible.

Further discussion with the client clarified the use case: clients make a mistake and need to compare their old data to their current data on the current live server.  Mysqldump works well for this, but can quite slow on larger schemas.

One of the downsides to mysqldump is the need to scan the full tables and in turn, load that data into and pollute the buffer pool.  Note that this can be somewhat mitigated using innodb_old_blocks_time, but that is outside the scope of this post.  Similarly, reloading the data will be a very IO intense operation (redo logs, binlogs, etc) as well further polluting the buffer pool.  Enter Xtrabackup…

As a quick refresher, Xtrabackup works by copying the dirty tablespace files while streaming the redo-logs to ensure that all transactions are also captured.  The –apply-logs phase simply utilizes the built in crash recovery and applies the redo-logs to the dirty tablespace and voila, you have a consistent binary backup at a point in time.

When running Percona Server, you can utilize the –export flag during the –apply-logs phase and then re-import those files to a running server.  However, you need to have existing table structures in place.  Fortunately, this can be done easily using mysqldump –no-data.

Now, enough with the theory, here is the procedure I used:

1. Locate an existing snapshot that contains the schema you are interested in (/tmp/snapshot/2013-06-03_11-30/orig)
2. Get the table structures:  mysqldump –no-data orig > /tmp/orig.schema.sql
3. Create the new target database:  mysqladmin create orig_old
4. Load the schema into the target database:  mysql orig_old < /tmp/orig.schema.sql
5. Ensure innodb_import_table_from_xtrabackup = 1  (dynamic variable)
6. Prepare the backup using the –export flag:  innobackupex –apply-log –export /tmp/snapshot/2013-06-03_11-30
7. For each table, run:  ALTER TABLE tblname DISCARD TABLESPACE
8. Copy the .exp and .ibd files from the snapshot to the new instance:  cp /tmp/snapshot/2013-06-03_11-30/orig/*[.exp|.ibd] /var/lib/mysql/orig_old
9. Make sure that the files are owned by mysql: chown mysql:mysql /var/lib/mysql/orig_old/*
10. For each table, run: ALTER TABLE tblname IMPORT TABLESPACE

Now, you have a old version of the schema running side by side with the current version on the same server.  This will allow you to compare and restore values (potentially corrupted via user error or other issues) with SQL rather than needing to import from a remote server.  This can allow for more targeted restores, easier comparison, and allow remote users to compare on a live system without needed to grant access to another “backup” server.

As noted by one of my colleagues (thanks Bill Karwin!), the cumbersome part of this process is the DISCARD/IMPORT TABLESPACE step as that is done manually for each table (currently a blueprint in innobackupex).  He also included this helpful script to generate all of those statements for steps 7 and 10 in two scripts:

For full disclosure, here are the versions I was using on my test VM:

• Percona Server 5.5.30
• Xtrabackup 2.0.6

Some other use cases that come to mind for this technique would be:

• Targeted schema migration (to new schema name) using –include=”^orig[.]“ (i.e. functional partitioning)
  • Combine with –replicate-rewrite-db
• Daily backups from production to staging with different db names (i.e. orig_prod restored to orig_staging)
• I’m sure there are others as well…

The post Migrating between MySQL schemas with Percona Xtrabackup appeared first on MySQL Performance Blog.

Thanks to everyone who was in attendance on 05 June 2013 for my “Choosing a MySQL HA Solution” webinar. If you weren’t able to make it but are interested in listening to the presentation, it’s currently up and available for viewing over at percona.com.

My apologies if we weren’t able to get to your question during the initial session, so I’ll address those lingering questions in this post, along with providing a bit more detail on some of the questions that I did cover during the session.

Q: What is the reason that I recommended DRBD be used only on physical hardware and not on virtual machines?
A: I covered this a bit during the session, but to provide a bit more commentary. There are really two main reasons that I don’t like DRBD with virtual machines. First is the disk IO performance hit that comes with DRBD. When you run a virtual machine, in most cases your virtual disk is basically nothing more than a file on the host filesystem rather than an actual physical volume. So, in effect, you’re adding performance hit to performance hit. The other reason is because virtual machines tend to handle process scheduling and timing much differently than traditional hardware, and I’ve seen situations where even under just a moderate load on the host system, DRBD can end up in a split-brain situation. Typically DRBD is run in combination with heartbeat or pacemaker or something similar, and if one of the servers in the DRBD pair is having issues receiving packets from the other side, things start to degrade.

If you’re just setting up a test environment to get familiar with the technology, certainly there’s nothing wrong with a virtual-machine approach, but for the optimal production deployment you want two identical physical boxes with at least 4 NIC ports. Why 4? Two of those NICs should be directly connected from one machine to another and configured with Linux network interface bonding in balance-rr mode; this is the only NIC bonding mode which will allow you to stripe a TCP connection over multiple ports, and with a two-NIC bonded pair you’ll get roughly 1.67x the throughput of a single port. [Don't try more than 2; the additional work that the kernel has to do in reordering TCP packets can actually result in performance that's worse than a single NIC.] The other two NIC ports should be bonded with active-backup or LACP (depending on your switching infrastructure). In essence, you’re looking for two of everything.

Q: Is it possible to replicate only some tables from master to slave, and if so, how?
A: Yes. There are a few of ways to do it. One way is to set up replication filters on the slave, as described in the MySQL manual. You can configure the slave to only replicate a specific database, a set of databases, or specific tables; you can also configure the slave to replicate everything EXCEPT a specified set of databases and tables. With this method, every event is still written to the binary log on the master, but the slave determines what to do with it. The other approach is to filter what gets written to the binary log on the master. This can be useful if you’re trying to limit the amount of data sent over the wire, but it comes at the cost of having binary logs which are no longer complete or useful for point-in-time recovery. Generally if you’re considering the binary log filtering approach, I think it’s safer to set up a relay master (it can be on the same machine if you use the BLACKHOLE storage engine) in between the actual master and the slaves at the bottom of the replication topology. On the top-level master, you leave all filters disabled; on the relay master you add binary log filters; this ensures that the top-level master and its binary logs are fully intact, and then the binary log filters are executed on the intermediate server, thus resulting in less binary log data being sent down to the lower-level slaves. You can also use the slave_compressed_protocol option in /etc/my.cnf if bandwidth is a concern.

If you’re thinking about employing a filtering solution of any sort and it’s something you’re not that familiar with, I’d suggest reading over the manual’s description of how replication filtering rules are processed. There are some informative flowcharts in addition to the textual explanation.

Q: What is my recommendation for MySQL 5.6? Is MHA ready for MySQL 5.6?
A: My personal recommendation for MySQL 5.6, based on what I have seen so far (admittedly, not that much – I have personally only worked with one customer that’s using 5.6), is that I question its readiness. There have been some unfortunate performance regressions compared to 5.5, such as bug #69258, and bug #69318, and I think it might take one or two more point releases before we can consider it truly production-ready. I suppose I should state an obvious disclaimer here in that the aforementioned statements reflect only my opinion and not any official Percona position, and I will look forward to being able to retract them.

MHA should work fine with 5.6 if you’re not using GTID-based replication. If you are using GTID-based replication, it looks like you’ll need to wait for the next release of MHA.

Q: How do you monitor MySQL to determine when it’s not responsive?
A: The easiest way to do this is to simply connect to it and attempt to run a simple query. I’d suggest doing this as a user that does NOT have the SUPER privilege; a user with SUPER will always be able to connect, and that may not accurately report what your application servers are seeing. But, if you can connect and run a simple query and receive a response back in a reasonable (what is reasonable is determined by your environment), it’s a safe bet that MySQL is up and running.

That said, there are plenty of other MySQL and OS-level status variables that you might want to keep an eye on to prevent a problem before it occurs. For example, if you see a large number of long-running queries in the output of SHOW PROCESSLIST that might be a sign that trouble is brewing. Or if you watch your MySQL server’s memory usage and you see that it’s starting to swap, that might indicate that something is up. FWIW, solutions like MHA and PRM simply attempt to connect and run a simple query, but it’s always possible to build something more involved if that’s what you need.

Q: What MySQL HA solution is most similar to Oracle RAC?
A: Oracle RAC is a “shared everything” system; there really isn’t anything quite like that in the MySQL world, except maybe MyISAM files on top of a clustered filesystem with an external lock manager. You might say that MySQL/NDB Cluster is also somewhat similar to Oracle RAC; all of the SQL nodes in a MySQL Cluster installation are going to be talking to the same set of data nodes on the back end, but I think that’s probably where the similarities end.

Q: What kind of replication issues can you get with storage solutions like GlusterFS?
A: I have to admit that this was probably the most interesting question asked; my initial reaction was, honestly, why would you ever want to use MySQL on top of something like that? I can’t imagine the performance being all that fantastic, and I could see network latency wreaking havoc with MySQL’s internal understanding of what’s happening on the system below. Having never experimented with GlusterFS, though, I decided to give it a shot. I set up a 3 node cluster in AWS, got the volume mounted, and went to try to install MySQL. It failed miserably on the mysql_install_db process.

Every time I tried, I got some variant of this message:
130606 2:22:41 [ERROR] /usr/libexec/mysqld: Incorrect information in file: ‘./mysql/servers.frm’
ERROR: 1033 Incorrect information in file: ‘./mysql/servers.frm’

Running mysql_install_db under strace, I saw that it was getting a lot of “bad file descriptor” errors. I was able to get the server up with –skip-grant-tables and insert a few rows into an InnoDB table, but trying to run a simple mysqlslap just hung on me, so that’s where I left it. Maybe I’ll try messing around with this again at some point in the future, but I’m not optimistic that this is a viable use case. If someone can prove me wrong, I’d be interested in knowing how you’ve set it up.

The post Choosing a MySQL HA Solution – Post-Webinar Q&A appeared first on MySQL Performance Blog.

Alexei Rodriguez presenting at PLMCE13

During the Percona Live MySQL Community Event this year we had the pleasure of Alexei Rodriguez discussing why Evernote uses Percona’s Private Custom MySQL Training. Alexei is VP of operations there and you can watch a video of his presentation on YouTube.

Alexei said he values having Percona tailor training to their needs, the expert delivery by our practitioner-trainers, and the ability to speak freely about operational issues during private training.

If you would like to talk with our team about private custom training, please contact Sally Martinez at (855) 55TRAIN or
(925) 271-5054, or via e-mail at sally.martinez@percona.com.

The post Private, custom Percona MySQL Training appeared first on MySQL Performance Blog.

Now that June has arrived it is time to plan what you will do over the summer months. In addition to your summer vacation plans, give thought to MySQL training for you and your team.

Summer is the time to brush up on those critical skills needed to ensure all systems are ready for the holiday shopping season.

In addition to our revised courses, that I talked about in a previous post, we are also running our new Moving to MySQL 5.6 class. This class covers new features in MySQL 5.6, migration planning, and application verification. This class was designed with the experienced MySQL DBA in mind–so it is a fast paced 2-day course.

Percona has a packed summer MySQL training schedule. In June we have:

• Moving to MySQL 5.6 – Austin, TX

  Begins Monday, June 10, 2013 , 9am – 5pm

• Moving to MySQL 5.6 – San Jose, CA

  Begins Thursday, June 13, 2013 , 9am – 5pm

• New York City, New York, USA

  Begins Monday, June 17, 2013 , 9am – 5pm Eastern

• Moving to MySQL 5.6 – Manchester, UK

  Begins Monday, June 17, 2013 , 9am – 5pm

In July we have:

• Moving to MySQL 5.6 – Utrecht, Netherlands

  Begins Monday, July 1, 2013 , 9am – 5pm

• London, United Kingdom

  Begins Monday, July 15, 2013 , 9am – 5pm BST

• Moving to MySQL 5.6 – Orlando, FL

  Begins Monday, July 15, 2013 , 9am – 5pm

• Moving to MySQL 5.6 – Salt Lake City, UT

  Begins Thursday, July 18, 2013 , 9am – 5pm

• Boston, Massachusetts, USA

  Begins Monday, July 22, 2013 , 9am – 5pm

• Moving to MySQL 5.6 – Belfast, IE
• Begins Thursday, August 1, 2013 , 9am – 5pm

In August we have:

• Moving to MySQL 5.6 – Miami, FL
• Begins Monday, August 5, 2013 , 9am – 5pm
• Moving to MySQL 5.6 – Minneapolis, MN

  Begins Thursday, August 8, 2013 , 9am – 5pm

• San Diego, California, USA

  Begins Monday, August 19, 2013 , 9am – 5pm

• Frankfurt, Germany

  Begins Monday, August 19, 2013 , 9am – 5pm

We have a 10% discount code for use when ordering, register early and save even more as the 10% discount can be applied to the early registration price.  Just use discount code mpb10 when checking to receive the discount.

The post Summertime Percona MySQL training update appeared first on MySQL Performance Blog.

I’ve seen a lot of people setting up clusters with 3-6+ nodes on 1 Gbps networks.  1 Gbps seems like a lot, doesn’t it?  Actually, maybe not as much as you think.  While the theoretical limit of 1Gbps is actually 120MBps, I start to get nervous around 100MBps. By default Galera uses unicast TCP for replication.  Because synchronous replication needs to replicate to all nodes at once, this means 1 copy of your replication message is sent to other node in the cluster.  The more nodes in your cluster, the more the bandwidth required for replication multiplies. Now, this isn’t really much different from standard mysql replication.  1 master with 5 async slaves is going to send a separate replication stream to each, so your bandwidth requirements will be similar.  However, with async replication you have the luxury of not blocking the master from taking writes if bandwidth is constrained and the slaves lag for a bit, not so in Galera. So, let’s see this effect in action.  I have a simple script that outputs the network throughput on an interface every second.  I’m running a sysbench test on one node and measuring the outbound (UP) bandwidth on that same node:

This isn’t much traffic in my puny local VMs, but you get the idea.  We can clearly see some factor in play adding the extra nodes.

One way to address this bandwidth constraint is to switch to multicast UDP replication in Galera.  This is actually really easy to do. First, we need to make sure our environment will support multicast.  This is a question for your network guys and beyond the scope of this post, but in my trivial VM environment, I just need to make sure that the multicast address space routes to my Galera replication interface, eth1:

In that space, we pick an unused mcast address (again, talk to your network guys).  I’m using 239.192.0.11, so we’ll add this to our my.cnf:

If you already have wsrep_provider_options set, add it to the semicolon separated list instead of a separate line in your config. If we already have a running cluster, we need to shut it down, configure our mcast address and re-bootstrap it:

We can see that a multicast node still needs to bind to the Galera replication port, and of course that needs to be bound to the interface that  the multicast will be received on.

Now, let’s re-do our above test:

So, we can see our outbound bandwidth on our master node doesn’t change as we add more nodes when we are using multicast.

We can also also bootstrap nodes using the mcast address:

And this works fine.  Pretty slick! Note that IST and SST will still use TCP unicast, so we still want to make sure those are configured to use the regular IP of the node. Typically I just set the wsrep_node_address setting on each node if this IP is not the default IP of the server. I could not find a way to migrate an existing unicast cluster to multicast with a rolling update.  I believe (but could be proven wrong) that you must re-bootstrap your entire cluster to enable multicast.

The post Multicast replication in Percona XtraDB Cluster (PXC) and Galera appeared first on MySQL Performance Blog.

Lately I have been working with a set of customers on a longer term basis which has given me time to explore new tools using their environments.  One tool that I am finding very helpful is called SchemaSpy.

SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. It’s also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints.

One of the things that I like about  ERD tools is that I can quickly generate a graphic representation of the database to see which tables are referenced the most, and which are candidates for dropping (why keep old data if you don’t need to?).  I get up to speed a lot faster and can contribute to the Development process a lot more effectively when I know the relationships between tables versus observing queries only.  An additional benefit is that SchemaSpy is command-line driven and builds the html after each run, so I find it convenient to set it up on crontab so that schema changes are automatically picked up.  Nice, eh?

Usage of this tool is very straightforward and it does a lot of the work for you.  Basically if your database has Foreign Keys then you’re laughing, if not don’t despair, you just have a bit more work ahead of you.  Most of the time SchemaSpy “does the right thing” but sometimes you need to give it a little help in the form of metadata files.

• Download the latest copy of SchemaSpy jar file (5.0.0 at time of writing)
• Make sure you have latest copy of Java JRE for your platform
• You will need a Java driver for your database — I’m using the MySQL Connector/J
• Install the graphiz package

While not explicitly required, I prefer to create this once so that the  command line is shorter and neater.  In my case here is the properties file in use for my Percona Server 5.6.10 sandbox:

Notice that in the case of child_A there is an implicit FK relationship to parent via parent_id, however in child_B it is explicit.  This has an impact on how SchemaSpy identifies the relationship, and whether you need to provide a hint or not.

When you run schemaSpy without a metadata file, you will see that the relationship between parent and child_B is properly displayed:

Table child_A can be made to display if you tag the Implied relationships box, but this generally only works if you have used the exact same name (in this case, parent_id) in the child and parent tables.  Your environment may be like a lot of shops that we see, where the parent table is known as id, and on the child tables they reference it as parent_id — this might make sense to Developers but it doesn’t help SchemaSpy at all.  This is where Metadata files come in to play

The format of the metadata file is XML-based.  I have included the metadata file I used to properly link child_A to parent table:

If you’re looking for other examples you can find a more comprehensive example here.

The syntax below if specific for MySQL, but note that SchemaSpy works for pretty much all the popular RDBMS out there.

The -o directive tells SchemaSpy where to write the output to, make sure this is a directory that can then be accessed by a web server.  Also note that since I created the mysql.properties file in the same directory as the jar file, I don’t need to pass any host:port or schema name information.

A completed Relationships view of these three tables should look like this:

While I find the Relationships tab the most useful component of SchemaSpy, I don’t want to leave you with the impression that this is the only component of the tool.  There are additional tabs:

• Tables – Names, number of children, parents, count of columns, row counts, and comments — a great way for a high level overview of the table sizes and an easy way to use the search feature of your browser to zero in on a particular table
• Constraints – lists the explicit Foreign Key constraints in the database (this does not include constraints identified via metadata files!)
• Anomalies – Identifies possible relationships between columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!), single column tables, incrementing column names in tables, and tables with the string NULL instead of the actual SQL NULL value. This is basically a quick sanity check of your schema for any significant errors or items requiring review.
• Columns – a listing of all columns in the schema, really handy to sort by name to see if you have any implicit constraints that you might have missed and can then write into your metadata file.
• Donate – This is free software and John Currier asks for donations so he can justify the time spent maintaining SchemaSpy to his wife

Finally don’t forget to automate SchemaSpy via crontab once you’re done.

What are some ERD tools you use and how do they compare with SchemaSpy?  Feel free to answer via the comments.  Thanks for reading!

The post Implementing SchemaSpy in your MySQL environment appeared first on MySQL Performance Blog.

Percona Server for MySQL version 5.6.11-60.3

Percona is glad to announce the first Release Candidate release of Percona Server 5.6.11-60.3 on June 3rd, 2013 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.6.11, including all the bug fixes in it, Percona Server 5.6.11-60.3 is the first RC release in the Percona Server 5.6 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.6.11-60.3 milestone at Launchpad.

This release contains all the bug fixes from latest Percona Server 5.5 release (currently Percona Server 5.5.31-30.3).

New Features:

• Percona Server has implemented Log Archiving for XtraDB. Currently this feature implementation is considered ALPHA quality.
• Complete list of the ported features can be seen in Percona Server 5.6.11-60.3 release notes.

Bugs Fixed:

• Transaction objects are now allocated calling calloc() directly instead of using InnoDB heap allocation. This may improve write performance for high levels of concurrency. Bug fixed #1185686.
• Under very rare circumstances, deleting a zero-size bitmap file at the right moment would make server stop with an I/O error if changed page tracking is enabled. Bug fixed #1184517.
• Missing path separator between the directory and file name components in a bitmap file name could stop the server starting if the innodb_data_home_dir variable didn’t have the path separator at the end. Bug fixed #1181887.
• Changed page tracking used to hold the log system mutex for the log reads needlessly, potentially limiting performance on write-intensive workloads. Bug fixed #1171699.
• Incorrect schema definition for the User Statistics tables in INFORMATION_SCHEMA (CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS, THREAD_STATISTICS, and USER_STATISTICS) led to the maximum counter values being limited to 32-bit signed integers. Fixed so that these values can be 64-bit unsigned integers now. Bug fixed #714925.
• Server would crash if an INNODB_CHANGED_PAGES query is issued that has an empty LSN range and thus does not need to read any bitmap files. Bug fixed #1184427.
• Query to the INNODB_CHANGED_PAGES table would cause server to stop with an I/O error if a bitmap file in the middle of requested LSN range was missing. Bug fixed #1179974.
• A warning is now returned if a bitmap file I/O error occurs after an INNODB_CHANGED_PAGES query started returning data to indicate an incomplete result set. Bug fixed #1185040.
• The INNODB_CHANGED_PAGES table couldn’t be queried if the log tracker wasn’t running. Bug fixed #1185304.
• Fixed the upstream bug #68970 that, in Percona Server, would cause small tablespaces to expand too fast around 500KB tablespace size. Bug fixed #1169494.
• Fixed the RPM package dependencies issues. Bug fixed #1186831.
• Reduced the overhead from the Handle Corrupted Tables check as it was missing branch predictor annotations. Bug fixed #1176864.

Other bug fixes: bug fixed #1184695, bug fixed #1184512, bug fixed #1183585, bug fixed #1178606, bug fixed #1177356, bug fixed #1160895, bug fixed #1182876, bug fixed #1180481, bug fixed #1163135, bug fixed #1157078, bug fixed #1182889, bug fixed #1133926, bug fixed #1165098, bug fixed #1182793, bug fixed #1157075, bug fixed #1183625, bug fixed #1155475, bug fixed #1157037, bug fixed #1182065, bug fixed #1182837, bug fixed #1177780, bug fixed #1154954.

Release notes for Percona Server for MySQL 5.6.11-60.3 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.6.11-60.3 first Release Candidate now available appeared first on MySQL Performance Blog.

On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions.  I answered as many as we had time for  during the session, but here are all the questions with my complete answers:

Q: Can you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)?

For performance, it’s hard to make a general rule, because there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw in my presentation that sometimes these perform worse and sometimes better than another equivalent solution.

If you’re talking about using a temporary table to store an interim result set, and then use that to ultimately produce the result you want, that’s another technique that I see used, although it’s less common.  It can be helpful to simplify the development of a report, but whether it’s a benefit to performance is a case-by-case decision.

There are other drawbacks to using temporary tables in this way, for example when using replication, a temporary table could vanish if the slave restarts and then subsequent queries against the temp table fail.

Q: Why doesn’t the query optimizer take care the execution decisions? Since they are all logically the same, I believe it should all translate to the same execution plan. (I know it doesn’t though.)

Why don’t you try writing a SQL query optimizer and see how easy it is?  ;-)  Generative grammars like SQL can produce an infinite variety of  queries.  The query optimizer analyzes queries for common patterns the MySQL designers know can be improved.  But there’s a finite amount of engineer-years spent developing and testing the query optimizer.  The developers have to make a decision about which query patterns the optimizer recognizes, and which are left to the developer to refactor manually.  There could also be some cases where optimizing a query automatically would be more costly than just running the query in a suboptimal form.

Q: Doesn’t the primary key solution for random selection only work when the IDs for movies are distributed uniformly over the range 1..MAX(id)?

Yes, there’s a risk if you have irregular distribution of matching rows that you’ll get skewed results.  For instance, if there’s a gap of id’s between 47000 and 50000 with no movies, but your random number generator picks values in that range with equal frequency as any other range, then the movie immediately following the “gap” will be picked more frequently.

Nearly every solution for randomly choosing rows involves some compromise, either of performance, or of accuracy of randomness.  The ORDER BY RAND() solution is known to have poor performance, but it returns a better random choice.

Another workaround may be to add a column to the table, and populate the rows you want to choose (movies, in this case) with  values known to be consecutive.  Then you could choose a random value, look up the row with “=” instead of “>” and be guaranteed to find exactly one match.  But the tradeoff of this solution is that it requires storing another column, and reinitializing the sequence after making certain insert/update/delete operations.  If you have a dataset that changes infrequently, then this might be a good tradeoff.

Q: Is the tuple comparison independent of sorting order? Wouldn’t it be a problem if the keywords were returned in a different order from what you specify in the query?

The tuple comparison you’re referring to is this example:

This is okay because each correlation name (k1, k2, k3) is an index lookup.  Look at the EXPLAIN output — it accesses these first, by looking up the keywords you specify.  It doesn’t matter what order you specify the keywords in this tuple, it is equivalent to this expression:

The AND operator is commutative, so the order of these terms doesn’t matter.

However, if I were using the tuple syntax to look up values against multiple columns in a compound index, then the order would matter, and it would have to match the order of columns in the index.  Example:

Q: On the Dynamic Pivot, the straight join, can you explain more about why you thought about doing that and why it helped?

When I did not use the STRAIGHT_JOIN, the query optimizer reordered the tables.  It seemed to prefer an index-scan of 7 rows in the `kind_type` table to be first, then look up matching rows in `title` by a secondary index.  But the result was that it created a temporary table to count the movies per production year for each kind_id.

It was more efficient in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order.  This made the first table in the EXPLAIN seem like it was scanning more rows.  But by avoiding the temporary table, and making lookups to the `kind_types` table by primary key, the result was a query that took half the time.

An important conclusion of my presentation today is that sometimes you find unexpected differences in performance like this, so it pays to test all different solutions, and measure the resulting performance!

Q: Bill, back to the query where you were trying to determine the last episode of each TV show. Couldn’t you have used a SUBQUERY to fetch and compare MAX(episode_id) as an option there?

Yes, I think what you’re referring to is what I covered as the Derived-Table Solution on slides 55-59.  Apologies if that wasn’t clear, because I didn’t present the details of all the tables and columns.  It turned out this solution was 150x faster for this case, so your suggestion is a good one!

Thanks again for attending my webinar!  Here are some more tips:

• Check out upcoming Percona Training classes in North America and Europe.
• Join Percona and the MySQL community at our events.  The next one is in London on November 11-12, 2013. We also look forward to the Percona Live MySQL Conference and Expo in Santa Clara, California starting March 31, 2014.
• Watch more webinars from Percona in the future!

The post MySQL Query Patterns, Optimized – Webinar questions followup appeared first on MySQL Performance Blog.

Here at the MySQL Performance Blog, we’ve been discussing the several new features that MySQL 5.6 brought: GTID-based replication, InnoDB Fulltext, Memcached integration, a more complete performance schema, online DDL and several other InnoDB and query optimizer improvements. However, I plan to focus on a series of posts on the small but handy improvements – changes and bug corrections – in MySQL 5.6 that can make our lives easier and have passed almost unnoticed by most (not all) DBAs.

Duplicate Index Detection

I commented about this on my last webinar, but did not have time to analyze it in-depth.  If you try to do something like this in MySQL 5.5, you will succeed without errors or warnings:

In previous versions of MySQL, you can create two indexes with the same columns (in the same order) and the server will not complain.

If we execute the same sentences in MySQL 5.6, the second ALTER will also succeed -and the index will be created-, but we will get a warning (note severity, to be exact):

As the message points correctly, this is a human mistake, as it is a waste of resources that could potentially impact our performance, and should be avoided. By the way, a good practice to avoid this is always naming your keys with a consistent pattern. This new behavior was introduced in 5.6.7 with the closing of this bug (although this was initially accepted as a bug as early as 2005!).

The report explains more in detail what the “will be disallowed in a future release” means. In MySQL 5.7 the checks will be stricter: in the default SQL mode, a duplicate index will throw a warning instead of a note. In strict mode, it will throw an error and the second ALTER will fail, preventing the creation of the duplicate index.

Does it mean that tools like pt-duplicate-key-checker will not be necessary for MySQL 5.6? Let’s have a look at the code implementing this feature. The warning will only be thrown if the index has not been created automatically, it is not a foreign key, and it has the exact column definition in the same order. In other words, it checks for duplicate keys, but not redundant ones. What is the difference? Let’s see an example. If we execute:

We get no warnings and no errors in 5.6, as the indexes are different. But as you may know, we can use the second index not only for filtering on both columns, but also for filtering by just the first one. Why can’t MySQL enforce this kind of constraints? For many reasons: the first one because it would break 99% of all applications out there that use MySQL, for which we at Percona tend to find redundant indexes. And second, because in some cases, we may need to have what at first seems redundant indexes but effectively they are not -for example, if one of the two indexes was unique or a foreign key.

This is the output of our tool when run on the same table, correctly identifying the redundancy:

Additionally, pt-duplicate-key-checker will detect subtle redundancies that are engine-dependent, like redundant suffixes for secondary keys in InnoDB. As some of this redundancies could be necessary, depending on the query optimizer and the MySQL version, we always recommend to check manually the optimizations proposed by Percona Toolkit. The MySQL server, of course, cannot risk to block directly all cases.

A set of MySQL utilities were introduced by Oracle recently, which includes mysqlindexcheck, similar to pt-duplicate-key-checker, but it does not detect all cases. For example:

By the way, if you want to get more familiar with this and other particularities of the latest MySQL GA release, have a look at our upcoming sessions for the “Moving to 5.6″ training course in America (Austin, San Jose) and Europe (Manchester, Utrecht).

The post The small improvements of MySQL 5.6: Duplicate Index Detection appeared first on MySQL Performance Blog.

The main benefit of using GTIDs is to have much easier failover than with file-based replication. We will see how to change the replication topology when using GTID-based replication. That will show where GTIDs shine and where improvements are expected.

This is the second post of a series of articles focused on MySQL 5.6 GTIDs. You can find part one here.

Our goal will be to go from setup #1 to setup #2 on the picture below, following various scenarios:

For these tests, all servers are running on 127.0.0.1 with ports ranging from 10000 for s0 to 10004 for s4.

This is the easiest case, we will make s2 a master and redirect replication on the other servers to s2. This scenario can happen when you want to perform a planned failover.

With GTIDs, all the operations are straightforward:

Those of you who have already done these operations with file-based replication know that it is usually very tedious and that proper recording of binlog file/binlog position needs to be done with care if you don’t want to break replication or corrupt your data.

Now let’s imagine that s0 has crashed, and that s1 has not received all writes (and therefore s3 and s4 are also lagging behind).

Can we still use master_auto_position = 1? Let’s hope so, as it is one of the ideas of GTIDs: having for each event across the cluster a monotonically incremental identifier for each event.

Notice that this is the same problem for s0 (which will be late when it comes back) and s1, s3 and s4.

Let’s give it a try!

Great! So again, using GTIDs avoids the tedious work of looking for the binlog position of a specific event. The only part were we should pay attention is the server we choose for promotion: if it is not up-to-date, data may be lost or replication may be broken.

If the binary logs of the master are no longer readable, you will probably lose the events that have not been sent to the slaves (your last chance is to be able to recover data from the crashed master, but that’s another story). In this case, you will have to promote the most up-to-date slave and reconfigure the other slaves as we did above.

So we will suppose that we can read the binary logs of the crashed master. The first thing to do after choosing which slave will be the new master is to recover the missing events with mysqlbinlog.

Let’s say that we want to promote s1 as the new master. We need to know the coordinates of the last event executed:

We can see that it’s not obvious to know which was the last executed event: is it 219be3a9-c3ae-11e2-b985-0800272864ba:1 or 3d3871d1-c3ae-11e2-b986-0800272864ba:4 ? A ‘Last_Executed_GTID’ column would have been useful.

In our case we can check that 3ec18c45-c3ae-11e2-b986-0800272864ba is the server UUID of s2, and that the other one is from s0 (for s0 which is crashed, the server UUID can be read in the auto.cnf file in the datadir).

So the last executed event is 219be3a9-c3ae-11e2-b985-0800272864ba:1. How can I instruct mysqlbinlog to start reading from there? Unfortunately, there is no --start-gtid-position option or equivalent. See bug #68566.

Does it mean that we cannot easily recover the data with mysqlbinlog? There is a solution of course, but very poor in my opinion: look for the binlog file/position of the last executed event and use mysqlbinlog with the good old --start-position option! Even with GTIDs, you cannot totally forget old-style replication positioning.

Reconfiguring replication when using GTIDs is usually straightforward: just connect the slave to the correct master with master_auto_position = 1. This can even be made easier with mysqlfailover from the MySQL Utilities (this will be the topic of a future post).

Unfortunately, this will not work for every use case, and until this is fixed, it is good to be aware of the current limitations.

The post Replication in MySQL 5.6: GTIDs benefits and limitations – Part 2 appeared first on MySQL Performance Blog.

If you are using Percona Replication Manager (PRM) with RHEL/CentOS prior to 6.4, upgrading your distribution to 6.4 may break your cluster. In this post I will explain you how to fix your cluster in case it breaks after a distribution upgrade that implies an update of pacemaker from 1.1.7 to 1.18. You can also follow the official documentation here.

The version of Pacemaker (always considered as Technology Preview by RedHat) provided with 6.4 is 1.1.8-x which is not 100% compatible with 1.1.7-x see this report.

So if you want to upgrade, you cannot apply any rolling upgrade process. So like for Pacemaker 0.6.x to 1.0.x, you need again to update all nodes as once. As notified in RHBA-2013-0375, RedHat encourages people to use Pacemaker in combination with the CMAN manager (It may become mandatory with the next release).

CMAN v3 is a Corosync plugin that monitors the names and number of active cluster nodes in order to deliver membership and quorum information to clients (such as the Pacemaker daemons) and it’s part of the RedHat cluster stack. If you were using some puppet recipes published previously here you are not yet using CMAN.

Let’s have look at what happens if we have a cluster with 3 nodes (CentOS 6.3) and using PRM as OCF:

[root@percona1 percona]# crm_mon -1
============
Last updated: Thu May 23 08:04:30 2013
Last change: Thu May 23 08:03:41 2013 via crm_attribute on percona2
Stack: openais
Current DC: percona1 – partition with quorum
Version: 1.1.7-6.el6-148fccfd5985c5590cc601123c6c16e966b85d14
3 Nodes configured, 3 expected votes
7 Resources configured.
============

Online: [ percona1 percona2 percona3 ]

reader_vip_1 (ocf::heartbeat:IPaddr2): Started percona3
reader_vip_2 (ocf::heartbeat:IPaddr2): Started percona2
reader_vip_3 (ocf::heartbeat:IPaddr2): Started percona1
writer_vip (ocf::heartbeat:IPaddr2): Started percona1
Master/Slave Set: ms_MySQL [p_mysql]
Masters: [ percona2 ]
Slaves: [ percona3 percona1 ]

[root@percona1 ~]# cat /etc/redhat-release
CentOS release 6.3 (Final)
[root@percona1 ~]# rpm -q pacemaker
pacemaker-1.1.7-6.el6.x86_64
[root@percona1 ~]# rpm -q corosync
corosync-1.4.1-7.el6_3.1.x86_64

Everything is working
Let’s update our system to 6.4 on one server…

NOTE: In production you should put the cluster in maintenance mode before the update, see bellow how to perform this action

[root@percona1 percona]# yum update -y

[root@percona1 percona]# cat /etc/redhat-release
CentOS release 6.4 (Final)

[root@percona1 ~]# rpm -q pacemaker
pacemaker-1.1.8-7.el6.x86_64
[root@percona1 ~]# rpm -q corosync
corosync-1.4.1-15.el6_4.1.x86_64

Let’s reboot it…

[root@percona1 percona]# reboot

If we check the cluster from another node, we see that percona1 is now offline:

============
Last updated: Thu May 23 08:29:36 2013
Last change: Thu May 23 08:03:41 2013 via crm_attribute on percona2
Stack: openais
Current DC: percona3 – partition with quorum
Version: 1.1.7-6.el6-148fccfd5985c5590cc601123c6c16e966b85d14
3 Nodes configured, 3 expected votes
7 Resources configured.
============

Online: [ percona2 percona3 ]
OFFLINE: [ percona1 ]

reader_vip_1 (ocf::heartbeat:IPaddr2): Started percona2
reader_vip_2 (ocf::heartbeat:IPaddr2): Started percona3
reader_vip_3 (ocf::heartbeat:IPaddr2): Started percona2
writer_vip (ocf::heartbeat:IPaddr2): Started percona3
Master/Slave Set: ms_MySQL [p_mysql]
Masters: [ percona2 ]
Slaves: [ percona3 ]
Stopped: [ p_mysql:2 ]

After the update and after fixing some small issues like the one bellow, you are able to start Corosync and Pacemaker but the node doesn’t join the cluster

May 23 08:34:12 percona1 corosync[1535]: [MAIN ] parse error in config: Can't open logfile '/var/log/corosync.log' for reason: Permission denied (13).#012.

So now you need to update all nodes to Pacemaker 1.1.8 but to avoid again issues with the next distribution update, I prefer to use CMAN as recommended.

First as we have 2 nodes of 3 running, we should try to not stop all our servers… let’s put the cluster in maintenance mode (don’t forget you should have done this even before updating the first node, but I wanted to simulate the problem):

[root@percona3 percona]# crm configure property maintenance-mode=true

We can see that the resources are unmanaged:

============
Last updated: Thu May 23 08:43:49 2013
Last change: Thu May 23 08:43:49 2013 via cibadmin on percona3
Stack: openais
Current DC: percona3 – partition with quorum
Version: 1.1.7-6.el6-148fccfd5985c5590cc601123c6c16e966b85d14
3 Nodes configured, 3 expected votes
7 Resources configured.
============

Online: [ percona2 percona3 ]
OFFLINE: [ percona1 ]

reader_vip_1 (ocf::heartbeat:IPaddr2): Started percona2 (unmanaged)
reader_vip_2 (ocf::heartbeat:IPaddr2): Started percona3 (unmanaged)
reader_vip_3 (ocf::heartbeat:IPaddr2): Started percona2 (unmanaged)
writer_vip (ocf::heartbeat:IPaddr2): Started percona3 (unmanaged)
Master/Slave Set: ms_MySQL [p_mysql] (unmanaged)
p_mysql:0 (ocf::percona:mysql): Master percona2 (unmanaged)
p_mysql:1 (ocf::percona:mysql): Started percona3 (unmanaged)
Stopped: [ p_mysql:2 ]

Now we can upgrade all servers to 6.4

[root@percona2 percona]# yum -y update
[root@percona3 percona]# yum -y update

Meanwhile, we can already prepare the first node to use CMAN:

[root@percona1 ~]# yum -y install cman ccs

Back on the two nodes that were updating, they are now updated to 6.4:

[root@percona3 percona]# cat /etc/redhat-release
CentOS release 6.4 (Final)

And let’s check the cluster status:

[root@percona3 percona]# crm_mon -1
Could not establish cib_ro connection: Connection refused (111)

Connection to cluster failed: Transport endpoint is not connected…

…but MySQL is still running:

[root@percona2 percona]# mysqladmin ping
mysqld is alive

[root@percona3 percona]# mysqladmin ping
mysqld is alive

Let’s install CMAN on percona2 and percona3 too:

[root@percona2 percona]# yum -y install cman ccs
[root@percona3 percona]# yum -y install cman ccs

Then on ALL nodes, stop Pacemaker and Corosync

[root@percona1 ~]# /etc/init.d/pacemaker stop
[root@percona1 ~]# /etc/init.d/corosync stop
[root@percona2 ~]# /etc/init.d/pacemaker stop
[root@percona2 ~]# /etc/init.d/corosync stop
[root@percona3 ~]# /etc/init.d/pacemaker stop
[root@percona3 ~]# /etc/init.d/corosync stop

Remove Corosync from the startup services:

[root@percona1 ~]# chkconfig corosync off
[root@percona2 ~]# chkconfig corosync off
[root@percona3 ~]# chkconfig corosync off

Let’s specify that the cluster can start without quorum:

[root@percona1 ~]# sed -i.sed “s/.*CMAN_QUORUM_TIMEOUT=.*/CMAN_QUORUM_TIMEOUT=0/g” /etc/sysconfig/cman
[root@percona2 ~]# sed -i.sed “s/.*CMAN_QUORUM_TIMEOUT=.*/CMAN_QUORUM_TIMEOUT=0/g” /etc/sysconfig/cman
[root@percona3 ~]# sed -i.sed “s/.*CMAN_QUORUM_TIMEOUT=.*/CMAN_QUORUM_TIMEOUT=0/g” /etc/sysconfig/cman

And create the cluster, perform the following command on one server only:

[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –createcluster lefred_prm

Now add the nodes to the cluster:

[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addnode percona1
Node percona1 added.
[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addnode percona2
Node percona2 added.
[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addnode percona3
Node percona3 added.

we need then to delegate the fencing to pacemaker (adding a fence device, fence methods to specific node and the instances) :

[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addfencedev pcmk agent=fence_pcmk

[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addmethod pcmk-redirect percona1
Method pcmk-redirect added to percona1.
[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addmethod pcmk-redirect percona2
Method pcmk-redirect added to percona2.
[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addmethod pcmk-redirect percona3
Method pcmk-redirect added to percona3.

[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addfenceinst pcmk percona1 pcmk-redirect port=percona1
[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addfenceinst pcmk percona2 pcmk-redirect port=percona2
[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –addfenceinst pcmk percona3 pcmk-redirect port=percona3

Encrypt the cluster:

[root@percona1 ~]# ccs -f /etc/cluster/cluster.conf –setcman keyfile=”/etc/corosync/authkey” transport=”udpu”

Let’s check if the configuration file is OK:

[root@percona1 ~]# ccs_config_validate -f /etc/cluster/cluster.conf
Configuration validates

We can now copy the configuration file on all nodes:

[root@percona1 ~]# scp /etc/cluster/cluster.conf percona2:/etc/cluster/
[root@percona1 ~]# scp /etc/cluster/cluster.conf percona3:/etc/cluster/

Enable CMAN at startup on all nodes:

[root@percona1 ~]# chkconfig cman on
[root@percona2 ~]# chkconfig cman on
[root@percona3 ~]# chkconfig cman on

And start the services on all nodes:

[root@percona1 ~]# /etc/init.d/cman start
Starting cluster:
Checking if cluster has been disabled at boot… [ OK ]
Checking Network Manager… [ OK ]
Global setup… [ OK ]
Loading kernel modules… [ OK ]
Mounting configfs… [ OK ]
Starting cman… [ OK ]
Waiting for quorum… [ OK ]
Starting fenced… [ OK ]
Starting dlm_controld… [ OK ]
Tuning DLM kernel config… [ OK ]
Starting gfs_controld… [ OK ]
Unfencing self… [ OK ]
Joining fence domain… [ OK ]
[root@percona1 ~]# /etc/init.d/pacemaker start
Starting cluster:
Checking if cluster has been disabled at boot… [ OK ]
Checking Network Manager… [ OK ]
Global setup… [ OK ]
Loading kernel modules… [ OK ]
Mounting configfs… [ OK ]
Starting cman… [ OK ]
Waiting for quorum… [ OK ]
Starting fenced… [ OK ]
Starting dlm_controld… [ OK ]
Tuning DLM kernel config… [ OK ]
Starting gfs_controld… [ OK ]
Unfencing self… [ OK ]
Joining fence domain… [ OK ]
Starting Pacemaker Cluster Manager: [ OK ]

[root@percona2 ~]# /etc/init.d/cman start
[root@percona2 ~]# /etc/init.d/pacemaker start
[root@percona3 ~]# /etc/init.d/cman start
[root@percona3 ~]# /etc/init.d/pacemaker start

We can now connect crm_mon to the cluster and check its status:

[root@percona2 percona]# crm_mon -1
Last updated: Thu May 23 09:18:58 2013
Last change: Thu May 23 09:16:31 2013 via crm_attribute on percona1
Stack: cman
Current DC: percona1 – partition with quorum
Version: 1.1.8-7.el6-394e906
3 Nodes configured, 3 expected votes
7 Resources configured.

Online: [ percona1 percona2 percona3 ]

reader_vip_1 (ocf::heartbeat:IPaddr2): Started percona3
reader_vip_2 (ocf::heartbeat:IPaddr2): Started percona2
reader_vip_3 (ocf::heartbeat:IPaddr2): Started percona1
writer_vip (ocf::heartbeat:IPaddr2): Started percona1
Master/Slave Set: ms_MySQL [p_mysql]
Masters: [ percona1 ]
Slaves: [ percona2 percona3 ]

We can see that some resources changed this is because we didn’t put it in maintenance on node1 before the update to 6.4

In case we put everything in maintenance mode as it should be before the upgrade to 6.4, it’s time to stop the maintenance mode… but crm command is not present any more

It’s still possible to find the command install crmsh (crm shell from another repository) or just install pcs (Pacemaker Configuration System)

[root@percona2 percona]# yum -y install pcs
[root@percona2 percona]# pcs status
Last updated: Thu May 23 09:24:37 2013
Last change: Thu May 23 09:16:31 2013 via crm_attribute on percona1
Stack: cman
Current DC: percona1 – partition with quorum
Version: 1.1.8-7.el6-394e906
3 Nodes configured, 3 expected votes
7 Resources configured.

Online: [ percona1 percona2 percona3 ]

Full list of resources:

reader_vip_1 (ocf::heartbeat:IPaddr2): Started percona3
reader_vip_2 (ocf::heartbeat:IPaddr2): Started percona2
reader_vip_3 (ocf::heartbeat:IPaddr2): Started percona1
writer_vip (ocf::heartbeat:IPaddr2): Started percona1
Master/Slave Set: ms_MySQL [p_mysql]
Masters: [ percona1 ]
Slaves: [ percona2 percona3 ]

So if you were in maintenance mode, you should have :

[root@percona2 percona]# pcs status
Last updated: Thu May 23 09:26:56 2013
Last change: Thu May 23 09:26:50 2013 via cibadmin on percona2
Stack: cman
Current DC: percona1 – partition with quorum
Version: 1.1.8-7.el6-394e906
3 Nodes configured, 3 expected votes
7 Resources configured.

Online: [ percona1 percona2 percona3 ]

Full list of resources:

reader_vip_1 (ocf::heartbeat:IPaddr2): Started percona3 (unmanaged)
reader_vip_2 (ocf::heartbeat:IPaddr2): Started percona2 (unmanaged)
reader_vip_3 (ocf::heartbeat:IPaddr2): Started percona1 (unmanaged)
writer_vip (ocf::heartbeat:IPaddr2): Started percona1 (unmanaged)
Master/Slave Set: ms_MySQL [p_mysql] (unmanaged)
p_mysql:0 (ocf::percona:mysql): Master percona1 (unmanaged)
p_mysql:1 (ocf::percona:mysql): Slave percona2 (unmanaged)
p_mysql:2 (ocf::percona:mysql): Slave percona3 (unmanaged)

And now you are able to stop maintenance mode:

[root@percona2 percona]# pcs property set maintenance-mode=false

You can also check your cluster using cman_tool or clustat (if you have installed rgmanager)

[root@percona3 ~]# cman_tool nodes
Node Sts Inc Joined Name
1 M 64 2013-05-23 09:52:03 percona1
2 M 64 2013-05-23 09:52:03 percona2
3 M 64 2013-05-23 09:52:03 percona3

[root@percona3 ~]# clustat
Cluster Status for lefred_prm @ Thu May 23 10:20:36 2013
Member Status: Quorate

Member Name ID Status
—— —- —- ——
percona1 1 Online
percona2 2 Online
percona3 3 Online, Local

Now the cluster is fixed and everything works again as expected and you should be ready for the next distro upgrade!

INFO: If you have the file /etc/corosync/service.d/pcmk you need to delete it before installing CMAN

The post How to fix your PRM cluster when upgrading to RHEL/CentOS 6.4 appeared first on MySQL Performance Blog.

Selecting the most appropriate solution for a MySQL HA infrastructure is as much a business and philosophical decision as it is a technical one, but often the choice is made without adequately considering all three perspectives.  When too much attention is paid to one of these aspects at the cost of the others, the resulting system may be over-engineered, poorly-performing, and/or various other flavors of suboptimal.

On Wednesday, June 5, at 10 a.m. PDT (1700 UTC), I will be presenting a webinar entitled, Choosing a MySQL HA Solution, in which we’ll explore the topic of MySQL HA from each of these perspectives.  The goal will be to motivate your thinking about HA in a holistic fashion and help guide you towards asking the right questions when considering a new or upgraded HA deployment.

This webinar will be both technical and non-technical in nature, beginning with a discussion of some general HA principles and some common misconceptions.  We will then explore some of the more well-known MySQL HA tools and technologies available today (largely grouped into those which use traditional MySQL replication, those which use some other MySQL-level replication, and those which replicate at some other layer of the system stack) and then conclude with some typical use cases where a given approach may be well-suited or particularly contraindicated.

If this topic interests you, then register today to reserve your spot.  I look forward to speaking with all of you next week.

The post Choosing a MySQL HA Solution – MySQL Webinar: June 5 appeared first on MySQL Performance Blog.

Percona Server for MySQL version 5.5.31-30.3

Percona is glad to announce the release of Percona Server for MySQL 5.5.31-30.3 on May 24, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.31, including all the bug fixes in it, Percona Server 5.5.31-30.3 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.31-30.3 milestone at Launchpad.

New Features:

• Percona Server has ported the Atomic write support for Fusion-io devices patch from MariaDB. This feature adds atomic write support for directFS filesystem on Fusion-io devices. This feature implementation is considered BETA quality.
• Percona Server has introduced innodb_read_views_memory and innodb_descriptors_memory status variables in the Extended Show Engine InnoDB Status to improve InnoDB memory usage diagnostics.

Bugs Fixed:

• Fix for bug #1131187 introduced a regression that could cause a memory leak if query cache was used together with InnoDB. Bug fixed #1170103.
• Fixed the RPM packaging regression that was introduced with the fix for bug #710799. This regression caused mysql schema to be missing after the clean RPM installation. Bug fixed #1174426.
• Fixed the Percona-Server-shared-55 and Percona-XtraDB-Cluster-shared RPM package dependences. Bug fixed #1050654.
• Fixed the upstream bug #68999 which caused compiling Percona Server to fail on CentOS 5 and Debian squeeze due to older OpenSSL version. Bug fixed #1183610.
• If a slave was running with its binary log enabled and then restarted with the binary log disabled, Crash-Resistant Replication could overwrite the relay log info log with an incorrect position. Bug fixed #1092593.
• Fixed the CVE-2012-5615 vulnerability. This vulnerability would allow remote attacker to detect what user accounts exist on the server. This bug fix comes originally from MariaDB (see MDEV-3909). Bug fixed #1171941.
• Fixed the CVE-2012-5627 vulnerability, where an unprivileged MySQL account owner could perform brute-force password guessing attack on other accounts efficiently. This bug fix comes originally from MariaDB (see MDEV-3915). Bug fixed #1172090.
• mysql_set_permission was failing on Debian due to missing libdbd-mysql-perl package. Fixed by adding the package dependency. Bug fixed #1003776.
• Rebuilding Debian source package would fail because dpatch and automake were missing from build-dep. Bug fixed #1023575 (Stephan Adig).
• Backported the fix for the upstream bug #65077 from the MySQL 5.6 version, which removed MyISAM internal temporary table mutex contention. Bug fixed #1179978.

Release notes for Percona Server for MySQL 5.5.31-30.3 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server for MySQL 5.5.31-30.3 now available appeared first on MySQL Performance Blog.

I am currently working with a large customer and I am involved with servers located in two data centers, one with Solaris servers and the other one with Linux servers. The Solaris side is cleverly setup using zones and ZFS and this provides a very low virtualization overhead. I learned quite a lot about these technologies while looking at this, thanks to Corey Mosher.

On the Linux side, we recently deployed a pair on servers for backup purpose, boxes with 64 300GB SAS drives, 3 raid controllers and 192GB of RAM. These servers will run a few slave instances each of production database servers and will perform the backups.  The write load is not excessive so a single server can easily handle the write load of all the MySQL instances.  The original idea was to configure them with raid-10 + LVM, making sure to stripe the LV when we need to and align the partition correctly.

We got decent tpcc performance, nearly 37k NoTPM using 5.6.11 and xfs.  Then, since ZFS on Linux is available and there is in house ZFS knowledge, we decided to reconfigure one of the server and give ZFS a try.  So I trashed the raid-10 arrays, configure JBODs and gave all those drives to ZFS (30 mirrors + spares + OS partition mirror) and I limited the ARC size to 4GB.  I don’t want to start a war but ZFS performance level was less than half of xfs for the tpcc test and that’s maybe just normal.  We didn’t try too hard to get better performance because we already had more than enough for our purpose and some ZFS features are just too useful for backups (most apply also for btrfs). Let’s review them.

Snapshots

ZFS does snapshot, like LVM but… since it is a copy on write filesystem, the snapshots are free, no performance penalty.  You can easily run a server with hundreds of snapshots.  With LVM, your IO performance drops to 33% after the first snapshot so keeping a large number of snapshots running is simply not an option.  With ZFS you can easily have:

• one snapshot per day for the last 30 days
• one snapshot per hour for the last 2 days
• one snapshot per 5min for the last 2 hours

and that will be perfectly fine.  Since starting a snapshot take less than a second, you could even be more zealous.  Pretty interesting to speed up point in time recovery when you dataset is 700GB.  If you google a bit with “zfs snapshot script” you’ll many scripts ready for the task.  Snapshots work best with InnoDB, with MyISAM you’ll have to start the snapshot while holding a “flush tables with read lock” and the flush operation will take some time to complete.

Compression

ZFS can compress data on the fly and it is surprisingly cheap.  In fact the best tpcc results I got were when using compression.  I still have to explain this, maybe it is related to better raid controller write cache use.  Even the fairly slow gzip-1 mode works well.  The tpcc database, which contains a lot of random data that doesn’t compress well showed a compression ration of 1.70 with gzip-1.  Real data will compress much more.  That gives us much more disk space than we expected so even more snapshots!

Integrity

With ZFS each record on disk has a checksum.  If a cosmic ray flip a bit on a drive, instead of crashing InnoDB, it will be caught by ZFS and the data will be read from the other drive in the mirror.

Better availability and disk usage

On purpose, I allocated mirror pairs using drives from different controllers.  That way, if a controller dies, the storage will still be working.  Also, instead of having 1 or 2 spare drives per controller, I have 2 for the whole setup.  A small but yet interesting saving.

All put together, ZFS on Linux is a very interesting solution for MySQL backup servers.  All backup solutions have an impact on performance with ZFS the impact is up front and the backups are almost free.

The post ZFS on Linux and MySQL appeared first on MySQL Performance Blog.