Is there a way to track IOPS usage on per query basis in Aurora PostgreSQL?

Question

Is it possible to log queries and their IOPS consumption for cost optimization purposes?

Accepted Answer

Hi,

On Postgresql, the pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server.

See https://www.postgresql.org/docs/current/pgstatstatements.html

Then, as per https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.PostgreSQL.html#USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.PostgreSQL.per-call

```
To view SQL digest statistics, the pg_stat_statements library must be loaded. 
For Aurora PostgreSQL DB clusters that are compatible with PostgreSQL 10, 
this library is loaded by default. For Aurora PostgreSQL DB clusters that are 
compatible with PostgreSQL 9.6, you enable this library manually. To enable
 it manually, add pg_stat_statements to shared_preload_libraries in the DB 
parameter group associated with the DB instance.
```

Additionally, you should turn on track_io_timing on your Aurora instance: see section "Aurora PostgreSQL cluster-level parameters" of https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Reference.ParameterGroups.html

See https://www.postgresql.org/docs/current/runtime-config-statistics.html for details on this parameter.

Combining all the above will allow you to track the requests consuming most I/Os and to optimize them.

Best,

DIdier

Answer

To add on to above answer, you may use below query to get the list of top 10 SQL queries having high I/O activity:

```
SELECT userid::regrole, dbid, query
FROM pg_stat_statements 
ORDER BY blk_read_time + blk_write_time DESC
LIMIT 10;
```

**Note**:  Make sure that you have pg_stat_statements extension installed and have track_io_timing enabled (i.e. track_io_timing=1)

Is there a way to track IOPS usage on per query basis in Aurora PostgreSQL?

Conteúdo relevante