InnoDB is the default storage engine, and most of MySQL's performance characteristics come from its internals. Its buffer pool caches data and index pages in memory, so `innodb_buffer_pool_size` is usually the single most impactful configuration parameter: a larger buffer pool keeps more hot pages in RAM and reduces disk I/O, which is the dominant cost on most workloads. On a dedicated database server, a common starting point is roughly 60% to 75% of system RAM, adjusted for workload and other memory consumers. Splitting the buffer pool into multiple instances via `innodb_buffer_pool_instances` reduces contention on internal latches in highly concurrent environments. Other helpful features include the adaptive hash index, which builds hash lookups on top of frequently used B-tree pages, and buffer pool dump/load, which warms the cache quickly after restarts.
Write performance is shaped by the redo log, the doublewrite buffer, and several related parameters. The redo log records changes so InnoDB can recover after a crash; its size, set with `innodb_log_file_size`, affects write performance and recovery time, with larger logs reducing checkpoint frequency at the cost of longer recovery. The `innodb_flush_log_at_trx_commit` setting controls how aggressively the redo log is flushed to disk: a value of 1 gives full ACID durability, while 2 trades some durability for fewer fsyncs and better throughput. The `sync_binlog` setting plays a similar role for the binary log. Background flushing is paced by `innodb_io_capacity`, which should reflect the storage's realistic IOPS so that flushing and purging keep up; setting it too low causes dirty page buildup and stalls, while setting it too high can saturate the disks and create latency spikes. The `innodb_read_io_threads` and `innodb_write_io_threads` parameters control parallelism of background IO, and increasing them can help on modern high-IOPS storage. Read-ahead (`innodb_read_ahead_threshold`) and the adaptive hash index can be tuned for specific workloads when defaults are not ideal.
Table-level settings also matter. `innodb_file_per_table` stores each table in its own tablespace file, which improves manageability and makes per-table operations like shrinking and moving tables easier. Modern row formats like DYNAMIC store large off-page values efficiently and improve cache hit rates, while COMPRESSED trades CPU for smaller pages and less I/O — a win on read-heavy, IO-bound workloads. Other caches such as `table_open_cache` and `table_definition_cache` reduce the cost of opening tables and parsing metadata; sizing them too small forces frequent opens and contention on the cache.
Concurrency is governed by MVCC and InnoDB's locking model. MVCC lets readers see a consistent snapshot without blocking writers, which is essential for OLTP throughput. The default isolation level, REPEATABLE READ, prevents non-repeatable reads but uses gap locks that can block inserts into ranges and increase contention; switching to READ COMMITTED reduces gap locking and can improve concurrency at the cost of weaker consistency guarantees. Higher isolation levels in general hold locks longer and reduce concurrency. Transactions should be kept small and short-lived, autocommit should usually stay enabled for OLTP, and long-running transactions should be avoided because they keep old row versions alive in the undo log, prevent purge, and grow storage. Deadlocks, which occur when transactions wait on each other's locks, are best avoided by accessing tables and rows in a consistent order, keeping transactions brief, and minimizing locking. `SELECT ... FOR UPDATE` is appropriate when pessimistic locking is truly required, but it acquires row locks even for reads and is expensive in hot paths; optimistic concurrency with version columns is often a cheaper alternative.