如何在AWS EC2与RDS中提取每秒事务数（TPS）

Hey there! Stack Overflow is absolutely the right spot to ask this question—no need to worry about the channel. Let’s break down how to grab those TPS (or equivalent) metrics for both your AWS resources:

You absolutely can view these directly in the AWS Console, which is the easiest starting point:

• Head to the RDS service in the AWS Console, locate your PostgreSQL instance
• Switch to the Monitoring tab—here you’ll find ready-to-use metrics:
  • Commit Throughput: This tracks the number of committed transactions per second, which is the core TPS metric for PostgreSQL
  • Rollback Throughput: Counts rollbacks per second; add this to Commit Throughput if you need total transaction volume
• Adjust the time range to your specific window, and you can even export the data as CSV or set up CloudWatch alarms for thresholds
• For more granular or custom analysis, enable PostgreSQL’s performance logs (in your RDS instance’s configuration) and use CloudWatch Insights to query them. Example query to calculate TPS over a period:

  SELECT 
    date_trunc('second', timestamp) as time,
    COUNT(*) FILTER (WHERE message LIKE '%COMMIT%') as commits_per_sec,
    COUNT(*) FILTER (WHERE message LIKE '%ROLLBACK%') as rollbacks_per_sec
  FROM cloudwatch_logs
  WHERE log_group_name = '/aws/rds/instance/your-instance-name/performance'
    AND timestamp BETWEEN 'YYYY-MM-DD HH:MM:SS' AND 'YYYY-MM-DD HH:MM:SS'
  GROUP BY time
  ORDER BY time;
  

This depends on what’s running on your EC2 instance—here are the common scenarios:

If your EC2 instance is running PostgreSQL: #

• Use PostgreSQL’s built-in stats to calculate TPS directly on the server:
  1. Connect to your database via psql, then run this query to get real-time TPS:

     SELECT 
       now()::timestamp(0) as timestamp,
       (xact_commit - lag(xact_commit) OVER (ORDER BY now())) as commits_per_sec,
       (xact_rollback - lag(xact_rollback) OVER (ORDER BY now())) as rollbacks_per_sec
     FROM pg_stat_database
     LIMIT 1;
     

  2. For continuous monitoring, wrap it in watch:

     watch -n 1 "psql -U your-db-user -d your-db-name -c 'SELECT (xact_commit - lag(xact_commit) OVER (ORDER BY now())) as tps FROM pg_stat_database LIMIT 1'"
     

  3. You can also parse PostgreSQL logs to count transactions. Example bash command (adjust the log path to match your setup):

     grep -E 'COMMIT|ROLLBACK' /var/log/postgresql/postgresql-14-main.log | awk '{print $1, $2}' | sort | uniq -c | awk '{print $3, $4, $1}'
     

If your EC2 instance is an application server (e.g., web app): #

• Calculate equivalent transaction metrics (like requests per second) using your app’s logs or monitoring tools:
  1. For Nginx/Apache, parse access logs to count requests per second:

     awk '{print $4}' /var/log/nginx/access.log | cut -d: -f1-2 | sort | uniq -c | awk '{print $2, $1}'
     

  2. Install the CloudWatch Agent on Ubuntu to send custom app metrics to AWS CloudWatch for centralized viewing. Run these commands to set it up:

     sudo apt-get update && sudo apt-get install amazon-cloudwatch-agent -y
     sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-config-wizard
     

     Follow the wizard to configure metrics specific to your application (e.g., request counts, response times).

Hope this gives you all the tools you need to get those metrics! Feel free to ask follow-ups if you hit any snags.

内容的提问来源于stack exchange，提问作者duduwe