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