]]>
]]>

You are here

Latest MySQL Performance Blog posts

Subscribe to Latest MySQL Performance Blog posts feed
Percona's MySQL & InnoDB performance and scalability blog
Updated: 1 hour 21 min ago

Sneak peek at the Percona Live MySQL Conference & Expo 2015

December 4, 2014 - 5:55am

You know you’ll be there so why not save some $$ by registering now for the Percona Live MySQL Conference & Expo 2015 (April 13-16 in Santa Clara, Calif.). Super Saver registration discounts are available through Dec. 14 at 11:30 p.m. PST. (That’s just 10 days away!)

What to expect this year? The Percona Live 2015 conference committee is putting together another fantastic event for the global MySQL community’s next meeting in April. The full conference agenda will be announced in January, but the initial roster includes:

  • Sunny Bains, Senior Engineering Manager at Oracle; “InnoDB 5.7- What’s New”
  • Yoshinori Matsunobu, Database Engineer at Facebook; “Fast Master Failover Without Data Loss”
  • Jeremy Cole, Senior Systems Engineer at Google, Inc.; “Exploring Your Data With InnoDB Explorer”
  • Tom Krouper, Staff Database Administrator at Twitter; “Upgrading to MySQL 5.6 @ Scale”
  • Jenni Snyder, Database Administrator at Yelp; “Schema changes multiple times a day? OK!”
  • Ike Walker, Database Architect at Flite; “Assembling the Perfect MySQL Toolbox”
  • Jean-François Gagné, Senior System Engineer/Architect at Booking.com; “Binlog Servers at Booking.com”
  • Jeremy Glick, Lead DBA at MyDBAteam, and Andrew Moore, MySQL DBA at Percona; “Using MySQL Audit Plugins and Elasticsearch ELK”
  • Tomáš Komenda, Team Lead and Database Specialist, and Lukáš Putna, Senior Developer and Database Specialist at Seznam.cz; “MySQL and HBase Ecosystem for Real-time Big Data Overviews”
  • Alexander Rubin, Principal Consultant at Percona; “Advanced MySQL Query Tuning”

And while the call for papers deadline has expired, there are still sponsorship opportunities available for the world’s largest annual MySQL event. Sponsors become a part of a dynamic and growing ecosystem and interact with more than 1,000 DBAs, sysadmins, developers, CTOs, CEOs, business managers, technology evangelists, solutions vendors, and entrepreneurs who attend the event.

Current sponsors include:

  • Diamond Plus: VMware
  • Gold: Codership, Pythian
  • Silver: Box, SpringbokSQL, Yelp
  • Exhibit Only: FoundationDB, Severalnines, Tokutek, VividCortex
  • Other Sponsors: MailChimp
  • Media Sponsor: Database Trends & Applications , Datanami, InfoQ , Linux Journal, O’Reilly Media

Percona Live 2015 will feature a variety of formal tracks and sessions related to High Availability, DevOps, Programming, Performance Optimization, Replication and Backup, MySQL in the Cloud, MySQL and NoSQL, MySQL Case Studies, Security, and What’s New in MySQL.

As usual the conference will be held in the heart of Silicon Valley at the Hyatt Regency Santa Clara and Santa Clara Convention Center. But this year Percona has also unveiled OpenStack Live 2015, a new conference that will run in parallel with Percona Live MySQL Conference & Expo 2015 on April 13 and 14.

And don’t forget, Super Saver registration discounts are available through Dec. 14 at 11:30 p.m. PST. I hope to see you in Santa Clara!

The post Sneak peek at the Percona Live MySQL Conference & Expo 2015 appeared first on MySQL Performance Blog.

Auto-bootstrapping an all-down cluster with Percona XtraDB Cluster

December 3, 2014 - 4:01am

One new feature in Percona XtraDB Cluster (PXC) in recent releases was the inclusion of the ability for an existing cluster to auto-bootstrap after an all-node-down event.  Suppose you lose power on all nodes simultaneously or something else similar happens to your cluster. Traditionally, this meant manually re-bootstrapping the cluster, but not any more.

How it works

Given the above all-down situation, if all nodes are able to restart and see each other such that they all agree what the state was and that all nodes have returned, then the nodes will make a decision that it is safe for them to recover PRIMARY state as a whole.

This requires:

  • All nodes went down hard — that is; a kill -9, kernel panic, server power failure, or similar event
  • All nodes from the last PRIMARY component are restarted and are able to see each other again.
Demonstration

Suppose I have a 3 node cluster in a stable state. I then kill all nodes simultaneously (simulating a power failure or similar event):

[root@node1 ~]# killall -9 mysqld [root@node2 ~]# killall -9 mysqld [root@node3 ~]# killall -9 mysqld

I can see that each node maintained a state file in its datadir called ‘gvwstate.dat’. This contains the last known view of the cluster:

[root@node1 ~]# cat /var/lib/mysql/gvwstate.dat my_uuid: 78caedfe-75a5-11e4-ac69-fb694ee06530 #vwbeg view_id: 3 78caedfe-75a5-11e4-ac69-fb694ee06530 9 bootstrap: 0 member: 78caedfe-75a5-11e4-ac69-fb694ee06530 0 member: 87da2387-75a5-11e4-900f-ba49ecdce584 0 member: 8a25acd8-75a5-11e4-9e22-97412a1263ac 0 #vwend

This file will not exist on a node if it was shutdown cleanly, only if the mysqld was uncleanly terminated. This file should exist and be the same on all the nodes for the auto-recovery to work.

I can now restart all 3 nodes more or less at the same time. Note that none of these nodes are bootstrapping and all of the nodes have the wsrep_cluster_address set to a proper list of the nodes in the cluster:

[root@node1 ~]# service mysql start [root@node2 ~]# service mysql start [root@node3 ~]# service mysql start

I can indeed see that they all start successfully and enter the primary state:

[root@node1 ~]# mysql -e "show global status like 'wsrep_cluster%'" +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_conf_id | 0 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 1ba6f69a-759b-11e4-89ba-62a713a26cd1 | | wsrep_cluster_status | Primary | +--------------------------+--------------------------------------+ [root@node2 ~]# mysql -e "show global status like 'wsrep_cluster%'" +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_conf_id | 0 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 1ba6f69a-759b-11e4-89ba-62a713a26cd1 | | wsrep_cluster_status | Primary | +--------------------------+--------------------------------------+ [root@node3 ~]# mysql -e "show global status like 'wsrep_cluster%'" +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_conf_id | 0 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 1ba6f69a-759b-11e4-89ba-62a713a26cd1 | | wsrep_cluster_status | Primary | +--------------------------+--------------------------------------+

Checking the logs, I can see this indication that the feature is working:

2014-11-26 19:59:36 1809 [Note] WSREP: promote to primary component 2014-11-26 19:59:36 1809 [Note] WSREP: view(view_id(PRIM,78caedfe,13) memb { 78caedfe,0 87da2387,0 8a25acd8,0 } joined { } left { } partitioned { }) 2014-11-26 19:59:36 1809 [Note] WSREP: save pc into disk 2014-11-26 19:59:36 1809 [Note] WSREP: clear restored view

Changing this behavior

This feature is enabled by default, but you can toggle it off with the pc.recovery setting in the wsrep_provider_options

This feature helps cover an edge case where manual bootstrapping was necessary in the past to recovery properly. This feature was added in Percona XtraDB Cluster version 5.6.19, but was broken due to this bug.  It was fixed in PXC 5.6.21

The post Auto-bootstrapping an all-down cluster with Percona XtraDB Cluster appeared first on MySQL Performance Blog.

Tips from the trenches for over-extended MySQL DBAs

December 2, 2014 - 12:00am

This post is a follow-up to my November 19 webinar, “Tips from the Trenches: A Guide to Preventing Downtime for the Over-Extended DBA,” during which I described some of the most common reasons DBAs experience avoidable downtime. The session was aimed at the “over-stretched DBA,” identified as the MySQL DBA short of time or an engineer of another discipline without the depth of the MySQL system. The over-stretched DBA may be prone to making fundamental mistakes that cause downtime through poor response time, operations that cause blocking on important data or administrative mishaps through the lack of best practice monitoring and alerting. (You can download my slides and view the recorded webinar here.)

Monitor the things
One of the aides to keeping the system up and running is ensuring that your finger is on the pulse of the environment. Here on the Percona Managed Services team, we leverage Percona Monitoring Plugins (open source plugins for Nagios, Cacti and Zabbix) to ensure we have visibility of our client’s operations. Having a handle on basics such as disk space, memory usage and MySQL operational metrics ensures that we avoid trivial downtime that would affect the client’s uptime or worse, their bottom line.

Road Blocks
One of the most common reasons that an application is unable to serve data to its end user is that access to a table is being blocked due to another ongoing operation. This can be blamed on a variety of sources: backups, schema changes, poor configuration and long running transactions can all lend themselves to costly blocking. Understanding the impact of actions on a MySQL server can be the difference between a happy end user and a frustrated one.

During the webinar I made reference to some resources and techniques that can assist the over extended DBA avoid downtime and here are some highlights….

Monitoring and Alerting
It’s important that you have some indications that something is reaching its capacity. It might be the disk, connections to MySQL or auto_increment limit on a highly used table. There is quite the landscape to cover but here are a handful of helpful tools:
* Percona Monitoring Plugins
* Monyog
* New Relic

Query Tuning
Poorly performing SQL can be indicative that the configuration is incorrect, that there’s a missing index or that your development team needs a quick lesson on MySQL anti-patterns. Arm yourself with proof that the SQL statements are substandard using these resources and work with the source to make things more efficient:
* Percona Cloud Tools
* pt-query-digest, explain, indexes

High Availability
If you need to ensure that your application survives hiccups such as hardware failure or network impairment, a well deployed HA solution will give you the peace of mind that you can quickly mitigate bumps in the road.
* MHA
Percona XtraDB Cluster, Galera
* Percona Replication Manager
* LinuxHA/Corosync/DRBD

Backups
A wise man once quoted “A backup today saves you tomorrow.” Covering all bases can be the difference between recovering from a catastrophic failure and job hunting. Mixing logical, physical and incremental backups while adding in some offsite copies can provide you with the safety net in the event that a small mistake like a dropped table is met or worse, all working copies of data and backups are lost in a SAN failure. It happens so be prepared.
* Percona XtraBackup
* mydumper
* mysqldump
* mysqlbinlog (5.6)
* mylvmbackup

We had some great questions from the attendees and regrettably were unable to answer them all, so here are some of them with my response.

Q: I use MySQL on Amazon RDS. Isn’t much of the operations automated or do these tips still apply?
A: It’s not completely automated. There are still challenges to address and configuration opportunities, but understanding the limitations of RDS is key. For example, the location and size of the tmpdir is something you are unable to customise on RDS. You would typically review this config in a production environment if your workload required it. Any costly queries that perform operations requiring tmp area to sort (think OLAP) might not be a good fit on RDS due to this limitation. Getting to know the limitations around hosted or DBaaS services is time well spent to avoid explaining what keeps taking the application down in peak hours.

Q: What other parts of Percona Toolkit do you recommend for MySQL operations?
A: Percona Toolkit is a well-evolved suite of tools that all MySQL DBAs should familiarize themselves with. In particular I will fit many tools into my weekly workflow:

Operations

  • pt-online-schema-change
  • pt-table-checksum
  • pt-table-sync

Troubleshooting

  • pt-stalk
  • pt-pmp
  • pt-config-diff

Knowledge Gathering

  • pt-summary
  • pt-mysql-summary
  • pt-duplicate -key-checker

The key with Percona Toolkit is that many common tasks or problems that could cause you to reinvent the wheel are covered, mature and production ready. As with any tool, you should always read the label or in this case the documentation so you’re well aware what the tools can do, the risks and the features that you can make use of.

Q: HA – are there any solutions that you would stay away from?
A: Using any particular HA solution is going to be another R&D exercise. You will need to understand the tradeoffs, configuration options and compare between products. Some might have a higher TCO or lack functionality. Once the chosen solution is implemented it’s pertinent that the engineers understand the technology to be able to troubleshoot or utilize the functionality in the situation where failover needs to be instigated. I like HA solutions to be fast to failover to and some entail starting MySQL from cold.

