(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:
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.
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.
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.
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.
mysql>select * from mysql.general_log;
mysql>select * from mysql.slow_log;
4.Save Disk Space caused by Log tables:
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