RDS CPU Utilization

(ctrl+l – clear ;-))

1. “show processlist” only gives you a snapshot of what’s executing precisely at the moment you run it. To catch expensive queries, you may need to run the command a few times in rapid succession.
2. But MySQL offers other ways to see what’s going on in your instance. You can enable “general_log”, “slow_query_log
“, and “log_queries_not_using_indexes” via parameter groups to analyze the activity over time. It is likely that you will find queries that are doing full table scans (and those usually consume more CPU, especially if the table in question is already in your buffer cache).

#select * from mysql.general_log;

Process of enabling Logs :

Both general log and slow query log are disabled when you first create an RDS MySQL instance. In order to enable logging you need to set “general_log” and “slow_query_log” server parameters to 1 using a parameter group.

Setting “general_log” to 1 causes the “mysql.general_log” table to start accumulating all activity on the database. Similarly, “slow_query_log” set to 1 will cause slow queries to be registered in the “mysql.slow_log” table. The threshold for considering a query to be slow is controlled by the “long_query_time” server variable (default = 10 seconds). You can change that as well using parameter groups.

Both tables “mysql.slow_log” and “mysql.general_log” will keep growing until the respective parameters are disabled (by changing “slow_query_log” and “general_log” parameters to zero again). You must use these parameters very carefully, as it’s not uncommon that a large amount of data gets accumulated over time, using up a considerable percentage of your allocated storage space.

In RDS you don’t have privileges to truncate those two tables, but you can easily rotate their contents by executing these two procedures that come pre-installed in your MySQL instance:
CALL mysql.rds_rotate_slow_log;
This procedure will create an empty mysql.slow_log table and will save all previous content to the mysql.slow_log_backup table. If you wish to completely remove the old data and reclaim the disk space, call the procedure once more.

CALL mysql.rds_rotate_general_log;
This procedure will create an empty mysql.general_log table and will save all previous content to the mysql.general_log_backup table. If you wish to completely remove the old data and reclaim the disk space, call the procedure once more.

Currently, logging to tables incurs significantly more server overhead than logging to files. If you enable the general log or slow query log and require highest performance, you should use file logging, not table logging.

If you create a DB parameter group, you need to add your DB Instance as a member. When you add a running DB instance to a DB parameter group, you must reboot the DB instance for the new DB parameter group settings to take effect.
Caution
Improperly setting parameters in a DB parameter group can have unintended adverse effects, including degraded performance and system instability. Always exercise caution when modifying database parameters and back up your data before modifying a DB parameter group. You should try out parameter group setting changes on a test DB instance before applying those parameter group changes to a production DB instance.

Steps :

1.Run this several times to capture expensive queries
#select * from mysql.general_log;

2.Go to DB’s parameter group and set the following option to 1
general_log = 1 for table – mysql.general_log
slow_query_log = 1 for table – mysql.slow_log
log_queries_not_using_indexes = 1 for table – mysql.slow_log
long_query_time = 10sec for slow query log

When both log_query_time and log_queries_not_using_indexes then both gets mixed up in slow_log table! It seems that once the log_queries_not_using_indexes is enabled, Mysql will log all the queries that not using indexes no matter what the “long_query_time” is And this is an Expected behaviour from MySQL.

TURN OFF “log_queries_not_using_indexes” if actual slow_query needs to be spotted.

3.Commands
mysql>user dbname;
mysql>select * from mysql.general_log;
mysql>select * from mysql.slow_log;

4.Save Disk Space caused by Log tables:
CALL mysql.rds_rotate_slow_log;
CALL mysql.rds_rotate_general_log;

Referene Article:
https://forums.aws.amazon.com/message.jspa?messageID=441869
http://dba.stackexchange.com/questions/27897/struggling-to-debug-high-cpu-usage-on-amazon-rds-mysql-instance
http://aws.amazon.com/articles/Amazon-RDS/2935
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html
https://forums.aws.amazon.com/thread.jspa?threadID=114506
Additional Ref
http://www.mysqlperformanceblog.com/2007/02/08/debugging-sleeping-connections-with-mysql/

 

