0

Normally when update table1 set field='any word' where primarykey=1234 take 1sec. or less but sometimes random take much more time possible 500sec. or more.

I was set very well and never see high CPU, or IOPS

I use MySQL RDS 8.0.17 and workaround was down wait_time from 3600(default) to 200 to close sleep connections and then working better.

7
  • 1
    Looks like your "sleep connections" are not committing their transactions and thus keeping locks. Revise your application logic. – mustaccio May 26 '20 at 1:48
  • Could you post TEXT results of A) SHOW GLOBAL VARIABLES LIKE '%connect%'; and B) SELECT * FROM information_schema.global_status WHERE variable_name LIKE '%connect%' OR variable_name = 'uptime' ORDER BY variable_name ; ? Thank you. – Wilson Hauck May 26 '20 at 17:35
  • character_set_connection utf8mb4 collation_connection utf8mb4_0900_ai_ci connect_timeout 10 disconnect_on_expired_password ON init_connect max_connect_errors 100 max_connections 10606 max_user_connections 0 mysqlx_connect_timeout 30 mysqlx_max_connections 100 performance_schema_session_connect_attrs_size 512 – jigorocano May 26 '20 at 20:02
  • You are missing 'max_used_connections' and 'threads_connected' in your posted data. It is unlikely your max_connections of 10606 requested in your configuration is reasonable. – Wilson Hauck May 26 '20 at 23:13
  • 1
    Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; for server workload tuning analysis to provide suggestions. – Wilson Hauck May 26 '20 at 23:24
0

Finally was the auto commit as mustaccio told me in a comment:

Looks like your "sleep connections" are not committing their transactions and thus keeping locks. Revise your application logic.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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