mysqldumpslowを使用します.pl遅いクエリーログで発生した問題と解決方法を分析する
10338 ワード
c:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl
Can't determine basedir from 'my_print_defaults mysqld' output: at mysqldumpslow.pl line 61.まず、この問題の原因を分析します.
c:\Program Files\MySQL\MySQL Server 8.0\bin>my_print_defaults mysqld
このコマンドを実行しても、出力はありません.
このコマンドを確認します.
c:\Program Files\MySQL\MySQL Server 8.0\bin>my_print_defaults --help
my_print_defaults Ver 8.0.13 for Win64 on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Prints all arguments that is give to some program using the default files
Usage: my_print_defaults [OPTIONS] groups
-c, --config-file=name
Deprecated, please use --defaults-file instead. Name of
config file to read; if no extension is given, default
extension (e.g., .ini or .cnf) will be added
-#, --debug[=#] This is a non-debug version. Catch this and exit
-c, --defaults-file=name
Like --config-file, except: if first option, then read
this file only, do not read global or per-user config
files; should be the first option
-e, --defaults-extra-file=name
Read this file after the global config file and before
the config file in the users home directory; should be
the first option
-g, --defaults-group-suffix=name
In addition to the given groups, read also groups with
this suffix
-e, --extra-file=name
Deprecated. Synonym for --defaults-extra-file.
-n, --no-defaults Ignore reading of default option file(s), except for
login file.
-l, --login-path=name
Path to be read from under the login file.
-s, --show Show passwords in plain text.
-?, --help Display this help message and exit.
-v, --verbose Increase the output level
-V, --version Output version information and exit.
Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf c:\Program Files\MySQL\MySQL Server 8.0\my.ini c:\Program Files\MySQL\MySQL Server 8.0\my.cnf
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
config-file my
defaults-file my
defaults-extra-file (No default value)
defaults-group-suffix (No default value)
extra-file (No default value)
login-path (No default value)
show FALSE
Example usage:
my_print_defaults --defaults-file=example.cnf client mysqlデフォルトのプロファイルを指定して試します.
c:\Program Files\MySQL\MySQL Server 8.0\bin>my_print_defaults -e "c:\programdata\mysql\mysql server 8.0\my.ini" mysqld
--port=3306
--basedir=C:/Program Files/MySQL/MySQL Server 8.0/
--datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
--default_authentication_plugin=caching_sha2_password
--default-storage-engine=INNODB
--sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
--log-output=FILE
--general-log=0
--general_log_file=DESKTOP-VI7DM9L.log
--slow-query-log=1
--slow_query_log_file=DESKTOP-VI7DM9L-slow.log
--long_query_time=10
--log-bin=DESKTOP-VI7DM9L-bin
--log-error=DESKTOP-VI7DM9L.err
--server-id=1
--lower_case_table_names=1
--secure-file-priv=C:/ProgramData/MySQL/MySQL Server 8.0/Uploads
--max_connections=151
--table_open_cache=2000
--tmp_table_size=25M
--thread_cache_size=10
--myisam_max_sort_file_size=100G
--myisam_sort_buffer_size=40M
--key_buffer_size=8M
--read_buffer_size=64K
--read_rnd_buffer_size=256K
--innodb_flush_log_at_trx_commit=1
--innodb_log_buffer_size=1M
--innodb_buffer_pool_size=8M
--innodb_log_file_size=48M
--innodb_thread_concurrency=9
--innodb_autoextend_increment=64
--innodb_buffer_pool_instances=8
--innodb_concurrency_tickets=5000
--innodb_old_blocks_time=1000
--innodb_open_files=300
--innodb_stats_on_metadata=0
--innodb_file_per_table=1
--innodb_checksum_algorithm=0
--back_log=80
--flush_time=0
--join_buffer_size=256K
--max_allowed_packet=4M
--max_connect_errors=100
--open_files_limit=4161
--sort_buffer_size=256K
--table_definition_cache=1400
--binlog_row_event_max_size=8K
--sync_master_info=10000
--sync_relay_log=10000
--sync_relay_log_info=10000
--loose_mysqlx_port=33060見てごらんmysqldumpslowplを変更します.
my $defaults = `my_print_defaults -e "c:\\programdata\\mysql\\mysql server 8.0\\my.ini" mysqld`;修正後、再度実行し、2つ目の問題に遭遇しました.
c:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl
Reading mysql slow query log from C:/ProgramData/MySQL/MySQL Server
Can't open C:/ProgramData/MySQL/MySQL: No such file or directory at mysqldumpslow.pl line 96.
Can't open Server: No such file or directory at mysqldumpslow.pl line 96.遅いクエリー・ログ・パスに問題があるのは明らかです.Perl言語に慣れていないため、eclipseでこのコードをデバッグし、最終的に問題を見つけました.
コードは次のように変更されます.
if ( -f $slowlog ) {
@ARGV = ($slowlog);
die "Can't find '$slowlog'
" unless @ARGV;
} else {
@ARGV = ;
die "Can't find '$datadir/$opt{h}-slow.log'
" unless @ARGV;
}引用符で$datadirを引き起こし、問題は解決します.
この問題の原因はwindowsコマンドラインのスペース付きパスを引用符で囲むことです.
再度実行すると、遅いクエリー・ログを分析できます.
c:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl
Reading mysql slow query log from C:/ProgramData/MySQL/MySQL Server
Can't open C:/ProgramData/MySQL/MySQL: No such file or directory at mysqldumpslow.pl line 96.
Can't open Server: No such file or directory at mysqldumpslow.pl line 96.
c:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl
Reading mysql slow query log from C:/ProgramData/MySQL/MySQL Server 8.0/Data/DESKTOP-VI7DM9L-slow.log
Count: 1 Time=0.00s (0s) Lock=0.03s (0s) Rows=0.0 (0), root[root]@localhost
show create table customers
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=122.0 (122), root[root]@localhost
select * from customers
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=122.0 (122), root[root]@localhost
select * from customers order by phone
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=122.0 (122), root[root]@localhost
select * from customers order by phone;
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
c:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
# Time: N-N-10T08:N:N.145812Z
# User@Host: root[root] @ localhost [::N] Id: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N
use yiibaidb;
SET timestamp=N;
show tables;
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N (MySQL Community Server - GPL). started with:
TCP Port: N, Named Pipe: (null)
# Time: N-N-11T00:N:N.815072Z
# User@Host: root[root] @ localhost [::N] Id: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N
SET timestamp=N;
show databases