Optimizing MySQL Performance: Choosing the Right Tool for the Job

Next Wednesday, I will present a webinar about MySQL performance profiling tools that every MySQL DBA should know.

Application performance is a key aspect of ensuring a good experience for your end users. But finding and fixing performance bottlenecks is difficult in the complex systems that define today’s web applications. Having a method and knowing how to use the tools available can significantly reduce the amount of time between problems manifesting and fixes being deployed.

In the webinar, titled “Optimizing MySQL Performance: Choosing the Right Tool for the Job,” we’ll start with the basic top, iostat, and vmstat then move onto advanced tools like GDB, Oprofile, and Strace.

I’m looking forward to this webinar and invite you to join us April 16th at 10 a.m. Pacific time. You can learn more and also register here to reserve your spot. I also invite you to submit questions ahead of time by leaving them in the comments section below. Thanks for reading and see you next Wednesday!

The post Optimizing MySQL Performance: Choosing the Right Tool for the Job appeared first on MySQL Performance Blog.


Running MySQL 5.6 on Amazon RDS: Webinar followup questions answered

Amazon RDSThanks to everyone who attended last week’s webinar, Running MySQL 5.6 on Amazon RDS.” If you weren’t able to attend, the recording and slides are available for viewing/download (or, if you were able to attend and just want to see it again). I’ve also answered the questions I didn’t have a chance to field during the event:

Q: Would you recommend Amazon RDS over manually setting up MySQL/Percona server on an EC2 instance?
A: This depends on many factors including your data set size, workload, uptime requirements, what the rest of your stack looks like, and many other factors.

Q: At Q&A time, can you go more into 5.6 InnoDB full text search vs the Amazon search offering? Was that CloudSearch you meant? Are they comparable functionality?
A: I’ll blog in more detail about Amazon’s CloudSearch in the near future.

Q: Provisioned IOPS cost – ballpark figures?
A: The official RDS Overview page has detailed answers, but somewhere (depending on the Region) around $0.10 per IOPS-Month ($0.20 for multi-AZ deployments) , in addition to the $0.125 per GB-month storage fee ($0.25 for multi-AZ).

Q: I want to have a minimal outage migration to custom applications using Amazon RDS mysql 5.5 to mysql 5.6. In non-RDS I make a Mysql 5.6 replica os the mysql 5.5 and that have a quick switch to the mysql 5.6 replica. What can I do in RDS?
A: Nothing. Yet. Quote from the AWS blog:

“Upgrading an existing database instance from MySQL 5.5 to MySQL 5.6 is not currently supported. However, we intend to provide this functionality in the near future.

In the meantime, if you would like to port your existing MySQL 5.5 database to MySQL 5.6, you can use mysqldump to export your database from your existing MySQL 5.5 database instance and import it into a new MySQL 5.6 database instance.”

Q: We finally got to meet the background baby on this slide :-) Awesome! Goes down as my favorite already!
A: Apologies for the baby in the background. It should be noted that those were screams of JOY as she played with Elmo.

Q: Can IOPS be easily increased as well (similar to storage)?
A: Yes. Simply click “Instance Actions” and then “Modify” and the option to increase provisioned IOPS is there.

Q: How do you shutdown a rds i see nothing in the console?
A: Simply click “Instance Actions” and then “Delete”. This will walk you through the process of deleting an RDS instance

Q: Is it possible to shutdown RDS instances when you don’t need them ? impact on pricing ?
A: Yes, it is possible (see previous answer). For on-demand instances, you only pay for the hours you use, so once they are deleted, you won’t pay for them any more (reserved instances are another story, which I covered in the webinar).

Q: Have any clue of how Amazon RDS backups mysql instances? is it with LVM snapshots, a tool like xtrabackup?
A: My *guess* would be basically the equivalent of EBS snapshots, but that’s only a guess. Anybody from Amazon want to comment?

Q: Amazon RDS Standby is different than a Read Replica, correct? Do we have access to the RDS Standby like we do with a Read Replica?
A: Correct. A read replica is different from the multi-AZ standby. There is no access to the RDS standby (except in the event of a failover, when it becomes the primary).