Q: You mentioned having tested backups. How do you perform this?
A: Percona’s method is using a dedicated host with access to the backup files. Then with a combination of mysqlsandbox and pt-table-checksum we can discover if we trust the files we capture for disaster recovery. Many people underestimate the importance of this task.

Q: Percona Cloud Tools – how much does it cost?
A: Right now it’s a free service. Visit cloud.percona.com for more information, but in a nutshell Percona Cloud Tools is a hosted service providing access to query performance insights for all MySQL uses.

Q: Is there API access to Percona Cloud Tools for application integration?
A: There is currently not a public API available. It is on the roadmap, though. We’d be interested to hear more about your use case so please sign up for the service and try it out. After signing in, all pages include a Feedback link to share your thoughts and ideas such as how you’d like to use a public API.

Q: Can you use MHA with Percona XtraDB Cluster?
A: MHA is not something that can be used with Percona XtraDB Cluster (PXC). It’s common to partner PXC with HAProxy for making sure your writes are going to the appropriate node.

Q: Can MHA make automatic failover? If MHA has automatic failover, what do you recommend? Configure it for automatic failover?
A: MHA can make an automatic failover. Personally I prefer managed failover. When working with automated failover it’s important that failback is manual to avoid “flapping.” “Splitbrain” is an ailment that you don’t want to suffer from as well and auto failover removes the human judgment from the decision to relocate operations from a failed node onto a standby node. If you are going to vote for an automatic failover it is advised to test all potential failure scenarios and to employ a STONITH method to really ensure that the unresponsive node is not serving read/write traffic.

Q: What is the best way to detect database blocking from DML statements? Is there a tool that will show blocking after the fact so you don’t have to catch it real-time?
A: Once again, Percona has a tool called pt-deadlock-logger that can detect and log deadlocks. Detecting locking can be achieved using “SHOW ENGINE INNODB STATUS” or utilizing the information_schema.innodb_locks table. Some engineering might be required for this to be logged but those resources exist for use.

Q: Since you mentioned tinkering with ELK I was wondering if you had any tips on good Kibana dashboards to build to monitor MySQL databases/clusters?
A: ELK is something that I’m looking to publish some information on soon so watch this space!

Thanks again everyone for the great questions! And as a reminder, you can download my slides and view the recorded webinar here.

The post Tips from the trenches for over-extended MySQL DBAs appeared first on MySQL Performance Blog.

Faster restarts for MySQL and Percona Server 5.6.21+

December 1, 2014 - 4:34am

By default in MySQL 5.6, each time MySQL is started (regular start or crash recovery), it iterates through all the binlog files when GTIDs are not enabled. This can take a very long time if you have a large number of binary log files. MySQL and Percona Server 5.6.21+ have a fix with the simplified-binlog-gtid-recovery option. Let’s explore this issue.

Understanding the issue

It was first reported by Yoshinori @ Facebook (bug #69097).

Let’s start by looking at a MySQL 5.6 instance where binary logging is enabled but GTIDs are disabled.

If we restart MySQL with strace, we’ll see:

# strace -e open service mysql start [...] open("/var/lib/mysql5621/mysql-bin.000004", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000003", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000002", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13 [...]

MySQL opens all binary log files in reverse order. This can bite if you have lots of binlog files or if the binlog files are large.

This does not happen with MySQL 5.5, so why such a change? The reason is … GTIDs. If you look at the bug report, MySQL tries to initialize a few GTID-related settings even if gtid_mode = OFF

The same kind of issue happens when you have binlog files with GTIDs and binlog files without GTIDs:

# strace -e open service mysql start [...] open("/var/lib/mysql5621/mysql-bin.000010", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000002", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000003", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000004", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000005", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000006", O_RDONLY) = 13 -> First binlog file with GTIDs [...]

Actually you can see that MySQL will do 2 scans: a reverse scan and a forward scan. Here not all binlogs need to be opened: MySQL will stop scanning files as soon as it finds GTID information. But that can again bite if you have just turned on GTIDs (and therefore most binlog files do not contain any GTID information).

Now what happens if you set gtid_mode = ON from the start or if all the binlog files without any GTID information have been removed?

# strace -e open service mysql start [..] open("/var/lib/mysql5621/mysql-bin.000011", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000006", O_RDONLY) = 13 [...]

Only the newest and the oldest binlog files are opened, which is expected.

The fix

The fix is easy: simply add simplified-binlog-gtid-recovery = 1 in your configuration file. When this is set, MySQL will open at most 2 binlog files: the newest one and the oldest one. See the documentation.

Let’s see what happens with our server containing some binlog files without GTID information:

# strace -e open service mysql start [...] open("/var/lib/mysql5621/mysql-bin.000012", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13 [...]

What is the performance overhead of the binlog scans? Of course YMMV, but I did a quick test on my laptop by creating 80x 100MB binlog files: by default, startup takes 6s while with simplified-binlog-gtid-recovery=1 it only takes 2s. 3x improvement with a single setting, not bad!

Conclusion

It is good to see that regressions introduced in MySQL 5.6 are being fixed over time. This one is pretty nasty as most people not using GTIDs will never think that something related to GTIDs can actually create performance issues. Is there any drawback if you enable this setting? I can’t think of any, so I’m hoping it will be enabled by default in 5.7.

The post Faster restarts for MySQL and Percona Server 5.6.21+ appeared first on MySQL Performance Blog.

MySQL benchmarking: Know your baseline variance!

November 26, 2014 - 3:00am

Often enough I find MySQL benchmark results where the difference between results is 1% or even less and some conclusions are drawn. Now it is not that 1% is not important – especially when you’re developing the product you should care about those 1% improvements or regressions because they tend to add up. However with such a small difference it is very important to understand whenever this is for real or it is just the natural variance for your baseline test.

Take a look at this graph:
Click the image for a larger view)

 

This is the result for a simple in-memory, read-only “select by primary key” SysBench benchmark on dedicated physical hardware that is otherwise idle, simple 1 socket system. I tried to stabilize it as much as possible, for example disabling CPU frequency scaling. But still I saw some 3% difference between “good runs” and bad runs.

What is the difference between those runs? Simply mysqld restarts.

Does this mean you can’t measure a smaller difference? You can by setting the appropriate test plan. Often having several runs makes sense, in others you need to make sure the system warms up before taking measurements or having benchmark runs that are long enough. Whatever method you use it is a good idea to apply your test methodology by conducting several runs of your baseline run to ensure the results are stable enough for your purpose. For example If I decided to do five 30-minute runs and average the results, if they all run within 0.1% I will consider 0.3% differences as meaningful.

Another practical trick that often helps me to separate real differences from some side effects is mixing the tests. Say if I have configurations I’m testing A and B instead of doing AAA BBB I would do ABABAB which helps with the case when there is some regression that can accumulate over time, such as with Flash.

You should also note that in modern systems there is almost always something happening in the background that can change performance – the SSD is doing garbage collection, MySQL (or Kernel) is flushing dirty pages, the CPU can even simply cool off and as a result being able to support Turbo-boost operations a little longer. So when you are stat running your benchmarks make sure you keep the idle time between runs the same – scripting benchmarks and iterating scenarios helps here.

Happy MySQL benchmarking!

The post MySQL benchmarking: Know your baseline variance! appeared first on MySQL Performance Blog.

Percona XtraDB Cluster 5.6.21-25.8 is now available

November 25, 2014 - 7:45am

Percona is glad to announce the new release of Percona XtraDB Cluster 5.6 on Novmeber 25th 2014. Binaries are available from downloads area or from our software repositories. We’re also happy to announce that Ubuntu 14.10 and CentOS 7 users can download, install, and upgrade Percona XtraDB Cluster 5.6 from Percona’s software repositories.

Based on Percona Server 5.6.21-70.1 including all the bug fixes in it, Galera Replicator 3.8, and on Codership wsrep API 25.7, Percona XtraDB Cluster 5.6.21-25.8 is now the current General Availability release. All of Percona‘s software is open-source and free, and all the details of the release can be found in the 5.6.21-25.8 milestone at Launchpad.

