mysql分布式innodb,mysql分布式思想（八）- my优化_mysql

一、上一节未完内容:

1.MyISAM 表锁机制优化建议

1.1 缩短锁定时间

a)尽量减少大的复杂的query，将复杂的拆分成多个简单的

b)尽可能高效的索引

c)myisam存储引擎表只存放必要的信息，控制字段类型

1.2分离并行操作

有参数concurrent_in,1,2三个值

1.3 读写优先级

有参数low_priority_updates=1 mysql尽量先处理读请求

如果写要求高不用设置该参数

2.Innodb 行锁优化建议

a)尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

b)合理设计索引，尽量缩小锁的范围

c)尽可能较少检索条件，避免间隙锁

d)尽量控制事务大小，减少锁定资源量和时间长度。

e)尽可能低级别事务隔离

3.系统锁定争用情况的查看

表级锁查看

show status like 'table%';

Table_locks_immediate---->产生表级锁定的次数

Table_locks_waited  ---->表级锁定争用而发生等待的次数

行级锁查看

show status like 'innodb_row_lock%';

5个参数状态分析

| Innodb_row_lock_current_waits | 0

| Innodb_row_lock_time          | 50896

| Innodb_row_lock_time_avg      | 10179

| Innodb_row_lock_time_max      | 16198

| Innodb_row_lock_waits         | 5

create table innodb_monitor(a int)engine=innodb;show innodb status;

二、mysql server 安装优化

1.选择合适的二进制发行版

除了官方提供的MySql AB 之外有很多第三方的也很不错。

第三方比较出名的percona,特别是针对innodb存储引擎做了很多的优化并提供了很多的性能诊断工具

2.可以通过源码安装

自己去编译

----->进入源码目录执行 ./configure -help ---->可以查看参数可以自己的需要制定参数设置去编译源代码

----->icc(interl c compile)编译器  --->多算法和很多方面做了更多的优化

三、mysql 日志设置优化

1.核心是binlog

--->相关参数　　show variables like '%binlog%';

binlog_cache_size(容纳二进制日志sql语句缓存的大小)

---->binlog_cache_use/binlog_cache_disk_use

binlog_direct_non_transactional_updates

binlog_format

binlog_stmt_cache_size

innodb_locks_unsafe_for_binlog

max_binlog_cache_size

---->能使用的最大的Cache大小

---->Multi-statement transaction required more than max_binlog_cache_ssize byte of storage的错误

max_binlog_size

----->日志的最大值 一般512M or 1G  大小并非严格

max_binlog_stmt_cache_size

sync_binlog(把binlog-cache中的数据同步到磁盘的策略)  在master/slave的时候很重要

----->对系统来说至关重要

---->0,n

---->在复制环境中有8个参数可以配置

Binlog_Do_DB  设置哪些数据库的Schema 要记录到binlog

Binlog_Ignore_DB  不要记录到binlog

Replicate_Do_DB

Replicate_Ignore_DB

Replicate_Do_Table

Replicate_Ignore_Table

Replicate_Wild_Do_Table支持通配符

Replicate_Wild_Ignore_Table支持通配符

2.Slow Query log

---->show variables like 'log_slow%';

show variables like 'long_query%';

四、Query Cache

1.了解query Cache 的利弊

---->hash运算    当有上万的查询来时，cpu损耗不能忽略不计

---->Cache失效问题  --->数据修改频繁

----->缓存的是ResultSet (一级缓存而不是二级缓存，可能重复缓存数据)

----->show variables like '%query_cache%';

have_query_cache

query_cache_limit 该参数要重视

query_cache_min_res_unit  该参数要重视

query_cache_size

query_cache_type

query_cache_wlock_invalidate

---->show status like 'Qcache%';

Qcache_free_blocks

Qcache_free_memory

Qcache_hits

Qcache_inserts

Qcache_lowmem_prunes

Qcache_not_cached

Qcache_queries_in_cache

Qcache_total_blocks

通过以上参数可以了解Cache的使用情况

2.第三方的插件  flushCache 可以把mysql的一级缓存改造成二级缓存

五、其它关于server的优化

1.max_connections;   500到800  只要mysql主机性能允许可以更大

2.max_user_connections;

3.net_buffer_length

4.thread_cache_size  50到100之间

5.thread_stack

show variables like 'thread%';

show status like 'connections';

show status like '%thread%';

Thread_Cache_hit = (connections-Thread_created)/connections *100%;

6.join_buffer_size ,sort_buffer_size 要注意