MySQL Architecture Flashcards
What are valid storage engines for MySQL Server Enterprise 5.7?
a) NDB
b) Aria
c) MyRocks
d) Innodb
e) Memory
f) Archive
d) and e) and f)
innodb
archive
memory
mysql> show engines \G
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
What are the ibdata files? idbdata1, ibdata2, etc
They are the set of files with names such as ibdata1, ibdata2, ect., that make up the InnoDB system tablespace. For information about the structures and data that reside in the system tablespace ibdata files, see Section 14.6.3.1, “The System Tablespace”.
Growth of the ibdata files is influenced by the innodb_autoextend_increment configuration option.
mysql 5.7 What is the system table space?
The system tablespace is the storage area for the InnoDB data dictionary, the doublewrite buffer, the change buffer, and undo logs.
It may also contain table and index data if tables are created in the system tablespace rather than file-per-table or general tablespaces.
select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
Which of the following is true, regarding The buffer pool:
a) The buffer pool is an area in main memory where InnoDB caches table data as it is accessed
b) The buffer pool is an area in main memory where InnoDB caches index data as it is accessed
c) The buffer pool permits frequently used data to be accessed directly from memory
d) All of the above
e) none of the above
Answer: D
all of the above.
The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.
ACID (atomicity, consistency, isolation, durability) model
When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
Which does this follow under?
A. Atomicity
B. consistency
C. isolation
D. durability
ATOMICITY
This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed.
ACID (atomicity, consistency, isolation, durability) model
On a server crash, InnoDB automatically recovers after the server is restarted.
Which does this follow under?
A. Atomicity
B. consistency
C. isolation
D. durability
B. consistency
On a server crash, InnoDB automatically recovers after the server is restarted. InnoDB automatically checks the logs and performs a roll-forward of the database to the present. InnoDB automatically rolls back uncommitted transactions that were present at the time of the crash.
ACID (atomicity, consistency, isolation, durability) model
Which of the follow parameters helps with consistency?
A. innodb_buffer_log_size
B. innodb_doublewrite
C. isolation_level
D. sql_mode
B. innodb_doublewrite
The doublewrite buffer is a storage area where InnoDB writes pages flushed from the buffer pool before writing the pages to their proper positions in the InnoDB data files. If there is an operating system, storage subsystem, or unexpected mysqld process exit in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
you should enable this
What type of lock a session gets when it executes: SELECT … FROM … FOR UPDATE
A. intention shared lock (IS)
B. intention exclusive lock (IX)
C. shared (S) lock
Ans: B
intention exclusive lock (IX)
An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.
For example, SELECT … LOCK IN SHARE MODE sets an IS lock, and SELECT … FOR UPDATE sets an IX lock.
What types of locks are compatible with the Intention Shared lock (IS)?
A. intention shared lock (IS)
B. shared lock (S)
C. intention exclusive lock (IX)
D. exclusive lock (X)
Ans: A, B and C
Table-level lock type compatibility is summarized in the following matrix.
X I X S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible
Which of the following is not correct?
A. SHOW COLUMNS B. SHOW DATABASES C. SHOW FUNCTION STATUS D. SHOW INDEX STATUS E. SHOW VARIABLES
Ans: D
The following are valid.
SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW DATABASES SHOW FUNCTION STATUS SHOW INDEX SHOW OPEN TABLES SHOW PROCEDURE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW TRIGGERS SHOW VARIABLES
When using the SHOW command you can include the use of LIKE and a WHERE clause.
SHOW CHARACTER SET WHERE Default collation LIKE ‘%japanese%’;
SHOW CHARACTER SET WHERE Maxlen > 1;
Some extensions to SHOW statements accompany the implementation of INFORMATION_SCHEMA:
- SHOW can be used to get information about the structure of INFORMATION_SCHEMA itself.
- Several SHOW statements accept a WHERE clause that provides more flexibility in specifying which rows to display.
INFORMATION_SCHEMA is an information database, so its name is included in the output from SHOW DATABASES. Similarly, SHOW TABLES can be used with INFORMATION_SCHEMA to obtain a list of its tables:
SHOW DATABASES;
or
SHOW TABLES FROM INFORMATION_SCHEMA;
