Our system was down recently as the database had exceeded the max_connection. We are using AWS RDS - Aurora MySQL.
Checking Performance Insights, we noticed that a query is the main factor. The query itself is totally fine, however, wait/synch/sxlock/innodb/fil_space_latch take 99% time consuming.
So I would like to understand what the metric is, especially what fil_space_latch does
Thank in advance.
I raised this question to AWS support and got answer as below:
The "fil_space_latch" is a wait event caused by locks acquired on operation related to tablespace. It can be indication of increased workloads. If for example, a temporary table is created when a JOIN or SELECT query is executed with a large numbers of tables and records queried, I/O operation to the temporary table will occur and the increased of this wait event can be see.
Refer to: https://dev.mysql.com/doc/dev/mysql-server/latest/fil0fil_8h.html#aa4426c0c081c18110a1047501c05b1b3
This can be observed from the DB metric 'Created_tmp_tables' and 'Innodb_rows_inserted' relevant to the query waits at the time in the Performance Insights of the target instance.