Introduction to MySQL Troubleshooting Webinar: Q & A for How to Create a Test Setup

MySQL Troubleshooting WebinarThank you for attending my March 2 MySQL troubleshooting webinar “Introduction to troubleshooting: How to create test setup,” which is the first in the “MySQL Troubleshooting Webinar” series. The recording and slides for the webinar are available here. Here is the list of your questions that I wasn’t able to answer during the webinar, with responses.

Q: “Copy Option file from Production to test server,” which option file we are talking about?

A: The MySQL configuration file, usually /etc/my.cnf . See also this manual.

Q: For future seminars, what environment we need to setup?

A: There is no special environment, but I will use a standard employee database for many examples. If you want to run it you can install it in advance. It is also better to have MySQL version 5.7, because I will speak about features that don’t exist in older versions.

Q: Hello, may I have a copy of the presentation as a future reference?

A: Yes, you can find it here

Q: What is the difference between mysqldump and mysqldbcopy and mysql sandbox ? Which one is better for this?

A: MySQL Sandbox is set of utilities that will help you to quickly install one or more MySQL servers in the same host, either standalone or in groups. It extracts *tar.gz archive, creates default directories, initializes the system database, creates default configuration files and aliases to utilities, but it does not dump or load data for you. mysqldump is just a backup program that does logical backup of your databases. mysqldbcopy copies database objects between two servers, or between two databases on the same server, without creating intermediary dump file. These are completely different tools that should be used for different purposes: MySQL Sandbox to quickly install from *tar.gz package into custom directory, mysqldump to make a dump of your data and mysqldbcopy to quickly clone a database.

Q: Open source project. Will MySQL  behave like a Java program when garbage collections hang temporally execution?

A: This is not normal behavior, but can happen in a high-load environment. For example, when InnoDB starts purging the history list aggressively and a bunch of new queries comes in. You can read about issues with InnoDB flushing and purge in these posts: 1, 2.

The InnoDB development team works on making these stalls smaller from version to version, though.

Q: What is the effect of disk speed? Why is MySQL software affected by disk speed ? I thought that the faster a disk is, the faster the database is…?

A: I believe you are asking about innodb_io_capacity option, and why it is not dynamically set inside InnoDB. This option sets an upper limit of IO activity for background tasks that happen inside InnoDB (when it flushes data to disk, for example). This is basically “how many pages per second to flush?” Faster disks can perform more operations per second than slower disks, therefore bigger values are suited for faster disks and smaller for slower. Read this blog post, it has greater detail about how InnoDB flushing works. Regarding why this number is not calculated by InnoDB itself, you should, of course, ask InnoDB developers (I can only speculate!). But I think it’s because desired disk speed is not something “built in stone” by the manufacturer. It often depends on various factors.

Regarding why this number is not calculated by InnoDB itself,  you should, of course, ask InnoDB developers, because I can only speculate. But I think this is because desired disk speed is not something “built in stone” by the manufacturer, but can depend on many factors. For example, if you run MySQL on a non-dedicated server,  you probably would want to leave some resources for other software.Another case is if you have to temporarily save backups on the same filesystem where data directory is located.

Q: For innodb_io_capacity, what specifically does that limit? IOPS that can be initiated?

A: It specifies how many pages per second InnoDB can flush or merge from the change buffer in the background. Actually, the documentation for this option is very good, and I recommend you use it.

Q: You are focusing on InnoDB. But what’s about the others? MyISAM for example? Is it possible that my applications would work faster on MyISAM instead of InnoDB?

A: In MySQL 5.7, only in one case: point-select workloads, which means a single select that reads a row by its primary key. You will find benchmarks comparing MyISAM vs InnoDB in this post by Dimitri Kravtchuk. Follow the links in the beginning of the post to find the benchmarks for earlier versions. I will mention MyISAM in future webinars. I am also thinking about making webinar about storage engines specifics and probably will do separate webinar for MyISAM.

I will address MyISAM in future webinars. I am also thinking about making a webinar about storage engines specifics, and probably will do a separate webinar for MyISAM.

Q: mysqldbcopy? Is it available with Linux Ubuntu version?

A: Yes. MySQL Utilities is a set of Python programs, so they work everywhere Python is installed. There is a separate package for Ubuntu.

