In this blog, I provide answers to the Q & A for the Troubleshooting hardware resource usage webinar.
First, I want to thank everybody who attended the May 26 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: How did you find the memory IO LEAK?
Q: Do you have common formulas you use to tune MySQL?
A: There are best practices: relate thread concurrency to number of CPU cores you have, set InnoDB buffer pool size large enough so it can contain all your working dataset (which is not always possible), and do not set the Query Cache size larger than 512MB (or even better, turn it off) to avoid issues with global lock set when it needs to be de-fragmented. I prefer not to call them “formulas,” because all options need to be adjusted to match the workload. If this weren’t the case, MySQL Server would have an automatic configuration. There is also a separate webinar on configuration (Troubleshooting configuration issues) where I discuss these practices.
Q: Slide 11: is this real time? Can we get this info for a job that has already finished?
A: Yes, this is real time. No, it is not possible to get this info for a thread that does not exist.
Q: Slide 11: what do negative numbers mean?
A: Numbers are taken from the CURRENT_NUMBER_OF_BYTES_USED field for table memory_summary_by_thread_by_event_name in Performance Schema. These values, in turn, are calculated as (memory allocated by thread) – (memory freed by thread). Negative numbers here mean either a memory leak or incorrect calculation of memory used by the thread. I reported this behavior in the MySQL Bugs database. Please subscribe to the bug report and wait to see how InnoDB and Performance Schema developers answer.
Q: Are TokuDB memory usage stats recorded in the sys.memory_by_thread_by_current_bytes table also? Do we have to set something to enable this collection? I ran the query, but it shows 0 for everything.
A: TokuDB currently does not support Performance Schema, thus its memory statistics are not instrumented. See the user manual on how memory instrumentation works.
Q: With disk what we will check for disk I/O?
A: I quite don’t understand the question. Are you asking on which disk we should check IO statistics? For datadir and other disks, look at the locations where MySQL stores data and log files (if you set custom locations).
Q: How can we put CPU in parallel to process multiple client requests? Put multiple requests In memory by parallel way. By defining transaction. Or there any query hints?
A: We cannot directly put CPU in parallel, but we can affect it indirectly by tuning InnoDB threads-related options ( innodb_threads_concurrency, innodb_read_io_threads, innodb_write_io_threads) and using the thread pool.
Q: Is there any information the Performance Schema that is not found in the SYS schema?
A: Yes. For example, sys schema does not have a view for statistics about prepared statements, while Performance Schema does, because sys schema takes its statement statistics from digest tables (which make no sense for prepared statements).
Q: What is your favorite tool to investigate a memory issue with a task/job that has already finished?
A: I don’t know that there is such a tool suitable for use in production. In a test environment, you can use valgrind or similar tools. You can also make core dumps of the mysqld process and investigate them after the issue is gone.