0

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.

10
  • 2
    is slow query log sufficent for your needs? Set long_query_time to the threshold. – danblack Feb 6 at 21:09
  • 1
    sys schema or the rawer version of performance schema might provide you some insights you need. – danblack Feb 6 at 21:27
  • 2
    ...there's no generally proactive way to limit the number of rows returned from an individual query by a certain threshold until after all the work has already been done to process & locate those records, unless you just do a blanket limit on all queries or review logs of past slow queries & put a blanket threshold on those individual type of queries. But again you may end up limiting a performant query that was previously slow due to other server contention not due to the # of rows. Thirdly, it won't be easy to ensure data accuracy by limiting random rows, unless you have a common sort field. – J.D. Feb 6 at 21:34
  • 1
    @Lifehouse Sorry, it just sounded like that was your end goal when you said "I realize there are simple ways to limit results in SQL". Without more context I don't fully follow how the JVM is at play here, but I assume you mean a Java application that is receiving the results of these queries in some capacity, and that is where your issue is occuring? If so, that sounds like something that should be corrected in the application layer as opposed the database later. If you have access to the application, it should be analyzed for potentially problematic queries, and logging & error handling... – J.D. Feb 7 at 13:52
  • 1
    ...should be occuring inside there, to catch any data overload. I did see you mentioned hosting, so not sure if you mean that you guys host JVM applications and their correlating databases for multiple users, and so your internal monitoring on an individual application is limited then. If so, even then I'd imagine you should have monitoring tools on each hosted JVM & they shouldn't step on each other. If within a single individual instance, a users application goes down because of their poor design, then the onus should be on them not you. But this is all speculation without more context. – J.D. Feb 7 at 13:55
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.

1
  • These are excellent recommendations for post mortem analysis. How would you recommend to continually monitor the slow log? – Litehouse Feb 13 at 13:28

Not the answer you're looking for? Browse other questions tagged or ask your own question.