Enabling MySQL slow query logs

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Fisheye および Crucible は除く

シナリオ

During performance troubleshooting, it can be helpful to know which queries are taking a long time to return a result. MySQL provides a way to log slow running queries that meet certain criteria. This option is disabled by default.

To avoid false positives, you should set the threshold for time to a value high enough that you won't capture query sent to the database, but just the long running ones. The default of 0.5 seconds should be enough to begin, however you may need to increment this upwards if you find too much noise in your slow query log file.

As this procedure requires a restart of MySQL (and thus a restart of your applications) you may wish to schedule some downtime for this to occur with minimal disruption to your user base.

Where is the MySQL Configuration file located?

The location of the MySQL configuration file (either my.cnf or my.ini depends on your operating system). MySQL will look at each location in order, and use the first file that it comes across. Often, if the file does not exist, it must be created first.

INSTALLDIR refers to the location that MySQL was installed to.

Linux based systems

ここをクリックして展開...

Linux based MySQL systems will use configuration files in the following order of precedence

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • SYSCONFDIR/my.cnf
  • $MYSQL_HOME/my.cnf

SYSCONFDIR refers to the directory specified when MySQL was built; and typically reverse to the etc directory located under the compiled-in installation directory.

MYSQL_HOME is an environment variable referring to the path where my.cnf can be found.

Regarding MySQL Safe Mode

When MySQL is started using mysqld_safe, MySQL will automatically set MYSQL_HOME if no such variable exists already. If a my.cnf file exists in the data directory (typically /usr/local/mysql/data), then that file will be used. Otherwise, the installation directory will be used (typically /usr/local/var). See the MySQL Documentation for more information on how to configure option files.

Windows based systems

ここをクリックして展開...

Windows based MySQL systems will use the configuration files in the following order of precedence

  • %PROGRAMDATA%\MySQL\<MySQL Server Version>\my.ini
  • %PROGRAMDATA%\MySQL\<MySQL Server Version>\my.cnf
  • %WINDIR%\my.ini
  • %WINDIR%\my.cnf
  • C:\my.ini
  • C:\my.cnf
  • INSTALLDIR\my.ini
  • INSTALLDIR\my.cnf

Enabling Slow Query Profiling

データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。

  1. Shut down your applications using MySQL
  2. Shut down MySQL itself
  3. Add the following configuration options to my.inimy.cnf:

    # What's the threshold for a slow query to be logged?
    long_query_time=0.5
    
    # Where should the queries be logged to?
    slow_query_log_file=/path/to/logfile
    
    # Enable slow query logging - note the dashes rather than underscores
    slow_query_log=1
  4. Save the file, and restart MySQL
  5. Restart your connected applications

Once you have the appropriate logs, follow the instructions at: Troubleshooting slow MySQL performance to analyze them.

Last modified on Mar 14, 2023

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.