・SQL実行ログの確認
show global variables like '%general%'
set global general_log='on'
set global log_output='table'
select * from mysql.general_log order by event_time desc;
set global general_log='off'
・sqlの解析
explain extended *****
show warnings\G
・テーブルの構造を見る
desc **
show create table ***
show fields from ** like '%**%'
・状態を確認する
show status like 'Handler_%';
Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 2 | ＝＞read use index!!!
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 5140 | =>this means a table scan!!!!
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 17 |
+----------------------------+-------+
・SHOW GLOBAL STATUS LIKE 'uptime';
=>起動からの秒数。
・check table **
repair table ** =>'The storage engine for the table doesn''t support repair' MyISAMだけサポートする
REPAIR TABLE EXTENDED ＊＊
mysqlcheckーー＞command!!
・select user(),current_user();
select user,host from mysql.user
・lock???
show processlist;
State
The internal state of the thread.==>lock,Waiting for table metadata lock(drop tableなど、テーブルに対する修正)
==>the state of our query is Locked, which means the query cannot proceed,
because another thread holds the lock our thread is waiting for
UPDATE t SET a=sleep(200) WHERE a=0
UPDATE t SET a=sleep(200) WHERE a=6
=->innodb_lock_wait_timeout
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits\G
select * from information_schema.innodb_trx\G
======>The main symptom is a thread that’s in the “Sleep” state for a
long time.
======>
＝>使用中のMutexの一覧
mysql> select * from performance_schema.mutex_instances where locked_by_thread_i
d is not null;
＝>誰かMutexを待っている？
mysql> SELECT THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE,
TIMER_START, OBJECT_INSTANCE_BEGIN, OPERATION FROM EVENTS_WAITS_CURRENT WHERE
THREAD_ID IN(SELECT LOCKED_BY_THREAD_ID FROM MUTEX_INSTANCES WHERE
LOCKED_BY_THREAD_ID IS NOT NULL)\G
＝>threadの一覧
SELECT * FROM THREADS\G
・SELECT @@autocommit
・sometimes the replication is not safe(statement base )
because the master doesn’t write anything to the binary log until the transaction is
committed unlike writing to the master's own table.
That makes the record's sequence differ from master and slave
when you do something related to the seq about the record then you will get different result..
・show engines
・connection test
mysqladmin -h127.0.0.1 -P3306 ping
telnet 127.0.0.1 3306
・max_allowed_packet＝１M
SELECT repeat('a',1025);
＝＞warning、Result of repeat() was larger than max_allowed_packet (1024) - truncated
1 row in set (0.00 sec)
・--no-defaults and --defaults-file options must be specified
as the first options passed to mysqld
・Unless you are 100% sure what is wrong, add options one by one, and then test
the configuration each time.
mysql variables 2
★Init SQL？＝＞when you get results different results check that settings!!
show variables like '%init%'
These options determine whether the server should execute some SQL statements
automatically at various times:
init_filet
Points to a file containing SQL statements that should be executed at server
startup
init_connect
Contains an SQL string that should run when each client connects
init_slave
Contains an SQL string that should run when the server starts its SQL thread
as a slave
★open_files_limit＝＞he more table files and temporary tables
you can have open, and therefore the greater the number of simultaneous connections
you can handle
★log_warnings＝ー＞not warnings that happen during SQL execution, but rather
debugging messages that show what is going on inside the server.
If set to 2, this option tells the server to log connection errors. This is very important
when you are troubleshooting situations where clients cannot connect or are losing
their connections.
★sql_warning==>show warnings that created by statements been generating warnings during execution.
★binlog-* and replicate-* filters＝ー＞binlog-*
options reduce the events that go into the binary logfile on the master, whereas
replicate-* specify those that go into the relay logfile on the slave.
★binlog_format＝＝＞個人的にmixeを推奨！
★binlog_direct_non_transactional_updates＝＝＞
コミットしなくてもすぐbin_logに書き込み、slaveに影響を与える
Before doing this, be sure that data in nontransactional
tables cannot be modified by any statement that uses a transactional
table.
only if statement-based replication is used.
Replication options
★binlog_cache_size and friends
binlog_cache_size
binlog_stmt_cache_size
max_binlog_cache_size
max_binlog_stmt_cache_size
issued
==>during a transaction before writing to the binary log. If max_binlog_cache_size is
reached, the statement aborts with the error "Multi-statement transaction
required more than 'max_binlog_cache_size' bytes of storage".
★slave_skip_errors＝>slaveの方はskipするね。
★read_only＝ー＞the option does not
restrict a user with the SUPER privilege from changing tables. Additionally, all users
are still allowed to create temporary tables
Engine options
★innodb_autoinc_lock_modeーー＞traditional、consecutive、interleaved.
・innodb_file_per_table-->save the index and data for each table in separate files
・innodb_table_locks＝＞
This variable defines how InnoDB handles table lock requests made by LOCK
TABLES statements. By default (when it is set) it returns immediately and locks the
table internally.When turned off (set to 0), it honors LOCK TABLE statements, so the
thread does not return from LOCK TABLES … WRITE until all locks are released同期非同期のようね。。
・innodb_lock_wait_timeout＝＞Try to handle lock wait errors at the
application level, and don’t set this value too high.the default is 50 seconds。The best value for this option
depends on your application and should be about the amount of time your normal
transaction takes.
・innodb_rollback_on_timeout＝ー＞When a query is aborted due to a lock wait error, only the last statements rolled
back, and the whole transaction is not aborted yet.set it to 1,the transaction will be rolled back immediately
after a lock wait timeout.
・innodb_use_native_aio＝＝＞If innodb_use_native_aio is set, InnoDB dispatches I/O requests to the kernel. This improves scalability because modern kernels can handle more
parallel I/O requests than simulated AIO.
Connection-related options
==>don't increase them blandly,find out the real reason !slow access to a huge table, or a flaky network
・connect_timeout＝＞
innodb_lock_wait_timeout-->You are already acquainted with innodb_lock_wait_timeout, which interrupts
a query that is waiting for a ★row lock★.
・lock_wait_timeout＝＞applies to metadata locks. This lock is set for all operations。Its default value is a year。
・connect_timeoutー＞the timeout that Mysql server and client to exchange authorization packets,the defautl value is 10
seconds
・interactive_timeout、wait_timeout-->コマンドラインからのコネクション！wait you to input the next command before kill the connection
・net_read_timeout/net_write_timeout==>How much time to wait for an answer from a client,then the server will kill the connection
Security-related options
・skip-grant-tables＝ー＞Here I just want to mention how to save yourself
if you forget a password. You need to start the server with the skip-grant-tables
option, edit the privilege tables in the mysql database manually, and then run the
query FLUSH PRIVILEGES. After that, new privileges are in effect.then restart the server without skip-grant-table
・safe-user-create=->cann't create user using the GRANT statement,unless insert into the mysql.user table
Performance-Related Options
・join_buffer_size＝＝＞to plain index scans,and range scans, and joins that do not use indexes
The buffer is allocated for each full join between two tables.
・net_buffer_length＝ー＞The size of a buffer that the server creates right after a client connects to hold the
request and the result. This size is increased to max_allowed_packet when needed.but you should
keep the value in mind when setting the max_connections option.
・query_prealloc_size＝＝＞This buffer is allocated for statement parsing and execution. The buffer is not freed
between statements.Increase it to the size in bytes of your largest query.
・read_buffer_size／read_rnd_buffer_size＝ー＞buffer the table scan and the result
・sort_buffer_size＝ー＞try the different values until Sort_merge_passes stops growing.
・sql_buffer_result＝ー＞the server buffers the results of each SELECT in temporary tables. This can help to release table locks earlier.To find out whether a query is spending a lot of time sending its result set, run
SHOW PROCESSLIST and check the amount of time the query is in the state “Sending data.”A status of “Sending data” in SHOW PROCESSLIST output means a thread is reading and processing rows, then sending data to the client.
・thread_cache_size＝＝＞The number of threads that should be cached for future use.This option does
not dramatically improve performance on systems with a good thread implementation,but still can be useful if an application uses hundreds of connections.
・thread_stack＝ー＞The stack size for each thread.Thread stack overrun
・query_cache_size＝ー＞don't set it two big.when the query needs to be removed from the cache,there is lock!especially true on multicore machines and highly concurrent environments.Reasonable values for this variable are less than 100 MB
query_cache_size to a reasonably small value and periodically defragment it using the query FLUSH QUERY CACHE, rather than increasing this value！use 「show status like '%Qcache%'」to check the status;
---------------------------------------------------------
くの LAMP アプリケーションはデータベースに大きく依存していますが、同じクエリーを何度も繰り返し行います。クエリーが実行されるたびに、データベースは同じ作業 を行わなければなりません (つまりクエリーを解析し、その実行方法を決定し、ディスクから情報をロードし、その情報をクライアントに返します)。MySQL にはクエリー・キャッシュと呼ばれる機能があり、クエリーの結果が再度必要な時のために、その結果をメモリーに保存します。多くの場合、これによってパ フォーマンスを劇的に向上させることができます。ただし注意する点として、クエリー・キャッシュはデフォルトで無効になっています。
query_cache_size = 32M を /etc/my.conf に追加すると、32MB のクエリー・キャッシュを有効にすることができます。クエリー・キャッシュを監視する
クエリー・キャッシュを有効にしたら、そのキャッシュが効果的に使われているかどうかを理解することが重要です。MySQL には、キャッシュの中の様子を調べるための、いくつかの変数があります。リスト 2 はキャッシュの状態を示しています。リスト 2. クエリー・キャッシュの統計を表示する
mysql> SHOW STATUS LIKE 'qcache%'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | Qcache_free_blocks | 5216 | | Qcache_free_memory | 14640664 | | Qcache_hits | 2581646882 | | Qcache_inserts | 360210964 | | Qcache_lowmem_prunes | 281680433 | | Qcache_not_cached | 79740667 | | Qcache_queries_in_cache | 16927 | | Qcache_total_blocks | 47042 | +-------------------------+------------+ 8 rows in set (0.00 sec)
表 1. MySQL のクエリー・キャッシュの変数
| 変数名 | 説明 |
|---|---|
Qcache_free_blocks | キャッシュの中にある連続したメモリー・ブロックの数を表します。この値が大きいと、キャッシュの断片化が起きていることを示します。FLUSH QUERY CACHE はキャッシュのデフラグを行い 1 つのフリー・ブロックのみにします。 |
Qcache_free_memory | キャッシュ内の空きメモリー容量を表します。 |
Qcache_hits | キャッシュにあるクエリーが使われるたびにインクリメントされます。 |
Qcache_inserts | クエリーが挿入されるたびにインクリメントされます。挿入された回数をヒット数で割るとミス・レートが得られ、1 からミス・レートの値を引くとヒット・レートが得られます。先ほどの例では、約 87% のクエリーがキャッシュから提供されています。 |
Qcache_lowmem_prunes | キャッ シュがメモリー不足になり、さらにクエリーをキャッシュするためにクリーンアップしなければならなかった回数を表します。この値は時間の経過に伴う変化を 調べるのが適切です。この値が増加している場合は、断片化が深刻であるか、あるいはメモリーが不足している兆候です (上記の free_blocks と free_memory でその状況を知ることができます)。 |
Qcache_not_cached | キャッシュの候補にならなかったクエリーの数 (通常はクエリーが SELECT 文ではなかったために候補にならなかったクエリーの数) を表します。 |
Qcache_queries_in_cache | 現在キャッシュされているクエリー (とレスポンス) の数を表します。 |
Qcache_total_blocks | キャッシュの中にあるブロックの数を表します。 |
・table_definition_cache,table_open_cache,Try to tune this option so that Opened_tables remains smaller than or equal to Open_tables.
Options that control the optimizer
・optimizer_prune_levelーー＞Change the option if you suspect the optimizer is not choosing the best plan for your query・innodb_additional_mem_pool_size-->use this to pool the data dictionary and internal data structures.if too small there wwill be error log
・innodb_buffer_pool_sizeー＞The size of the memory that InnoDB allocates to store data, indexes, table structures,
adaptive hash indexes, and so on.You can set it to up to 80% of your physical RAM
・innodb_buffer_pool_instancesーー＞This option sets the number of instances that the buffer pool should be split into.
The size of each instance is innodb_buffer_pool_size divided by innodb_buffer_pool_instances, and should be at least 1GB This option does not take effect if innodb_buffer_pool_size is less than 1GB.the concurrent sessions!
・innodb_checksums-->check the disk data to indetify whether a datafile is broken.you should usually keep it on!
・innodb_commit_concurrency-->The number of threads that can commit at the same time mysql calls storage engine’s methods to check the status.
write_row;
update_row;
delete_row
At start of each of these methods InnoDB checks count of already entered threads,
and if this count is over innodb_thread_concurrency then the thread waits
for innodb_thread_sleep_delay microseconds before a next try.
If secound try still is unsuccess ? thread sleeps in thread-queue (FIFO).
Once thread entered ? it receives innodb_concurrency_tickets tickets,
so next innodb_concurrency_tickets times thread will not be checked, and will enter free.
--->面白いね。。。
default value is 0: infinite concurrency or no concurrency checking
・innodb_doublewrite-->By default, InnoDB stores data twice: first to the doublewrite buffer, and then to
datafiles.
・innodb_io_capacityーーー＞
innodb_read_io_threads
innodb_write_io_threads
InnoDBは専用のスレッドでI/Oを行う、I/Oが少ないならデフォルトのまま使っていても良いが、I/Oがボトルネックになりそうな場合には、ベンチマークで最適値を算出しておくといいだろう
・innodb_log_buffer_sizeーー＞Increasing this variable can save disk I/O operations, but this makes sense only if
you have big transactions.Innodb_log_waits status variable
・innodb_log_file_sizeーーー＞The size of each logfile. Large logfiles reduce checkpoint activity and save disk I/O. However, large logfiles can drastically slow recovery after a crash.4 Sensible values range from 1 MB up to, but less than, innodb_buffer_pool_size/log_files_in_group.
・innodb_open_files-->This variable is meaningful only when you use innodb_file_per_table.
innodb_open_files is the number of .ibd files that InnoDB can open at the same
time. The default value is 300. It makes sense to increase it to the total number of
InnoDB tables.
★the whole server calculate how much RAM in megabytes
SELECT (@@query_cache_size + @@innodb_additional_mem_pool_size +
@@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size)/(1024*1024);
★thread毎に
SELECT @@max_connections * (@@global.net_buffer_length + @@thread_stack +
@@global.query_prealloc_size + @@binlog_cache_size + @@binlog_stmt_cache_size) /
(1024 * 1024)
★なんか怖い。。。
・make sure that the total amount of physical RAM - max_connections*sum(thread options)
- options for whole server is greater than zero
★(buffer size) * (number of buffers allocated for particular kind of query)
* (number of such queries that can be executed in parallel)
★if you need to set max_join_size really high for a statistics query that you run once per week, there is no sense to set it globally; set it just before running the query. Even with this precaution, don’t forget about memory usage
as a whole.
★
set @join_tables = YOUR_ESTIMATE_PER_THREAD;
set @scan_tables = YOUR_ESTIMATE_PER_THREAD;
set @tmp_tables = YOUR_ESTIMATE_PER_THREAD;
SELECT (@@query_cache_size + @@innodb_additional_mem_pool_size +
@@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size +
@@max_connections * (@@global.net_buffer_length + @@thread_stack +
@@global.query_prealloc_size + @@global.read_rnd_buffer_size +
@@global.sort_buffer_size + @@myisam_mmap_size +
@@global.myisam_sort_buffer_size + @@global.bulk_insert_buffer_size +
@@global.preload_buffer_size + @@binlog_cache_size +
@@binlog_stmt_cache_size + @@global.join_buffer_size * IFNULL(@join_tables,
1) + @@global.read_buffer_size * IFNULL(@scan_tables, 1) +
@@global.tmp_table_size * IFNULL(@tmp_tables, 1))) / (1024 * 1024)