MySQL is the world's most popular relational database, running 60%+ of websites globally. But default settings are conservative and inadequate for modern sites — with proper tuning your site can run 2-5x faster. Core tuning parameters follow.
InnoDB tuning
innodb_buffer_pool_size — RAM cache for InnoDB databases. The most important parameter. Recommended: 50-70% of available RAM. Example: 4GB RAM server, allocate 2GB to MySQL — innodb_buffer_pool_size = 2G. This keeps data and indexes in RAM and minimizes disk I/O.
innodb_log_file_size — InnoDB transaction log size. Recommended: 256M-1G. A larger log fits more transactions and speeds up crash recovery.
innodb_flush_log_at_trx_commit — where to flush logs on each transaction. 1 = ACID compliant (safest), 2 = OS cache (faster, can lose data on OS crash), 0 = once per second (fastest). Production: 1 or 2.
Connection and thread tuning
max_connections — maximum parallel connections. Default: 151. Recommended: 200-1000 depending on site size. In WordPress each user may use 1-3 connections.
thread_cache_size — caches threads for reuse without creating new ones. Recommended: max_connections / 4.
Query cache (older versions)
Up to MySQL 5.7 query_cache existed. MySQL 8.0 removed it (problems under many connections). MariaDB still supports it. If you use MariaDB: query_cache_size = 64M, query_cache_type = 1.
Slow query log
slow_query_log = 1, long_query_time = 2 (queries longer than 2 seconds), slow_query_log_file = /var/log/mysql-slow.log. Use this log to find queries needing optimization. View stats with mysqldumpslow: mysqldumpslow -s c -t 10 /var/log/mysql-slow.log.
Indexing and EXPLAIN
Missing indexes are MySQL's biggest speed problem. Check each query's plan with EXPLAIN SELECT: type "ALL" means full table scan (bad); "ref" or "eq_ref" means an index is used (good). Add indexes on columns used in WHERE and JOIN clauses: CREATE INDEX idx_user_email ON users(email);
Sayt.uz practice
MySQL/MariaDB tuning on Sayt.uz hosting plans is handled by our DBA. For WordPress sites: innodb_buffer_pool_size 256M (Basic), 512M-1G (Pro). Most Pro clients have slow_query_log enabled and we review it weekly. In e-commerce stores slow pages from improper indexing — we run audits every 2-3 months. Tip: indexing accounts for 80% of MySQL speed, the rest of the parameters 20%. Fix database structure and indexes first.