105. AWS RDS 01 - qyjohn/AWS_Tutorials GitHub Wiki
This section covers the basic usage of RDS, with a focus on RDS MySQL. We will use the AWS CLI and SDK to create and terminate RDS instances. We will create users, databases, tables, records on your RDS MySQL instance using the MySQL client. We will also interact with the RDS instances using the Java (through JDBC) and PHP.
(1) MySQL Basics
First all, launch an EC2 instance with Ubuntu 16.04 to learn some MySQL basics. When the EC2 instance is running, SSH into the EC2 instance to install MySQL server. During the installation you will be prompted for your MySQL root password. Remember the password because you will need it later.
$ sudo apt-get update
$ sudo apt-get install mysql-server
$ sudo service mysql start
You can access the MySQL server using the standard MySQL client, which is installed when you install the MySQL server:
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
It should be noted that all MySQL commands end with a semicolon. If the phrase does not end with a semicolon, the command will not execute. MySQL command lines are not case sensitive. To make it easier to read, MySQL commands are usually written in uppercase and databases, tables, usernames, or text are in lowercase to make them easier to distinguish.
You can quickly check what databases are available by typing:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
Now we create a new database with CREATE DATABASE:
mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db |
+--------------------+
5 rows in set (0.00 sec)
Then we delete the newly created database with DROP DATABASE:
mysql> DROP DATABASE test_db;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
When you need access a database, you need to USE it. You can use SHOW TABLES to display the tables in a database - a database can contain many tables.
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)
Now we create a table with CREATE TABLE, and display the table structure with DESCRIBE TABLE:
mysql> CREATE TABLE users (id INT, name VARCHAR(50));
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
mysql> DESCRIBE users;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Use INSERT INTO to write new records into your table, and SELECT to retrieve data from your table:
mysql> INSERT INTO users (id, name) VALUES (1, "Johnny");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO users (id, name) VALUES (2, "Danny");
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO users (id, name) VALUES (3, "Sissy");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM users;
+------+--------+
| id | name |
+------+--------+
| 1 | Johnny |
| 2 | Danny |
| 3 | Sissy |
+------+--------+
3 rows in set (0.00 sec)
Use UPDATE to modify records in your table:
mysql> UPDATE users SET name='Jacky' WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE users SET id=5 WHERE name='Sissy';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM users;
+------+--------+
| id | name |
+------+--------+
| 1 | Johnny |
| 2 | Jacky |
| 5 | Sissy |
+------+--------+
3 rows in set (0.00 sec)
Use DELETE FROM to delete records in your table:
mysql> DELETE FROM users WHERE id=5;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM users WHERE name='Johnny';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM users;
+------+-------+
| id | name |
+------+-------+
| 2 | Jacky |
+------+-------+
1 row in set (0.00 sec)
In these examples we are using the root user of the MySQL server to perform these operations. The root user has a lot of privileges, which might be destructive when used in an improper way. As such, it is desirable to create users with less privileges.
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON test.* TO 'username'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
Now we login with the newly created user:
$ mysql -u username -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
As you can see, this newly created users sees less databases than the root user:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
Because we grant ALL privileges on the test database to this user, this user is able to USE the test database, including INSERT, UPDATE, and DELETE.
mysql> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users;
+------+-------+
| id | name |
+------+-------+
| 2 | Jacky |
+------+-------+
1 row in set (0.00 sec)
If you are not familiar with MySQL, we recommend that you go through the following tutorial. There are many other good tutorials on the web and you might want to use other tutorials that you find helpful.
(2) Connect to a Remote MySQL Server
Launch another EC2 instance with Ubuntu 16.04. SSH into the EC2 instance and install the standard MySQL client:
$ sudo apt-get update
$ sudo apt-get install mysql-client
It is quite likely that you are not able to connect to your MySQL server. In the following example, the IP address belongs to the EC2 instance running your MySQL server:
$ mysql -h 172.31.10.225 -u username -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '172.31.10.225' (111)
When you are not able to connect to a remote server running on an EC2 instance, the first thing to check is whether the security group on the source EC2 instance (the MySQL client) allows the outbound traffic to the destination EC2 instance (the MySQL server), and whether the security group on the destination EC2 instance (the MySQL server) allows the inbound traffic from the source EC2 instance (the MySQL client). It should be noted that the MySQL server runs on port 3306. When the MySQL client initiates a connection to the MySQL server, the MySQL client uses an ephemeral port and the port number is not known to you before the connection is created. Here we assume that you know enough about security group and you have the proper security group in place.
More importantly, we need to make sure that the MySQL server is accepting connections from the outside. From the EC2 instance running the MySQL server, we run the following command to do a quit test. As we can see, the connection is successful when we attempt to connect to port 3306 on localhost, but is refused when we attempt to connect to port 3306 using the private IP address. This indicates that the MySQL server is listening (and accepting connections from) localhost only.
$ telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
[
5.7.17-0ubuntu0.16.04.W
{)G ?0h-,5pd
mysql_native_password^]
telnet> quit
Connection closed.
ubuntu@ip-172-31-10-225:~$ telnet 172.31.10.225 3306
Trying 172.31.10.225...
telnet: Unable to connect to remote host: Connection refused
On Ubuntu 16.04, the MySQL server configuration is stored in /etc/mysql/mysql.conf.d/mysqld.cnf. Use a text editor to open this file, and find the following lines:
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
As we can see, MySQL is configure to listen to 127.0.0.1 (localhost) only. We will change it to the private IP address of the EC2 instance for the time being.
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 172.31.10.225
Save the configuration file and restart the MySQL server:
$ sudo service mysql restart
Now we do another telnet test from the MySQL server. As you can see, now you can telnet to port 3306 on the MySQL using its private IP address. If you do the same test from the MySQL client, you should be able to see the same result. If you do not see the same result, double check your security group rules.
$ telnet 172.31.10.225 3306
Trying 172.31.10.225...
Connected to 172.31.10.225.
Escape character is '^]'.
[
5.7.17-0ubuntu0.16.04.1MJ#lN2Gh/jc:}#Kmmysql_native_password^]
telnet> quit
Connection closed.
Now we connect to the MySQL server from your MySQL client:
$ mysql -h 172.31.10.225 -u username -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
(3) Launch an RDS MySQL Instance
As you can see from the many options in your MySQL server configuration file, managing a MySQL server can be quite a complicate job. From now on, we do not want to manage our own MySQL servers. We will use the RDS MySQL service instead.
Terminate the EC2 instance running your MySQL server. Keep the EC2 instance running your MySQL client. Launch an RDS MySQL instance and connect to it from the MySQL client. You will probably need the following AWS documentation to get started.
Now you have an RDS MySQL instance and you are able to connect to it using the standard MySQL client. (If you are not able to connect to the RDS MySQL instance, check your security group rules.) Try to create a new database with a couple of tables, do some INSERT, UPDATE, DELETE.
At this point, you should use the AWS CLI to do the following:
- describe-db-instances
- create-db-snapshort
- describe-db-snapshots
- restore-db-instance-from-db-snapshot
- delete-db-instance
(4) AWS SDK for Java
In this section, we use the AmazonRDSClient to accomplish some basic tasks such as launching an RDS instance, listing all RDS instances in a particular region, as well as terminating a particular RDS instance. You should also take a look at the Java docs for the AmazonRDSClient to get yourself familiar with the various properties and methods.
First of all, we create an instance of the AmazonRDSClient:
public class DemoRDS
{
public AmazonRDSClient client;
public DemoRDS()
{
// Create the AmazonRDSClient
client = new AmazonRDSClient();
// Set the region to ap-southeast-2
client.configureRegion(Regions.AP_SOUTHEAST_2);
}
To launch an RDS instance, you will need to create a CreateDBInstanceRequest object, then pass it to the createDBInstance() method of the AmazonRDSClient, which returns a DBInstance object. From the DBInstance object, you will be able to obtain information about the newly created RDS instance. Due to the asynchronous nature of AWS API calls, some information might not be available in the DBInstance object returned by the createDBInstance() method. For example, the DNS endpoint for the newly created RDS instance will not be available until several minutes later, therefore instance.getEndpoint() will return a null result. If you try to convert this null result into a String, you will get an exception.
public String launchInstance()
{
System.out.println("\n\nLAUNCH INSTANCE\n\n");
try
{
// The CreateDBInstanceRequest object
CreateDBInstanceRequest request = new CreateDBInstanceRequest();
request.setDBInstanceIdentifier("Sydney"); // RDS instance name
request.setDBInstanceClass("db.t2.micro");
request.setEngine("MySQL");
request.setMultiAZ(false);
request.setMasterUsername("username");
request.setMasterUserPassword("password");
request.setDBName("mydb"); // database name
request.setStorageType("gp2"); // standard, gp2, io1
request.setAllocatedStorage(10); // in GB
// VPC security groups
ArrayList list = new ArrayList();
list.add("sg-efcc248a"); // security group, call add() again to add more than one
request.setVpcSecurityGroupIds(list);
// Create the RDS instance
DBInstance instance = client.createDBInstance(request);
// Information about the new RDS instance
String identifier = instance.getDBInstanceIdentifier();
String status = instance.getDBInstanceStatus();
Endpoint endpoint = instance.getEndpoint();
String endpoint_url = "Endpoint URL not available yet.";
if (endpoint != null)
{
endpoint_url = endpoint.toString();
}
// Do some printing work
System.out.println(identifier + "\t" + status);
System.out.println(endpoint_url);
// Return the DB instance identifier
return identifier;
} catch (Exception e)
{
// Simple exception handling by printing out error message and stack trace
System.out.println(e.getMessage());
e.printStackTrace();
return "ERROR";
}
}
To list all RDS instances, we simply call the describeDBInstances() method of the AmazonRDSClient. This method returns a list of DBInstance objects, and you need to traverse through the list to obtain information about each individual DBInstance object.
public void listInstances()
{
System.out.println("\n\nLIST INSTANCE\n\n");
try
{
// Describe DB instances
DescribeDBInstancesResult result = client.describeDBInstances();
// Getting a list of the RDS instances
List<DBInstance> instances = result.getDBInstances();
for (DBInstance instance : instances)
{
// Information about each RDS instance
String identifier = instance.getDBInstanceIdentifier();
String engine = instance.getEngine();
String status = instance.getDBInstanceStatus();
Endpoint endpoint = instance.getEndpoint();
String endpoint_url = "Endpoint URL not available yet.";
if (endpoint != null)
{
endpoint_url = endpoint.toString();
}
// Do some printing work
System.out.println(identifier + "\t" + engine + "\t" + status);
System.out.println("\t" + endpoint_url);
}
} catch (Exception e)
{
// Simple exception handling by printing out error message and stack trace
System.out.println(e.getMessage());
e.printStackTrace();
}
}
To terminate an RDS instance, we need to create a DeleteDBInstanceRequest, then pass the DeleteDBInstanceRequest to the deleteDBInstance() method. In the DeleteDBInstanceRequest, you should at least specify the DB instance identifier and whether you want to skip the final snapshot for the RDS instance to be deleted. If you want to create a final snapshot, you will need to set the name of the final snapshot in the DeleteDBInstanceRequest object.
public void terminateInstance(String identifier)
{
System.out.println("\n\nTERMINATE INSTANCE\n\n");
try
{
// The DeleteDBInstanceRequest
DeleteDBInstanceRequest request = new DeleteDBInstanceRequest();
request.setDBInstanceIdentifier(identifier);
request.setSkipFinalSnapshot(true);
// Delete the RDS instance
DBInstance instance = client.deleteDBInstance(request);
// Information about the RDS instance being deleted
String status = instance.getDBInstanceStatus();
Endpoint endpoint = instance.getEndpoint();
String endpoint_url = "Endpoint URL not available yet.";
if (endpoint != null)
{
endpoint_url = endpoint.toString();
}
// Do some printing work
System.out.println(identifier + "\t" + status);
System.out.println(endpoint_url);
} catch (Exception e)
{
// Simple exception handling by printing out error message and stack trace
System.out.println(e.getMessage());
e.printStackTrace();
}
}
Once you are able to make the above-mentioned code segments working, you should be able to use the AWS SDK for Java to work with the RDS service.
(5) JDBC Basics
With Java, people interact with database using JDBC (Java Database Connectivity). This is done in a 4-step approach:
- loading the JDBC driver using a class loader
- establishing a connection using DriverManager
- working with the database
- close the connection
The JDBC drivers for MySQL, PostgreSQL, Oracle and SQL Server can be found from the following URL. You will need to put the corresponding JAR file into your CLASSPATH to make things work.
Once you downloaded the JDBC drivers, you need to make them available in your CLASSPATH. This is discussed in your [AWS EC2 101]] tutorial.
With JDBC, we connect to database using connection URL, which includes properties such as the hostname or IP address of the database server, the port number to use for the connection, the name of the database to work with, as well as username and password. For different database engines, the format of the connection URL is slightly different. The following pseudo-code provides example connection URLs for MySQL, PostgreSQL, Oracle and SQL Server. If you need a definitive guidance on constructing connection URL for a specific database engine, please refer to the following URL:
- JDBC Connection URL for MySQL
- JDBC Connection URL for PostgreSQL
- JDBC Connection URL for Oracle
- JDBC Connection URL for SQL Server
// MySQL
Class.forName("com.mysql.jdbc.Driver");
String jdbc_url = "jdbc:mysql://hostname/database?user=username&password=password";
Connection conn = DriverManager.getConnection(jdbc_url);
// PostgreSQL
Class.forName("org.postgresql.Driver");
String jdbc_url = "jdbc:postgresql://hostname/database?user=username&password=password&ssl=true"";
Connection conn = DriverManager.getConnection(jdbc_url);
// Oracle
Class.forName ("oracle.jdbc.OracleDriver");
String jdbc_url = "jdbc:oracle:thin:@hostname:1521:orcl";
Connection conn = DriverManager.getConnection(jdbc_url, "username", "password");
// SQL Server
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String jdbc_url = "jdbc:microsoft:sqlserver://hostname:1433;DatabaseName=database";
Connection conn = DriverManager.getConnection(jdbc_url, "username", "password");
The following demo code provides an example on using MySQL Connector/J to connect to an RDS instance, then carry out some operations such as CREATE TABLE, INSERT, and SELECT in an infinite loop. The properties of the database (including hostname, database, username, password) are provided in a property file db.properties in the top level folder of the demo code. When we run the demo code, we load these properties from an InputStream. This way we do not need to provide database credentials in the source code. (The benefit of doing this is that when your database credentials changes, you do not need to recompile your Java code. All you need to do is to update the properties in db.properties.)
In this demo code we catch Exception in two levels – the first level Exception might occur when loading the property file (file does not exist, incorrect format, or required entry missing) or loading the MySQL JDBC driver (the JAR file is not in CLASSPATH), while the second level Exception might occur within the infinite loop (can not open a connection to the database, can not CREATE TABLE or execute INSERT or SELECT queries). When the first level Exception occurs, there are errors in the resource level, so we can’t move forward at all. When the second level Exception occurs, there might be things that we can fix from within the RDS instance, so we simply print out the error messages and keep on trying using the infinite loop.
Below is the content of your db.properties configuration file:
db_hostname=dns-endpoint-of-rds-instance
db_username=username
db_password=password
db_database=database_name
Below is the method needed for your JDBC test:
public void runJdbcTests()
{
System.out.println("\n\nJDBC TESTS\n\n");
try
{
// Getting database properties from db.properties
Properties prop = new Properties();
InputStream input = new FileInputStream("db.properties");
prop.load(input);
String db_hostname = prop.getProperty("db_hostname");
String db_username = prop.getProperty("db_username");
String db_password = prop.getProperty("db_password");
String db_database = prop.getProperty("db_database");
// Load the MySQL JDBC driver
Class.forName("com.mysql.jdbc.Driver");
String jdbc_url = "jdbc:mysql://" + db_hostname + "/" + db_database + "?user=" + db_username + "&password=" + db_password;
// Run an infinite loop
Connection conn = null;
while (true)
{
try
{
// Create a connection using the JDBC driver
conn = DriverManager.getConnection(jdbc_url);
// Create the test table if not exists
Statement statement = conn.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS jdbc_test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, content VARCHAR(80))";
statement.executeUpdate(sql);
// Do some INSERT
PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO jdbc_test (content) VALUES (?)");
String content = "" + UUID.randomUUID();
preparedStatement.setString(1, content);
preparedStatement.executeUpdate();
System.out.println("INSERT: " + content);
// Do some SELECT
sql = "SELECT COUNT(*) as count FROM jdbc_test";
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next())
{
int count = resultSet.getInt("count");
System.out.println("Total Records: " + count);
}
// Close the connection
conn.close();
// Sleep for some time
Thread.sleep(20000);
} catch (Exception e1)
{
System.out.println(e1.getMessage());
e1.printStackTrace();
}
}
} catch (Exception e0)
{
System.out.println(e0.getMessage());
e0.printStackTrace();
}
}
After creating an RDS instance and update the properties in db.properties, you can run the JDBC tests using the following command. You can stop the execution of this demo using CTRL-C.
(6) TPC-C Benchmark
TPC-C MySQL is a well-accepted TPC-C benchmark tool developed by Percona Lab. Below is a set of step-by-step instructions on how to compile and run the benchmark.
#
# Install necessary software packages and compile TPCC-MySQL
#
sudo apt-get update
sudo apt-get install gcc make git mysql-server libmysqlclient-dev
git clone https://github.com/Percona-Lab/tpcc-mysql
cd tpcc-mysql/src
make
cd ..
#
# Create table and populate data
#
mysqladmin create tpcc1000 -u root -p
mysql -u root -p tpcc1000 < create_table.sql
./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "password" -w 1000
#
# Benchmark with 32 threads (-c32)
#
./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800 -p password
Launch a couple of EC2 instances with different instance types and run the TPC-C MySQL. Try to explain the benchmark results. (The benchmark needs approximately 200 GB disk space. It takes approximately 12 hours to load the data, and another 6 hours to run one test.)
(7) TPC-E Benchmark
TPC-E MySQL is a well-accepted TPC-E benchmark tool developed by Percona Lab. Below is a set of step-by-step instructions on how to compile and run the benchmark.
#
# Install necessary software packages and create MySQL database
#
sudo apt-get update
sudo apt install gcc g++ make git mysql-server libmysqlclient-dev unixodbc-dev
mysql -u root -p -e "CREATE DATABASE tpce"
mysql -u root -p -e "CREATE USER 'tpce'@'localhost' IDENTIFIED BY 'tpce'"
mysql -u root -p -e "GRANT ALL PRIVILEGES ON tpce.* TO 'tpce'@'localhost'"
Download and compile TPC-E:
git clone https://github.com/Percona-Lab/tpce-mysql
cd tpce-mysql
mkdir bin obj lib flat_out
cd prj
make
Generate test data and load the test data into database, with 100000 customers, load unit 1000, scale factor 500, and 30 initial trade days.
cd ~/tpce-mysql
./bin/EGenLoader -c 100000 -t 1000 -w 30
cd scripts/mysql
mysql -u root -p tpce < 1_create_table.sql
mysql -u root -p tpce < 2_load_data.sql
mysql -u root -p tpce < 3_create_index.sql
mysql -u root -p tpce < 4_create_fk.sql
mysql -u root -p tpce < 5_create_sequence.sql
Run the TPC-E tests:
cd ~/tpce-mysql
./bin/EGenSimpleTest -S localhost -D tpce -c 100000 -a 100000 -d 30 -r 10 -t 3000 -u 12
Launch a couple of EC2 instances with different instance types and run the TPC-E MySQL. Try to explain the benchmark results. (The benchmark needs approximately 500 GB disk space. It takes approximately 12 hours to load the data, and another 6 hours to run one test.)
(8) How to crash MySQL
In this step, we try to crash the MySQL service by intentionally causing an out-of-memory error. In order to do this, we will use the sysbench utility to create a big table with 20,000,000 records. The total size of the data is approximately 6.5 GB.
Create an m3.large RDS MySQL instance with 2vCPU and 8 GB of memory. Connect to the RDS instance to create a big table.
sudo apt-get install sysbench
mysql -h [hostname] -u [username] -p
mysql> CREATE DATABASE dbtest;
mysql> quit
sysbench --test=oltp --oltp-table-size=20000000 --mysql-host=[hostname] --mysql-user=[username] --mysql-password=[password] --mysql-db=dbtest prepare
Run the following command to understand how MySQL performs sorting:
mysql -u[username] -p[password] -h[hostname] -e 'SELECT * FROM dbtest.sbtest ORDER BY c DESC LIMIT 100'
mysql -u[username] -p[password] -h[hostname] -e 'SELECT * FROM dbtest.sbtest ORDER BY c DESC' > /dev/null
Create a parameter group for MySQL and set sort_buffer_size to 5368709120. Modify the RDS MySQL instance to use the new parameter group. Reboot the RDS MySQL instance for the new settings to take effect.
Now we attempt to run 50 queries in parallel, each query performs a full sort of 20,000,000 records in the table. For each sorting, MySQL will attempt to use a 5 GB sort buffer (sort_buffer_size). 50 x 5 GB requires 250 GB memory, causing an out-of-memory error.
for i in {1..50}; do (mysql -u[username] -p[password] -h[hostname] -e 'SELECT * FROM dbtest.sbtest ORDER BY c DESC' >/dev/null &); done
When you run this you should be able to see the following output, indicating a crash on the MySQL server side..
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
If you look into the RDS console, under "Recent Events" you should see the restart of the MySQL server process.
March 16, 2018 at 9:26:28 AM UTC+11 sysbench Database could not be started due to incompatible parameters. It is recommended to tune your parameter to reduce memory consumption.
March 16, 2018 at 9:26:28 AM UTC+11 sysbench The database process was killed by the OS due to excessive memory consumption. It is recommended to tune your database workload and/or parameter usage to reduce memory consumption.
March 16, 2018 at 9:25:01 AM UTC+11 sysbench DB instance restarted
(9) Summary and Homework
In this session, we learn some basics about MySQL, how to launch an RDS MySQL instance, as well as how to use JDBC to to work with MySQL.
As you homework, create an RDS MySQL instance, then create a table with the following columns:
id - auto increment int
first name - varchar(50)
last name - varchar(50)
city - varchar(50)
state - varchar(50)
country - varchar(50)
Populate the table with 10 million records in the shortest time possible. Please measure the time needed to accomplish this job, as well as your runtime environment such as server (RDS MySQL) and client (Java application) configurations. You might want to refer to the following links for some ideas on first names and last names:
- https://github.com/enorvelle/NameDatabases
- http://stackoverflow.com/questions/18391799/database-or-list-of-english-first-and-last-names
Observe the CPU, disk I/O, memory consumption, and network traffic of your RDS instance. Are you hitting any of bottleneck during this process? If no, what changes you should make (on the client side) to achieve the better performance? If yes, what changes you should make (on the server side) to achieve better performance?
After you have 10 million records in your table, do a search for a particular first name or last name. How long does it take to complete the search (and how do you measure it)? Create indexes for the first name and last name columns. How long does it take to create the indexes (and how do you measure it)?
Use mysqldump to create a backup of your database and save a copy of the backup on S3. Observe the CPU, disk I/O, memory consumption, and network traffic during this process.
Use TRUNCATE TABLE to delete all records in your table. Let's pretend that you carry out this operation by mistake and now you need your data back. Let's see if you can do a Point-in-Time Restore to get your data back.
Another option in disaster recover is to import the data from a backup version (produced by mysqldump). Launch a new RDS MySQL instance and import the data from your dumped file. Observe the CPU, disk I/O, memory consumption, and network traffic during this process. Refer to Bulk Data Loading for InnoDB Tables to understand how to improve the performance of you data loading process. Do the tests necessary to show how you restore your database in the shortest time possible (with data to support your arguments).
Trouble Shooting Exercise
-
MySQL server keeps on failing.
-
You (or your application) can't connect to your remote database server.
-
You are importing a large amount of data into your database server. The import is very slow. What might be the bottleneck and how can you speed up?