Mysql常用优化建议 及 命令解释

mysql 编译参数优化

 

./configure –prefix=/usr/local/mysql \

–without-debug \

–without-bench \

–enable-thread-safe-client \

–enable-assembler \

–enable-profiling \

–with-mysqld-ldflags=-all-static \

–with-client-ldflags=-all-static \

–with-charset=latin1 \

–with-extra-charset=utf8,gbk \

–with-innodb \

–with-csv-storage-engine \

–with-federated-storage-engine \

–with-mysqld-user=mysql \

–without-embedded-server \

–with-server-suffix=-community \

–with-unix-socket-path=/usr/local/mysql/sock/mysql.sock

 

———————————

mysql 慢查询:

 

mysql 5.0

[mysqld]

long_query_time = 1

log-slow-queries = /var/log/mysql/slow.log

 

mysql 5.1

[mysqld]

long_query_time = 1

slow_query_log=1

slow_query_log_file = /var/log/mysql/slow.log

 

long_query_time 是指执行超过多久的sql会被log下来,这里是1秒。

log-slow-queries和slow_query_log_file 设置把日志写在哪里

 

 

 

临时打开慢查询:

mysql> show global variables like ‘%query%’;

+——————————+————————————————+

| Variable_name | Value |

+——————————+————————————————+

| ft_query_expansion_limit | 20 |

| have_query_cache | YES |

| long_query_time | 1.000000 |

| query_alloc_block_size | 8192 |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 1073741824 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

| query_prealloc_size | 8192 |

| slow_query_log | OFF |

| slow_query_log_file | /dataa/xdbs65-slow.log |

+——————————+————————————————+

12 rows in set (0.00 sec)

 

mysql> set global slow_query_log=ON;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set global slow_query_log=off;

Query OK, 0 rows affected (0.00 sec)

————————————————————–

mysql 优化

 

show variables like ‘log_slow%’;

show variables like ‘long_query%’;

 

show variables like ‘%query_cache%’;

show status like ‘Qcache%’;

 

show variables like ‘%binlog%’;

show variables like ‘thread%’;

show status like ‘connections’;

show variables like ‘%thread%’;

 

show variables like ‘table_cache’;

show status like ‘open_tables’;

show variables like ‘%buffer%’;

 

 

 

 

解决锁表:

show status like ‘table%’;

show status like ‘innodb_row_lock%’;

 

key cache 优化的几个参数 207页。

key_buffer_usageratio keycache总利用率

key_buffer_read_hitratio 读myi文件和 读 keycache的比率,应该在99%

key_buffer_write_hitratio 读入myi文件到keycache,再写 和 修改key cache 的比率。

 

innodb优化:

show status like ‘innodb_buffer_pool_%’;

show status like ‘innodb_log%’;

 

QPS 每秒query量 show status like ‘Questions’;

TPS 每秒事物量 show status like ‘com_commit’;

show status like ‘com_rollback’;

 

key buffer 命中率

show status like ‘key%’;

 

innodb buffer 命中率

show status like ‘innodb_buffer_pool_read%’;

 

query cache 命中率

show status like ‘Qcache%’;

 

table cache 命中率

show status like ‘open%’;

 

thread cache 命中率

show status like ‘thread%’;

show status like ‘connections’;

 

查看未写到磁盘的缓存:

show status like “Key_blocks_not_flushed”;

 

锁表:

show status like ‘%lock%’;

 

临时表:

show status like ‘created_tmp%’;

 

binlog cache 使用状况:

show status like ‘binlog_cache%’;

 

innodb_log_waits 量:

show status like ‘innodb_log_waits%’;

 

mysql主从停止:

STOP SLAVE IO_THREAD;

STOP SLAVE SQL_THREAD;

mysql 优化要点:

/etc/fstab defaults,noatime 加noatime,不记录文件创建时间,提高io

加大文件描述符限制

Vim /etc/security/limits.conf

加上

* soft nofile 65535

* hard nofile 65535

文件系统选择 xfs

/dev/sda5 /data xfs defaults,noatime 1 2

编码选择 单字节 latin1 多字节 utf8(汉字占3个字节,英文字母占用一个字节) 如果含有中文字符的话最好都统一采用utf8类型,避免乱码的情况发生。

wait_time_out参数 线程连接的超时时间,尽量不要设置很大,推荐10s

—————————————————————–

MySQL

考虑因素:

复杂数据查询操作是否需要预留内存以及上限

数据备份dump对系统的影响,避免swap

开启binlog带来的性能开销

限制最大链接数

 

mysql 优化建议 未测试

max_binlog_cache_size=2G

max_binlog_size= 500M

max_connections= 1020

max_user_connections=1000

query_cache_size= 30M

skip-name-resolve

wait_timeout = 32

interactive_timeout = 32

 

innodb从服务器推荐配置:

log-bin = mysql-bin

server-id = 2

relay-log = mysql-relay-bin

log_slave_updates = 1 #从库开启binlog

read-only =1

skip_slave_start #阻止服务器在崩溃后自启动

# replicate-do-db=test 需要备份的数据库名

# replicate-ignore-db=mysql 忽略的数据库

innodb_flush_log_at_trx_commit = 2

innodb_file_per_table

 

InnoDB引擎

考虑因素:

尽可能大的BP(buffer pool)

日志和数据分设备存储

离散数据走direct-IO,顺序日志走buffered-IO

减少脏页的同步,提高命中率

减少锁对多核CPU性能的影响

提高底层存储默认的IO能力

innodb_buffer_pool_size= 72G

innodb_flush_method= O_DIRECT

innodb_sync_spin_loops=0

innodb_log_group_home_dir= /u02/

innodb_io_capacity=2000

innodb_thread_concurrency= 64

 

修复表:

/usr/local/mysql/bin/myisamchk -c -r live_message.MYI

—————————————————————–

080829 14:15:14 InnoDB: Error: cannot allocate 12884918272 bytes of

InnoDB: memory with malloc! Total allocated memory

查看内核限制:

cat /proc/sys/vm/nr_hugepages

6000

取消内核限制:

echo 0 > /proc/sys/vm/nr_hugepages

—————————————————-

mysql> show variables like ‘%log%’; |

 

这个默认是0,也就是logs不过期,这个是一个global的参数,所以需要执行

set global expire_logs_days=8;

—————————————————————————————

处理主从不同步跳过1条:

stop slave;

set global sql_slave_skip_counter=1;

start slave;

show slave status\G;

—————————————————————————————-

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

slave-skip-errors=1062