Abstract: In view of the pressure caused by website traffic in busy shopping season to the database, the author gives some skills of MySQL performance tuning. These skills are of great reference value. Through these tuning, it can effectively avoid server downtime caused by excessive traffic, thus causing economic losses to enterprises. The following is the translation
Halloween is long gone, and it’s time to focus on the upcoming holiday season. First Thanksgiving, then Black Friday and Cyber Monday, and finally Christmas/Boxing Week (which starts on Boxing Day on December 26th and ends on New Year’s Eve on December 31st for six days or more. The term was coined by the retail industry around the mid-2000s in an attempt to extend their Boxing Day sales) to hit the shopping peak. For business owners, this time of year marks the long-awaited year-end profit-taking. For some DBAs, it can cause fear, unease, and even sleepless nights as they try to get their systems back online.
Thankfully, this is not the case. By making some proactive adjustments to MySQL performance variables, you can insulate your database server from the increased demand that comes with high shopping season.
Tip #1: Determine the maximum number of MySQL connections
For the maximum number of MySQL connections, it is best to send 5 requests to the Web server at a time. Some of the five requests to the Web server will be for resources such as CSS stylesheets, images, and scripts. Getting an accurate rate of mysql-to-Web server requests can be difficult for reasons such as browser caching; To get an exact number, you need to analyze the log files of the Web server. For example, Apache’s “access_log” log file can be accessed manually or through utilities such as Analog or Webalizer.
Once you have an accurate estimate of a particular usage, multiply that ratio by the maximum number of connections to your Web server. For example, if the Web server is configured to serve a maximum of 256 clients and the ratio of MySQL requests to Web requests is 1/8, it is best to set the maximum number of database connections to 32. You also have to worry about having a safety margin, so you multiply this number by 2 to get the final number. You can only attempt to match the maximum number of database connections to the client limit of the Web server if the infrastructure supports it. In most cases, it’s best to stay close to 32.
Check the MySQL connection in Monyog
In a MySQL database, the maximum number of concurrent connections for MySQL is stored in the global variable max_connections. Monyog reports the variable “max_connections” as the “maximum allowed” metric in the current connection monitoring group. It also divides this number by the number of open connections to generate the connection usage percentage:
There is also a connection history monitor to help calculate the optimal maximum number of concurrent connections. It includes the number of attempts, rejections and successful connections. In addition, the percentage of the maximum metric allowed to be reached is displayed as a progress bar, which allows you to quickly evaluate the maximum number of concurrent connections reached by the server in the past:
Tip #2: Allocate enough memory for temporary tables
In some cases, the server creates internal temporary tables while processing statements. Temporary tables are used for internal operations such as GROUP BY and DISTINCT, as well as some ORDER BY queries and subqueries in UNION and FROM clauses (derived tables). These are memory tables created in memory. The maximum size of temporary tables in memory is determined by the smaller values in tmp_TABLE_size and max_HEAP_table_size. If the size of a temporary table exceeds this threshold, it is converted to an InnoDB or MyISAM table on disk. Also, if the query involves BLOB or TEXT columns that cannot be stored in an in-memory table, the temporary table always points directly to disk.
This conversion is expensive, so consider increasing the size of the max_HEap_TABLE_size and tmp_table_size variables to help reduce the number of temporary tables created on disk. Keep in mind that this will require a lot of memory because the size of temporary tables in memory is based on the “worst case”. For example, memory tables always use fixed-length columns, so character columns use VARCHAR (255). This can make temporary tables in memory much larger than expected – in fact, many times larger than the total size of the query table! When increasing the size of max_HEAP_TABLE_size and tmp_table_sizevariables, it is important to monitor server memory usage, as temporary tables in memory may increase the risk of reaching server memory capacity.
In general, 32M to 64M are recommended values to start with and tune as needed.
Temporary table monitoring in Monyog
Monitoring of temporary tables is one of many predefined Monyog monitoring. It provides some metrics to use for temporary tables, including:
- Maximum allowed: Displays the value of the tmp_table_size server variable, which defines the maximum size of temporary tables created in memory. Along with max_HEap_TABLE_size, this value defines the maximum size of temporary tables that can be created in memory. If the memory temporary table is larger than this size, it is stored on disk.
- Maximum MEMORY table size: Displays the value of the max_HEap_TABLE_size server variable that defines the maximum size of the MEMORY storage engine table that is explicitly created.
- Total number of temporary tables created: Displays the value of the CREated_tmp_tables server variable, which defines the number of temporary tables created in memory.
- Temporary tables created on disk: Displays the value of the CREated_tmp_disk_tables server variable, which defines the number of temporary tables created on disk. If this value is high, you should consider increasing the tmp_TABLE_size and max_HEAP_TABLE_size values to increase the number of temporary tables created in memory, thereby reducing the number of temporary tables created on disk.
- Disk: Total ratio: calculated based on created_tmp_disk_tables divided by created_tmp_tables. Percentage of temporary tables created on disk due to insufficient tmp_TABLE_size or max_HEAP_TABLE_size. Monyog displays this number as a progress bar and percentage to quickly determine how much disk is used for temporary tables instead of memory.
The trend chart can be used for the total number of tables created and the total ratio of tables created on disks to disks. These give us a glimpse of their evolution over time:
Tip #3: Increase the thread cache size
The connection manager thread handles client connection requests on the network interface that the server listens to. The connection manager thread associates each client connection with a thread dedicated to it, which handles authentication and all request processing for that connection. Therefore, there is a one-to-one ratio between the thread and the currently connected client. It is important to ensure that the thread cache is large enough to accommodate all incoming requests.
MySQL provides a number of server variables related to connection threads:
The thread cache size is determined by the thread_cache_size system variable. The default value is 0 (no caching), which causes one thread to be set up for each new connection and to be processed when the connection terminates. If you want your server to receive hundreds of connection requests per second, you should set thread_CACHE_size high enough that most new connections can use the cache thread. You can set the value of MAX_connections when the server is up or running.
You should also monitor the number of threads in the cache (Threads_cached) and how many threads are created, because threads cannot be fetched from the cache (Threads_created). Regarding the latter, if Threads_created continues to grow at more than a few threads per minute, consider increasing the value of thread_cache_size.
Run the MySQL show status command to display MySQL variables and status information. Here are a few examples:
SHOW GLOBAL STATUS LIKE '%Threads_connected%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 2 |
+-------------------+-------+
SHOW GLOBAL STATUS LIKE '%Threads_running%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 1 |
+-----------------+-------+
Copy the codeMonyog thread cache monitoring
Monyog provides a screen for monitoring thread caches called threads. Server variables associated with MySQL threads map to the following Monyog metrics:
- Thread_cache_size: Number of threads that can be cached.
- Threads_cached: Number of threads in the cache.
- Threads_created: Creates threads that handle connections.
The Monyog thread screen also includes a “thread cache hit ratio” metric. This is an indication of thread cache hit ratio. If the value is low, you should consider increasing the thread cache. Display the value as a percentage in the status bar; The closer it gets to 100%, the better.
If the values of these metrics are equal to or exceed the specified values, each indicator can be configured to issue a warning and/or critical alert.
Other relevant server variables
In addition to the above metrics, the following should also be monitored:
- InnoDB buffer pool size: InnoDB buffer pool size plays a critical role in MySQL databases that use InnoDB. The buffer pool caches both data and indexes. Its value should be as large as possible to ensure that the database uses memory rather than a hard disk drive for reading operations.
- Temporary table size: MySQL uses the smaller of max_heap_table_size and tmp_table_size to limit the size of temporary tables in memory. Having a large value can help reduce the number of temporary tables created on disk, but it also increases the risk of server memory capacity because this metric applies to each client. In general, 32M to 64M are recommended values, starting with these two variables and tuning as needed.
- InnoDB log buffer size: Every time MySQL writes to a log file, it makes use of important system resources available for processing sales data. Therefore, it makes sense to set the InnoDB log buffer size to a large value. This reduces the number of times the server writes to disk in large transactions, minimizing these time-consuming operations. 64M is a good starting point for this variable.
conclusion
While even the largest corporate websites can suffer from outages, the impact is especially critical for small and medium-sized businesses handling online sales. According to a recent report, a one-minute outage costs the average business about $5,000. Don’t let your business be part of that statistic. Take the initiative to tune your MySQL database server (S) before the holiday rush and reap the rewards!
2 comments