New Features:

  • Galera 3.8 introduces auto-eviction for nodes in the cluster experiencing network issues like packet loss. It is off by default and is turned on with evs.auto_evict option. This feature requires EVS protocol version (evs.version) 1. During the EVS protocol upgrade all membership changes are communicated over EVS protocol version 0 to preserve backwards compatibility, protocol is upgraded to the highest commonly supported version when forming a new group so if there exist a single node with older version in the group, the group protocol version remains as 0 and auto-eviction is not functional. (#1274192).
  • Percona XtraDB Cluster now supports backup locks in XtraBackup SST (in the default xtrabackup-v2 wsrep_sst_method). Backup locks are used in lieu of FLUSH TABLES WITH READ LOCK on the donor during SST. This should allow for minimal disruption of existing and incoming queries, even under high load. Thus, this should allow for even faster SST and node being in ‘donor/desynced’ state. This also introduces following constraints: Percona XtraDB Cluster 5.6.21 requires Percona XtraBackup 2.2.5 or higher; An older (< 5.6.21) joiner cannot SST from a newer (>= 5.6.21) donor. This is enforced through SST versioning (sent from joiner to donor during SST) and logged to error log explicitly. (#1390552).
  • Percona XtraDB Cluster is now shipped with Galera MTR test suite.

Bugs Fixed:

  • Percona XtraDB Cluster now shows a warning in case additional utilities, like pv which may not affect critical path of SST, are not installed. Bug fixed #1248688.
  • mysqldump SST can now use username/password from wsrep_sst_auth under group of [sst] in my.cnf in order not to display the credentials in the error log. Bug fixed #1293798.
  • Normal shutdown under load would cause server to remain hanging because replayer failed to finish. Bug fixed #1358701.
  • wsrep_causal_reads variable was not honored when declared as global. Bug fixed #1361859.
  • garbd would not work when cluster address was specified without the port. Bug fixed #1365193.
  • If mysqld gets killed during the SST it will leave an unclean data directory behind. This would cause Percona XtraDB Cluster to fail when the server would be started next time because the data directory would be corrupted. This was fixed by resuming the startup in case wsrep-recover failed to recover due to corrupted data directory. The old behavior is still achievable through --exit-on-recover-fail command line parameter to mysqld_safe or exit-on-recover-fail under [mysqld_safe] in my.cnf. Bug fixed #1378578.
  • Percona XtraDB Cluster now reads environment variables for mysqld from following files (if present): /etc/default/mysql in Debian/Ubuntu; /etc/sysconfig/mysql in CentOS 6 or lower; /etc/sysconfig/mysql in CentOS 7 with mysql.service; /etc/sysconfig/XYZ in CentOS 7 with mysql@XYZ.service (/etc/sysconfig/bootstrap is supplied by default). Bug fixed #1381492.
  • gvwstate.dat file was removed on joiner when Xtrabackup SST method was used. Bug fixed #1388059.
  • Percona XtraDB Cluster now detects older joiners which don’t have the backup lock support. Bug fixed #1390552.
  • Inserts to a table with auto-increment primary key could result in duplicate key error if another node joined or dropped from the cluster during the insert processing. Bug fixed #1366997.
  • TOI now skips replication if all tables are temporary. Bugs fixed #11 and #13.
  • Bootstrapping a node tried to resolve gcomm address list specified in wsrep-cluster-address. Bug fixed #88.
  • In case stored procedure containing a non-InnoDB statement (MyISAM) performed autocommit, that commit would be entered two times: at statement end and next time at stored procedure end. Bug fixed #2.
  • Two appliers conflicting with local transaction and resulting later in (acceptable) BF-BF lock conflict, would cause cluster to hang when the other BF thread would not grant the lock back after its local transaction got aborted. Bug fixed #7.

Other bugs fixed #1378138, #1377226, #1376965, #1356859, #1026181, #1367173, #1390482, #1391634, and #1392369.

Release notes for Percona XtraDB Cluster 5.6.21-25.8 are available in our online documentation along with the installation and upgrade instructions.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

The post Percona XtraDB Cluster 5.6.21-25.8 is now available appeared first on MySQL Performance Blog.

(More) Secure local passwords in MySQL 5.6 and up

November 25, 2014 - 12:00am

I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:

[client] user=root password=secret

This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

Warning: Using a password on the command line interface can be insecure.

MySQL 5.6 introduces a utility to make this easier and more secure. First, let’s start with a new mysqld instance with a blank root password and make it more secure:

[vagrant@localhost ~]$ mysqladmin -u root password New password:secret Confirm new password:secret [vagrant@localhost ~]$ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Ok, so I’ve added a password, now I want to create my .my.cnf file:

[vagrant@localhost ~]$ mysql_config_editor set --user=root --password Enter password:secret [vagrant@localhost ~]$ mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 Server version: 5.6.21-70.0 Percona Server (GPL), Release 70.0, Revision 688 Copyright (c) 2009-2014 Percona LLC and/or its affiliates Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>

What did ‘mysql_config_editor set’ actually do? It creates a .mylogin.cnf file (which stands in for a .my.cnf) in my home directory that contains my credentials, just in encrypted form:

[vagrant@localhost ~]$ ls -alh .my* -rw-------. 1 vagrant vagrant 100 Nov 19 16:20 .mylogin.cnf -rw-------. 1 vagrant vagrant 29 Nov 19 16:20 .mysql_history [vagrant@localhost ~]$ cat .mylogin.cnf ��>NTv�&�S���/�, >ј$%KZ 9i�V�jK䉦H[��� k. [vagrant@localhost ~]$ mysql_config_editor print [client] user = root password = *****

The mysql client picks this up right away and will use it by default. This file has good default filesystem permissions, is local to my homedir, and is a whole lot better than specifying it on the command line or typing it in every time.

This utility also supports a feature called ‘login-path’ wherein you can add multiple mysql logins (perhaps to different servers) and refer to them with the —login-path option in the mysql client:

[vagrant@localhost ~]$ mysql_config_editor set --login-path=remote --host=remote --user=remote --password Enter password:secure [vagrant@localhost ~]$ mysql_config_editor print --all [client] user = root password = ***** [remote] user = remote password = ***** host = remote [vagrant@localhost ~]$ mysql --login-path=remote ERROR 2005 (HY000): Unknown MySQL server host 'remote' (0)

The ‘remote’ host doesn’t exist here, but you get the idea. You can create as many login-paths as you want with varied hostnames, credentials and other login parameters and quickly access them with any client supporting login-path.

Now, how secure is this really?  This isn’t secure from anyone who roots your DB server.  I would say the benefits are more about reducing careless password storage and tidier management of local credentials.

The post (More) Secure local passwords in MySQL 5.6 and up appeared first on MySQL Performance Blog.

Percona Server 5.6.21-70.1 is now available

November 24, 2014 - 7:28am

Percona is glad to announce the release of Percona Server 5.6.21-70.1 on November 24, 2014. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.21, including all the bug fixes in it, Percona Server 5.6.21-70.1 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free. Complete details of this release can be found in the 5.6.21-70.1 milestone on Launchpad.

Bugs Fixed:

  • A slave replicating in RBR mode would crash, if a table definition between master and slave differs with an allowed conversion, and the binary log contains a table map event followed by two row log events. This bug is an upstream regression introduced by a fix for bug #72610. Bug fixed #1380010.
  • An incorrect source code function attribute would cause MySQL to crash on an InnoDB row write, if compiled with a recent GCC with certain compilation options. Bug fixed #1390695 (upstream #74842).
  • MTR tests for Response Time Distribution were not packaged in binary packages. Bug fixed #1387170.
  • The RPM packages provided for CentOS 5 were built using a debugging information format which is not supported in the gdb version included with CentOS 5.10. Bug fixed #1388972.
  • A session on a server in mixed mode binlogging would switch to row-based binlogging whenever a temporary table was created and then queried. This switch would last until the session end or until all temporary tables in the session were dropped. This was unnecessarily restrictive and has been fixed so that only the statements involving temporary tables were logged in the row-based format whereas the rest of the statements would continue to use the statement-based logging. Bug fixed #1313901 (upstream #72475).

Other bugs fixed: #1387227, and #1388001.

Release notes for Percona Server 5.6.21-70.1 are available in the online documentation. Please report any bugs on the launchpad bug tracker

The post Percona Server 5.6.21-70.1 is now available appeared first on MySQL Performance Blog.

Schema changes in MySQL for OpenStack Trove users

November 24, 2014 - 12:00am

People using OpenStack Trove instances can hit a common issue in the MySQL world: how to perform schema change operations while minimizing the impact on the database server? Let’s explore the options that can allow online schema changes.

Summary

With MySQL 5.5, pt-online-schema-change from Percona Toolkit is your best option for large tables while regular ALTER TABLE statements are only acceptable for small tables. Also beware of metadata locks.

With MySQL 5.6, almost all types of schema changes can be done online. Metadata locks can also be an issue. pt-online-schema-change can still be worth using as it is also online on read replicas.

Regular ALTER TABLE with MySQL 5.5

If you are still using MySQL 5.5, almost all schema changes will require a table rebuild and MySQL will set a write lock. Therefore all writes to the table that gets modified will be blocked. As soon as the table gets large or if you cannot afford to have a maintenance window, using ALTER TABLE becomes tricky.

The only exception is that secondary indexes can be added or removed without rebuilding the whole table. The table is still write locked during the operation but it is much faster.

You can spot this ‘fast index creation’ process by looking at SHOW PROCESSLIST (see manage keys in the State field):

+----+------+-----------+--------+---------+------+-------------+-----------------------------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+------+-----------+--------+---------+------+-------------+-----------------------------------------+-----------+---------------+-----------+ | 1 | root | localhost | sbtest | Query | 4 | manage keys | alter table sbtest2 add index idx_k (k) | 0 | 0 | 0 | +----+------+-----------+--------+---------+------+-------------+-----------------------------------------+-----------+---------------+-----------+

It is helpful for small or even medium tables, but it is not fast enough for large tables.

A common workaround when you have a master-slave configuration is to perform the schema change on the slave first, promote the slave and then perform the schema change on the old master. However replication is not only available if you are using Icehouse and even with Juno, replicas are read-only. So this workaround is not usable with Trove.

pt-online-schema-change

A good alternative is to use pt-online-schema-change. In a nutshell the tool creates a new table with the desired structure, creates triggers on the original table, and copies rows in chunks from the original table to the new table.

Nice features of the tool include being able to write to the original table while the schema change is being performed (hence the name of the tool), monitoring replication lag on slaves and automatic throttling to keep lag under a reasonable threshold.

Let’s assume you want to add a new field called ‘city’ in the ‘user’ table. Instead of running:

mysql> ALTER TABLE mydb.user ADD city VARCHAR(50) NOT NULL DEFAULT '';

you will run:

# pt-online-schema-change --progress=percentage,10 --alter="ADD city VARCHAR(50) NOT NULL DEFAULT ''" h=localhost,u=root,D=mydb,t=user --execute

All ALTER TABLE statements can be converted to an equivalent pt-online-schema-change command.

Is there any tradeoff? The first one is that pt-online-schema-change will be slower than a plain ALTER TABLE. This is expected as the tool has extra code to throttle itself if it adds too much load.

How much slower is it? As always it depends. On a lightly loaded server the difference will be minimal, for instance I measured 3mn24s for ALTER TABLE and 3mn30s for pt-online-schema-change on a test table. However on a heavily loaded server, it can be like 5x slower.

The second tradeoff is that pt-online-schema-change adds triggers on the original table to capture data change. So if you already have triggers, you can’t use the tool (this will be lifted in MySQL 5.7).

Metadata Locks

Metadata locks were introduced in MySQL 5.5 for better transaction isolation.

But one side effect is: if you start a schema change operation on a table while another transaction is reading or writing on the same table, ALTER TABLE will be stuck in the Waiting for metadata lock state.

Another negative side effect of metadata locks is that in some situations ALTER TABLE will also block all queries to the table that is being altered, even reads. In the worst cases, this may create a query pileup situation that will freeze the whole server. For more on this topic, have a look at this post.

What about pt-online-schema-change and metadata locks? It is of course hit by this issue/feature. However the good thing is that the timeout for metadata locks is set by the tool to 60s instead of the default 1 year. So after 60s, pt-online-schema-change will simply retry the operation that failed because of metadata locks.

MySQL 5.6: Online Schema Changes?

Metadata locks also exist with MySQL 5.6, the same issues as described above can then happen.

However the good news with MySQL 5.6 is that most schema changes can be done online. Now the question is: should you use pt-online-schema-change or a regular online ALTER TABLE statement?

Both have pros and cons:

  • ALTER TABLE is easy to use while being confident with pt-online-schema-change requires some time.
  • There is no way for ALTER TABLE to know if it’s overloading a replica while pt-online-schema-change monitors replication lag and throttles itself if needed.
  • ALTER TABLE is only online for the master, not for replicas while pt-online-schema-change is online for all servers.

The last point can be a bit confusing: why an online ALTER TABLE on the master wouldn’t be online on the slave as well? The truth is that unless you are using multi-threaded replication, writes on slaves are serialized. So while the ALTER TABLE is running, the slave will not process any other write, which is not much different from an offline ALTER TABLE.

So if you are running a single server (all Icehouse users for instance), ALTER TABLE is probably the right choice. But if you have read replicas and specifically if the application cannot tolerate replication lag, pt-online-schema-change is definitely a better choice.

Conclusion

Performing schema changes is becoming easier with newer versions of MySQL. However potential issues can be found with any version, so always be careful when you need to run ALTER TABLE.

The post Schema changes in MySQL for OpenStack Trove users appeared first on MySQL Performance Blog.

Google Compute Engine adds Percona XtraDB Cluster to click-to-deploy process

November 21, 2014 - 8:00am

I’m happy to announce that Google has added Click-to-deploy functionality for Percona XtraDB Cluster (PXC) on Google Cloud Compute nodes. This gives you the ability to rapidly spin up a cluster for experimentation, performance testing, or even production use on Google Compute Engine virtual machines.

What is Percona XtraDB Cluster?

Percona XtraDB Cluster is a virtually synchronous cluster of MySQL Innodb nodes. Unlike conventional MySQL asynchronous replication which has a specific network topology of master and slaves, PXC’s nodes have no specific topology.  Functionally, this means that there are no masters and slaves, so you can read and write on any node.

Further, any failure in the cluster does not require any re-arranging of the replication topology. Instead, clients just reconnect to another node and continue reading and writing.

We have a ton of material about Percona XtraDB Cluster in previous posts, in the PXC manual, and in various webinars. If you want a concentrated hour overview of Percona XtraDB Cluster, I’d recommend watching this webinar.

How do I use Click-to-deploy?

Simply visit Google Cloud’s solutions page here: https://cloud.google.com/solutions/percona to get started. You are given a simple setup wizard that allows you choose the size and quantity of nodes you want, disk storage type and volume, etc.  Once you ‘Deploy Cluster’, your instances will launch and form a cluster automatically with sane default tunings. After that, it’s all up to you what you want to do.

Seeing it in action

Once your instances launch, you can add an SSH key to access (you can also install the Google Cloud SDK). This, handily, creates a new account based on the username in the SSH public key text. Once I add the key, I can easily just ssh to the public IP of the instance and I have my own account:

jayj@~ [500]$ ssh [public ip] Linux percona-mysql-niyr 3.2.0-4-amd64 #1 SMP Debian 3.2.60-1+deb7u3 x86_64 The programs included with the Debian GNU/Linux system are free software; the exact distribution terms for each program are described in the individual files in /usr/share/doc/*/copyright. Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent permitted by applicable law. jayj@percona-mysql-niyr:~$

Once there I installed sysbench 0.5 from the Percona apt repo:

jayj@percona-mysql-niyr:~$ sudo -i root@percona-mysql-niyr:~# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A root@percona-mysql-niyr:~# echo "deb http://repo.percona.com/apt wheezy main" > /etc/apt/sources.list.d/percona.list root@percona-mysql-niyr:~# apt-get update; apt-get install sysbench -y ... Setting up sysbench (0.5-3.wheezy) ... root@percona-mysql-niyr:~# logout jayj@percona-mysql-niyr:~$

Now we can load some data and run a quick test:

jayj@percona-mysql-niyr:~$ sysbench --test=/usr/share/doc/sysbench/tests/db/parallel_prepare.lua --oltp-tables-count=16 --oltp-table-size=1000000 --oltp-auto-inc=off --num-threads=8 --mysql-user=root --mysql-password=[the admin password you set in the wizard] --mysql-db=test run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 8 Random number generator seed is 0 and will be ignored Threads started! thread prepare4 Creating table 'sbtest5'... thread prepare2 Creating table 'sbtest3'... thread prepare6 Creating table 'sbtest7'... thread prepare0 Creating table 'sbtest1'... thread prepare7 Creating table 'sbtest8'... thread prepare1 Creating table 'sbtest2'... thread prepare5 Creating table 'sbtest6'... thread prepare3 Creating table 'sbtest4'... Inserting 100000 records into 'sbtest5' Inserting 100000 records into 'sbtest3' Inserting 100000 records into 'sbtest7' Inserting 100000 records into 'sbtest1' Inserting 100000 records into 'sbtest8' Inserting 100000 records into 'sbtest2' Inserting 100000 records into 'sbtest6' Inserting 100000 records into 'sbtest4' Creating table 'sbtest13'... Creating table 'sbtest15'... Inserting 100000 records into 'sbtest13' Inserting 100000 records into 'sbtest15' Creating table 'sbtest11'... Creating table 'sbtest12'... Inserting 100000 records into 'sbtest11' Creating table 'sbtest16'... Inserting 100000 records into 'sbtest12' Creating table 'sbtest10'... Creating table 'sbtest14'... Creating table 'sbtest9'... Inserting 100000 records into 'sbtest16' Inserting 100000 records into 'sbtest9' Inserting 100000 records into 'sbtest10' Inserting 100000 records into 'sbtest14' OLTP test statistics: queries performed: read: 0 write: 608 other: 32 total: 640 transactions: 0 (0.00 per sec.) read/write requests: 608 (11.33 per sec.) other operations: 32 (0.60 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 53.6613s total number of events: 10000 total time taken by event execution: 0.0042s response time: min: 0.00ms avg: 0.00ms max: 0.02ms approx. 95 percentile: 0.00ms Threads fairness: events (avg/stddev): 1250.0000/3307.19 execution time (avg/stddev): 0.0005/0.00 jayj@percona-mysql-niyr:~$ sysbench --test=/usr/share/doc/sysbench/tests/db/update_index.lua --oltp-tables-count=16 --oltp-table-size=1000000 --num-threads=8 --mysql-user=root --mysql-password=[the admin password you set in the wizard]--mysql-db=test --max-requests=0 --max-time=10 --report-interval=1 --oltp-auto-inc=off --rand-init=on --rand-type=uniform run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 8 Report intermediate results every 1 second(s) Initializing random number generator from timer. Random number generator seed is 0 and will be ignored Threads started! [ 1s] threads: 8, tps: 0.00, reads: 0.00, writes: 1396.43, response time: 10.85ms (95%), errors: 0.00, reconnects: 0.00 [ 2s] threads: 8, tps: 0.00, reads: 0.00, writes: 1314.13, response time: 14.91ms (95%), errors: 0.00, reconnects: 0.00 [ 3s] threads: 8, tps: 0.00, reads: 0.00, writes: 1382.87, response time: 12.34ms (95%), errors: 0.00, reconnects: 0.00 [ 4s] threads: 8, tps: 0.00, reads: 0.00, writes: 949.09, response time: 12.88ms (95%), errors: 0.00, reconnects: 0.00 [ 5s] threads: 8, tps: 0.00, reads: 0.00, writes: 1312.01, response time: 11.27ms (95%), errors: 0.00, reconnects: 0.00 [ 6s] threads: 8, tps: 0.00, reads: 0.00, writes: 898.92, response time: 11.64ms (95%), errors: 0.00, reconnects: 0.00 [ 7s] threads: 8, tps: 0.00, reads: 0.00, writes: 1541.71, response time: 10.59ms (95%), errors: 0.00, reconnects: 0.00 [ 8s] threads: 8, tps: 0.00, reads: 0.00, writes: 1551.35, response time: 11.48ms (95%), errors: 0.00, reconnects: 0.00 [ 9s] threads: 8, tps: 0.00, reads: 0.00, writes: 923.07, response time: 10.40ms (95%), errors: 0.00, reconnects: 0.00 [ 10s] threads: 8, tps: 0.00, reads: 0.00, writes: 1273.99, response time: 11.01ms (95%), errors: 0.00, reconnects: 0.00 OLTP test statistics: queries performed: read: 0 write: 12551 other: 0 total: 12551 transactions: 0 (0.00 per sec.) read/write requests: 12551 (1254.65 per sec.) other operations: 0 (0.00 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 10.0036s total number of events: 12551 total time taken by event execution: 79.9602s response time: min: 1.13ms avg: 6.37ms max: 389.52ms approx. 95 percentile: 11.68ms Threads fairness: events (avg/stddev): 1568.8750/4.81 execution time (avg/stddev): 9.9950/0.00

So, we see ~1200 tps on an update test in our little cluster, not too bad!

What it is not

This wizard is a handy way to get a cluster setup for some experimentation or testing. However, it is not a managed service:  after you launch it, you’re responsible for maintaining, tuning, etc. You could use it for production, but you may want some further fine tuning, operational procedures, etc. All of this is absolutely something Percona can help you with.

The post Google Compute Engine adds Percona XtraDB Cluster to click-to-deploy process appeared first on MySQL Performance Blog.

Sys Schema for MySQL 5.6 and MySQL 5.7

November 20, 2014 - 6:31am

Performance Schema (P_S) has been available since MySQL 5.5, more than 4 years ago. It is still difficult to see production servers with P_S enabled, even with MySQL 5.6 where the feature is enabled by default. There have been several complaints like the overhead, that the fix is a work in progress, and the ease of use. 52 tables to query and 31 configuration variables is enough to scare people.

There is a solution for the second problem, the usability. It’s name is “sys schema“. It is a collection of views, functions and procedures to make P_S more user friendly.

Installation

If you are a MySQL Workbench user the installation is pretty easy because sys schema is already included. You just need to install it. Click on “Performance – Performance Reports” and there you will find the “Install Helper” button that will install sys schema.

If you don’t use MySQL Workbench you need to download sys_56.sql or sys_57.sql (depends if you use 5.6 or 5.7) from the github repository. Then, just import the sql file as usual:

mysql -u root -p < ./sys_56.sql

Usage

After the import, you will have a new “sys” schema with some very descriptive table names. Let’s see an example. Do you want to know what tables are using most of our InnoDB buffer memory? Easy:

mysql> select * from sys.innodb_buffer_stats_by_table; +---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+ | test | t | 63.61 MiB | 58.06 MiB | 4071 | 4071 | 4071 | 2101222 | | InnoDB System | SYS_FOREIGN | 32.00 KiB | 0 bytes | 2 | 2 | 2 | 0 | | InnoDB System | SYS_TABLES | 32.00 KiB | 1.11 KiB | 2 | 2 | 2 | 10 | | InnoDB System | SYS_COLUMNS | 16.00 KiB | 4.68 KiB | 1 | 1 | 1 | 71 | | InnoDB System | SYS_DATAFILES | 16.00 KiB | 324 bytes | 1 | 1 | 1 | 6 | | InnoDB System | SYS_FIELDS | 16.00 KiB | 722 bytes | 1 | 1 | 1 | 17 | | InnoDB System | SYS_INDEXES | 16.00 KiB | 836 bytes | 1 | 1 | 1 | 12 | | InnoDB System | SYS_TABLESPACES | 16.00 KiB | 318 bytes | 1 | 1 | 1 | 6 | | mysql | innodb_index_stats | 16.00 KiB | 274 bytes | 1 | 1 | 1 | 3 | | mysql | innodb_table_stats | 16.00 KiB | 53 bytes | 1 | 1 | 1 | 1 | +---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+

Pretty easy and useful, right? You can also get what is the database using more memory in the buffer pool querying innodb_buffer_stats_by_schema.

For each table there is another similar ones that it’s name starts with x$. For example, you have user_summary_by_file_io and x$user_summary_by_file_io. The difference is that the first table has a human readable output values while the second one has the real values. Let’s see an example:

mysql> select * from sys.user_summary_by_file_io; +------------+-------+------------+ | user | ios | io_latency | +------------+-------+------------+ | root | 19514 | 2.87 s | | background | 5916 | 1.91 s | +------------+-------+------------+ 2 rows in set (0.00 sec) mysql> select * from sys.x$user_summary_by_file_io; +------------+-------+---------------+ | user | ios | io_latency | +------------+-------+---------------+ | root | 19514 | 2871847094292 | | background | 5916 | 1905079715132 | +------------+-------+---------------+

For humans, at least for me, it is easier to read seconds rather than picoseconds

There are multiple tables with very descriptive names.

– io_by_thread_by_latency
– schema_unused_indexes
– statements_with_temp_tables
– statements_with_errors_or_warnings
– user_summary_by_statement_type
– waits_by_host_by_latency

There are lot more, and they are explained with examples in project’s README file.

Configuration

On the MySQL side nothing special is needed. Just enable performance_schema:

performance_schema="on"

sys schema also provides some procedures to enable/disable some features. For example:

– ps_setup_enable_background_threads
– ps_setup_enable_consumers
– ps_setup_enable_instrument
and so on…

We also have the same procedures with “disable”. After you have made the changes you can save them calling ps_setup_save() and reload it later on if you want calling ps_setup_reload_saved(). If you want to reset the configuration to default values just call ps_setup_reset_to_default().

For example, we can check that some consumers are disabled. It is possible to enable them and save the configuration:

mysql> CALL sys.ps_setup_show_disabled_consumers(); +--------------------------------+ | disabled_consumers | +--------------------------------+ | events_stages_current | | events_stages_history | | events_stages_history_long | | events_statements_history | | events_statements_history_long | | events_waits_current | | events_waits_history | | events_waits_history_long | +--------------------------------+ mysql> CALL sys.ps_setup_enable_consumers('events'); +---------------------+ | summary | +---------------------+ | Enabled 8 consumers | +---------------------+ mysql> CALL sys.ps_setup_show_disabled_consumers(); Empty set (0.00 sec) mysql> CALL sys.ps_setup_save(TRUE);

Conclusion

Performance Schema is very useful when we want to know what is happening inside the database. It is getting more features with each new GA and will probably be the single point of information in near future. Now thanks to sys schema it is also easy to use.

The post Sys Schema for MySQL 5.6 and MySQL 5.7 appeared first on MySQL Performance Blog.

How a set of queries can be killed in MySQL using Percona Toolkit’s pt-kill

November 19, 2014 - 7:18am

You might have encountered situations where you had to kill some specific select queries that were running for long periods and choking the database. This post will go into more detail with an example of report query offloading.

Report query (select) offloading to a slave server is a common practice to reduce the workload of the master DB server. The long running selects will be executed in the slave for report generation. I have observed in many cases where the slave used to get delayed or the slave DB encounters a slowdown due to some heavy long-running orphaned selects from some wrong reports.

There are two main ways to kill queries in MySQL: 1. use custom scripts that match on a regular expression, or 2. use a tool written and supported by Percona that is designed to kill queries based on matching conditions. Below is one script that will help you to kill those queries. The script will take the process list from MySQL and filter the long-running select query considering the “User”, “Time” and “State” from the list. However I suggest that you use the pt-kill tool from Percona Toolkit which provides a more reliable choice and options based on your requirements.

Process list:

+-----+------+-----------+------+---------+------+------------------------------+-----------------------------+ | Id  | User | Host      | db   | Command | Time | State                        | Info                        | +-----+------+-----------+------+---------+------+------------------------------+-----------------------------+ | 103 | root | localhost | test | Query   |    0 | init                         | show processlist            | | 104 | root | localhost | test | Sleep   |  383 |                              | NULL                        | | 106 | root | localhost | test | Query   |  377 | Waiting for table level lock | SELECT * FROM t FOR UPDATE  | | 107 | root | localhost | test | Query   |  364 | Waiting for table level lock | insert into t value(5)      | | 108 | rpt  | localhost | test | Query   |  345 | Waiting for table level lock | SELECT c1 FROM t FOR UPDATE | +-----+------+-----------+------+---------+------+------------------------------+-----------------------------+

1. Shell script example:

List all queries from rpt user having query time greater than 1 minute: [root@test3 ~]# for id in $(mysql -t -e "SHOW FULL PROCESSLIST" | /bin/awk -F "|" '($6 ~ /Query/) && (60< $7) && ($3 ~ /rpt/) { print $2}'); do echo "KILL QUERY $id;"; done KILL QUERY 108; Kill all queries from rpt user having query time greater than 1 minute: [root@test3 ~]# for id in $(mysql -t -e "SHOW FULL PROCESSLIST" | /bin/awk -F "|" '($6 ~ /Query/) && (60< $7) && ($3 ~ /rpt/) { print $2}'); do mysql -e "KILL QUERY $id;"; done

2. Pt-kill example:

List all queries from rpt user having query time greater than 1 minute (–print):

[root@test3 ~]# pt-kill --busy-time 60 --match-command Query --user root --print --match-user rpt --socket /tmp/mysql.sock # 2014-11-12T03:51:01 KILL 108 (Query 485 sec) SELECT c1 FROM t FOR UPDATE # 2014-11-12T03:51:31 KILL 108 (Query 515 sec) SELECT c1 FROM t FOR UPDATE # 2014-11-12T03:52:01 KILL 108 (Query 545 sec) SELECT c1 FROM t FOR UPDATE

Kill all queries from rpt user having query time greater than 1 minute (–kill-query):

[root@test3 ~]# pt-kill --busy-time 60 --match-command Query --user root --print --kill-query --match-user rpt --socket /tmp/mysql.sock # 2014-11-12T03:53:26 KILL QUERY 108 (Query 630 sec) SELECT c1 FROM t FOR UPDATE

Process list:

+-----+------+-----------+------+---------+------+------------------------------+----------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+------------------------------+----------------------------+ | 103 | root | localhost | test | Query | 0 | init | show processlist | | 104 | root | localhost | test | Sleep | 843 | | NULL | | 106 | root | localhost | test | Query | 837 | Waiting for table level lock | SELECT * FROM t FOR UPDATE | | 107 | root | localhost | test | Query | 824 | Waiting for table level lock | insert into t value(5) | | 108 | rpt | localhost | test | Sleep | 805 | | NULL | | 111 | root | localhost | NULL | Sleep | 25 | | NULL | +-----+------+-----------+------+---------+------+------------------------------+----------------------------+

 Note : –kill-query option makes pt-kill kill matching queries. This requires MySQL 5.0 or newer. Unlike –kill which kills the connection for matching queries, this option only kills the query, not its connection.

pt-kill captures queries from SHOW PROCESSLIST, filters them, and then either kills or prints them. This is also known as a “slow query sniper” in some circles. The idea is to watch for queries that might be consuming too many resources, and kill them. For details please read the tool’s (pt-kill) documentation.

The post How a set of queries can be killed in MySQL using Percona Toolkit’s pt-kill appeared first on MySQL Performance Blog.

Avoiding MySQL ALTER table downtime

November 18, 2014 - 9:50am

MySQL table alterations can interrupt production traffic causing bad customer experience or in worst cases, loss of revenue. Not all DBAs, developers, syadmins know MySQL well enough to avoid this pitfall. DBAs usually encounter these kinds of production interruptions when working with upgrade scripts that touch both application and database or if an inexperienced admin/dev engineer perform the schema change without knowing how MySQL operates internally.

Truths
* Direct MySQL ALTER table locks for duration of change (pre-5.6)
* Online DDL in MySQL 5.6 is not always online and may incurr locks
* Even with Percona Toolkit‘s pt-online-schema-change there are several workloads that can experience blocking

Here on the Percona MySQL Managed Services team we encourage our clients to work with us when planning and performing schema migrations. We aim to ensure that we are using the best method available in their given circumstance. Our intentions to avoid blocking when performing DDL on large tables ensures that business can continue as usual whilst we strive to improve response time or add application functionality. The bottom line is that a business relying on access to its data cannot afford to be down during core trading hours.

Many of the installations we manage are still below MySQL 5.6, which requires us to seek workarounds to minimize the amount of disruption a migration can cause. This may entail slave promotion or changing the schema with an ‘online schema change’ tool. MySQL version 5.6 looks to address this issue by reducing the number of scenarios where a table is rebuilt and locked but it doesn’t yet cover all eventualities, for example when changing the data type of a column a full table rebuild is necessary. The topic of 5.6 Online Schema Change was discussed in great detail last year in the post, “Schema changes – what’s new in MySQL 5.6?” by Przemysław Malkowski

With new functionality arriving in MySQL 5.7, we look forward to non-blocking DDL operations such as; OPTIMIZE TABLE and RENAME INDEX. (More info)

The best advice for MySQL 5.6 users is to review the matrix to familiarize with situations where it might be best to look outside of MySQL to perform schema changes, the good news is that we’re on the right path to solving this natively.

Truth be told, a blocking alter is usually going to go unnoticed on a 30MB table and we tend to use a direct alter in this situation, but on a 30GB or 300GB table we have some planning to do. If there is a period of time where activity is low and the this is permissive of locking the table then sometimes it is better execute within this window. Frequently though we are reactive to new SQL statements or a new performance issue and an emergency index is required to reduce load on the master in order to improve the response time.

To pt-osc or not to pt-osc?

As mentioned, pt-online-schema-change is a fixture in our workflow. It’s usually the right way to go but we still have occasions where pt-online-schema-change cannot be used, for example; when a table already uses triggers. It’s an important to remind ourselves of the the steps that pt-online-schema-change traverses to complete it’s job. Lets look at the source code to identify these;

[moore@localhost]$ egrep 'Step' pt-online-schema-change # Step 1: Create the new table. # Step 2: Alter the new, empty table. This should be very quick, # Step 3: Create the triggers to capture changes on the original table and <--(metadata lock) # Step 4: Copy rows. # Step 5: Rename tables: orig -> old, new -> orig <--(metadata lock) # Step 6: Update foreign key constraints if there are child tables. # Step 7: Drop the old table.

I pick out steps 3 and 5 from above to highlight a source of a source of potential downtime due to locks, but step 6 is also an area for concern since foreign keys can have nested actions and should be considered when planning these actions to avoid related tables from being rebuilt with a direct alter implicitly. There are several ways to approach a table with referential integrity constraints and they are detailed within the pt-osc documentation a good preparation step is to review the structure of your table including the constraints and how the ripples of the change can affect the tables around it.

Recently we were alerted to an incident after a client with a highly concurrent and highly transactional workload ran a standard pt-online-schema-change script over a large table. This appeared normal to them and a few hours later our pager man was notified that this client was experiencing max_connections limit reached. So what was going on? When pt-online-schema-change reached step 5 it tried to acquire a metadata lock to rename the the original and the shadow table, however this wasn’t immediately granted due to open transactions and thus threads began to queue behind the RENAME command. The actual effect this had on the client’s application was downtime. No new connections could be made and all existing threads were waiting behind the RENAME command.

Metadata locks
Introduced in 5.5.3 at server level. When a transaction starts it will acquire a metadata lock (independent of storage engine) on all tables it uses and then releases them when it’s finished it’s work. This ensures that nothing can alter the table definition whilst a transaction is open.

With some foresight and planning we can avoid these situations with non-default pt-osc options, namely –nodrop-new-table and –no-swap-tables. This combination leaves both the shadow table and the triggers inplace so that we can instigate an atomic RENAME when load permits. This illustrates that even with a tool like pt-online-schema-change it is important to understand the caveats presented with the solution you think is most adequate. To help decide the direction to take use the flow chart to ensure you’re taking into account some of the caveats of the MySQL schema change. Be sure to read up on the recommended outcome though as there are uncharted areas such as disk space, IO load that are not featured on the diagram.

Choosing the right DDL option

Ensure you know what effect ALTER TABLE will have on your platform and pick the right method to suit your uptime. Sometimes that means delaying the change until a period of lighter use or utilising a tool that will avoid holding a table locked for the duration of the operation. A direct ALTER is sometimes the answer like when you have triggers installed on a table.

– In most cases pt-osc is exactly what we need
– In many cases pt-osc is needed but the way in which it’s used needs tweaking
– In few cases pt-osc isn’t the right tool/method and we need to consider native blocking ALTER or using failovers to juggle the change into place on all hosts in the replica cluster.

If you want to learn more about avoiding avoidable downtime please tune into my webinar Wednesday, November 19 at 10 a.m. PST. It’s titled “Tips from the Trenches: A Guide to Preventing Downtime for the Over-Extended DBA.” Register now! (If you miss it, don’t worry: Catch the recording and download the slides from that same registration page.)

The post Avoiding MySQL ALTER table downtime appeared first on MySQL Performance Blog.

MySQL’s INNODB_METRICS table: How much is the overhead?

November 18, 2014 - 5:00am

Starting with MySQL 5.6 there is an INNODB_METRICS table available in INFORMATION_SCHEMA which contains some additional information than provided in the SHOW GLOBAL STATUS output – yet might be more lightweight than PERFORMANCE_SCHEMA.

Too bad INNODB_METRICS was designed during the Oracle-Sun split under MySQL leadership and so it covers only InnoDB counters. I think this would be a great replacement to all counters that are currently provided though SHOW STATUS – it captures more information such as providing MIN/MAX counts for variables as well as providing the type of the counter (whenever it is current or commutative) as well as human readable comment – describing what such counter means.

The examples of data you can get only from the INNODB_METRICS table includes information about InnoDB Page Splits and merging (which can cause quite an impact to the database performance).

As well as details of InnoDB purging performance, adaptive hash index activity, details about InnoDB flushing or how index condition pushdown (ICP) is working for you.

The InnoDB Metrics come disabled by default as of MySQL 5.6 and it provides very elaborate configuration commands – you can enable/disable individual counters or counters for specific subsystems you’re interested in. I would expect most users though would need only basic configuration:

set global innodb_monitor_disable = all; set global innodb_monitor_enable = all;

…which enables and disables all InnoDB Metrics appropriately. Of course if you just rather keep changes permanently you would want to keep it as a setting in the MySQL Configuration file. Small side note – some of the InnoDB metrics correspond to SHOW STATUS counters and those are permanently enabled.

As those metrics are disabled by default I was wondering if they really do have so huge a overhead that we can’t keep them enabled. In my tests I’ve measured less than 1% overhead, really the variance between runs of benchmark was larger than caused by having metrics enabled. It might be on very large systems with heavy workloads that the overhead might be higher – if you observe any significant overhead from using INNODB_METRICS please comment on this post so we know.

Finally let me post a selection of metrics that have been actively running a simple sysbench test, in total there are 214 metrics as of the current MySQL 5.6 release.

mysql> select name,count,round(avg_count,3) avg_cnt ,type,comment from innodb_metrics where count>0 or max_count>0; +------------------------------------------+---------------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+ | name | count | avg_cnt | type | comment | +------------------------------------------+---------------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+ | metadata_table_handles_opened | 1 | 0.000 | counter | Number of table handles opened | | metadata_table_reference_count | -6 | -0.000 | counter | Table reference counter | | metadata_mem_pool_size | 8388608 | NULL | value | Size of a memory pool InnoDB uses to store data dictionary and internal data structures in bytes | | lock_deadlocks | 521 | 0.002 | counter | Number of deadlocks | | lock_timeouts | 6857 | 0.027 | counter | Number of lock timeouts | | lock_rec_lock_waits | 1837441 | 16.150 | counter | Number of times enqueued into record lock wait queue | | lock_rec_lock_requests | 65270862 | 573.694 | counter | Number of record locks requested | | lock_rec_lock_created | 36872834 | 324.091 | counter | Number of record locks created | | lock_rec_lock_removed | 36872830 | 324.091 | counter | Number of record locks removed from the lock queue | | lock_rec_locks | 4 | 0.000 | counter | Current number of record locks on tables | | lock_table_lock_created | 12239169 | 107.575 | counter | Number of table locks created | | lock_table_lock_removed | 12239165 | 107.575 | counter | Number of table locks removed from the lock queue | | lock_table_locks | 4 | 0.000 | counter | Current number of table locks on tables | | lock_row_lock_current_waits | 55 | 0.000 | status_counter | Number of row locks currently being waited for (innodb_row_lock_current_waits) | | lock_row_lock_time | 1936523774 | 7548.123 | status_counter | Time spent in acquiring row locks, in milliseconds (innodb_row_lock_time) | | lock_row_lock_time_max | 51969 | NULL | value | The maximum time to acquire a row lock, in milliseconds (innodb_row_lock_time_max) | | lock_row_lock_waits | 1901237 | 7.411 | status_counter | Number of times a row lock had to be waited for (innodb_row_lock_waits) | | lock_row_lock_time_avg | 1018 | NULL | value | The average time to acquire a row lock, in milliseconds (innodb_row_lock_time_avg) | | buffer_pool_size | 6442450944 | NULL | value | Server buffer pool size (all buffer pools) in bytes | | buffer_pool_reads | 370556494 | 1444.344 | status_counter | Number of reads directly from disk (innodb_buffer_pool_reads) | | buffer_pool_read_requests | 48016336694 | 187156.603 | status_counter | Number of logical read requests (innodb_buffer_pool_read_requests) | | buffer_pool_write_requests | 4273025649 | 16655.268 | status_counter | Number of write requests (innodb_buffer_pool_write_requests) | | buffer_pool_read_ahead | 6461109 | 25.184 | status_counter | Number of pages read as read ahead (innodb_buffer_pool_read_ahead) | | buffer_pool_pages_total | 393208 | NULL | value | Total buffer pool size in pages (innodb_buffer_pool_pages_total) | | buffer_pool_pages_misc | 13 | NULL | value | Buffer pages for misc use such as row locks or the adaptive hash index (innodb_buffer_pool_pages_misc) | | buffer_pool_pages_data | 387844 | NULL | value | Buffer pages containing data (innodb_buffer_pool_pages_data) | | buffer_pool_bytes_data | 6354436096 | NULL | value | Buffer bytes containing data (innodb_buffer_pool_bytes_data) | | buffer_pool_pages_dirty | 2646 | NULL | value | Buffer pages currently dirty (innodb_buffer_pool_pages_dirty) | | buffer_pool_bytes_dirty | 43352064 | NULL | value | Buffer bytes currently dirty (innodb_buffer_pool_bytes_dirty) | | buffer_pool_pages_free | 5351 | NULL | value | Buffer pages currently free (innodb_buffer_pool_pages_free) | | buffer_pages_created | 15712031 | 61.242 | status_counter | Number of pages created (innodb_pages_created) | | buffer_pages_written | 88472873 | 344.847 | status_counter | Number of pages written (innodb_pages_written) | | buffer_pages_read | 389195724 | 1516.995 | status_counter | Number of pages read (innodb_pages_read) | | buffer_data_reads | 6370189115392 | 24829527.611 | status_counter | Amount of data read in bytes (innodb_data_reads) | | buffer_data_written | 3238687916032 | 12623658.353 | status_counter | Amount of data written in bytes (innodb_data_written) | | buffer_flush_batch_scanned | 303528 | 0.000 | set_owner | Total pages scanned as part of flush batch | | buffer_flush_batch_num_scan | 1066763 | 9.376 | set_member | Number of times buffer flush list flush is called | | buffer_flush_batch_scanned_per_call | 0 | 0.000 | set_member | Pages scanned per flush batch scan | | buffer_flush_batch_rescan | 89 | 0.001 | counter | Number of times rescan of flush list forced | | buffer_flush_batch_total_pages | 634250 | 3.000 | set_owner | Total pages flushed as part of flush batch | | buffer_flush_batches | 197859 | 1.739 | set_member | Number of flush batches | | buffer_flush_batch_pages | 1 | 0.000 | set_member | Pages queued as a flush batch | | buffer_flush_neighbor_total_pages | 6051959 | 0.000 | set_owner | Total neighbors flushed as part of neighbor flush | | buffer_flush_neighbor | 34246946 | 301.011 | set_member | Number of times neighbors flushing is invoked | | buffer_flush_neighbor_pages | 0 | 0.000 | set_member | Pages queued as a neighbor batch | | buffer_flush_n_to_flush_requested | 0 | 0.000 | counter | Number of pages requested for flushing. | | buffer_flush_avg_page_rate | 0 | 0.000 | counter | Average number of pages at which flushing is happening | | buffer_flush_lsn_avg_rate | 16570 | 0.146 | counter | Average redo generation rate | | buffer_flush_pct_for_lsn | 0 | 0.000 | counter | Percent of IO capacity used to avoid reusable redo space limit | | buffer_flush_adaptive_total_pages | 631237 | 11.000 | set_owner | Total pages flushed as part of adaptive flushing | | buffer_flush_adaptive | 56061 | 0.493 | set_member | Number of adaptive batches | | buffer_flush_adaptive_pages | 1 | 0.000 | set_member | Pages queued as an adaptive batch | | buffer_flush_background_total_pages | 39668544 | 420.000 | set_owner | Total pages flushed as part of background batches | | buffer_flush_background | 94284 | 0.829 | set_member | Number of background batches | | buffer_flush_background_pages | 26 | 0.000 | set_member | Pages queued as a background batch | | buffer_LRU_batch_scanned | 5664563354 | 3153.000 | set_owner | Total pages scanned as part of LRU batch | | buffer_LRU_batch_num_scan | 1796523 | 15.790 | set_member | Number of times LRU batch is called | | buffer_LRU_batch_scanned_per_call | 2748 | 0.024 | set_member | Pages scanned per LRU batch call | | buffer_LRU_batch_total_pages | 39665531 | 420.000 | set_owner | Total pages flushed as part of LRU batches | | buffer_LRU_batches | 94281 | 0.829 | set_member | Number of LRU batches | | buffer_LRU_batch_pages | 26 | 0.000 | set_member | Pages queued as an LRU batch | | buffer_LRU_get_free_search | 378687232 | 3328.446 | counter | Number of searches performed for a clean page | | os_data_reads | 389095626 | 1516.605 | status_counter | Number of reads initiated (innodb_data_reads) | | os_data_writes | 93493818 | 364.417 | status_counter | Number of writes initiated (innodb_data_writes) | | os_data_fsyncs | 7023487 | 27.376 | status_counter | Number of fsync() calls (innodb_data_fsyncs) | | os_log_bytes_written | 339601504256 | 1323688.320 | status_counter | Bytes of log written (innodb_os_log_written) | | os_log_fsyncs | 2181429 | 8.503 | status_counter | Number of fsync log writes (innodb_os_log_fsyncs) | | os_log_pending_fsyncs | 0 | 0.000 | status_counter | Number of pending fsync write (innodb_os_log_pending_fsyncs) | | os_log_pending_writes | 0 | 0.000 | status_counter | Number of pending log file writes (innodb_os_log_pending_writes) | | trx_rw_commits | 12239165 | 107.575 | counter | Number of read-write transactions committed | | trx_commits_insert_update | 12223766 | 107.440 | counter | Number of transactions committed with inserts and updates | | trx_rollbacks | 2073 | 0.018 | counter | Number of transactions rolled back | | trx_rollbacks_savepoint | 6860 | 0.060 | counter | Number of transactions rolled back to savepoint | | trx_active_transactions | -10769 | -0.095 | counter | Number of active transactions | | trx_rseg_history_len | 9469305 | NULL | value | Length of the TRX_RSEG_HISTORY list | | trx_undo_slots_used | 81 | 0.001 | counter | Number of undo slots used | | trx_undo_slots_cached | -16487 | -0.145 | counter | Number of undo slots cached | | trx_rseg_current_size | 498385 | NULL | value | Current rollback segment size in pages | | purge_del_mark_records | 2900462 | 25.493 | counter | Number of delete-marked rows purged | | purge_upd_exist_or_extern_records | 2900334 | 25.492 | counter | Number of purges on updates of existing records and updates on delete marked record with externally stored field | | purge_invoked | 15036193 | 132.160 | counter | Number of times purge was invoked | | purge_undo_log_pages | 2901506 | 25.503 | counter | Number of undo log pages handled by the purge | | log_checkpoints | 7880 | 0.069 | counter | Number of checkpoints | | log_lsn_last_flush | 338495604612 | NULL | value | LSN of Last flush | | log_lsn_last_checkpoint | 337905680629 | NULL | value | LSN at last checkpoint | | log_lsn_current | 338495612626 | NULL | value | Current LSN value | | log_lsn_checkpoint_age | 589931997 | 5185.167 | counter | Current LSN value minus LSN at last checkpoint | | log_lsn_buf_pool_oldest | 337905680629 | NULL | value | The oldest modified block LSN in the buffer pool | | log_max_modified_age_async | 6087493925 | NULL | value | Maximum LSN difference; when exceeded, start asynchronous preflush | | log_max_modified_age_sync | 6522314920 | NULL | value | Maximum LSN difference; when exceeded, start synchronous preflush | | log_pending_checkpoint_writes | 0 | 0.000 | counter | Pending checkpoints | | log_num_log_io | 1618018 | 14.221 | counter | Number of log I/Os | | log_write_requests | 733043164 | 2857.233 | status_counter | Number of log write requests (innodb_log_write_requests) | | log_writes | 2160056 | 8.419 | status_counter | Number of log writes (innodb_log_writes) | | index_page_splits | 725862 | 6.380 | counter | Number of index page splits | | index_page_merge_attempts | 6318656 | 55.537 | counter | Number of index page merge attempts | | index_page_merge_successful | 600659 | 5.279 | counter | Number of successful index page merges | | index_page_reorg_attempts | 324359 | 2.851 | counter | Number of index page reorganization attempts | | index_page_reorg_successful | 324359 | 2.851 | counter | Number of successful index page reorganizations | | index_page_discards | 850 | 0.007 | counter | Number of index pages discarded | | adaptive_hash_searches | 996683138 | 3884.841 | status_counter | Number of successful searches using Adaptive Hash Index | | adaptive_hash_pages_added | 6787699 | 59.660 | counter | Number of index pages on which the Adaptive Hash Index is built | | adaptive_hash_pages_removed | 6165530 | 54.192 | counter | Number of index pages whose corresponding Adaptive Hash Index entries were removed | | adaptive_hash_rows_added | 971809814 | 8541.656 | counter | Number of Adaptive Hash Index rows added | | adaptive_hash_rows_removed | 450086501 | 3956.005 | counter | Number of Adaptive Hash Index rows removed | | adaptive_hash_rows_deleted_no_hash_entry | 59 | 0.001 | counter | Number of rows deleted that did not have corresponding Adaptive Hash Index entries | | file_num_open_files | 4 | NULL | value | Number of files currently open (innodb_num_open_files) | | ibuf_merges_insert | 281620042 | 1097.690 | status_counter | Number of inserted records merged by change buffering | | ibuf_merges_delete_mark | 21569667 | 84.074 | status_counter | Number of deleted records merged by change buffering | | ibuf_merges_delete | 3332320 | 12.989 | status_counter | Number of purge records merged by change buffering | | ibuf_merges | 13938295 | 54.328 | status_counter | Number of change buffer merges | | ibuf_size | 17478 | 0.068 | status_counter | Change buffer size in pages | | innodb_master_thread_sleeps | 82021 | 0.721 | counter | Number of times (seconds) master thread sleeps | | innodb_activity_count | 167679087 | 653.574 | status_counter | Current server activity count | | innodb_master_active_loops | 82021 | 0.721 | counter | Number of times master thread performs its tasks when server is active | | innodb_background_drop_table_usec | 58451 | 0.514 | counter | Time (in microseconds) spent to process drop table list | | innodb_ibuf_merge_usec | 2815277852 | 24744.692 | counter | Time (in microseconds) spent to process change buffer merge | | innodb_log_flush_usec | 2843869697 | 24995.998 | counter | Time (in microseconds) spent to flush log records | | innodb_dict_lru_usec | 25556359671 | 224625.875 | counter | Time (in microseconds) spent to process DICT LRU list | | innodb_checkpoint_usec | 417383675 | 3668.565 | counter | Time (in microseconds) spent by master thread to do checkpoint | | innodb_dblwr_writes | 1991948 | 7.764 | status_counter | Number of doublewrite operations that have been performed (innodb_dblwr_writes) | | innodb_dblwr_pages_written | 88472873 | 344.847 | status_counter | Number of pages that have been written for doublewrite operations (innodb_dblwr_pages_written) | | innodb_page_size | 16384 | NULL | value | InnoDB page size in bytes (innodb_page_size) | | innodb_rwlock_s_spin_waits | 48060144 | 187.327 | status_counter | Number of rwlock spin waits due to shared latch request | | innodb_rwlock_x_spin_waits | 5197488 | 20.259 | status_counter | Number of rwlock spin waits due to exclusive latch request | | innodb_rwlock_s_spin_rounds | 1476389094 | 5754.624 | status_counter | Number of rwlock spin loop rounds due to shared latch request | | innodb_rwlock_x_spin_rounds | 434074879 | 1691.924 | status_counter | Number of rwlock spin loop rounds due to exclusive latch request | | innodb_rwlock_s_os_waits | 42616148 | 166.108 | status_counter | Number of OS waits due to shared latch request | | innodb_rwlock_x_os_waits | 11139632 | 43.420 | status_counter | Number of OS waits due to exclusive latch request | | dml_reads | 5382057652 | 20978.019 | status_counter | Number of rows read | | dml_inserts | 1012583832 | 3946.818 | status_counter | Number of rows inserted | | dml_deletes | 12583838 | 49.049 | status_counter | Number of rows deleted | | dml_updates | 25177697 | 98.137 | status_counter | Number of rows updated | +------------------------------------------+---------------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+ 133 rows in set (0.00 sec)

To learn more about the INNODB_METRICS table check out the MySQL Manual as well as a Getting Started blog post by Oracle.

The post MySQL’s INNODB_METRICS table: How much is the overhead? appeared first on MySQL Performance Blog.

Percona Toolkit 2.2.12 is now available

November 17, 2014 - 10:44am

Percona is pleased to announce the availability of Percona Toolkit 2.2.12.  Released on November 14, Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

This release is the current GA (Generally Available) stable release in the 2.2 series. Downloads are available here and from the Percona Software Repositories.

New Features:

  • pt-stalk now gathers dmesg output from up to 60 seconds before the triggering event.

Bugs Fixed:

  • Fixed bug 1376561: pt-archiver was not able to archive all the rows when a table had a hash partition. Fixed by implementing support for tables which have primary or unique indexes.
  • Fixed bug 1217466: pt-table-checksum would refuses to run on Percona XtraDB Cluster if server_id was the same on all nodes. Fixed by using the wsrep_node_incoming_address as a unique identifier for cluster nodes, instead of relying on server_id.
  • Fixed bug 1269695: pt-online-schema-change documentation now contains more information about limitations on why it isn’t running ALTER TABLE for a table which has only a non-unique index.
  • Fixed bug 1328686: Running pt-hearbeat with --check-read-only option would cause an error when running on server with read_only option. Tool now waits for server read_only status to be disabled before starting to run.
  • Fixed bug 1373937: pt-table-checksum now supports none as valid --recursion-method when using with Percona XtraDB Cluster.
  • Fixed bug 1377888: Documentation was stating that pt-query-digest is able to parse a raw binary log file, while it can only parse a file which was decoded with mysqlbinlog tool before. Fixed by improving the documentation and adding a check for binary file and providing a relevant error message.

Details of the release can be found in the release notes and the 2.2.12 milestone at Launchpad. Bugs can be reported on the Percona Toolkit launchpad bug tracker.

The post Percona Toolkit 2.2.12 is now available appeared first on MySQL Performance Blog.

Typical misconceptions on Galera for MySQL

November 17, 2014 - 7:43am

Even if a Galera node looks like a regular MySQL server, the underlying replication mechanism is very different. This implies some changes in the way you have to configure the Galera nodes. Here are some of the most common misconceptions about Galera.

Q: Why should I enable binary logging as it is not needed by Galera replication?
Unlike for regular asynchronous MySQL replication, it is true that you don’t need to enable binary logging to use Galera replication. However what if someone runs an accidental DROP TABLE?

In this case, the statement will be replicated immediately on all nodes. Then your main option to recover lost data is to use a backup. But if binary logging is not enabled, all changes to the table after the backup will be lost.

What if you have an asynchronous slave that is intentionally delayed? This is another good option to quickly recover the lost data, but to be able to set up an async slave, you will also have to enable binary logging!

So don’t forget to add on all your nodes:

log_bin log_slave_updates binlog_format = ROW

Q: If I set innodb_flush_log_at_trx_commit = 2, I may lose data in some cases, right?
For a regular MySQL master, it is recommended to set innodb_flush_log_at_trx_commit = 1 because it is the only way to make sure that every committed transaction is stored permanently on disk. The main drawback is that it can slow down writes a lot because it involves one fsync at each commit.

With Galera, the difference is that commit is synchronous: this means that a transaction is committed on node #1, it has already been replicated to all the other nodes (it has not necessarily been executed on the remote nodes though).

As Galera implements cluster-wide durability, there is no need to have single server durability and you can safely use innodb_flush_log_at_trx_commit = 2 on all nodes.

Actually this is not totally accurate… For instance if all nodes lose power at the same time, you may lose some transactions. The likeliness of such a failure is related to how nodes are hosted: with each node in a separate datacenter, this is very unlikely. But with 3 nodes being VMs on a single physical host, it could well happen from time to time.

Q: The network RTT between my servers is 100ms. I know that each commit takes at least as much time as a network RTT so can I only hope to run 10 writes/s?

This needs some clarification: commit is synchronous because the full transaction is replicated to all nodes when it is committed. However you pay the same price whether the transaction has a single statements or several statements.

So if all your writes are auto-committed transactions, each write will trigger a commit that will need at least one RTT to complete. If RTT is 100ms, that means 10 writes/s.

But if your transactions have 10 writes, you will only need a commit every 10 writes: with 10 commits/s, you can now run 100 writes/s.

And finally several threads can commit at the same time, increasing the write throughput. With 10 concurrent threads executing transactions with 10 statements, you get 1000 writes/s.

Of course, this is only theory. In the real world, you will probably not get so perfectly aligned numbers. You can look at this post (Comparing Percona XtraDB Cluster with Semi-Sync replication Cross-WAN) to see real numbers with 1 thread and 32 threads.

Conclusion: network latency is a limiting factor for write throughput, that’s true. But that may not be as bad as you can think. And remember Callaghan’s law: “In a Galera cluster a given row can’t be modified more than once per RTT”.

I want to write on all nodes to get write scalability. Is it a good idea?

Let me start by stating that Galera cannot be a true solution for scale writes: the reason is simply that all writes need to be applied on all nodes.

But Galera brings a limited form of write scalability when writing concurrently to several nodes because:

  • Write sets can be applied in parallel on the remote nodes.
  • Galera is using row-based replication, so applying the replicated events can be faster than executing the original write.

There is a caveat however when writing on several nodes: because of optimistic locking, concurrent transactions on different nodes can lead to write conflicts. In this case Galera will roll back one of the transactions, and it is up to the application to retry executing the transaction.

Conclusion
Galera is a nice technology that can help solve challenges around high availability. But a basic understanding of how it works is useful as it can avoid frustration or unreasonable expectations.

The post Typical misconceptions on Galera for MySQL appeared first on MySQL Performance Blog.

RDS for Aurora unveiled at AWS re:Invent

November 14, 2014 - 8:25am

One of the big announcements at the Amazon Web Services re:Invent 2014 conference this week was the unveiling of Aurora. The result of years of internal work, Aurora, currently in preview, is a MySQL 5.6-compatible option that “combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases” on the AWS infrastructure. The Aurora database engine will be made available through the Amazon RDS for Aurora service. This new database option is another example of the vibrant innovation coming from the MySQL ecosystem and key role that relational databases play in applications of today and tomorrow.

Integration with other AWS components

Since the Aurora relational database engine will only be made available as a service via RDS, Amazon Web Services can do some interesting new things including:

  • Fully leveraging available compute, memory and networking resources
  • Automatically growing the size of database volumes as storage needs grow
  • Tighter integration with AWS monitoring and restore capabilities
  • Moving from RDS for MySQL to RDS for Aurora will be enabled by a migration tool
Aurora performance

In the keynote, Amazon Web Services shared that Aurora delivers up to five times the performance of stock MySQL 5.6 without requiring any changes to most MySQL applications. Their FAQ reveals some insights into their testing methodology: “Amazon Aurora delivers over 500,000 SELECTs/sec and 100,000 updates/sec, five times higher than MySQL running the same benchmark on the same hardware.”

More RDS for Aurora details coming soon

We’re very excited about Amazon pushing the boundaries in relational databases for the enterprise and look forward to see what we, as a MySQL community, can learn from their new cloud-centric and cross-service approach.

Like many, Percona is looking forward to trying out Aurora. We’ll definitely do some benchmark testing to compare additional database options such as a tuned Percona Server 5.6. Additionally, we’ll share our expert advice and ideas for improvement that we uncover from our testing of RDS for Aurora back to the team at AWS so the service becomes better for all.

The post RDS for Aurora unveiled at AWS re:Invent appeared first on MySQL Performance Blog.

Q&A: Percona XtraDB Cluster as a MySQL HA solution for OpenStack

November 14, 2014 - 6:00am

Thanks to all who attended my Nov. 12 webinar titled, “Percona XtraDB Cluster as a MySQL HA Solution for OpenStack.” I had several questions which I covered at the end and a few that I didn’t. You can view the entire webinar and download the slides here.

Q: Is the read,write speed reduced in Galera compared to normal MySQL?

For reads, it’s the same (unless you use the sync_wait feature, used to be called causal reads).

For writes, the cost of replication (~1 RTT to the worst node), plus the cost of certification will be added to each transaction commit.  This will have a non-trivial impact on your write performance, for sure.  However, I believe most OpenStack meta store use cases should not suffer overly by this performance penalty.

Q: Does state transfers affect a continuing transaction within the nodes?

Mostly, no.  The joining node will queue ongoing cluster replication while receiving its state transfer and use that to do its final ‘catchup’.  The node donating the state transfer may get locked briefly during a full SST, which could temporarily block writes on that node.  If you use the built-in clustercheck (or check the same things it checks), you can avoid this by diverting traffic away from a donor.

Q: Perhaps not the correct webinar for this question, but I was also expecting to hear something about using PXC in combination with OpenStack Trove. If you’ve got time, could you tell something about that?

Trove has not supported the concept of more than a single SQL target.  My understanding is a recent improvement here for MongoDB setups may pave the way for more robust Trove instances backed by Percona XtraDB Cluster.

Q: For Loadbalancing using the Java Mysql driver, would you suggest HA proxy or the loadbalancing connection in the java driver. Also how does things work in case of persistent connections and connection pools like dbcp?

Each node in a PXC cluster is just a mysql server that can handle normal mysql connections.  Obviously if a node fails, it’s easy to detect that you should probably not keep using that node, but in Percona XtraDB Cluster you need to watch out for things like cluster partitions where nodes lose quorum and stop taking queries (but still allow connections)  I believe it’s possible to configure advanced connection pooling setups to properly health check the nodes (unless the necessary features are not in the pool implementation), but I don’t have a reference architecture to point you to.

Q: Are there any manual solutions to avoid deadlocks within a high write context to force a query to execute on all nodes?

Yes, write to a single node, at least for the dataset that has high write volume.

Remember what I said in the webinar:  you can only update a given row once per RTT, so there’s an upper cap of throughput on the smallest locking granularity in InnoDB (i.e., a single row).

This manifests itself in two possible ways:

1) In a multi-node writing cluster by triggering deadlocks on conflicts.  Only approximately 1 transaction modifying a given row per RTT would NOT receive a deadlock

2) In a single-node writing cluster by experiencing longer lock waits.  Transaction times (and subsequently lock times) are extended by the replication and certification time, so other competing transactions will lock wait until the blocking transaction commit. There are no replication conflict deadlocks in this case, but the net effect is exactly the same:  only 1 transaction per row per RTT.

Galera offers us high data redundancy at the cost of some throughput.  If that doesn’t work for your workload, then asynchronous replication (or perhaps semi-sync in 5.7) will work better for you.

Note that there is wsrep_retry_autocommit.  However, this only works for autocommited transactions.  If your write volume was so high that you need to increase this a lot to get the conflict rate down, you are likely sacrificing a lot of CPU power (rollbacks are expensive in Innodb!) if a single transaction needs multiple retries to commit.  This still doesn’t get around the law:  1 trx per row per RTT at best.

That was all of the questions. Be sure to check out our next OpenStack webinar on December 10 by my colleague Peter Boros. It’s titled “MySQL and OpenStack Deep Dive” and you can register here (and like all of our webinars, it’s free). I also invite you to visit our OpenStack Live 2015 page and learn more about that conference in Santa Clara, California this coming April 13-14. The Call for Papers ends Nov. 16 and speakers get a full-access conference pass. I hope to see you there!

The post Q&A: Percona XtraDB Cluster as a MySQL HA solution for OpenStack appeared first on MySQL Performance Blog.

Optimizing MySQL for Zabbix

November 14, 2014 - 3:00am


This blog post was inspired by my visit at the annual Zabbix Conference in Riga, Latvia this year, where I gave a couple of talks on MySQL and beyond.

It was a two day single-track event with some 200 participants, a number of interesting talks on Zabbix (and related technologies) and really well-organized evening activities. I was amazed how well organized the event was and hope to be invited to speak there next year as well.   Zabbix is an enterprise-class open source distributed monitoring solution for networks and applications.

I must secretly confess, it was also the first conference where I honestly enjoyed being on stage and connecting with the audience – I was even looking forward to it rather than being scared as hell (which is what typically happens to me)! I guess it was all about the positive atmosphere, so big thanks to all the speakers and attendees for that. It meant a lot to me.

If I had to mention one negative vibe I heard from attendees, it would be that there was not enough deeply technical content, however, I think this is slightly biased, because people I talked to most, were ones who enjoyed my technical talks and so they were craving for more.

And now, without further ado, let me get to the essence of this blog post.

Zabbix and MySQL

The very first thing I did when I arrived at the conference was to approach people who I knew use Zabbix on a large scale and tried to figure out what were the biggest challenges they face. Apparently, in all of the cases, it was MySQL and more specifically, MySQL disk IO.

With that in mind, I would like to suggest a few optimizations that will help your MySQL get the best out of your disks (and consequentially will help your Zabbix get the best out of MySQL) and the available hardware resources in general.

SSD is a game changer

“Will MySQL would run better on SSDs?” I’ve been hearing this question over and over again, both publicly and privately.

I can tell you without a shadow of doubt, if IO is currently your bottle-neck – either because some queries take a long time to run and you see diskstat reporting 100-250 reads per second until the query completes (latency), or because you are overloading the disks with requests and wait time suffers (throughput), SSDs will definitely help and not just by little, by much!

Consider this: the fastest-spinning disk can do 250 random IO operations per second tops (at this point it is limited by physics) and single query will only ever read from one disk even if you have RAID10 made of 16 disks, so if you need to read 15,000 data points to display a graph, reading those data points from disk will take 60s.

Enterprise-class SSD disk, on the other hand, can do 15,000 or even more 16k random reads per second with a single-thread (16k is the size of an InnoDB block). And as you increase the throughput, it only gets better! So that means that the query in the previous example would take 1s instead of 60s, which is a significant difference. Plus you can run more requests on the same SSD at the same time and the total number of IO operations will only increase, while a single spinning disk would have to share the available 250 IO operations between multiple requests.

The only area where SSDs don’t beat spinning disks (yet) is sequential operation, especially single-threaded sequential writes. If that is your typical workload (which might be the case if you’re mostly collecting data and rarely if ever reading it), then you may want to consider other strategies.

MySQL configuration

Besides improving your disk IO subsystem, there’s ways to reduce the pressure on IO and I’m going to cover a few my.cnf variables that will help you with that (and with other things such as internal contention).

Note, most of the tunables are common for any typical high-performance MySQL setup, though some are explicitly suited for Zabbix because you can relax a few parameters for great effect at the price of, in the worst case, loosing up to 1s worth of collected data which, from discussions during the conference, didn’t seem like a big deal to anyone.

- innodb_buffer_pool_size – if you have a dedicated MySQL server, set it as high as you can (ceiling would be 75% of total available memory). Otherwise, you should balance it with other processes on the server, but if it’s only zabbix server, I would still leave it very high, close to 75% of total RAM.

- innodb_buffer_pool_instances – on MySQL 5.5, set it to 4, on MySQL 5.6 – 8 or even 16.

- innodb_flush_log_at_trx_commit = 0 – this is where you compromise durability for significantly improved write throughput, especially if you don’t own a disk subsystem with non-volatile cache. Basically the loss you may incur is up to 1s worth of writes during MySQL or server crash. A lot of websites actually run with that (a lot of websites still run on MyISAM!!!), I’m quite sure it’s not an issue for Zabbix setup.

- innodb_flush_method = O_DIRECT – if you are running Linux, just leave it set to that.

- innodb_log_file_size – you want these transaction logs (there’s two of them by default) to hold 1 to 2 hours worth of writes. To determinte that, you can probably have a look at the Zabbix graphs for your MySQL server, but also you can run the following from the mysql command line:

mysql> pager grep seq; show engine innodb statusG select sleep(3600); show engine innodb statusG PAGER set to 'grep seq' Log sequence number 8373513970951 ... Log sequence number 8373683996767

The difference between the two numbers is how many bytes InnoDB has written during last hour. So on this server above, I would set innodb_log_file_size=128M and would end up with 256M of log file space allowing me to store more than 1h worth of writes in transaction logs (See this on changing the log file size if you run MySQL 5.5 or earlier)

- innodb_read_io_threads, innodb_write_io_threads – don’t overthink these, they are not as important as they may seem, especially if you are using Async IO (you can check that by running “show global variables like ‘innodb_use_native_aio'” in mysql cli). On MySQL 5.5 and 5.6 you generally want to be using Async IO (AIO), so check mysql log to understand why, if you are not. That said, if you are not using AIO and you are not going to, just set these values to 8 and leave them there.

- innodb_old_blocks_time = 1000 – this will help you prevent buffer pool pollution due to occasional scans. This is now default in MySQL 5.6 (On 5.5, it needs to be set explicitly).

- innodb_io_capacity – set this to as many write iops as your disk subsystem can handle. For SSDs this should be at least few thousand (2000 could be a good start) while for rotating disks somewhat lower values – 500-800, depending on number of bearing disks, will do. Best to benchmark disks or do the math for actual numbers, but default of 200 is definitely too low for most systems nowadays.

- sync_binlog=0 – this is the default setting, but just in case it’s above 0, turn it off, unless you run something else besides Zabbix. The price of not synchronising binary logs is that in case of a master crash, replicas can get out of sync, but if you are constantly hitting IO bottle-neck due to binary log synchronisation just because you want to avoid the hassle of synchronising the slave once every five years when master crashes, you should reconsider this option.

- query_cache_size=0, query_cache_type=0 – that will disable the query cache. Most of the time you don’t want query cache. And if it’s not disabled in the kernel by these settings, queries (especially small ones) will likely suffer due to query cache mutex contention.

- sort_buffer_size, join_buffer_size, read_rnd_buffer_size – if you ever configured these variables, cancel those changes (just remove them or comment them out). I find these are the top three mistuned variables on most customer servers, while in many cases it’s best if they are not touched at all. Just leave them at their defaults and you’re set.

- tmpdir – sometimes it’s a good idea to point tmpdir to /dev/shm so that on-disk temporary tables are actually written to memory, but there’s one important caveat starting with MySQL 5.5: if you do that, it disables AIO acorss the board, because tmpfs does not support AIO. So I would monitor the activity on current tmpdir (/tmp usually) and only switch it to /dev/shm if I see it being an issue.

MySQL Partitioning

I know that Zabbix now supports partitions with a purpose of easier data pruining, however I think there are some extra benefits you could get from partitions. Well actually subpartitions if you are already using partitions by date.

The KPI for Zabbix, that you could hear over and over again, is the “new values per second” number that you can find in the status of Zabbix. Basically the higher the value (given you have enough values to monitor), the better is the throughput of your Zabbix. And this is where a lot of people are hitting the Zabbix limits – MySQL can’t insert enough new values per second.

Besides the optimizations I have already mentioned above (they should greatly increase your write throughput!), I would encourage you to try out partitions (if you’re not using partitions already) or subpartitions (if you are) BY HASH as we found that partitioning in some cases can increase the throughput of InnoDB.

I did not test it with Zabbix specifically and as it’s not supported by Zabbix out of the box, you would have to hack it to make it work, but if you’ve done all the changes above and you still can’t get enough new values per second (AND it is not the hardware that is limiting you), try partitioning or subpartitioning the key tables by hash.

If this sounds interesting but you’re not sure where to start, feel free to contact us and we’ll work with you to make it work.

On MySQL High Availability

There are options to make MySQL highly available, even though many believed it’s not the case. We’ve been writing a lot on it on our blog so I will not paraphrase or repeat, instead I would like to point you to a few valuable resources on that topic:

 

Percona Server, Percona XtraDB Cluster, Percona Toolkit – it’s all FREE!

I’m not really sure why, but many people I talked to at the conference thought that all of the Percona software either needs to be bought or that it has some enterprise features that are not available unless you buy a license.

The truth is that neither of it is true. All Percona software is completely free of charge. Feel free to download it from our website or through repositories and use it as you please.

See you at the Zabbix conference next year!

The post Optimizing MySQL for Zabbix appeared first on MySQL Performance Blog.

Percona Live London 2014 Wrap Up

November 13, 2014 - 6:16am

The 2014 edition of Percona Live London brought together attendees from 30 countries to hear insightful talks from leaders in the MySQL community. The conference kicked off on Monday with a full day of tutorials followed by the very popular Community Dinner featuring a double decker bus shuttle from the conference to the event.

Tuesday started with keynote talks by representatives from MySQL, VMware, HGST, Codership, and Percona. I particularly enjoyed the talks by Tomas Ulin of MySQL (which highlighted the upcoming MySQL 5.7 release) and Robert Hodges of VMware (which focused on the evolution of MySQL). The remainder of the day was filled by six time slots of breakout sessions (30 sessions in all) broken into 10 tracks. The day wrapped up with the always popular Community Networking Reception. Attesting to the quality of the conference, 4 out of 5 respondents to our post conference survey indicate they are likely to attend the conference again in 2015.

The session slides are available by visiting the Percona Live London 2014 conference website (look for the “Session Slides” button in the right hand column). Slides are added as they come in from the speakers so please check back if the slides are not yet available for a specific talk that interests you.

Special thanks goes out to the Percona Live London 2014 Conference Committee which put together such a great program:

  • Cedric Peintre of Dailymotion
  • David Busby of Percona
  • Colin Charles of MariaDB
  • Luis Motta Campos of the ebay Classified Group
  • Nicolai Plum of Booking.com
  • Morgan Tocker of Oracle
  • Art van Scheppingen of Spil Games
Percona Live London 2014 Attendee Survey

This year we collaborated with ComputerworldUK to run a short survey at the conference which should appear in that publication in the near future. We had 64 responses, all of them being form MySQL professionals who attended the conference. The results were interesting:

Do you agree with the statement that “Oracle has been a good steward of MySQL over the past twelve months”?
YES = 81%
NO = 19%

Are you currently running a production OpenStack environment in your organization?
YES = 17%
NO = 83%

Have you evaluated OpenStack within the past twelve months?
YES = 25%
NO = 75%

Do you plan to evaluate OpenStack in the next twelve months?
YES = 48%
NO = 52%

Are you currently using an AWS product to run MySQL in the Cloud?
YES = 28%
NO = 72%

Are you more likely to switch to a non-MySQL open source database now than you were twelve months ago?
YES = 35%
NO = 65%

The sentiment about Oracle’s stewardship of MySQL compares favorably with the comments by our own Peter Zaitsev in a recent ZDNet article titled “MySQL: Why the open source database is better off under Oracle“.

Percona Live MySQL Conference and OpenStack Live Conference

The ratings related to OpenStack mirror our experience with the strong growth in interest in that technology. In response, we are launching the inaugural OpenStack Live 2015 conference in Silicon Valley which will focus on making attendees more successful with OpenStack with a particular emphasis on the role of MySQL and Trove. The event will be April 13-14, 2015 at the Santa Clara Convention Center. The call for proposals closes on November 16, 2014.

Our next Percona Live MySQL Conference and Expo is April 13-16, 2015 in Silicon Valley. Join us for the largest MySQL conference in the world – last year’s event had over 1,100 registered attendees from 40 countries who enjoyed a full four days of tutorials, keynotes, breakout sessions, networking events, BOFs, and more. The call for speaking proposals closes November 16, 2014 and Early Bird registration rates are still available so check out the conference website now for full details.

Thanks to everyone who helped make Percona Live London 2014 a great success. I look forward to the Percona Live MySQL Conference and the OpenStack Live Conference next April in Silicon Valley!

The post Percona Live London 2014 Wrap Up appeared first on MySQL Performance Blog.

Pages

]]>