In this blog, I will provide answers to the Webinar Q & A for Introduction to Troubleshooting Performance: What Affects Query Execution?
First, I want to thank you for attending the April, 7 webinar. This webinar is the third in the “MySQL Troubleshooting” webinar series and last introductory webinar in the 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: If we had some MyISAM tables, could we use clustering in MariaDB?
A: Do you mean Galera Cluster? You can use it, but keep in mind what MyISAM support in Galera is still experimental and not recommended to use in production. You need to enable variable wsrep_replicate_myisam to enable MyISAM support.
Q: Is there a filesystem type that is better than another?
A: If you’re speaking about modern versus old file systems, modern is usually better. For example, ext3 or NTFS is certainly better than FAT32, which does not support files larger than 4GB. But that type of file system can be very helpful when you need to store data on a flash drive that can be read by any computer or mobile device. The same advice applies to ext2.
So a better answer to this question is it depends on your purpose. You can start from these Percona Live Slides by Ammon Sutherland, who describes the difference between file systems, their options and how they can improve or not MySQL performance. Then check this blog post by Dimitri Kravtchuk, and this one by Yves Trudeau.
Certainly NFS is not a good choice because it does not provide MySQL storage engines (particularly InnoDB) a reliable answer if data was really flushed to disk.
Q: Can I download this training somewhere on your website?
A: This webinar and all future webinars are available at the same place you registered. In this case, links to slides and recorded webinar are available here.
Q: What are good system level tools that are helpful?
A: In the webinar I presented a minimal set of tools that collect essential information, necessary for troubleshooting MySQL performance issues. Usually, you can start with them, then consult Brendan D. Gregg’s great picture. Regarding which tools which we like in Percona: a favorite is certainly perf.
Q: I am planning to use Percona Toolkit to optimize MySQL query performance. When I tried to install Percona binaries, there is a conflict on MySQL binaries. Could you please help with how to install Percona binaries. Requesting for prerequisite?
A: A common reason for such conflicts is the client or shared libraries. You just need to replace them with Percona’s equivalent.
Q: How do you increase buffers for a high load case?
A: Buffers are linked to MySQL system variables. To increase them try SET variable_name=NEW_VALUE first, then test in the same session. If you are not happy with the result, increase the global buffer: SET GLOBAL variable_name=NEW_VALUE, then test the performance of the whole database. If you are still not happy with the result, adjust the variable value in your configuration file. Of course, you don’t need to try to set a variable that has global scope. Try setting only in the session first. Sometimes you cannot change the variable value online. In this case, be especially careful: test first, be ready to rollback changes, choose a less busy time, etc.
Q: How do you handle deadlocks?
Q: Which are the best practices to fix performance caused by InnoDB deadlocks?
A: These two questions are about practically same thing. Deadlocks are not 100% avoidable in InnoDB, therefore the best solution is to code the application in such a way that it can simply re-run transactions that were rolled back. But if you see deadlocks too often, this is not always possible. In such cases, you need to investigate which rows are locked by each transaction, find out why this pattern repeats and fix your transactions or tables (sometimes if you search without indexes, a query can lock more rows than needed to resolve the query – adding an appropriate index can solve locking issue).
Q: Is it important in MySQL 5.7 to separate logs and data in differents disks or partitions?
A: By separating data and logs on different disk partitions you gain performance because you can write more data in parallel and it’s more stable. In the case of a data disk breaking, you will have log files untouched and can restore data from them. But this only applies to cases when partitions are on different physical disks.
Q: When are we going to see something like Datastax OpsCenter for MariaDB, with nice performance monitoring and support for easy partitioning?
A: There are many monitoring and administration tools for MySQL and MariaDB, which include VividCortex, SolarWinds, Webyog, MySQL Enterprise Monitor, MySQL Workbench, etc. Please specify in comments which feature in Datastax OpsCenter you miss in these products. I can probably answer if there is an alternative. I don’t know about any plans for cloning Datastax OpsCenter for MariaDB or MySQL.
Q: If you are storing queries in stored procedures, and you make changes to those SP’s, how long will it take for them to be cached? The next time they are run, or after x amount of times?
A: What do you mean queries would be cached? If you mean MySQL Query Cache: the call of the SP will be in cache, and the result will be reloaded next time the procedure is called. If you mean the data retrieved by these queries, if it is stored in the InnoDB buffer pool or not it is same: the next time when you call the SP, new data will be in the buffer pool.
Q: I have a very big table (up to 9GB data), and it is a very heavy read/write table. We actually store all our chat messages in that table: every record in the table is a row. What would be the best way to get out of this situation, NoSQL or partition? Will Percona be helpful for me in this situation?
A: This depends on your table definition and how you use it. It is hard to answer having only this description of your issue. Regarding Percona help, this looks like a case for our Support or Consulting.
Q: I want to archive data on a daily basis. I use INSERT INTO table_achive SELECT * FROM table. This takes about 45 minutes for 7.000.000 rows. Is that slow?
A: As I mentioned at the beginning of the webinar, there is no “yes” or “no” answer. It depends on the speed of your disk, how large the table is (retrieving 7,000,000 rows which contain only two integer columns would be certainly faster than retrieving 7,000,000 rows, each of them has maybe 42 columns). But what I can certainly say is that most likely this dataset does not fit into your memory, and this query requires you to create disk-based temporary tables. This query most likely sets too many locks and can slow down other queries on the “table”. If all this concerns you, consider copying data in chunks: for example INSERT INTO table_achive SELECT * FROM table WHERE Primary_Key BETWEEN start AND end. You can use utility pt-online-schema-change as a guide.