Max Connections for each RDS Instance Type

t1.micro: 34 connections
m1.small: 150 connections
m1.large: 640 connections
m1.xlarge: 1263 connections
m2.xlarge: 1441 connections
m2.2xlarge: 2900 connections
m2.4xlarge: 5816 connections

 

RDS Scaling

 

 

If your app gets to the point that you need to start scaling either up or out, it is a good idea to switch to multi-AZ if you don’t run it already. If you have a simple RDS instance, you will degrade your service significantly while scaling, as you can expect to lose the ability to write and/or read. With multi-AZ RDS instances, your service is almost uninterrupted.

Scaling Up (or Down)

Scaling up is so easy it is almost ridiculous. The only drawback is that you have some downtime during the operation. If you don’t have multi-AZ enabled, the downtime of your RDS instance could be several minutes, as you have to wait until a new instance is launched and fully functional. For multi-AZ RDS instances, you will experience some downtime as a failover is initiated after the slave has been scaled up (or down). This failover doesn’t take more than a minute most of the time.

If you initiate a scaling activity via the Console, make sure you enable Apply Immediately if you are in a hurry. If you don’t, scaling will take place during the scheduled maintenance period

Scaling using the command-line tools is a two-step process. First scale, and then reboot:

$ rds-modify-db-instance production –db-instance-class db.m1.xlarge –apply-immediately
$ rds-reboot-db-instance production

Scaling Out

You can scale out a relational database in two different ways:

* Using read-only slaves (read replicas in AWS)
* Sharding or partitioning

There are still some hard problems to solve, as sharding/partitioning has not been addressed yet with RDS. Master-slave type scaling is available, though. A slave, or read replica, is easily created from the Console. The only requirement on the master RDS instance is that backups are not disabled by setting the backup retention period to 0. Currently, you can have up to five read replicas that you have to launch one by one. Amazon is working on the ability to launch multiple replicas at once, but that is not yet available.

On a multi-AZ RDS instance, launching a read replica goes unnoticed. A snapshot is taken from the standby, the replica is launched, and when it is ready, it starts to catch up with the master. For a normal RDS instance, there is a brief I/O suspension in the order of one minute. AWS advises to use the same instance classes, as differing classes may incur replica lag. With read replicas, you basically introduce eventual consistency in your database (cluster).

RDS Sharding & Quick Commands

 

 

Source article on RDS sharding by Amazon.

What is sharding?

Here unlike ec2 instances there is no rds-db-instance-id instead we use the db-instance names (called as dbinstance_identifiers) in our rds commmands to identify among rds-db-instances.

Example-
Database Name – news
RDS DB Instances – shard1, shard2

Consider creating 2 db-shards ie we need 2 db-instance to map those db-shards to db-instances. To reduce the schema setup overhead we use the snapshot and restore capabilities of Amazon RDS to do the following:

1. Create a single “seed” DB Instance.
2. Set up the schema on that database.
3. Snapshot the database.
4. Create more databases from that snapshot using the RDS RestoreDBInstanceFromSnapshot API.

1.Create SEED DATABASE INSTANCE

#rds-create-db-instance shard1 –engine mysql5.1 –master-username unni –master-user-password 123 –allocated-storage 5 –db-instance-class db.t1.micro –db-name news –region eu-west-1 -K ec2_certs/pk-K4.pem -C ec2_certs/cert-K4.pem

$$output$$
DBINSTANCE  shard1  db.t1.micro  mysql  5  unni  creating  1  ****  n  5.1.63  general-public-license
SECGROUP  default  active
PARAMGRP  default.mysql5.1  in-sync
OPTIONGROUP  default:mysql-5-1  in-sync

Add IP address to DB Security Group to gain access
1.Find IP from http://www.whatismyipaddress.com – say its – 122.174.199.204
2.Add the CIDR – 122.174.199.204/32 in the DB-Security Group
3.Try gaining access to rds by – #mysql -h shard1.covaztmm6tup.eu-west-1.rds.amazonaws.com -u unni -p123

