We are launching a global platform and need expert advice on directly through the my.cnf configuration file. Which specific variables besides the buffer pool size should we modify to handle thousands of concurrent write transactions without deadlocks or latency issues?
3 answers
Beyond the buffer pool, you must tune innodb_log_file_size and innodb_log_buffer_size. Setting log files to 1GB or 2GB allows MySQL to write transactions to disk smoothly in the background. Also, look at innodb_flush_log_at_trx_commit. Setting it to 2 instead of 1 dramatically increases write IOPS because it flushes to the OS cache instead of disk on every commit, though there is a tiny risk of losing a second of data during power failure. Lastly, increase max_connections to match your pool size.
Have you evaluated whether switching your database storage engine from InnoDB to a NoSQL alternative or a memory-cached layer like Redis would solve the high write pressure better?
Do not overlook the query_cache_type setting. In modern, high-concurrency MySQL versions, the query cache is deprecated and should be turned off to avoid global mutex contention.
Raymond is entirely correct. Disabling the built-in query cache entirely is one of the quickest ways to improve performance on high-concurrency production servers.
Timothy, using Redis as a caching layer in front of MySQL works wonders for read-heavy loads, but for strict ACID-compliant write transactions, proper relational database tuning via my.cnf variables remains absolutely mandatory to prevent system crashes.