How to monitor for MySQL select queries that return too many records? [closed]

Question

I realize there are simple ways to limit results in SQL. I have a legacy implementation. I would like to get a handle on select queries that return results exceeding some user defined limit. The goal would be to have better insights to the kind of queries sent to the database. Some of these queries can translate into performance issues in the code that processes the results and/or excessive heap consumption. To gain insights, I'd like to monitor a production databases with a log entry for any query exceeding a threshold. Configuring an alarm/alert would even be better. The solution needs to be one that can be deployed to production. I'm not looking for a QA or Performance tool solution.

Answer 1

SUGGESTION #1 : STATUS VARIABLES

You could check the status variable

SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';

or read it from the performance_schema

SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_rows_read';

You could see other row metrics as well:

mysql> select version(); show global status like 'InnoDB_rows%';
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 0     |
| Innodb_rows_read     | 8     |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec)

mysql>

SUGGESTION #2 : SLOW LOG

If you need to see the number of rows returned or examined by any given query, you will need to enable the slow query log.

Here is an example of a slow log entry that sees a mysqldump in progress

# Time: 150419  6:00:43
# User@Host: web[web] @ localhost []
# Query_time: 7.730519  Lock_time: 0.000070 Rows_sent: 167620  Rows_examined: 167620
SET timestamp=1429416043;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;

As shown, the number of rows returned and examined are in the header.