MySQLデータベース最適化プロファイル
サーバ物理ハードウェアの最適化
MySQLはコンパイルでインストールする必要があります
1.スロークエリ
2.接続数
3.key_buffer_size
4.テンポラリ・テーブル
5.表を開いた場合
7.クエリー・キャッシュ(querycache)
10.InnoDB_buffer_pool_Cache合理的設定
現在実行可能なシナリオは次のとおりです.
本文は“平淡が本当です”のブログから出て、転載して作者と連絡してください!
， MySQL ， ：
1、 （ I/O）， SAS15000 ， 6 RAID1+0。MySQL 、 ， ， ， I/O MySQL 。 100 PV （Discuz）、 （Wordpress）， ， MySQL ！ ： RAID1+0 ， RAID5，MySQL PAID5 ， ， SSD SAS RAID1+0。
2、CPU MySQL ， CPU。 DELL R710（ ）， 。
3、 MySQL Database Server ， 2GB， 4GB ， ， ， 32GB， 32GB 。
DELL R710/R720， ， ， 。MySQLはコンパイルでインストールする必要があります
MySQL ， ， 。 CentOS6.7 X86_64， Debug ， Debug MySQL ， ， --without-debug Debug 。 --with-mysqld-ldflags --with-client-ld-flags --all-static ， ， 。 ， 5% 10% 。 MySQL ， ， 。
MySQL [client]
port = 3306 # 3306
socket =/data/3306/mysql.sock #
default-character-set = utf8 # ,( character_set_client、character_set_connection、character_set_results)
[mysql]
no-auto-rehash # updates deletes
[mysqld] # mysqld ， ， MySQL ， 、 、 ， 、 、 ， MySQL 。
user = mysql #mysql_safe MySQL ( --user=mysql ), mysql 。
port = 3306 #MySQL 。 , 。
socket =/data/3306/mysql.sock #socket Linux/Unix ， Linux/Unix TCP/IP unix socket MySQL。
basedir = /application/mysql #mysql , mysql 、 、
datadir = /data/3306/data #MySQL ( )
character-set-server = utf8 # 。( utf8, )
log-error=/data/3306/mysql_xuliangwei.err #mysql ( , 。)
pid-file=/data/3306/mysql_xuliangwei.pid #MySQL_pid mysqld pid，pid ProcessID。
skip-locking # MySQL ， ， 。
skip-name-resolv # MySQL DNS ， MySQL DNS 。 ， ， IP ， MySQL ！
skip-networking # MySQL TCP/IP ， Web MySQL ， ， ！
open_files_limit = 1024 #MySQLd , too mant openfiles 。
back_log = 384 #back_log MySQL ， 。 ， ， TCP/IP 。 。 back_log ， 50. Linux ， 512 。
max_connections = 800 # MySQL 。 Too Many Connections ， 。
max_connect_errors = 6000 # ， ，MySQL host ， MySQL flush hosts host 。
wait_timeout = 120 # ， 4GB ， 5~10。
table_cache = 614K #table_cache 。 MySQL ， MySQL ， ， 。 ， Open_tables Open_tables， table_cache ， Open_tables table_cache ， Opened_tables ， 。
external-locking = FALSE #MySQL 。True 。
max_allowed_packet =16M # ，
sort_buffer_size = 1M # ， 2MB。
： ， 100 ， 100 x6=600MB。 ， 4GB ， 6MB~8MB
join_buffer_size = 8M # ， sort_buffer_size ， 。
thread_cache_size = 64 # Thread Cache ， 0~16384， 0. ， ， ; ， , ， ， ， 。 Connections Threads_created ， 。 :1GB 8,2GB 16,3GB 32,4GB 4GB 64 。
thread_concurrency = 8 # CPU x 2， ， CPU， CPU H.T ， 4 x 2 = 8。 。
query_cache_size = 64M # MySQL 。 MySQL ， Qcache_lowmem_prunes ， ; Qcache_hits ， 。 ， 。 Qcache_free_blocks， ， 。
query_cache_limit = 2M #
query_cache_min_res_unit = 2k # ， ， ， 。 4K， 1K~16K。
default_table_type = InnoDB # InnoDB
thread_stack = 256K # MySQL ， ， 。 128KB 4GB， 192KB
#transaction_isolation = Level # (READ UNCOMMITTED( ) READ COMMITTED( ) REPEATABLE READ( ) SERIALIZABLE( ))
tmp_table_size = 64M # 。 ， ， 1KB 4GB。
max_heap_table_size = 64M # 。
table_cache = 614 # ， ， 。 ， IO， , 614。
table_open_cache = 512 # 。 ， 。 ， table_cache max_connections 。 ， 200 ， 200 × N ， N 。 ， 。
long_query_time = 1 # , 10s, (1s~2s)
log_long_format # 。( , )
log-slow-queries = /data/3306/slow.log # ( , )
log-bin = /data/3306/mysql-bin #logbin , update、delete、create binlog , logbin
relay-log = /data/3306/relay-bin #relay-log I/O , SQL relay-log
relay-log-info-file = /data/3306/relay-log.info # , relay-log.info。
binlog_cache_size = 4M # binlog sql cache ， ， ， ， binlog ， binlog ， ， ， 。
max_binlog_cache_size = 8M # Cache 。
max_binlog_size = 1G # ( 1GB) ,MySQL 。
expire_logs_days = 7 # 7 binlog,mysql ( , )
key_buffer_size = 256M # ， 。 4GB ， 256MB 384MB。
： ！
read_buffer_size = 4M # 。 sort_buffer_size ， 。
read_rnd_buffer_size = 16M # 。 read_buffer_size Buffer ， ， 。 ， Buffer 。 256KB， 4GB。
bulk_insert_buffer_size = 8M # , 16MB~32MB, 8MB。
#myisam_sort_buffer_size = 8M # REPAIR Table Create index Alter table ， 4Bytes 4GB， 8MB
lower_case_table_names = 1 # MySQL 。( - )
slave-skip-errors = 1032,1062 # (mysql , mysql , )。
replicate-ignore-db=mysql # , 。
server-id = 1 # 1, ， ,serverid 。
myisam_sort_buffer_size = 128M # REPAIR, OPTIMIZE, ALTER ，MySQL ， LOAD DATA INFILE ， 。
myisam_max_sort_file_size = 10G # （REPAIR，ALTER，TABLE， LOAD，DATA，TNFILE） ，MySQL 。
myisam_repair_threads = 1 # , MyISAM .
myisam_recover # MyISAM .
innodb_additional_mem_pool_size = 4M # InnoDB 。 ， 。 ， ， 。 InnoDB ，InnoDB ， MySQL 。 1MB， ， 。
innodb_buffer_pool_size = 64M #InnoDB ， ， I/O 。 InnoDB Buffer Pool 50%~80%， 。
innodb_data_file_path = ibdata1:128M:autoextend # 128MB ， ibdata1. ， MySQL 。
innodb_file_io_threads = 4 #InnoDB I/O 。 4， windows I/O
innodb_thread_concurrency = 8 # CPU ， ， 8。
innodb_flush_log_at_trx_commit = 1 # 0 innodb_log_buffer_size ， 1， 。
innodb_log_buffer_size = 2M # 1MB， 8~16MB 。
innodb_log_file_size = 32M # ， ， 。
innodb_log_files_in_group = 3 # ,MySQL 。 3。
innodb_max_dirty_pages_pct = 90 #InnoDB 。
innodb_lock_wait_timeout = 120 #InnoDB 。InnoDB 。InnoDB locak tables 。 50 。
innodb_file_per_table = 0 #InnoDB ， 。0 ，1 。 ：
1、 。
2、 。
3、 。
4、 （ drop table ， 。）[mysqldump]
quick
max_allowed_packet = 2M # 。 1MB， 1GB， 1024 。 。
[mysqld_safe] ：
InnoDB Buffer Pool 50%~80%， 。
key_reads ， my.cnf key_buffer_size ， key_reads/key_read_re-quests 1/100 ， 。
qcache_lowmem_prunes ， query_cache_size 。
， MySQL status 。
MySQL MySQL /etc/my.cnf( DELL R710、16GB 、RAID10)， MySQL ：[client]
port = 3306
socket =/data/3306/mysql.sock
default-character-set = utf8
[mysqld]
user = mysql
port = 3306
character-set-server = utf8
socket =/data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
log-error=/data/3306/mysql_err.log
pid-file=/data/3306/mysql.pid
log_slave_updates = 1
log-bin = /data/3306/mysql-bin
binlog_format = mixed
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 90
binlog-ignore - db = mysql
binlog-ignore - db = information_schema
key_buffer_size = 384M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
join_buffer_size = 2M
thread_cache_size = 8
query_cache_size = 32M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
thread_concurrency = 32
table_cache = 614
table_open_cache = 512
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet =16M
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 256M
max_heap_table_size = 512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
long_query_time = 2
slow_query_log
slow_query_log_file = /data/3306/slow.log
skip-name-resolv
skip-locking
skip-networking
server-id = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no � auto - rehashMySQL status
MySQL ， status ， MySQL ：
mysql > show global status;
show status like ' %';1.スロークエリ
Query ， ， Slow Que-ry log。 ：mysql> show variableslike '%slow%';
+---------------------+-----------------------------------------+
| Variable_name |Value |
+---------------------+-----------------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+---------------------+-----------------------------------------+
mysql> show globalstatus like '%slow%';
+---------------------+-------+
| Variable_name | Value|
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 2128 |
+---------------------+-------+ ， MySQL ， ， ， 。 ， MySQL mysqldumpslow 。 ： 20 SQL ：mysqldumpslow-s c -t 20 host-slow.log2.接続数
MySQL：ERROR1040：Too manyconnections ， ，MySQL ， 。 MySQL max_connections 。 。mysql>show variables like'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 800 |
+-----------------+-------+ 256， ；mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value|
+----------------------+-------+
| Max_used_connections | 245 |
+----------------------+-------+MySQL 245， 800， 1040 。
Max_used_connections/max_connections * 100% = 85%
85% , 10% ， MySQL 。3.key_buffer_size
key_buffer_size MyISAM ， MyISAM 。 MyISAM ：mysql> show variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 536870912 |
+-----------------+-----------+ ， 512MB key_buffer_size。 key_buffer_size ：mysql> show global status like 'key_read%';
+-------------------+--------------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 27813678766 |
| Key_reads | 6798830 |
+-------------------+--------------+ 27813678766 ， 6798830 ， 。
key_cache_miss_rate = key_reads /key_read_requests * 100%
，key_cache_miss_rate 0.0244%，4000% ， ，key_cache_miss_rate 0.1% ， key_cache_miss_rate 0.01% ， key_buffer_size ， 。4.テンポラリ・テーブル
， ， ：mysql> show global status like 'created_tmp%';
+-------------------------+----------+
| Variable_name |Value |
+-------------------------+----------+
| Created_tmp_disk_tables | 21119 |
| Created_tmp_files |6 |
| Created_tmp_tables |17715532 |
+-------------------------+----------+ ，Created_tmp_table ， ，Created_tmp_disk_tables 。Created_tmp_files MySQL ， ：
Created_tmp_disk_tables/ Created_tmp_files * 100% <= 25%
Created_tmp_disk_tables/ Created_tmp_files * 100% =1.20%， 。 MySQL ：mysql> show variables where Variable_name in('tmp_table_size','max_heap_table_size');
+---------------------+---------+
| Variable_name |Value |
+---------------------+---------+
| max_heap_table_size | 2097152 |
| tmp_table_size |2097152 |
+---------------------+---------+5.表を開いた場合
Open_tables ，Opened_tables ， ：mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 351 |
| Opened_tables | 1455 | Opened_tables ， table_open_cache 。 table_open_cache;mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 2048 |
+------------------+-------+ ：
open_tables/ opened_tables* 100% > = 85%
open_tables/ table_open_cache* 100% < = 95%
6.
MySQL thread_cache_size， ， ( )Thread_created ， ：mysql> show global status like 'thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 40 |
| Threads_connected | 1 |
| Threads_created | 330 |
| Threads_running | 1 |
+-------------------+-------+ Threads_created ， MySQL ， ， thread_cache_size 。 thread_cache_size ：mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 100 |
+-------------------+-------+ MySQL 。7.クエリー・キャッシュ(querycache)
，query_cache_size MySQL Query Cache ，query_cache_type ， ：mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name |Value |
+-------------------------+-----------+
| Qcache_free_blocks |22756 |
| Qcache_free_memory |76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts |208894227 |
| Qcache_lowmem_prunes |4010916 |
| Qcache_not_cached |13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks |111212 |
+-------------------------+-----------+MySQL ：
Qcache_free_blocks： 。 。flush query cache ， 。
Qcache_free_memory： 。
Qcache_hits： 。 Query Cache 。
Qcache_inserts： ， 1。 。
Qcache_lowmem_prunes： Query Query Cache。 Qcache_lowmem_prunes Query_free_memory ， Query Cache ， Query 。
Qcache_not_cached： ， select now() 。
Qcache_queries_in_cache： 。
Qcache_total_blocks： 。
query_cache ：mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 2097152 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+ ：
query_cache_limit： 。
query_cache_min_res_unit： 。
query_cache_size： 。
query_cache_type： ， ， select sql_no_cache 。
query_cache_wlock_invalidat： MyISAM ， WRITELOCK Query Cache ， OFF（ Query Cache 。）
query_cache_min_res_unit ， 4KB， ， ， 。
=Qcache_free_blocks /Qcache_total_blocks * 100%
20%， flushquery cache ， query_cache_min_res_unit， 。
=(Qcache_free_size � Qcache_free_memory)/query_cache_size * 100%
25% query_cache_size ， ; 80% Qcache_lowmem_prunes> 50 query_cache_size ， 。
= (Qcache_hits- Qcache_insert)/Qcache)hits * 100%
20% ， 50%， 2%， ， ， 。
8.
Buffer， ：mysql> show global status like 'sort%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Sort_merge_passes | 10 |
| Sort_range | 37431240 |
| Sort_rows | 6738691532|
| Sort_scan | 1823485 |
+-------------------+----------+Sort_merge_passes ：MySQL ， sort_buffer_size ， ， MySQL ， MySQL ， 。 sort_merge_passes。 ，MySQL ， sort_merge_passes 。 ， ， sort_buffer_size sort_merge_passes ， sort_buffer_size 。
9. (open_files)
MySQL ， Open_files open_files_limit ，MySQL ， Nginx 。 ， ：show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 1481 |
+---------------+-------+
mysql> show global status like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+--------+
| Open_files_limit | 4509 |
+------------------+--------+ ：Open_files/ Open_files_limit * 100% < = 75%10.InnoDB_buffer_pool_Cache合理的設定
InnoDB MyISAM ，InnoDB ， 。 InnoDB Buffer ， ， InnoDB 。
MySQL InnoDB ， 50%~80%。 ， 。
MySQL
， ， ， 。 DBA ， 。( )
MySQL
MySQL ， MySQL ( MySQL ) ：
， Pc server 。
， 。
。現在実行可能なシナリオは次のとおりです.
（1）MySQL Cluter
， 。 ， 。 ， Bug， ， 。
（2）DRBD
， ， 。I/O ， 。 ， ， ， MySQL Replication。 ，DRBD MySQL ， 。
（3）MySQL Replication
， MySQL 、 ， ， 、 。本文は“平淡が本当です”のブログから出て、転載して作者と連絡してください!