Here’s a detailed comparison table between Oracle and MySQL architectures, highlighting components, memory structures, process models, transaction behavior, and other key aspects — tailored for an Oracle DBA transitioning to MySQL.
🏗️ Oracle vs MySQL Architecture (Detailed Comparison Table)
| Component / Area | Oracle RDBMS | MySQL (with InnoDB) |
|---|---|---|
| Architecture Type | Multi-process architecture (background processes) | Single-process, multi-threaded architecture |
| Core Daemon Process | Multiple: PMON, SMON, DBWR, LGWR, CKPT, ARCH, etc. | Single daemon: mysqld |
| Storage Engine Model | Integrated (Oracle controls all storage) | Pluggable (e.g., InnoDB, MyISAM, MEMORY) — InnoDB is default |
| Memory Structures | SGA (shared), PGA (per process) | Buffer Pool (InnoDB), Query Cache (deprecated), Thread buffers |
| • Shared Pool | SQL & PL/SQL parsing, dictionary cache | InnoDB uses adaptive hash, no separate “shared pool” |
| • Buffer Cache | Caches data blocks (DB_CACHE_SIZE) | InnoDB Buffer Pool (innodb_buffer_pool_size) |
| • Redo Log Buffer | Stores redo before writing to log files | InnoDB log buffer for redo (innodb_log_buffer_size) |
| Process / Threading Model | Dedicated Server Process or Shared Server | Thread-per-connection (within mysqld process) |
| Background Processes | Extensive set of helper processes | Mostly handled by internal threads (e.g., IO, flush, purge threads) |
| Listener / Network | tnslsnr, TNS listener, service names | Built-in TCP/IP listener (default port 3306) |
| Data Dictionary | Stored in shared pool, access via DBA_, USER_, V$ views | Stored in information_schema, performance_schema, sys, and mysql databases |
| Transaction Model | Full ACID with undo/redo; UNDO tablespace | Full ACID via InnoDB with MVCC and redo logs |
| Redo Logs | Online redo logs (redo01.log, etc.) | ib_logfile0, ib_logfile1 (InnoDB redo logs) |
| Undo Management | Separate Undo tablespace | Rollback segments in InnoDB tablespace |
| Control Files | Yes (control01.ctl, etc.) | No direct equivalent. Metadata stored in data dictionary |
| Datafiles / Tablespaces | Yes (mapped to tablespaces and schema objects) | InnoDB manages tablespace files automatically (or innodb_file_per_table) |
| Temporary Tablespaces | Yes (TEMP) | Memory or disk-backed temporary tables, no dedicated TEMP tablespace |
| Concurrency Control | MVCC + locks + latches | MVCC (via InnoDB), row-level locking |
| Lock Granularity | Row, Block, Table, DDL | Row-level (InnoDB), Table-level (MyISAM) |
| Job Scheduling | DBMS_SCHEDULER, DBMS_JOB | Event Scheduler (via CREATE EVENT) |
| Replication | Advanced (Data Guard, Streams, GoldenGate) | Built-in replication (async, semi-sync, GTID); also InnoDB Cluster |
| Backup Options | RMAN, datapump (expdp/impdp), cold backup | Logical: mysqldump, Physical: xtrabackup, mysqlpump |
| Security | Roles, profiles, fine-grained access, auditing | Users + GRANTs; roles introduced in MySQL 8.0 |
| High Availability | RAC, Data Guard | Group Replication, MySQL InnoDB Cluster, Galera |
| PL/SQL Support | Full support for PL/SQL | Limited stored procedures/functions (no packages, no exception, limited cursors) |
| Partitioning | Native, rich (range, list, hash, composite) | Supported in InnoDB (range, list, hash), but less powerful |
| Advanced Features | Flashback, Resource Manager, VPD, etc. | Limited. MySQL Enterprise includes Firewall, Audit plugin, etc. |
🔧 Key Observations for Oracle DBAs
- No redo/archive distinction in MySQL — redo-like logging is handled by InnoDB internally.
- No equivalent to control files, listener.ora, tnsnames.ora — simpler networking.
- Monitoring & troubleshooting in MySQL is done via
SHOW,INFORMATION_SCHEMA,performance_schema. - Memory management is mostly global (
innodb_buffer_pool_size) — no SGA/PGA split. - Background process architecture is simpler, but less tunable compared to Oracle.
- SQL syntax differences: MySQL lacks advanced PL/SQL constructs (like
packages,exceptions, etc.).