Quiz: MySQL / MariaDB Operations

← Back to quiz index

7 questions

L1 (4 questions)

1. What is the difference between InnoDB and MyISAM, and why is InnoDB the default in modern MySQL?

Show answer

InnoDB supports transactions (ACID), row-level locking, foreign keys, and crash recovery via redo/undo logs. MyISAM uses table-level locking, has no transaction support, and is prone to corruption on crash. InnoDB is the default because it handles concurrent writes safely, recovers automatically after crashes, and supports referential integrity. MyISAM's only advantage was full-text search (now supported by InnoDB) and slightly faster bulk reads on read-only workloads.

2. You need to run ALTER TABLE on a 500GB production table. How do you do it without downtime?

Show answer

Use an online schema change tool: pt-online-schema-change (Percona) or gh-ost (GitHub). These create a new table with the desired schema, copy data in chunks using triggers or binlog tailing, then atomically swap the table names. Direct ALTER TABLE would lock the table for hours. gh-ost is preferred because it uses binlog streaming instead of triggers (less write amplification). Always test on a replica first and run during low-traffic windows.

3. How do you create and verify a MySQL backup that you can actually restore from?

Show answer

Use xtrabackup (Percona) for physical hot backups — it copies InnoDB data files without locking. For logical backups use mysqldump --single-transaction (consistent snapshot without locks for InnoDB). Critical: always test restores. Run a weekly restore-verification job that loads the backup into a test instance and runs validation queries. A backup you have never restored is not a backup — it is a hope. Also back up the binlogs for point-in-time recovery.

4. What is the MySQL slow query log and how do you use it to find performance problems?

Show answer

Enable with slow_query_log=ON and long_query_time=1 (log queries >1 second). Use pt-query-digest (Percona Toolkit) to aggregate the slow log and rank queries by total time, frequency, and lock time. Focus on the top 5 queries by total time — these have the biggest impact. For each: run EXPLAIN to check for full table scans, add missing indexes, and check for N+1 query patterns in the application. Also enable log_queries_not_using_indexes to catch queries that work now but will degrade as data grows.

L2 (3 questions)

1. How does MySQL replication work and what is the difference between statement-based, row-based, and mixed replication?

Show answer

MySQL replication streams the binary log (binlog) from primary to replica. Statement-based (SBR) logs SQL statements — compact but non-deterministic functions (NOW(), RAND()) cause drift. Row-based (RBR) logs actual row changes — deterministic but verbose for bulk updates. Mixed mode uses SBR by default and switches to RBR for non-deterministic statements. Modern best practice: use ROW format (binlog_format=ROW) for consistency. GTID-based replication (gtid_mode=ON) simplifies failover by giving each transaction a global ID.

2. What is the InnoDB buffer pool and how do you size it correctly?

Show answer

The InnoDB buffer pool caches data and index pages in memory. It is the single most impactful MySQL performance setting. Size it to 70-80% of available RAM on a dedicated database server. Monitor buffer pool hit ratio: SHOW ENGINE INNODB STATUS — look for buffer pool hit rate. If it drops below 99%, the pool is too small. Use innodb_buffer_pool_instances (one per GB, up to 64) to reduce mutex contention on large pools. Warm it after restart with innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup.

3. What is a MySQL deadlock and how do you diagnose and prevent them?

Show answer

A deadlock occurs when two transactions each hold a lock the other needs. InnoDB detects deadlocks and rolls back the smaller transaction (fewer undo log records). Diagnose: SHOW ENGINE INNODB STATUS shows the latest deadlock details (locks held and waited for). Prevention:
1. Access tables and rows in a consistent order across all transactions.
2. Keep transactions short.
3. Use SELECT ... FOR UPDATE only when necessary.
4. Add appropriate indexes so queries lock fewer rows.
5. Consider READ COMMITTED isolation level to reduce gap locking.