CLI for Amazon RDS Performance Insights
Installing CLI on LINUX
1. install PIP
https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-linux.html#awscli-install-linux-pip
curl -O https://bootstrap.pypa.io/get-pip.py
python get-pip.py --user
2. install AWS CLI
https://docs.aws.amazon.com/cli/latest/userguide/installing.html
pip install awscli --upgrade --user
3. configure
aws configure
For “aws configure” you will need
- AWS Access Key ID:
- AWS Secret Access Key:
Which you can get by going to the AWS console, going to IMS and creating access key.
Running example
Once “aws” is configured you can run the CLI like
aws \ pi get-resource-metrics \ --region us-east-1 \ --service-type RDS \ --identifier db-xxxxxx \ --metric-queries "{\"Metric\": \"db.load.avg\"}" \ --start-time `expr \`date +%s\` - 60480000 ` \ --end-time `date +%s` \ --period-in-seconds 86400
That “—identifier” is for one of my databases, so you will have to change that.
You will also have to modify region if you are accessing a database in a different region
getting json output
export AWS_DEFAULT_OUTPUT="json"
documentation
API
- GetResourceMetrics
- DescribeDimensionKeys
CLI
- get-resource-metrics
- describe-dimension-keys
examples
My databases
- db-YTDU5J5V66X7CXSCVDFD2V3SZM ( Aurora PostgreSQL)
- db-2XQCJDBHGIXKDYUVVOIUIJ34LU ( Aurora MySQL)
- db-Z2PNRYPV4J7LJLGDOKMISTWRQU (RDS MySQL)
see these blogs for loads on these databases
- Blog Aurora Postgres https://aws.amazon.com/blogs/database/analyzing-amazon-rds-database-workload-with-performance-insights
- Blog Aurora MySQL https://aws.amazon.com/blogs/database/analyze-amazon-aurora-mysql-workloads-with-performance-insights/
- Blog RDS MySQL https://aws.amazon.com/blogs/database/tuning-amazon-rds-for-mysql-with-performance-insights/
CPU load last 5 minutes
aws \ pi get-resource-metrics \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --metric-queries '{ "Metric": "db.load.avg", "Filter":{"db.wait_event.type": "CPU"} } ' \ --end-time `date +%s` \ --period-in-seconds 300
Top SQL by load
aws pi describe-dimension-keys \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --end-time `date +%s` \ --metric db.load.avg \ --group-by '{"Group":"db.sql"}'
Top Waits by load
aws pi describe-dimension-keys \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --end-time `date +%s` \ --metric db.load.avg \ --group-by '{"Group":"db.wait_event"}'
.
Top User by load
aws pi describe-dimension-keys \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --end-time `date +%s` \ --metric db.load.avg \ --group-by '{"Group":"db.user"}'
.
"Total": 0.15100671140939598, "Dimensions": { "db.sql.db_id": "pi-4101593903", "db.sql.id": "209554B4D97DBF72871AE0854DAD97385D553BAA", "db.sql.tokenized_id": "1F61DDE1D315BB8F4BF198DB219D4180BC1CFE05", "db.sql.statement": "WITH cte AS (\n SELECT id \n FROM authors \n LIMIT 1 \n )\nUPDATE authors s\nSET email = 'toto' \nFROM cte\nWHERE s.id = cte.id\n\n" }
Top SQL by waits grouped
aws pi describe-dimension-keys \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --end-time `date +%s` \ --metric db.load.avg \ --group-by '{"Group":"db.sql"}' \ --partition-by '{"Group": "db.wait_event"}'
.
{ "Total": 0.1644295302013423, "Dimensions": { "db.sql.db_id": "pi-4101593903", "db.sql.id": "209554B4D97DBF72871AE0854DAD97385D553BAA", "db.sql.tokenized_id": "1F61DDE1D315BB8F4BF198DB219D4180BC1CFE05", "db.sql.statement": "WITH cte AS (\n SELECT id \n FROM authors \n LIMIT 1 \n )\nUPDATE authors s\nSET email = 'toto' \nFROM cte\nWHERE s.id = cte.id\n\n" }, "Partitions": [ 0.003355704697986577, 0.14093959731543623, 0.020134228187919462 ] }, "PartitionKeys": [ { "Dimensions": { "db.wait_event.type": "CPU", "db.wait_event.name": "CPU" } }, { "Dimensions": { "db.wait_event.type": "IO", "db.wait_event.name": "IO:XactSync" } }, { "Dimensions": { "db.wait_event.type": "Lock", "db.wait_event.name": "Lock:transactionid" } }
Top SQL over last 5 minutes based on CPU
aws pi describe-dimension-keys \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --end-time `date +%s` \ --metric db.load.avg \ --group-by '{"Group":"db.sql"}' \ --filter '{"db.wait_event.type": "CPU"}'
{ "Total": 0.003355704697986577, "Dimensions": { "db.sql.db_id": "pi-4101593903", "db.sql.id": "209554B4D97DBF72871AE0854DAD97385D553BAA", "db.sql.tokenized_id": "1F61DDE1D315BB8F4BF198DB219D4180BC1CFE05", "db.sql.statement": "WITH cte AS (\n SELECT id \n FROM authors \n LIMIT 1 \n )\nUPDATE authors s\nSET email = 'toto' \nFROM cte\nWHERE s.id = cte.id\n\n" }
load over last 5 minutes based on CPU
aws \ pi get-resource-metrics \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --metric-queries '{ "Metric": "db.load.avg", "Filter":{"db.wait_event.type": "CPU"} } ' \ --end-time `date +%s` \ --period-in-seconds 300
…
Top SQL over last 5 minutes based on CPU
aws pi describe-dimension-keys \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --end-time `date +%s` \ --metric db.load.avg \ --group-by '{"Group":"db.sql"}' \ --filter '{"db.wait_event.type": "CPU"}'
alternatively with a partition by waits
aws pi describe-dimension-keys \ --region us-east-1 \ --service-type RDS \ --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \ --start-time `expr \`date +%s\` - 300 ` \ --end-time `date +%s` \ --metric db.load.avg \ --group-by '{"Group":"db.sql"}' \ --partition-by '{"Group": "db.wait_event"}' \ --filter '{"db.wait_event.type": "CPU"}'
CLI with counter metrics
aws \ pi get-resource-metrics \ --region us-east-1 \ --service-type RDS \ --identifier db-VMM7GRZMTGWZNPWAJOLWTHQDDE \ --metric-queries "{\"Metric\": \"db.Transactions.xact_commit.avg\"}" \ --start-time `expr \`date +%s\` - 3600 ` \ --end-time `date +%s` \ --period-in-seconds 60 \ --endpoint-url https://api.integ.pi.a2z.com
Recent Comments