mysql -vv_一文读懂MySQL

QPS:每秒钟处理的查询量，

影响数据库性能的因素。

网卡流量：网卡io被占满(1000Mb/8=100MB)

特点

并发性与锁级别(使用的是表级锁)

表损坏修复()

创建表时指定存储引擎engine=myisam

check table tablename

repair table tablename

myisam表支持的索引类型。全文索引

myisam表支持数据压缩

myisampack

myisam -b -f myIsam.MYI

限制

适用于非事务型应用

只读类应用

空间类应用

innodb mysql5.5及以上的版本默认存储引擎。

锁的粒度

表级锁(加锁时锁定整张表)

lock table tablename write；

unlock tables；

行级锁(在存储引擎中进行实现。)

阻塞和死锁

什么是阻塞：不同锁之间的兼容性的关系。一个事务的锁需要等待另一个事务的锁的释放

什么是死锁： 两个或者两个以上的事务相互占用了对方的资源，(死锁，数据库会自动发现，并且选择占用资源最少的事务进行回滚，释放资源。)

innodb：支持全文索引，和空间函数，。

适合作为数据交换的中间表

适用场景：日志和数据采集类应用，

适用场景：偶尔的统计分析及手工查询，

mysql服务器参数

内存配置相关参数

确定可以使用的内存上限，

确定mysql的每个连接使用的内存

sort_buffer_size

join_buffer_size

read_buffer_size

read_rnd_buffer_size

确定需要为操作系统保留多少内存

基准测试的目的

建立musql服务器 的性能基准线，确定当前mysql 的服务器运行情况

模拟比当前系统更高的负载，找出系统的扩展瓶颈增加数据库并发，观察QPS,TPS变化，确定并发量与性能的最优关系。

测试不同的硬件，软件和操作系统配置。

证明新的硬件设备是否配置正确。

** 如何进行基准测试**

create database test;

grant all privileges on . to root@‘localhost’ indentified by ‘123456’;

cd /home/test/sysbench-0.5/sysbench/

ls

cd tests/db

ls -l *lua

sysbench --test=./oltp.lua --mysql-table-engine=innodb oltp-table size=10000 --mysql-db=test --mysql-user=root --mysql password=123456 --oltp-tables-count=10 --mysql socket=/usr/local/mysql/data/mysql.sock prepare/run(测试)

show variables like ‘binlog_format’;

set session binlog_format=statement;

FULL:记录修改列所有的字段。

MIMIMAL：只记录列中修改的字段，

NOBLOB；

set session binlog_format=row;

show variables like ‘binlog_format’ ;

flush logs;

show variables like ‘binlog_row_image’;

ls -lh

mysqlbinlog mysql-bin.000003

mysqlbinlog -vv mysql-bin.000003

set session binlog_row_image=minimal;

对主从数据的一致性更加有保证，基于行的复制(RBR).

mysqldump --singel-transaction --master-data --triggers --routines -all-databases -yroot -p >> all.sql

ls

ls -lh

csp_all.sql root@192.168.1.22:/root

mysqldump --single-transaction --master-data-2 triggers --routines --all-databases -uroot -p >all2.sql

scp -P9880 all2.sql root@192.168.1.22:/root

change master to master_host=‘192.168.3.100’

master_user=‘root’

master_password=‘123456’

master_suto_position=1;

start slave;

show slave status \G

mysql5.7之前，一个从库，只能有一个主库。

mysql5.7之后支持一从多主架构。

show variables like ‘slave_parallel_type’;

MHA-----支持 GTID的复制。

MMM----不支持GTID的复制。

MHA配置步骤

配置集群内所有的主机的SSH免认证登陆，

比如故障转移过程中，保存原主服务器，二进制日志，配置虚拟IP地址等，

写操作，只能在主上进行操作，读操作主从上都可以。

全值匹配的查询。

匹配最左前缀的查询。

匹配列前缀查询。

匹配范围值的查询。

精确匹配左前列并范围匹配另外一列。

只访问索引的查询。

mysql的索引是在存储引擎层实现的。

set global slow_query_log=on;

优先检查这个查询是否命中查询缓存中的数据，

通过一个对大小写敏感的哈希查找实现的，

hash查找只能进行全值匹配。

检查用户权限

从查询缓存中直接返回结果并不容易。

对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率。所以不建议使用查询缓存。

show profile cpu query 1；

如何修改大表的表结构

创建新表—》导入老表的数据—》建立触发器，把老表的数据也同步更新—》创建排它锁—》新表重命名为老表的名字—》删除老表。

可以减少主从延迟。

把一个节点中的多个数据库拆分到不同的节点。

把一个库中的表分离到不同的数据库中。

数据库监控工具：：：nagios zabbix

对什么进行监控

1.对数据库的可用性进行监控。

通过网络连接到数据库并且确定数据库是可以对外提供服务的。

对数据库的性能进行监控。

QPS和TPS。 并发线程的数量。

对主从复制进行监控。

主从复制链路状态的监控。

主从复制延迟的监控。

定期确认主从复制的数据是否一致。

对服务器资源的监控。

磁盘空间，服务器的磁盘空间大并不意味着mysql数据库能使用的空间就足够大。

CPU的使用情况，内存的使用情况，swap分区的使用情况，以及网络IO的情况等，

确认数据库是否可以通过网络连接。

mysqladmin -umonitor_user -p -h ping。

telnet IP db_port。

使用程序通过网络建立数据库连接。

数据库可用性监控

记录性能监控过程中所采集到的数据库的状态

查看连接号

select connection_id();

修改锁的超时时间。

set global innodb_lock_wait_timeout=180;

begin；

show slave status；