Q: What do you use to keep data synced between the master/slaves/dev environments?

A: If you want to keep data synced, you simply need to set up a master-slave environment. If you want to sync tables that already exist, but one of them has slightly outdated data, you can use pt-table-sync.

Q: For the binary backup do we have to do both of those things or just the cp?

A: Any of the commands from the slide work: either cp or XtraBackup.

Q: How we could identify slow queries? 

A: I will run a separate webinar called “Introduction to Troubleshooting Performance” where I will describe this in detail, but usually you can find such queries in the slow query log.

Q: Have you more dedicated recommendations for All-Flash storages or/and Amazon?

A: Regarding test setup: no. But if you run MySQL server on Amazon and want to test it on a local machine or just a cheaper instance, pay attention to slides about how to avoid limitations.

Q: No offense but so far too much time wasted on slides that convey things we already know…let’s get right to the main reason for the webinar!

A: Well, this was introductory webinar. I am planning to have about 16. The next two will also be introductory: “Introduction to MySQL Troubleshooting: Basic Techniques” and “Intro to Troubleshooting Performance: What Affects Query Execution” (exact date TBD). If you are an experienced user, you can re-join starting from the fourth webinar where I will speak about specifics like storage engines, replication and options.

Q: Is there a way to simulate the production load to the test server for better troubleshooting with MySQL?  I got that we restore the data. However I am keen to simulate the load with all the queries that were running on production to test.

A: This is good question, and I believe that many people who do QA or troubleshooting  want such a tool. The tool exists: this is QueryPlayback (formerly known Percona Playback), designed by Percona. But the issue with all such tools is that they aren’t perfect. For example, QueryPlayback is hampered by these pretty serious bugs: 1, 2. Also, replaying queries that modify data doesn’t always work if you did not have a backup taken prior to the query run in production. This means you still need to adjust your tests manually. You can also use scriptable benchmarks tools, such as SysBench, to emulate the load similar to one you have in production.

Q: Does innodb_thread_concurrency depend on the CPU cores (like quad core, hex core etc.) or just number of CPUs on the host?

A: Yes, it depends on the number of CPU cores.

Q: After converting a table from MyISAM to InnoDB, I’m finding the same queries to be significantly slower (all else same). Is there a common/obvious reason for this to happen?

A: The main differences between MyISAM and InnoDB are:

  • InnoDB is transactional and MyISAM is not. This means what every query you run on InnoDB is part of transaction. If this is an update query or if you use a version earlier than 5.6, InnoDB has to make a snapshot of all rows this query accesses to be able to restore it if the query fails.
  • InnoDB and MyISAM use different locking models
  • InnoDB does not support such options as Concurrent Inserts , Low Priority Updates or Delayed Inserts.
  • InnoDB has its own options for tuning to better performance, which are quite low by default

Q: How do I identify how many cores MySQL is currently using?

A: You should use OS-level tools, such as `top -H -p PID_OF_MYSQL`

Q: What is the performance impact while reducing the buffer poll when the chunk size is the default (128MB) in MySQL 5.7?

A: innodb_buffer_pool_chunk_size controls the size of chunks in which a resizing operation will happen. It should affect performance in the same way as copying smaller files compared with copying a bigger file in your environment: copying bigger files requires more memory, but copying many smaller files requires more operations.

Q: Why do queries, especially desc table_name type of queries, sometimes get stuck at open tables thread state? Even increasing the table cache and open files will not release hung queries until we restart MySQL.

A: This depends on many factors: if the OS allows mysqld to have enough file descriptors to handle so many tables open in parallel (check output of ulimit -n , taken for the user you run mysqld as) to other overtaxed resources, or bug in MySQL.

Q: Is it possible to extract one table from the binary backup (taken with xtrabackup or something similar) ?

A: Yes, this is possible for tables created with the option innodb-file-per-table=1 . Instructions for XtraBackup are here. XtraBackup supports extracting a single table from any type of backup. MySQL Enterprise Backup, instead, requires you to use option --use-tts (with MySQL server 5.6 or newer) in order to perform partial restore. Instructions are here.

Q; Is there any direct command to measure disk speeds in rpm or in some other way on Linux?

A: I usually use ioping.

Q: So it’s OK to use mysqldump when the dataset is about few hundred GB big?

A: Yes.


Share this post