Troubleshooting configuration issues: Q & ASveta Smirnova
In this blog, I will provide answers to the Q & A for the Troubleshooting configuration issues webinar.
First, I want to thank you for attending the June, 9 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:
Q: What are the predefined roles installed per default?
A: MySQL does not support roles by default. But, if you need roles you can either use MariaDB or emulate roles with Percona PAM Plugin and proxy users. But MySQL has pre-built user accounts. One of them is root@localhost, which has all available privileges, and anonymous account that can access the test database without a password. See the user manual for more information.
Q: How do you determine the root cause for a query that just hangs?
A: There are several possible reasons for a hanging query. They include:
- Poorly optimized query – it doesn’t really “hang,” but just requires a lot of time to execute. Dealing with these types of queries was discussed in the Troubleshooting Slow Queries webinar.
- The query is waiting for a lock set by another query or transaction. These issues were discussed in the Troubleshooting locking issues webinar
- A bug in MySQL.
When I see hanging queries I look into all the above possibilities. But I prefer to start at query optimization.
Q: Can we get the powerpoint slides?
A: I do not use PowerPoint to create slides. Slides are available in PDF format only.
Q: 1) Is it safety to use SQL_LOG_BIN = 0 for specific connections (statements), for example for DELETE, when we need to keep old data on a slave, but delete from master? What are side-effects? Can it break replication?
A: Using SQL_LOG_BIN = 0 itself is safe, but you need to understand what you are doing. For example, if you delete data in a table that has a unique key on the master, and then insert a row that has the same unique value that existed in one of rows you deleted, replication will fail with “Duplicate key” error.
Q: Is it reasonable to disable query_cache_type (set 0) on MySQL instances with very big (huge) amount of data?
A: Yes. I would recommend it.
Q: 3) How does the number of innodb_buffer_pool_instances affect performance? Does a lot of innodb_buffer_pool_instances = high performance?
A: InnoDB introduced buffer pool instances to reduce contention “as different threads read and write to cached pages“. However, they improve performance only if you have many concurrent threads inside InnoDB.
Q: I have a question, where can I download the threadpool plugin at Percona? I checked your download page and couldn’t find it. Is it bundled in the Percona official release? wW have 5.6.28,29 and 30 and there is no thread.so in the plugin directory. Can you let me know how to get it?
A: Percona built thread pool into Percona Server; a separate download isn’t necessary. See the user manual for instructions.