Everything Aurora (MySQL)

Recently AWS released their version of MySQL called Aurora, they claim it delivers up to five times the throughput of standard MySQL. This article explores some of the depths of this database technology, especially coming from an Oracle Database background.

Character Sets
In an Oracle system, you can define 2 character sets, one is used for char, varchar2, clob etc but national Character set is used for nchar, nvarchar2,nclob.To find values you can issue the following statement:

select * from nls_database_parameters where parameter like '%SET%';

MySQL contains character set support that enables data to be stored using an assortment of character sets and perform comparisons according to a variety of collations. Character sets can be set at the server level, database and table left and at the column level. MySQL also supports the use of character sets for the MyISAM, MEMORY, and InnoDB storage engines.

You can see the supported character sets from the following statement

Select * from information_schema.character_sets;

Note: The default character set is single byte, this can cause issues especially if you are using different languages and extended characters.

Character set problems affect not only data storage, but also communication between client programs and the MySQL server. If there is a requirement to communicate with the database server using a different character set from the default, the following example can be used after connecting to the server:

SET NAMES 'utf8';

Applications that store data using the default character set and collation (single byte – latin, latin1_swedish_ci), no special configuration should be needed. If an application requires data storage using a different character set or collation (multi byte), different character sets can be set several ways.

Performance and Locking

show open tables where in_use=1;
show full processlist;
show engine innodb status;
select * from information_schema.events;
select * from information_schema.character_sets;
show character set;
show status 'max_used_connections';
select * from information_schema.PARAMETERS;
select * from information_schema.GLOBAL_VARIABLES;

SET FOREIGN_KEY_CHECKS=1;