Q: What is the best way to migrate existing data (on MySQL instance in EC2) into Amazon RDS?
A: mysqldump. There are other, more complicated, ways. But mysqldump is the easiest and most proven.

Q: I need to set outgoing firewall rules to the multi-AZ RDS server. Since I only get a CNAME from RDS, do I have to set my outgoing firewall to the entire internet on port 3306?
A: I’m not sure that I follow. Access to the instance should be controlled via RDS Security Groups (or VPC setup).

Thanks again for attending and submitting your questions; Amazon is making constant improvements in RDS, making it a more-and-more compelling option to a number of organizations.

The post Running MySQL 5.6 on Amazon RDS: Webinar followup questions answered appeared first on MySQL Performance Blog.


Amazon RDS with MySQL 5.6 – Configuration Variables

One longstanding complaint I have heard for the past several years, and still hear today, is that Amazon’s Relational Database Service (RDS) does not allow the configuration flexibility as running MySQL in an ec2 instance. While true, this ignores the consistent work that Amazon has done to provide access to the most important configuration variables needed to tune a MySQL instance (after all, how relevant is it for a customer to set bind_address in an RDS instance).

Let’s take a look visually:

Screen Shot 2013-08-18 at 11.39.50 AM

MySQL provides 523 options (35 of them NDB specific, so aren’t relevant to RDS), while RDS provides (via the web UI) 283, with 58 of those being immutable (things like basedir, datadir, and a variety of other variables).

So, what’s missing from the RDS configuration? The system variables can be roughly grouped into the following categories:

  • Audit Logs
  • Memcached Daemon
  • Binary Log Settings
  • Performance Schema
  • Relay Log Settings
  • Semi-Sync Replication
  • SSL
  • Thread Pool
  • Other

Let’s look at the relevance of these individually:

Audit Logs

The Audit log PlugIn is a commercial extension not available in the MySQL Community Edition offered by Amazon, so it’s not relevant.

Memcached Daemon

RDS is designed for relational database access, not key-value store access. If you need Memcached functionality, check out Amazon’s ElastiCache

Binary Log Settings

Binary logging is enabled by default on RDS, you just lose the ability to:

  • Use the old version of binary logging (pre-5.6.6)
  • Specify where the binlogs are saved or their base name
  • Control the maximum binary log size

The flexibility of controlling the maximum binary log size would be helpful in some workloads, but isn’t something that is generally tuned in the majority of engagements that I have been a part of.

Performance Schema

That these configuration parameters are not available via the Web UI is a bit of a misnomer. It is possible to enable/disable the Performance Schema and then control the collection via SQL as usual.

Relay Log Settings

Like the Binary Log settings, there is not much that we would want to tune here. The standard settings are appropriate for general workloads.

Semi-Sync Replication

Amazon RDS has a proprietary failover solution and block level replication across availability zones. It is not surprising that this functionality is not provided by default in the Web UI, but certainly something that could be useful for a small cross section of workloads.


For companies with strict security needs, the lack of SSL may be a deal breaker for using RDS. But, depending upon the security policies in place, can be worked around by using Amazon’s VPC with SSL. For many companies, though, this may not play a role in the decision process. I find it hard to believe that, with Amazon’s resources, providing this is an insurmountable technical challenge. Perhaps we’ll see this becoming available in future RDS releases.

Thread Pool

The Thread Pool PlugIn is a commercial extension not available in the Community Edition of MySQL, so is not relevant to what RDS provides. There are, however, solutions in both Percona Server and MariaDB that Amazon may choose to port in the future.


Amazon still has a ways to go to be fully compatible with configuration variables, but by and large the important ones are available to customers, with minor exception (I’m looking at you, innodb_log_file_size).

I’ll be talking about this topic in more detail, as well as a variety of other RDS 5.6-specific issues, in my upcoming Webinar on August 28 titled “Running MySQL 5.6 on Amazon RDS.”

The post Amazon RDS with MySQL 5.6 – Configuration Variables appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com