MySQL(2)- performance monitor

Currently, I use MySQL as Database to store data. The whole architecture is build under Play framework. But I meet the problem, like MySQL connection timeout, etc. So MySQL performance becomes more serious important. Here I list some commands to monitor MySQL performance. I don’t like to install additional tools to do it. In fact, MysqlAdmin already can do a lot of things.

1. MysqlAdmin

  • check MySQL server status
    mysqladmin -u root -p ping
  • check MySQL version
    mysqladmin -u root -p version
  • find out the current status of MySQL server?
    mysqladmin -u root -p status
    # Output:
    Uptime: 269161  Threads: 11  Questions: 36607  Slow queries: 0  Opens: 96  Flush tables: 1  Open tables: 70  Queries per second avg: 0.136
  • check status of all MySQL server variable’s and value’s?
    mysqladmin -u root -p extended-status
  • see all MySQL server variables and values
    mysqladmin -u root -p variables
  • check all the running process of MySQL server
    mysqladmin -u root -p processlist
  • some useful MySQL flush commands
    mysqladmin -u root -p flush-hosts 
    mysqladmin -u root -p flush-tables 
    mysqladmin -u root -p flush-threads 
    mysqladmin -u root -p flush-logs 
    mysqladmin -u root -p flush-privileges 
    mysqladmin -u root -p flush-status
  • kill sleeping MySQL client process
    mysqladmin -u root -p processlist
    mysqladmin -u root -p kill #id,#id

2. Within MySQL

  • find slow query

    • show configuration/variables first
      show variables like "%slow%";

      “slow_query_log” and “slow_query_log_file” are important.
      screenshot-2016-11-02-14-10-57

       show variables like 'long_query_time';

      Screenshot 2016-11-02 14.34.46.png

    • set long_query_time and turn on slow_query_log
      set long_query_time=1;
      set global slow_query_log='ON';
      
    • view log file
      cat /var/lib/mysql/<file_name>.log | more
    • show slow queries
      show global status like "%slow%";

      Screenshot 2016-11-02 14.11.34.png

  • check read/write collision or table lock check

    • table status
      show status like 'Table%';

      Table_locks_immediate shows the table-level lock number and these locks are immediately released. Table_locks_waited shows the table-level lock number which needs wait.
      If Table_locks_immediate/Table_locks_waited > 5000, this means you’d better to adopt InnoDB engine. Because InnoDB uses row-level lock, but MyISAM uses table-level lock. For high concurrent write application, InnoDB would be better.
      If Table_looks_waited is too high, this means it exists worse table-level lock contention situation.
      Screenshot 2016-11-02 14.12.27.png

  • check key lock which is related to index buffer usage status

    • show key buffer size
      show variables like "key_buffer_size";
      

      Screenshot 2016-11-02 14.13.24.png

    • show hit ratio
      show global status like "key_blocks_u%";
      

      Note: If “Key_blocks_unused” is too high, buffer size should be decreased.
      Screenshot 2016-11-02 14.13.51.png

    • modify key buffer size
      set global key_buffer_size=4194304;
  • find full table scan statement

    select * from sys.statements_with_full_table_scans;

    Screenshot 2016-11-02 14.15.01.png

  • others
    show [session|global] status like ...

    The difference betwee seesion and global is that session is only for current conversation, global is for the whole running time.

    # show all mysql operating info
    show status;
    # show insert times
    show status like "com_insert%";
    # show delete times
    show status like "com_delete%";
    # show query times
    show status like "com_select%";
    # show mysql service running time
    show status like "uptime";
    # show myslq connect times
    show status like "connections";

3. MySQL Health Check

http://www.fromdual.com/mysql-database-health-check is an online website which helps you to check MySQL status.

Screenshot 2016-11-02 14.15.36.png

4. Related Links which deserve to read

Advertisements

One thought on “MySQL(2)- performance monitor

  1. Pingback: Scala – Performance Optimization | Play Harder, Work Harder

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s