2.SETUP SCHEMA ON THE DB

mysql>show database;
mysql>use news;
mysql>CREATE TABLE articles (
article_id numeric(64,0) NOT NULL PRIMARY KEY,
category char(13),
CHECK category IN (‘BUSINESS’, ‘ENTERTAINMENT’, ‘HEALTH’, ‘SCIENCE’, ‘SPORTS’, ‘TECHNOLOGY’, ‘WORLD’),
title char(128),
submit_time timestamp
);

3.SNAPSHOT and CREATE OTHER DB INSTANCES

#rds-create-db-snapshot shard1 –db-snapshot-identifier news-seed-database –region eu-west-1 -K ec2_certs/pk-K4.pem -C ec2_certs/cert-K4.pem

$$output$$
DBSNAPSHOT  news-seed-database  shard1  2012-09-04T12:02:30.129Z  mysql  5  creating  unni  5.1.63  general-public-license  manual

Once the snapshot is available it can be used to create any number of DB Instances. We’re only creating two DB Instances in this example, but it could easily be many more. Also, for our trivial setup, the only efficiency we gain is that we don’t need to create the schema on each. However, in a typical scenario there might be a lot more setup involved in creating the seed (e.g., application configuration data, user accounts, permissions, etc.).

4.DB-INSTANCE FROM DB-SNAPSHOT

The following command can be used to create new DB Instances from the seed snapshot:

#rds-restore-db-instance-from-db-snapshot shard2 –db-snapshot-identifier news-seed-database –db-instance-class db.t1.micro –region eu-west-1 -K ec2_certs/pk-K4.pem -C ec2_certs/cert-K4.pem

$$output$$
DBINSTANCE  shard2  db.t1.micro  mysql  5  unni  creating  1  n  5.1.63  general-public-license
SECGROUP  default  active
PARAMGRP  default.mysql5.1  in-sync
OPTIONGROUP  default:mysql-5-1  pending

—————–

To LIST RDS-INSTANCES
#rds-describe-db-instances –region eu-west-1 -K ec2_certs/pk-K4.pem -C ec2_certs/cert-K4.pem

TO DELETE RDS-INSTANCE with FINAL SNAPSHOT
#rds-delete-db-instance shard1 –region eu-west-1 -K ec2_certs/pk-K4.pem -C ec2_certs/cert-K4.pem –final-db-snapshot-identifier shard-final-snapshot

TO DELETE RDS-INSTANCE without FINAL SNAPSHOT
#rds-delete-db-instance shard2 –region eu-west-1 -K ec2_certs/pk-K4.pem -C ec2_certs/cert-K4.pem –skip-final-snapshot

TO LIST RDS-DB-SNAPSHOTS
#rds-describe-db-snapshots –region eu-west-1 -K ec2_certs/pk-K4.pem -C ec2_certs/cert-K4.pem

TO DELETE RDS-DB-SNAPSHOTS
#rds-delete-db-snapshot shard-final-snapshot –region eu-west-1 -K ec2_certs/pk-K4.pem -C ec2_certs/cert-K4.pem

NOTE
Creating a final snapshot (before dbinstance termination) incurs additional storage fees.

RDS CLI Tools Setup

 

 

#cd /usr/local/rds
#chmod 744 bin/*

Setup Credentials into the file to use a default AWS Account —
$ cd /usr/local/rds
$ sudo cp credential-file-path.template credential-file
$ sudo vi credential-file
$ sudo chmod 600 /usr/local/aws/rds/credential-file

Now add to the ~/.bashrc
# Set location of the ec2 and rds command line tools
export EC2_HOME=/usr
export AWS_RDS_HOME=/usr/local/aws/rds
# Set AWS path
export PATH=$PATH:$EC2_HOME/bin:$AWS_RDS_HOME/bin

Source the .bashrc file —
$ source .bashrc

Reference Link 1