Thank you for attending my webinar on Wednesday, December 20, 2017, InnoDB Performance Optimization. In this blog, I will provide answers to the Q & A for the webinar.

Are the T2 CPUs similar to the M4 series?

I would expect them to be similar. Amazon does not disclose what specific version of CPUs they use for T2 instances. More details are available here.

Delay in spinlock code is pretty old code. Need to optimize based on today’s CPU? Your views?

There have been a number of improvements to the InnoDB Spinlock code during the last few years. For example, using CPU …

The website or mobile app is the storefront for participating in the modern digital era. It’s your portal for inviting users to come and survey your products and services. Much attention focuses on front-end development; this is where the HMTL5, CSS, and JavaScript are coded to develop the landing page that everyone sees when they visit your site.

But the real magic happens on the backend. This is the ecosystem that really powers your website. One writer has articulated this point very nicely as follows:

The technology and programming that “power” a site—what your end user doesn’t see but what makes the site run—is called the back end. Consisting of the server, the database, and the server-side applications, it’s the behind-the-scenes functionality—the brain of a site. …

As part of our support services, we do a lot of query optimization. This is where most performance gains come from. Here’s an example of the work we do.

Some days ago a customer arrived with the following table:

CREATE TABLE `infamous_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL DEFAULT '0',
  `email` varchar(200) NOT NULL DEFAULT '',
  `msg_type` varchar(255) NOT NULL DEFAULT '',
  `t2send` int(11) NOT NULL DEFAULT '0',
  `flag` char(1) NOT NULL DEFAULT '',
  `sent` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `f` (`flag`),
  KEY `email` (`email`),
  KEY `msg_type` (`msg_type`(5)),
  KEY `t_msg` (`t2send`,`msg_type`(5))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And a query that looked like this:

SELECT COUNT(*)
  FROM `infamous_table`
 WHERE `t2send` > 1234
   AND …

Sometimes we are not sure if the database design we have created and our site has been using is correct and optimised. We all do have this feeling at times don't we :)


Well, MySQL provides with PROCEDURE ANALYSE() to help us detect inconsistencies in our database design by suggesting for an optimal datatype and data length for columns.

The syntax for using PROCEDURE ANALYSE() is as below:

SELECT ... FROM ... WHERE ... PROCEDURE
ANALYSE([max_elements,[max_memory]])

Ref: …

Welcome to another post in the series of Percona Live featured tutorial speakers blogs! In these blogs, we’ll highlight some of the tutorial speakers that will be at this year’s Percona Live conference. We’ll also discuss how these tutorials can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!

In this Percona Live featured tutorial, we’ll meet Øystein Grøvlen, Senior Principal Software Engineer at Oracle. His tutorial is on How to Analyze and Tune MySQL Queries for Better Performance. SQL query …

Very often MySQL is much less stable than we realize. In this video I explain how to detect and dianose these MySQL stalls that last for 5-10 seconds or more.

The reason nobody knows about these issues is that they’re:
a) rare
b) intermittent
c) monitoring software can’t catch them

You can use pt-stalk to detect and diagnose such issues. All of the tools I have mentioned in this video are part of Percona Toolkit (no wonder they all start with PT), you can find the toolkit here:

Percona Toolkit

Here’s the main commands I have discussed in this video:

Starting pt-stalk in foreground

# pt-stalk

Starting pt-stalk in background with email notification:

# pt-stalk …

When your car doesn’t start, you don’t just blindly change the battery, starter, fuel pump, spark plugs or all of the above. Instead, you go to your mechanic and ask him to check what is wrong (or you check it yourself if you are the mechanic) and then fix whatever is broken.

Yet very often I see DBAs doing exactly the opposite with their MySQL servers. Rather than assessing what is the server so busy with, they keep changing configuration options until the problem “goes away”. Alternatively, they add more RAM, more CPUs or faster disks, depending on which resources seems to be the most busy at a time. Or they switch to a new server altogether.

MySQL (with a help of some tools) has a really convenient way to analyse the workload and see clearly what exactly is MySQL so busy doing. And even how much improvement you can expect by, say, fixing a specific MySQL query.

…

Thank you for attending my 22nd July 2016 webinar titled “Top Most Overlooked MySQL Performance Optimizations“. In this blog, I will provide answers to the Q & A for that webinar.

For hardware, which disk raid level do you suggest? Is raid5 suggested performance-wise and data-integrity-wise?
RAID 5 comes with high overhead, as each write turns into a sequence of four physical I/O operations, two reads and two writes. We know that RAID 5s have some write penalty, and it could affect the performance on spindle disks. In most cases, we advise using alternative RAID levels. Use RAID 5 when disk capacity is more important than performance (e.g., archive databases …

Are you looking to improve your MySQL performance? Application success is often limited by poor MySQL performance. Please join Percona CEO and Founder Peter Zaitsev for this exclusive webinar on Wednesday, July 20th, 2016 at 11:00 AM PDT (UTC – 7) as he presents “Practical MySQL Performance Optimization“.

Peter Zaitsev discusses how to get excellent MySQL performance while being practical. In other words, spending time on what gives you the best return. The webinar updates Peter’s ever-popular Practical MySQL Performance Optimization presentation. It covers the important points for improving MySQL performance. It also includes a discussion of the new tools and features in the latest MySQL 5.7 …

Proper MySQL Query Optimization starts with a proper Slow Query Logging session. And MySQL Query Optimization is where I spend 70-80% of my time when doing MySQL performance optimization.

In part 2 here, we will go over the pt-query-digest report, that we have prepared in part 1.

Here’s links to the other two parts:

• Advanced MySQL Slow Query Logging Part 1: the essentials
• Advanced MySQL Slow Query Logging Part 3: fine-tuning the logging process

The post Advanced MySQL Slow Query Logging Part 2: …