This document is part of a series that provides key information and guidance related to planning and performing Oracle® 11g/12c database migrations to Cloud SQL for MySQL version 5.7, second-generation instances. The series includes the following parts:
- Migrating Oracle users to Cloud SQL for MySQL: Terminology and functionality (this document)
- Migrating Oracle users to Cloud SQL for MySQL: Data types, users, and tables
- Migrating Oracle users to Cloud SQL for MySQL: Queries, stored procedures, functions, and triggers
- Migrating Oracle users to Cloud SQL for MySQL: Security, operations, monitoring, and logging
Terminology
This section details the similarities and differences in database terminology between Oracle and Cloud SQL for MySQL. It reviews and compares core aspects of each of the database platforms. The comparison distinguishes between Oracle versions 11g and 12c, due to architectural differences (for example, Oracle 12c introduces the multi-tenant feature). The Cloud SQL for MySQL version referenced here is 5.7.x.
Differences in terminology between Oracle 11g and Cloud SQL for MySQL
Oracle 11g | Description | Cloud SQL for MySQL | Key differences |
---|---|---|---|
Instance | A single Oracle 11g instance can hold only one database. | Instance | One MySQL instance can hold multiple databases. |
Database | A database qualifies as a single instance (the name of the database is identical to the instance name). | Database | Multiple or single databases serve multiple applications. |
Schema | Schema and users are identical because both are considered to be the owners of database objects (a user can be created without specifying or being allocated to a schema). | Schema | A schema is referred to as a database, while database objects are created under a certain schema/database. |
User | Identical to schema because both are owners of database objects—for example, instance → database → schemas/users → database objects. | User | A database user with specific permissions to connect or modify database objects on specific schemas/databases—for example, instance → databases/schemas → database objects. |
Role | Defined set of database permissions that can be chained as a group and can be assigned to database users. | MySQL privileges | MySQL 5.7 does not support roles management. Permissions can be
configured using the GRANT clause for users to have privileges
on database objects at different levels (READ/WRITE, etc.). |
Admin/SYSTEM users | Oracle administrator users with the highest level of access:SYS SYSTEM |
Super users | Once deployed, Cloud SQL for MySQL has the root@'%'
user (can connect from any host) and an additional user named
mysql.sys , which can connect only from the localhost. |
Dictionary/metadata | Oracle uses the following metadata tables:USER_TableName ALL_TableName DBA_TableName |
Dictionary/metadata | MySQL uses several databases/schemas for dictionary/metadata:MYSQL INFORMATION_SCHEMA PERFORMANCE_SCHEMA SYSEach of these databases are also system databases and are created with each Cloud SQL for MySQL deployment. |
System dynamic views | Oracle dynamic views:V$ViewName |
System dynamic views | MySQL dynamic views can be found in several of the system databases:INFORMATION_SCHEMA PERFORMANCE_SCHEMA SYS |
Tablespace | The primary logical storage structures of Oracle databases; each tablespace can hold one or more data files. | Tablespace | Similar to Oracle, a MySQL tablespace is a logical unit, but represents only
a single table, unlike the Oracle tablespace, which can have multiple data files
(holds data for multiple tables). MySQL supports the creation of a new tablespace with an allocated data file, then creating a new table attached to the newly created tablespace. Note that tablespace configuration settings such as sizing and limits are set using database parameters and not during tablespace creation. |
Data files | The physical elements of an Oracle database that hold the data and are
defined under a specific tablespace. A single data file is defined by the initial size and max size and can hold data for multiple tables. Oracle data files uses the .dbf suffix (not mandatory). |
Data files | Cloud SQL for MySQL uses the innodb_file_per_table
parameter with the value "ON " (default); this configuration
generates a new data file for each table and a dedicated tablespace.MySQL data files use .ibd (data) and .frm (metadata)
files. |
System tablespace | Contains the data dictionary tables and views objects for the entire Oracle database. | System tablespace | Same as Oracle, contains the dictionary/metadata tables. Stored in the
innodb_system tablespace attached with the ibdata1
data file. |
Temporary tablespace | Contains schema objects valid for the duration of a session; in addition, it supports running operations that cannot fit in server memory. | Temporary tablespace | Purpose is identical to Oracle, stored in MySQL
innodb_temporary tablespace attached with the ibtmp1
data file. |
Undo tablespace |
A special type of system-permanent tablespace used by Oracle to manage rollback operations when running the database in automatic undo management mode (default). | Undo tablespace | Similar to Oracle, MySQL Undo tablespace contains undo logs for rollback
purposes. By default, this option is set to OFF and mentioned
as deprecated
for future MySQL releases. |
ASM | Oracle Automatic Storage Management is an integrated, high-performance database file system and disk manager all run automatically by an Oracle database configured with ASM. | Not supported | MySQL uses the term storage engines to describe different data handling implementations, but does not support Oracle ASM. Cloud SQL for MySQL supports many features that provide storage automation, such as automatic storage increases, performance, and scalability. |
Tables/views | User-created fundamental database objects. | Tables/views | Identical to Oracle. |
Materialized views | Defined with specific SQL statements and can be manually or automatically refreshed based on specific configurations. | Not supported by MySQL | As a workaround, triggers/views can be used instead of Oracle materialized views. |
Sequence | Oracle unique value generator. | Auto increment | MySQL does not support Oracle sequences; use the
AUTO_INCREMENT as an alternative solution to automatic
sequence generation functionality. |
Synonym | Oracle database objects that serve as alternative identifiers for other database objects. | Not supported | MySQL does not support Oracle Synonym; as a workaround, views can be used while setting the appropriate permissions. |
Partitioning | Oracle provides many partitioning solutions for splitting large tables into smaller managed pieces. | Partitioning | MySQL has more limited partitioning support to improve performance, while retaining data management and maintenance operations, like Oracle partitions. |
Flashback database | Oracle proprietary feature that can be used to initialize an Oracle database to a previous defined time, allowing you to query or restore data that was modified or corrupted by mistake. | Not supported | For an alternative solution, you can use Cloud SQL backups and point-in-time recovery to restore a database to a previous state (for example, restoring before a table drop). |
sqlplus | Oracle command-line interface that lets you query and manage the database instance. | mysql | MySQL-equivalent command line-interface for querying and managing. Can be connected from any client with the appropriate permissions to Cloud SQL. |
PL/SQL | Oracle extended procedural language to ANSI SQL. | MySQL | MySQL has its own extended procedural language with different syntax and implementation, and does not have additional naming for this extended language. |
Package & package body | Oracle-specific functionality to group stored procedures and functions under the same logical reference. | Not supported | MySQL supports stored procedures and functions as single objects with group allocation. |
Stored procedures & functions | Uses PL/SQL to implement code functionality. | Stored procedure & functions | The creation of stored procedures and functions is supported by MySQL with its proprietary procedural language implementation. |
Trigger | Oracle object used to control DML implementation over tables. | Trigger | Identical to Oracle. |
PFILE/SPFILE | Oracle instance and database level parameters are kept in a binary file
known as the SPFILE (in previous versions, the file was called
PFILE ), which can be used as a text file for setting
parameters manually. |
Cloud SQL for MySQL database flags | You can set or modify Cloud SQL for MySQL parameters through the database flags
utility. You cannot change database parameters in Cloud SQL for MySQL by
using the MySQL client command-line interface (for example, mysql>
SET GLOBAL ... ). You can change these parameters only by using the
database flags utility. |
SGA/PGA/AMM | Oracle memory parameters that control memory allocation to the database instance. |
INNODB_BUFFER_POOL_SIZE
|
MySQL has its own memory parameters. An equivalent parameter could be
INNODB_BUFFER_POOL_SIZE . In Cloud SQL for MySQL, this
parameter is predefined by the chosen instance type, and the value changes
accordingly. |
Result Cache | Reduces SQL I/O operations by retrieving rows from the buffer cache, which can be managed using database parameters and hints at the session level. | Query Cache | Has the same fundamental purpose as Oracle Result Cache and can be managed at the database level and at the session level. |
Database hints | Controlled impact to SQL statements that influence the optimizer's behavior in order to achieve better performance. Oracle has more than 50 different database hints. | Database hints | MySQL supports a limited set of database hints in comparison to Oracle (optimizer hints and index hints). Note that MySQL uses different database hints, syntax, and naming. |
RMAN | Oracle Recovery Manager utility. Used to take database backups with extended functionality to support multiple disaster recovery scenarios and more (cloning, etc.). | Cloud SQL for MySQL Backup | Cloud SQL for MySQL offers two methods for applying full backup: on-demand and automated backups. |
Data Pump (EXPDP/IMPDP) | Oracle dump generation utility that can be used for many features, such as export/import, database backup (at the schema or object level), schema metadata, generate schema SQL files and more. | mysqldump/mysqlimport |
MySQL dump (export) utility that can be connected as a client (remotely)
and generate a dump file (SQL). Later, you can compress the dump file and
move it to Cloud Storage. The mysqldump utility is for the
export step only. |
SQL*Loader | Tool that provides the ability to upload data from external files such as text files, CSV files, and more. | mysqlimport/ |
The mysqlimport utility provides the ability to load text or
CSV files (Oracle supports additional file formats) into a database table
with a corresponding structure. |
Data Guard | Oracle disaster recovery solution using a standby instance, enabling users to perform `READ` operations from the standby instance. | Cloud SQL for MySQL high availability and replication | To achieve disaster recovery or high availability, Cloud SQL for MySQL offers the failover replica architecture and for read-only operations (READ/WRITE separation) using the Read Replica. |
Active Data Guard/Golden Gate | Oracle's main replication solutions, which can serve multiple purposes such as Standby (DR), Read-Only instance, Bi-Directional replication (multi-source), data warehousing, and more. | Cloud SQL for MySQL Read Replica | Cloud SQL for MySQL Read Replica to implement clustering with READ/WRITE separation. Currently, there is no support for multi-source configuration, such as Golden Gate Bi-Directional replication, or heterogeneous replication. |
RAC | Oracle Real Application Cluster. Oracle proprietary clustering solution for providing high availability by deploying multiple database instances with a single storage unit. | Not supported | Multi-source architecture is not yet supported in Google Cloud Cloud SQL. To achieve clustering architecture with READ/WRITE separation and high availability, use Cloud SQL high availability and read replica. |
Grid/Cloud Control (OEM) | Oracle software for managing and monitoring databases and other related services in a web app format. This tool is useful for real-time database analysis to understand high workloads. | Cloud SQL for MySQL Console, Cloud Monitoring | Use Cloud SQL for MySQL for monitoring, including detailed time and resources-based graphs. Also use Cloud Monitoring to hold specific MySQL monitoring metrics and logs analysis for advanced monitoring capabilities. |
REDO logs | Oracle transaction logs that consist of two (or more) pre-allocated defined files that store all data modifications as they occur. The purpose of the redo log is to protect the database in case of an instance failure. | REDO logs | MySQL also has redo logs files, which are used during crash recovery to correct data written by incomplete transactions' redo logs mechanism. |
Archive logs | Archive logs provide support for backup and replication operations and more. Oracle writes to archive logs (if enabled) after each redo log switch operation. | binlogs | MySQL implementation of transaction logs retention. Used mainly for replication purposes (enabled by default with Cloud SQL). |
Control file | The Oracle control file holds information about the database, such as data files, redo logs names, and locations, the current log sequence number, and information about the instance checkpoint. | MySQL | MySQL architecture doesn't include a control file similar to Oracle
implementation. This is controlled through the MySQL parameters and by
using the SHOW MASTER STATUS command in order to see the
current binary log position. |
SCN | Oracle SCN (System Change Number) is the primary method of maintaining data consistency by all Oracle database components in order to satisfy the ACID model of transaction. | Log Sequence Number | MySQL implementation for database consistency uses the LSN (Log Sequence Number). |
AWR | Oracle AWR (Automatic Workload Repository) is a verbose report providing detailed information about Oracle database instance performance and is considered a DBA tool for performance diagnostics. | performance_schema |
MySQL doesn't have a report equivalent to Oracle AWR, but MySQL gathers
performance data collected by performance_schema . An
alternative solution would be to use MySQL Workbench
performance dashboards. |
DBMS_SCHEDULER |
Oracle utility used to set and time predefined operations. | EVENT_SCHEDULER |
MySQL database internal scheduler functionality. By default, this
feature is set to OFF . |
Transparent data encryption | Encrypts data stored on disks as data-at-rest protection. | Cloud SQL Advanced Encryption Standard | Cloud SQL for MySQL uses the 256-bit Advanced Encryption Standard (AES-256) for data-at-rest security protection, as well as for in-transit data |
Advanced compression | To improve the database storage footprint, reduce storage costs, and improve database performance, Oracle provides data (tables/indexes) advanced compression capabilities. | InnoDB table compression | MySQL offers table
compression by creating a table using the ROW_FORMAT
parameter set to COMPRESSED . Read more about index
compression. |
SQL Developer | Oracle free SQL GUI for managing and running SQL and PL/SQL statements (can be used with MySQL as well). | MySQL Workbench | MySQL free SQL GUI for managing and running SQL and MySQL code statements. |
Alert Log | Oracle main log for general database operations and errors. | MySQL Error Log | Uses Cloud Logging Logs Viewer to watch MySQL error logs |
DUAL table |
Oracle special table holding mostly for retrieving pseudo column values
such as SYSDATE or
USER |
DUAL table | MySQL allows DUAL to be specified as a table in SQL statements
that do not depend on data from any tables. |
External table | Oracle allows users to create external tables having the source data on files outside the database. | Not supported. | No direct equivalent. |
Listener | Oracle network process tasked with listening for incoming database connections | Cloud SQL Authorized networks | MySQL accepts connections from remote sources once allowed in the Cloud SQL Authorized Networks configuration page |
TNSNAMES | Oracle network configuration file that defines database addresses for establishing connections by using connection aliases. | Does not exist | MySQL accepts external connections using the Cloud SQL Instance connection name or private/public IP address. Cloud SQL Proxy is an additional secure access method to connect to Cloud SQL for MySQL (second-generation instances) without having to allow specific IP addresses or configure SSL. |
Instance default port | 1521 | Instance default port | 3306 |
Database link | Oracle schema objects which can be used to interact with local/remote database objects. | Not supported | For an alternative workaround, use application code to connect and retrieve data from a remote database. |
Differences in terminology between Oracle 12c and Cloud SQL for MySQL
Oracle 12c | Description | Cloud SQL for MySQL | Key differences |
---|---|---|---|
Instance | The multi-tenant ability introduced in Oracle 12c instance that can hold multiple databases as a pluggable database (PDB), as opposed to Oracle 11g, where an Oracle instance can only obtain one database. | Instance | Cloud SQL for MySQL can hold multiple databases with different naming serving multiple services and applications. |
CDB | A multi-tenant container database (CDB) can support one or more PDBs, while CDB global objects (affects all PDBs) can be created, such as roles. | MySQL Instance | The MySQL instance is comparable to the Oracle CDB. Both provide a system layer for the PDBs. |
PDB | PDBs (pluggable databases) can be used to isolate services and applications from one another and can be used as a portable collection of schemas. | MySQL databases/schemas | A MySQL database can serve multiple services and applications as well as many database users. |
Session sequences | Starting with Oracle 12c, sequences can be created at the session level (return unique values only within a session) or at the global level (for example, when using temporary tables). | Auto increment |
Sequences are not supported by MySQL, but users can use the
AUTO_INCREMENT column property as an alternate solution. |
Identity columns | The Oracle 12c IDENTITY type generates a sequence and
associates it with a table column without the need to manually create a
separate sequence object. |
Auto increment | Use the AUTO_INCREMENT column property to simulate the same
functionality as Oracle 12c identity column (an alternative solution to
automatic sequence generation functionality). |
Sharding | Oracle sharding is a solution in which one Oracle database is partitioned into multiple smaller databases (shards) to allow scalability, availability, and geo-distribution for OLTP environments. | Not supported (as a feature) | MySQL does not have an equivalent sharding feature. Sharding can be implemented using MySQL (as the data platform) with a supporting application layer. |
In-memory database | Oracle provides a suite of features which can improve database performance for OLTP as well for and mixed workloads. | Not supported | MySQL does not have an equivalent feature. You can use Memorystore as an alternative. |
Redaction | As part of Oracle's advanced security features, redaction can perform column masking to prevent sensitive data from being displayed by users and applications. | Not supported | MySQL does not have an equivalent feature. |
Functionality
Although Oracle 11g/12c and Cloud SQL for MySQL databases work on different architectures (infrastructure and extended procedural languages), they share the same fundamental aspects of a relational database. They support database objects, multi-user concurrency workloads, and transactions (ACID compatibility). They also manage locking contentions that support multiple isolation levels (based on the business needs), and they serve ongoing application requirements as a relational data store for Online Transactional Processing (OLTP) operations and Online Analytical Processing (OLAP).
The following section provides an overview of some of the main functional differences between Oracle and Cloud SQL for MySQL. In some cases, where needed, the section includes detailed technical comparisons.
Creating and viewing existing databases
Oracle 11g/12c | Cloud SQL for MySQL 5.7 |
---|---|
You usually create databases, and view existing ones, by using the Oracle
Database
Creation Assistant (DBCA) utility. Manually created databases or
instances require that you specify additional parameters:SQL> CREATE DATABASE ORADB USER SYS IDENTIFIED BY password USER SYSTEM IDENTIFIED BY password EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE temp UNDO TABLESPACE undotbs1 DEFAULT TABLESPACE users; |
Use a statement of the form CREATE DATABASE Name; , as
in this example:
mysql> CREATE DATABASE MYSQLDB; |
Oracle 12c | Cloud SQL for MySQL 5.7 |
In Oracle 12c, you can create PDBs from the seed, either from a container
database (CDB) template or by cloning a PDB from an existing PDB. You use
several parameters:SQL> CREATE PLUGGABLE DATABASE PDB ADMIN USER usr IDENTIFIED BY passwd ROLES = (dba) DEFAULT TABLESPACE sales DATAFILE '/disk1/ora/dbs/db/db.dbf' SIZE 250M AUTOEXTEND ON FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/salespdb/') STORAGE (MAXSIZE 2G) PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'; |
Use a statement of the form CREATE DATABASE Name; , as
in this example:mysql> CREATE DATABASE MYSQLDB; |
List all PDBs:SQL> SHOW is PDBS; |
List all existing databases:mysql> SHOW DATABASES; |
Connect to a different PDB:SQL> ALTER SESSION SET CONTAINER=pdb; |
Connect to a different database:mysql> use databaseName;Or: mysql> \u databaseName; |
Open or close a specific PDB (open/read-only):SQL> ALTER PLUGGABLE DATABASE pdb CLOSE; |
Not supported for a single database. All databases are under the same Cloud SQL for MySQL instance; thus, all databases are all up or all down. |
Managing a database through the Cloud SQL console
In the Google Cloud console, go to Storage > SQL > Instance > Databases > View/Create.
System databases and schemas
Oracle database instances obtain certain system schemas—for example,
SYS/SYSTEM
—with the owners role of database metadata objects.
In contrast, MySQL holds several system databases (as opposed to Oracle schemas), which serve the metadata layer (note that the database names are case sensitive):
-
The
mysql
system database holds tables that store information required by the MySQL server as it runs, such as the following:- System privileges tables
- Objects information tables
- Log system tables
- Replication system tables
- Optimizer system tables
- Time zone system tables
-
INFORMATION_SCHEMA
serves as the main database data dictionary and system catalog. It provides access to database metadata, which is internal database information about the MySQL server, such as the name of a database or table, the column data type, and the access privileges. -
A system database that collects statistical information about the MySQL instance. The
performance_schema
system database holds metrics for server execution monitoring at a very granular level. The schema provides server internal execution inspection at run time, and it can serve as the main source for analyzing database performance issues.The
performance_schema
is not enabled by default using Cloud SQL for MySQL. To enable the schema, use thegcloud
command-line tool:gcloud sql instances patch INSTANCE_NAME --database-flags performance_schema=on
To complete this configuration, you must reboot the instance. You cannot modify the
--database-flags
parameter by using the Cloud SQL for MySQL database flags page in the Google Cloud console. -
The
sys
schema exists from MySQL version 5.5.7 and holds mainly views on theperformance_schema
system tables. This schema provides a more readable set of views that summarizeperformance_schema
data into a more easily understandable form. Thesys
schema also holds several stored procedures and functions to perform operations such as configuringperformance_schema
and generating diagnostic reports.The
sys
schema displays information only when theperformance_schema
is enabled.
Viewing metadata and system dynamic views
This section provides an overview of some of the most common metadata tables and system dynamic views used in Oracle and their corresponding database objects in Cloud SQL for MySQL version 5.7.
Oracle provides hundreds of system metadata tables and views, while MySQL only holds several dozen. For each case, there can be more than one database object, serving a specific purpose.
Oracle provides several levels of metadata objects, each requiring different privileges:
USER_TableName
: Can be viewed by the user.ALL_TableName
: Can be viewed by all users.DBA_TableName
: Can be viewed only by users with the DBA privilege, such asSYS
andSYSTEM
.
For dynamic performance views, Oracle uses the V$
and GV$
prefixes. For a
MySQL user to be able to view system metadata tables or views, they must have
specific permissions on system objects. For more information about security, see
the
Security
section.
Metadata type | Oracle table/view | MySQL table/view/show (MySQL CMD) |
---|---|---|
Open sessions | V$SESSION |
SHOW PROCESSLIST INFORMATION_SCHEMA.PROCESSLIST performance_schema.threads |
Running transactions | V$TRANSACTION |
INFORMATION_SCHEMA.INNODB_TRX |
Database objects | DBA_OBJECTS |
Not supported. Query each object by its type. |
Tables | DBA_TABLES |
INFORMATION_SCHEMA.TABLES INFORMATION_SCHEMA.INNODB_SYS_TABLES |
Table columns | DBA_TAB_COLUMNS |
INFORMATION_SCHEMA.COLUMNS INFORMATION_SCHEMA.INNODB_SYS_COLUMNS |
Table and column privileges | TABLE_PRIVILEGES DBA_COL_PRIVS ROLE_TAB_PRIVS |
INFORMATION_SCHEMA.COLUMN_PRIVILEGES |
Partitions | DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS |
INFORMATION_SCHEMA.PARTITIONS SHOW CREATE TABLE TableName SHOW TABLE STATUS LIKE 'TableName' |
Views | DBA_VIEWS |
INFORMATION_SCHEMA.VIEWS |
Constraints | DBA_CONSTRAINTS |
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS SHOW CREATE TABLE TableName |
Indexes | DBA_INDEXES DBA_PART_INDEXES |
INFORMATION_SCHEMA.STATISTICS INFORMATION_SCHEMA.INNODB_SYS_INDEXES INFORMATION_SCHEMA.KEY_COLUMN_USAGE |
Materialized views | DBA_MVIEWS |
Not supported |
Stored procedures | DBA_PROCEDURES |
INFORMATION_SCHEMA.ROUTINES |
Stored functions | DBA_PROCEDURES |
INFORMATION_SCHEMA.ROUTINES |
Triggers | DBA_TRIGGERS |
INFORMATION_SCHEMA.TRIGGERS |
Users | DBA_USERS |
mysql.user |
User privileges | DBA_SYS_PRIVS DBA_ROLE_PRIVS SESSION_PRIVS |
INFORMATION_SCHEMA.USER_PRIVILEGES |
Jobs/ Scheduler |
DBA_JOBS DBA_JOBS_RUNNING DBA_SCHEDULER_JOBS DBA_SCHEDULER_JOB_LOG |
INFORMATION_SCHEMA.EVENTS |
Tablespaces | DBA_TABLESPACES |
INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES |
Data files | DBA_DATA_FILES |
INFORMATION_SCHEMA.FILES INFORMATION_SCHEMA.INNODB_SYS_DATAFILES |
Synonyms | DBA_SYNONYMS |
Not supported |
Sequences | DBA_SEQUENCES |
Not supported |
Database links | DBA_DB_LINKS |
Not supported |
Statistics | DBA_TAB_STATISTICS DBA_TAB_COL_STATISTICS DBA_SQLTUNE_STATISTICS DBA_CPU_USAGE_STATISTICS |
INFORMATION_SCHEMA.STATISTICS INFORMATION_SCHEMA.KEY_COLUMN_USAGE SHOW INDEXES FROM TableName |
Locks | DBA_LOCK DBA_DDL_LOCKS DBA_DML_LOCKS V$SESSION_BLOCKERS V$LOCKED_OBJECT |
INFORMATION_SCHEMA.INNODB_LOCKS INFORMATION_SCHEMA.INNODB_LOCK_WAITS INFORMATION_SCHEMA.INNODB_TRX performance_schema.metadata_locks performance_schema.rwlock_instances SHOW PROCESSLIST |
Database parameters | V$PARAMETER V$NLS_PARAMETERS SHOW PARAMETER Param |
performance_schema.global_variables performance_schema.session_variables INFORMATION_SCHEMA.CHARACTER_SETS SHOW VARIABLES LIKE '%variable%'; |
Segments | DBA_SEGMENTS |
Segments table is not supported. Query each object by its type. |
Roles | DBA_ROLES DBA_ROLE_PRIVS USER_ROLE_PRIVS |
Roles not supported use instead: information_schema.COLUMN_PRIVILEGES information_schema.SCHEMA_PRIVILEGES information_schema.TABLE_PRIVILEGES information_schema.USER_PRIVILEGES mysql.columns_priv mysql.procs_priv mysql.proxies_priv mysql.tables_priv |
Session history | V$ACTIVE_SESSION_HISTORY DBA_HIST_* |
sys.statement_analysis performance_schema.events_stages_history performance_schema.events_stages_history_long performance_schema.events_statements_history performance_schema.events_statements_history_long performance_schema.events_transactions_history performance_schema.events_transactions_history_long performance_schema.events_waits_history performance_schema.events_waits_history_long |
Version | V$VERSION |
sys.version SHOW VARIABLES LIKE '%version%'; |
Wait events | V$WAITCLASSMETRIC V$WAITCLASSMETRIC_HISTORY V$WAITSTAT V$WAIT_CHAINS |
performance_schema.events_waits_current performance_schema.events_waits_history performance_schema.events_waits_history_long sys.innodb_lock_waits sys.io_global_by_wait_by_bytes sys.io_global_by_wait_by_latency sys.schema_table_lock_waits sys.wait_classes_global_by_avg_latency sys.wait_classes_global_by_latency sys.waits_by_host_by_latency sys.waits_by_user_by_latency sys.waits_global_by_latency |
SQL tuning and analyzing |
V$SQL V$SQLAREA V$SESS_IO V$SYSSTAT V$STATNAME V$OSSTAT V$ACTIVE_SESSION_HISTORY V$SESSION_WAIT V$SESSION_WAIT_CLASS V$SYSTEM_WAIT_CLASS V$LATCH V$SYS_OPTIMIZER_ENV V$SQL_PLAN V$SQL_PLAN_STATISTICS |
performance_schema.events_statements_current performance_schema.events_statements_history performance_schema.events_statements_history_long sys.statement_analysis sys.host_summary_by_statement_latency sys.host_summary_by_statement_type sys.statements_with_errors_or_warnings sys.statements_with_full_table_scans sys.statements_with_runtimes_in_95th_percentile sys.statements_with_sorting sys.statements_with_temp_tables sys.user_summary_by_statement_latency sys.user_summary_by_statement_type slow-query-log general-log SHOW STATUS LIKE '%StatusName%'; |
Instance memory tuning |
V$SGA V$SGASTAT V$SGAINFO V$SGA_CURRENT_RESIZE_OPS V$SGA_RESIZE_OPS V$SGA_DYNAMIC_COMPONENTS V$SGA_DYNAMIC_FREE_MEMORY V$PGASTAT |
information_schema.INNODB_CMPMEM_RESET information_schema.INNODB_CMPMEM performance_schema.memory_summary_by_account_by_event_name performance_schema.memory_summary_by_host_by_event_name performance_schema.memory_summary_by_thread_by_event_name performance_schema.memory_summary_by_user_by_event_name performance_schema.memory_summary_global_by_event_name performance_schema.replication_group_member_stats performance_schema.replication_group_members sys.memory_by_host_by_current_bytes sys.memory_by_thread_by_current_bytes sys.memory_by_user_by_current_bytes sys.memory_global_by_current_bytes sys.memory_global_total |
MySQL storage engines
Unlike many other RDBMS (including Oracle's), MySQL can work polymorphically due to its pluggable storage system. The MySQL pluggable storage engine architecture enables a database administrator to select a specialized storage engine for a particular application need.
The MySQL pluggable storage engine component in the MySQL database server is responsible for performing the data I/O operations, including storing the data into the disks or memory buffers. The pluggable storage engine architecture provides a standard set of management and support services that are common among all underlying storage engines.
From MySQL version 5.5 and higher, the default storage engine is the InnoDB
storage engine, while InnoDB is also handling the temporary tables. You can
configure storage engines during table CREATE
or ALTER
, as in the following
example:
mysql> SHOW CREATE TABLE JOBS \G;
The output is the following:
*************************** 1. row *************************** Table: JOBS Create Table: CREATE TABLE `JOBS` ( `JOB_ID` varchar(10) NOT NULL, `JOB_TITLE` varchar(35) NOT NULL, `MIN_SALARY` decimal(6,0) DEFAULT NULL, `MAX_SALARY` decimal(6,0) DEFAULT NULL, PRIMARY KEY (`JOB_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
For more information, see MySQL
different storage engines.
You can view the storage engine configuration by using the following query:
mysql> SHOW VARIABLES LIKE '%storage%';
The output is similar to the following:
+----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | enforce_storage_engine | Innodb | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+
You can view all built-in storage engines:
mysql> SHOW STORAGE ENGINES;
The output is similar to the following:
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
Note that InnoDB is the default storage engine, and is the only storage engine to support transactions (ACID compliant). Because InnoDB is the only storage engine that approximates Oracle functionality, we recommend that you use InnoDB at all times. Cloud SQL for MySQL Second Generation supports only the InnoDB storage engine.
System parameters
Both Oracle and Cloud SQL for MySQL databases can be specifically
configured to achieve certain functionality beyond the default configuration. To
alter configuration parameters in Oracle, certain administration permissions are
required (primarily the SYS/SYSTEM
user permissions).
Following is an example of altering the Oracle configuration by using the
ALTER SYSTEM
statement. In this example, the user changes the "maximum
attempts for failed logins" parameter at the spfile
configuration level only
(with the modification valid only after after a reboot):
SQL> ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=2 SCOPE=spfile;
In the next example, the user is requesting to simply view the Oracle parameter value:
SQL> SHOW PARAMETER SEC_MAX_FAILED_LOGIN_ATTEMPTS;
The output is similar to the following:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_max_failed_login_attempts integer 2
The Oracle parameter modification works in three scopes:
- SPFILE: Parameter modifications are written to the Oracle
spfile
, with an instance reboot required for the parameter to take effect. - MEMORY: Parameter modifications are applied in the memory layer only while no static parameter change is allowed.
- BOTH: Parameter modifications are applied in both the server parameter file and in the instance memory, where no static parameter change is allowed.
Cloud SQL for MySQL configuration flags
You can modify the Cloud SQL for MySQL system parameters by using the configuration flags in the Google Cloud console, the gcloud CLI, or CURL. See the complete list of all parameters supported by Cloud SQL for MySQL that you can alter.
MySQL parameters can be divided into several scopes:
- Dynamic parameters: Can be altered at run time.
- Static parameters: Require an instance reboot to take effect.
- Global parameters: Will have a global effect on all current and future sessions.
- Session parameters: Can be altered at the session level for the current session lifetime only, isolated from other sessions.
The Cloud SQL for MySQL memory parameter
innodb_buffer_pool_size
(one of the crucial parameters to consider when planning and sizing a MySQL
environment) is determined by the instance type and cannot be altered by using
the configuration flags or by any other method—for example:
- Instance type
db-n1-standard-1
has 1.4 GB of memory allocation. - Instance type
db-n1-highmem-8
has 38 GB of memory allocation.
Examples of altering Cloud SQL for MySQL parameters
Console
Use the Google Cloud console to enable the event_scheduler
parameter.
Go to the Edit instance page of Cloud Storage.
Under Flags, click Add item and search for
event_scheduler
as in the following screenshot.
gcloud
Use the gcloud CLI to enable the
event_scheduler
parameter.:gcloud sql instances patch INSTANCE_NAME \ --database-flags event_scheduler=on
The output is the following:
WARNING: This patch modifies database flag values, which may require your instance to be restarted. Check the list of supported flags - /sql/docs/mysql/flags - to see if your instance will be restarted when this patch is submitted. Do you want to continue (Y/n)?
MySQL session
Disable AUTOCOMMIT
mode at the session level. This alteration stays in
effect for the current session and holds for the session lifetime only.
Show variables like
autocommit
:mysql> SHOW VARIABLES LIKE '%autoc%';
You see the following output, where
autocommit
isON
:+---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+
Disable
autocommit
:mysql> SET autocommit=off;
Show variables like
autocommit
:mysql> SHOW VARIABLES LIKE '%autoc%';
You see the following output, where
autocommit
isOFF
:+---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+
Transactions and isolation levels
This section describes the main differences between Oracle and Cloud SQL for MySQL relevant to transaction and isolation level migrations.
Commit mode
Oracle works by default in non-autocommit mode, where each DML transaction must
be determined with COMMIT/ROLLBACK
statements. One of the fundamental
differences between Oracle and MySQL is that MySQL works by default in
autocommit mode, and each DML transaction is autocommitted with explicitly
specifying the COMMIT/ROLLBACK
statements.
To force MySQL to work in a non-autocommit mode, there are several options:
- When managing transactions within the scope of stored procedures, use
the
START TRANSACTION
clause to enter the same transactional mode same as Oracle. Use the following statement to set the
autocommit
system parameter toOFF
at the session level and explicitly useCOMMIT/ROLLBACK
statements in DML transactions:mysql> SET autocommit=off;
Isolation levels
The ANSI/ISO SQL standard (SQL92) defines four levels of isolation. Each level provides a different approach for handling concurrent execution of database transactions:
- Read Uncommitted: A currently processed transaction can see uncommitted data made by the other transaction. If a rollback is performed, all data is restored to its previous state.
- Read Committed: A transaction only sees data changes that were committed; uncommitted changes("dirty reads") are not possible.
- Repeatable Read: A transaction can view changes made by the other
transaction only after both transactions issued a
COMMIT
or both are rolled back. - Serializable: The strictest/strongest isolation level. This level locks all records that are accessed, and locks the resource so that records cannot be appended to the table.
Transaction isolation levels manage the visibility of changed data as seen by other running transactions. Also, when the same data is accessed by several concurrent transactions, the selected level of transaction isolation affects the way different transactions interact.
Oracle supports the following isolation levels:
- Read Committed (default)
- Serializable
- Read-Only (not a part of the ANSI/ISO SQL standard (SQL92)
Oracle MVCC (Multiversion Concurrency Control):
- Oracle uses the MVCC mechanism to provide automatic read consistency across the entire database and all sessions.
- Oracle relies on the System Change Number (SCN) of the current transaction to obtain a consistent view of the database; therefore, all database queries only return data committed with respect to the SCN at the time of query execution.
- Isolation levels can be changed at the transaction and session levels.
Here's an example of setting isolation levels:
-- Transaction Level
SQL> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SQL> SET TRANSACTION READ ONLY;
-- Session Level
SQL> ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;
SQL> ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;
Cloud SQL for MySQL, like Oracle, supports the following four transaction isolation levels specified in the ANSI SQL:92 standard:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ (default)
SERIALIZABLE
The Cloud SQL for MySQL default isolation level is REPEATABLE READ
. Any
new data will be available for both only after both transactions issue a
COMMIT
command. These isolation levels can be altered at SESSION
level and
at GLOBAL
level
(global
level modification is currently in beta stage using configuration flags).
To verify current isolation levels both at SESSION
and GLOBAL
levels, use
the following statement:
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
The output is the following:
+-----------------------+-----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+
You can modify the isolation level syntax as follows:
SET [SESSION] TRANSACTION ISOLATION LEVEL [READ WRITE | READ ONLY]
| REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE]
And you can modify the isolation level at SESSION
level:
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Verify
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
The output is the following:
+-----------------------+------------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+------------------+ | REPEATABLE-READ | READ-UNCOMMITTED | +-----------------------+------------------+
Cloud SQL for MySQL transactions structure
Transactions syntax:
START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...] |
---|
Transactions can be implemented with START TRANSACTION
or BEGIN
.
The WITH CONSISTENT SNAPSHOT
option starts a consistent READ
transaction,
which is effectively the same as issuing a START TRANSACTION
, followed by a
SELECT
from any table. The WITH CONSISTENT SNAPSHOT
clause that starts a
consistent READ
(a READ
operation that uses snapshot information to present
query results based on a point in time) does not change the transaction
isolation level and is supported only by the REPEATABLE READ
isolation
level.
A consistent READ
uses snapshot information to make query results available
based on a point-in-time, regardless of modifications performed by concurrent
transactions. If queried data has been changed by another transaction, the
original data is reconstructed using the undo log. This will assist avoiding
locking issues that may reduce concurrency.
With REPEATABLE READ
isolation level, the snapshot is based on the time the
first READ
operation is performed. With the READ COMMITTED
isolation level,
the snapshot is reset to the time of each consistent READ
operation.
Here's an example of transaction and isolation level setting:
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
mysql> START TRANSACTION;
mysql> INSERT INTO tbl1 VALUES (1, 'A');
mysql> UPDATE tbl2 SET col1 = 'Done' WHERE KeyColumn = 1;
mysql> COMMIT;