InnoDB Performance Optimization: Webinar Q & APeter Zaitsev
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 wait instructions rather than spinning a hot, tight loop as quickly as possible. There are probably more improvements still possible. For practical purposes, however, tuning innodb_spin_wait_delay is still warranted in some cases.
Did you say utf8 will slow things down?
Yes, compared to latin1, utf8 can slow things down significantly. Unfortunately, it is not very well publicized as most benchmarks are run with the latin1 character set. The utf8 character set slow things down because it needs 3x (or 4x) times as much space in memory. In addition, it has much more complicated string comparison rules. MySQL 8 will switch the default character set to utf8mb4 from latin1. To do that without large regressions, a lot of optimization work was done. You can read about some of them here.
In print screenshots, you show some Grafana graphics. Are you using Prometheus to monitor and exporting metrics to Grafana? If so can you share the best metrics for you? And where can we get those metrics? 🙂
All of the metrics graphs used in this presentation come from Percona Monitoring and Management (PMM). Many of them come directly from our demo environment at pmmdemo.percona.com. To answer your first question, yes PMM uses Prometheus and Grafana internally. There are no single set of “best metrics.” They are very use-case-specific, and we have a number of blog posts and presentations covering them in detail.
In a DB with around 3000 connections, which open limit page and open page cache limit you think we should put?
File descriptors do not cost much on modern servers, which tend to have many GBs of memory. I would usually set open_file_limit=64000. Depending on your operating system, however, you will have a different way to increase this variable. I assume you meant the table_open_cache variable. This setting will depend on the number of tables you have, and how many of them can be used concurrently from many connections — there is no simple formula. I generally like to set it large enough so that Table_open_cache_overflows global status value does not grow to more than one a second.
Would it make sense to have “innodb_thread_concurrency” match the number of CPU cores on a high volume/traffic database?
Approximately matching the number of CPU cores can be a good ballpark figure, but it is not guaranteed to give you the best performance. Sometimes you have to go two times the number of cores (or more) to get the best performance. In other cases, numbers that are significantly less than the number of CPU cores on the box result in the best performance. You need to run experiments and fine tune it. Note: this is an advanced variable, and it only needs to be set if you are observing problems to begin with.
What is the best way to determine “working set” for an existing MySQL database system?”
I don’t know of any fully automated way, i.e., run the script and it tells you the working set of this workload is 96.7GB. One practical way to do it is to run the workload when the database completely fits in memory. It will be CPU bound at first, especially read-only workloads. You can then reduce the amount of memory available to see when the workload transitions to IO-bound. The minimum amount of memory you need keep your workload CPU-bound is roughly your working set.
“Threads_running”: What exactly does it mean? Is Query execution in progress? From which to which stages?
Threads_Running is pretty simple status variable. It means the thread is not idle (not in the Sleep status). This definition is rather coarse, but still helpful. For example, a thread may be waiting on disk IO or InnoDB row-level lock, and it still is considered “running.”
What are the key variables that we need to adjust? All the ones you discussed?
This presentation goes into a lot of details. If you are looking for the top three variables you may want to adjust for InnoDB, they are innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method.
Will there be a later webinar that will cover some architecture optimization?
This is a very broad topic that is hard to cover in a single webinar. Attending our Percona Live Open Source Database Conference is a great way to learn about use cases for different MySQL architectures.
Is there a good way to determine how much memory would be required for the database to be optimal?
The best way is simply to test it. Temporarily getting a box with a large amount of memory so you can see your performance when disk IO is not an issue, and then reducing the memory size to see how performance changes is a great way to determine the optimal value.
Anything about optimizing your innodb_buffer_pool_size?
A good rule of thumb is 75% of memory, if the system is dedicated to the MySQL instance and is only running the InnoDB storage engine.
Thanks again for attending this webinar on InnoDB Performance Optimization. You can get the slides and video of the presentation here.