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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s