0

Mysql: 5.7 on AWS RDS

On our production server some queries are running so long.

show processlist output

Here this select was running from 1278013 seconds. We have given the production read access to multiple users and even to the customers and write access to few so might be the case they are executing query through PMA and they are not able to see the result in PMA, but the query is sending data in the background.

What we can do to kill such long running queries.

Solution I suggested:

  1. set max_execution_time to 1 or 2 hours. Currently it is 0.

Problem with the solution:

  1. We are not aware the longest running time for query in entire system
  2. There will be a problem for data correction scripts, which are taking longer than usual, if we set limited time.

Requesting you to please suggest how we can get rid off this in production, as this caused a serious problem of metadata lock in one of the process.

Rdba
  • 3
  • 3
  • The most obvious solution is not to allow users directly executing queries on your production database. If your users need a to run reports, then have a read-only replica they can use for this purpose. – Shadow May 24 '23 at 11:14
  • We are using AWS RDS, which has reader node and writer node already. The problem is: Some users require write permissions, and we then cannot stop them from just using the writer node. As AWS has two nodes, the reader and the writer. It may be the case that they "switch" and the writer becomes the reader and vice versa, during a maintenance window or a scaling operation. Therefore if a very long running query is running, it will appear on the "writer" as they switched. – Rdba May 24 '23 at 11:58
  • Obviously I'm not familiar with your system, but having users with direct write access to production database is dangerous and should not really be used. I would set up a dedicated, always read-only replica (kind of a reporting database) and would do away with direct write access. – Shadow May 24 '23 at 12:47

0 Answers0