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

0

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

profile picture
m0ltar
preguntada hace 9 meses515 visualizaciones
2 Respuestas
1
Respuesta aceptada

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

profile pictureAWS
EXPERTO
respondido hace 9 meses
profile picture
EXPERTO
revisado hace un mes
  • Thank you @Didier_Durand

1

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)

AWS
respondido hace 9 meses
  • @aws_abhi Love it, thanks. How does "blk_*_time" correlate with the IOPS?

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas