How to Tune MySQL for Holiday Traffic Spikes: 3 Essential Tricks

Trick #1: Determine the Proper max_connections Value

Estimate the ratio of MySQL requests to web‑server requests by analyzing web‑server logs (e.g., Apache access_log or tools like Analog/Webalizer). Multiply this ratio by the web server's maximum client connections; for example, with 256 web clients and a 1/8 ratio, set max_connections to about 32, then double it for safety, yielding a final value around 64. In most cases, keeping it near 32 is sufficient, provided the infrastructure can support it.

In Monyog, the variable max_connections is shown as the "Maximum Allowed" metric, and the tool also displays the percentage of used connections.

Trick #2: Allocate Sufficient Memory for Temporary Tables

MySQL creates internal temporary tables for operations such as GROUP BY, DISTINCT, certain ORDER BY queries, and subqueries in UNION or derived tables. The maximum size of an in‑memory temporary table is the smaller of tmp_table_size and max_heap_table_size. If a temporary table exceeds this limit, it is converted to an on‑disk InnoDB or MyISAM table, which is costly.

Increase both tmp_table_size and max_heap_table_size (commonly to 32 MiB–64 MiB as a starting point) to reduce disk‑based temporary tables, but monitor overall memory usage because large values can exhaust server RAM.

Monyog monitors temporary‑table metrics, showing the maximum allowed size, the current memory‑table size, the total number of created temporary tables, the number created on disk, and the disk‑to‑memory ratio.

Trick #3: Increase the Thread Cache Size

Each client connection is handled by a dedicated thread. Setting thread_cache_size to a value large enough to cache most incoming connections avoids the overhead of creating a new thread for every connection.

Use SHOW GLOBAL STATUS LIKE '%Threads_connected%' and SHOW GLOBAL STATUS LIKE '%Threads_running%' to observe current thread usage. Monitor Threads_cached and Threads_created; if Threads_created rises rapidly, increase thread_cache_size.

SHOW GLOBAL STATUS LIKE '%Threads_connected%';
SHOW GLOBAL STATUS LIKE '%Threads_running%';

Monyog displays these variables and provides a "Thread Cache Hit Rate" indicator; a low hit rate suggests raising thread_cache_size.

Other Relevant Server Variables

InnoDB Buffer Pool Size : Should be as large as possible to keep data and indexes in memory rather than on disk.

Temporary Table Size : Controlled by the smaller of max_heap_table_size and tmp_table_size; typical starting values are 32 MiB–64 MiB.

InnoDB Log Buffer Size : Larger values (e.g., 64 MiB) reduce disk writes during large transactions.

Conclusion

Even the biggest e‑commerce sites suffer revenue loss from downtime, but small‑ and medium‑size businesses are especially vulnerable. Studies show a single minute of outage can cost roughly $5,000 on average. Proactively applying the above MySQL tuning steps before the holiday rush can prevent costly crashes and keep sales flowing smoothly.

Source: ITeye Original URL: http://www.iteye.com/news/32796