0

I usually received requests from client that DB works slow. And, it is important for me to understand what should I do first to understand a problem with MySQL server?

I have found for myself a list of commands but maybe someone can add more useful command:

  1. Important to see state and time column

    SHOW FULL PROCESSLIST;
    
  2. https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks https://dev.mysql.com/doc/refman/8.0/en/show-engine.html

    SHOW ENGINE INNODB STATUS;
    
  3. List of active transactions

    SELECT * FROM information_schema.innodb_trx;
    
  4. List of active locks

    SELECT * from performance_schema.data_locks;
    

The goal of my questions is to understand other useful 'magic' commands for MySQL that can help to understand different issues in production.

0

1 Answer 1

1

None of the above. First your 1-4, then my own opinions...

  1. PROCESSLIST shows you what is running. If the same query is running in many connections, you should try to speed up that query.

  2. The main thing I get from SHOW ENGINE INNODB STATUS; is the "most recent deadlock". But not all deadlocks are preventable. But it does show you two queries to study. Speeding up the queries is likely to decrease the frequency of deadlocks.

  3. I'm not a masochist.

  4. Ditto.

  5. I prefer to turn on the slowlog with a low value for long_query_time. Later I analyze it using pt-query-digest. More: SlowLog . This is how I address sluggishness, regardless of where it comes from (bad response time, user gripes, inadequate indexes, poorly written queries, etc). I often find that fixing the one or two "worst" queries in the slowlog is all that it takes to make the system feel faster.

  6. "If it ain't broke, don't fix it." That is, let your users (including yourself) tell you that "the system is too slow". Then the slowlog can help you find sluggish parts of the program. Or even help you preemptively locate sluggish parts of the program.

  7. When you find a sluggish query, post it, plus SHOW CREATE TABLE on stackoverflow.com .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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