Are you concerned about the impactof collection overhead on the queries diagnosed? Maybe not light, but I'm optimistic. Oracle has the track record of long use, and MySQL provides performance schema starting from 5.6.
partially, I afraid about total performance (about impact on IO) - when we use a usual tables, then any analyses without indexes are slow, so you need a indexes, and we cannot deferred index update. You should thinking about retention policy - and without partitioning you got massive deletes. So I cannot to imagine a usage of table based solution together with some higher load. Our MVCC storage is not practical for storing only inserted data, and some custom storage has no indexes - so this design is relative big project.
I have just terrible negative experience with Vertica, where this design is used - almost all information about queries are available, but any query to related tables are terrible slow, so I am inclined to more simple design oriented to log based solution. Table based solutions is not practical when you exec billions queries per day. I understand to motivation, but I afraid so it can be very expensive and slow on highly load servers.
Which do you mean by "query related to tables", the queries from applications being diagnosed, or the queries that diagnose the performance using statistics views?
Could you elaborate on your experience with Vertica? That trouble may be just because Vertica's implementation is not refined.
sure - Vertica is not mature database. More it has only one storage type optimized for OLAP, what is wrong for long catalog, and for working with performance events too.
I understand the feeling of being inclined to log based solution for its implementation simplicity. However, the server log is difficult or impossible to access using SQL queries. This prevents the development of performance diagnostics functionality in GUI administration tools. Also, statistics views allow for easy access on PAAS like Amazon RDS and Heroku.
I prefer a possibility to read log via SQL (maybe some FDW) than use tables for storing log. These tables can be relative very large in few days - and we cannot to write specialized engine like MySQL simply.
