#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

MySQL Performance Tuning: Server-Level Tips

Server-level tuning means changing how mysqld itself uses memory, disk and connections, as opposed to rewriting individual queries. Be clear about one thing before you open my.cnf: no setting will rescue a query that scans ten million rows because an index is missing. Find slow queries first (step 1 below), fix the ones you can with query-level optimisation, and then size the server for the workload that is left.

The defaults quoted on this page are MySQL 8.0 values, checked on 8.0.42. Check your own server, because hosting providers often change them.

Step 0: see what you are running with

SELECT @@innodb_buffer_pool_size/1024/1024 AS buffer_pool_mb,
       @@max_connections,
       @@innodb_flush_log_at_trx_commit,
       @@innodb_redo_log_capacity/1024/1024 AS redo_mb,
       @@tmp_table_size/1024/1024 AS tmp_mb,
       @@slow_query_log, @@long_query_time;
buffer_pool_mb@@max_connections@@innodb_flush_log_at_trx_commitredo_mbtmp_mb@@slow_query_log@@long_query_time
128.000000001511100.0000000016.00000000010.000000

That is an untouched 8.0 install: a 128 MB buffer pool, 151 connections, the slow log off, and a 10-second threshold for "slow". The 128 MB buffer pool is fine on a laptop and far too small for a production server with several GB of RAM.

Step 1: turn on the slow query log

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;     -- seconds; applies to NEW connections
SELECT @@slow_query_log_file;

One detail catches people out: long_query_time is copied into each session when it connects. Your current session still shows 10 until you reconnect. Leave the log on for a normal business day. Then summarise it with mysqldumpslow (bundled with MySQL) or Percona's pt-query-digest. Fix the top few statements before you change anything else. Finding and fixing slow queries walks through that process, and the EXPLAIN guide shows how to read their plans.

The built-in sys schema can also point at problems without any log file:

SELECT query, exec_count, total_latency
FROM sys.statements_with_full_table_scans
ORDER BY exec_count DESC LIMIT 10;

SELECT * FROM sys.schema_unused_indexes;   -- indexes that cost writes but are never read

Read schema_unused_indexes with care. It counts only since the last restart, and on a fresh test server it listed foreign-key indexes, which InnoDB needs even if no query reads them.

Step 2: size the InnoDB buffer pool

The buffer pool caches table and index pages in memory, and it is the single most important memory setting. The MySQL manual suggests that a dedicated database server can give it up to about 80% of RAM. Leave room for per-connection memory, the OS and anything else on the machine. On a shared VM running the app as well, aim much lower.

SET PERSIST innodb_buffer_pool_size = 268435456;   -- 256 MB, resized online
SELECT @@innodb_buffer_pool_size/1024/1024 AS buffer_pool_mb;
buffer_pool_mb
256.00000000

SET PERSIST changes the running server and writes the value to mysqld-auto.cnf, so it survives a restart. The value is rounded to a multiple of the chunk size. Check whether the pool is big enough by comparing logical reads with reads that had to go to disk:

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');

Innodb_buffer_pool_reads counts requests that missed the cache. Measure the change over an hour of normal traffic, not since startup: a freshly started server always shows misses while the cache warms up. If misses stay high and the server has free RAM, grow the pool. On a machine that hosts nothing but MySQL, innodb_dedicated_server = ON (set in the config file) lets MySQL size the pool and redo log automatically. Its sizing rules changed in 8.4, so read the manual for your version. The buffer pool and caching guide goes deeper.

Step 3: give writes enough redo log

Heavy insert or update workloads can stall when the redo log fills and InnoDB has to flush pages in a hurry. From 8.0.30 one variable controls the total size, innodb_redo_log_capacity (default 100 MB), and you can change it online. Older 8.0 releases used innodb_log_file_size × innodb_log_files_in_group. Increase it if write-heavy periods show stalls; there is little benefit on a read-mostly server.

Step 4: choose durability deliberately

innodb_flush_log_at_trx_commitBehaviourWhat a crash can lose
1 (default)Redo log written and flushed to disk at every commitNothing committed
2Written at commit, flushed about once per secondAbout the last second, on an OS crash or power cut
0Written and flushed about once per secondAbout the last second, even if only mysqld crashes

Setting 2 can speed up bulk loads noticeably. Use it for a staging import or an analytics copy you can rebuild. Never use it for a payments or orders database where every committed row matters.

Step 5: connections, threads and temporary tables

Advice you can ignore on MySQL 8

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →