美文网首页
数据库问题手册一

数据库问题手册一

作者: 我只是一个小白 | 来源:发表于2017-09-29 18:13 被阅读0次

批量sql

SELECT CONCAT('RENAME TABLE ',table_name,' TO ',UPPER(table_name),';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='GTA_QDB' AND table_name<>'UTSUPLOG';

查看io

yum install dstat -y
dstat -M topio -d -M topbio

yum install sysstat -y
# %util 表示io饱和度
iostat -x nvme0n1 5

数据库日常巡检脚本

echo -e '\n\033[32m######非Innodb#####\033[0m'
/application/mysql/bin/mysql -ulambert -p -e "SELECT table_name,TABLE_SCHEMA, table_type, engine FROM information_schema.tables where engine <> 'InnoDB' and table_schema in ('echemi_db_v0_3','market_db_v1','buying_lead_db_v1');"

echo -e '\n\033[32m######没有主键#####\033[0m'
/application/mysql/bin/mysql -ulambert -p -e "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema in ('echemi_db_v0_3','market_db_v1','buying_lead_db_v1');">/home/sa/lambert/xunjian/a.txt

t_tables=`cat /home/sa/lambert/xunjian/a.txt |tail -n +2`

for i in $t_tables
do
t_result=`/application/mysql/bin/mysql -ulambert -p -e "show index from $i where Key_name='PRIMARY';"`
[[ $t_result == '' ]]&&echo $i
done

echo -e '\n\033[32m######text类型#####\033[0m'
for i in $t_tables
do
t_result=`/application/mysql/bin/mysql -ulambert -p -e "show columns from $i where type='text';"`
[[ $t_result == '' ]]||echo $i
done

echo -e '\n\033[32m######列数大于50#####\033[0m'
for i in $t_tables
do
t_result=`/application/mysql/bin/mysql -ulambert -p -e "show columns from $i;"|wc -l`
[[ $t_result -gt 50 ]]&&echo $i
done

echo -e '\n\033[32m######大于1G的表#####\033[0m'
/application/mysql/bin/mysql -ulambert -p -e "select concat(table_schema,'.',table_name), (data_length/1024/1024) as data_mb from information_schema.tables where data_length >1000000000 and table_schema in ('echemi_db_v0_3','market_db_v1','buying_lead_db_v1');"

echo -e '\n\033[32m######索引占表四分之一的表#####\033[0m'
/application/mysql/bin/mysql -ulambert -p -e "select table_schema,table_name,data_length,index_length,(index_length/data_length) ratio from information_schema.tables where (index_length/data_length) >0.25 and table_schema in ('echemi_db_v0_3','market_db_v1','buying_lead_db_v1');"

系统优化

  • numa关闭

1.bios方式进入时设置。改成节点交叉存取

2.在kernel行最后加上numa=off
[root@local host grub]#vim /etc/grub.conf

3.修改启动脚本

shell>vi $MYSQL_HOME/bin/mysqld_safe
...
cmd="/usr/bin/numactl --interleave all $cmd"

4.mysql 5.7里直接设置参数
(好像没啥卵用)

loose_innodb_numa_interleave=1
  • swap使用率调小
sysctl vm.swappiness=10
cat /proc/sys/vm/swappiness
echo 'vm.swappiness=10'>> /etc/sysctl.conf

事务的隔离级别

  • 1.查看
select @@tx_isolation;
  • 2.修改
set global tx_isolation='read-committed';

注释:一般使用rc或者rr隔离级别,但是rc可以避免gap锁,默认是rr,日常中除特殊需求,可以调成rc

查看锁

show processlist;    查看现有的线程锁的信息
select * from information_schema.INNODB_TRX \G    查看有没有其他事务在工作
select * from information_schema.INNODB_LOCKS  \G    查看现有锁的信息
select * from information_schema.innodb_lock_waits \G    看锁的id和被阻塞的id
show OPEN TABLES where In_use > 0;    看表级锁

select * from information_schema.INNODB_TRX;
select * from information_schema.innodb_lock_waits;
select * from information_schema.INNODB_LOCKS;
show OPEN TABLES where In_use > 0; 
show full processlist;
show engine innodb status;

mysqlbinlog命令

mysqlbinlog --skip-gtids --database=crm_core --start-datetime="2017-05-31 10:00:00" --stop-datetime='2017-05-31 14:00:00' --start-position=27284 bin-log.00039[1-9]

主从延迟分析

1.dump线程(tps是否很高),网络传输,应用线程分析(从库io能力)
iostat -t 3
2.双一:没有特别需求,可以关闭

innodb_flush_log_at_trx_commit=1
sync_binlog=1

3.是否有备份(read lock)

show OPEN TABLES where In_use > 0; 

4.ddl语句
跳过事务

(root@localhost) [(none)]>stop slave;
Query OK, 0 rows affected (3.27 sec)

(root@localhost) [(none)]>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)

5.大事务,长事务

  • 大、长事务定义:
    大事务:修改批量数据,要占用大量的undo页
    长事务:开始一个事务一直不提交。这段时间操作过的表产生的undo数据都不能被删除,会一直被MVCC --长事务危害往往比大事务要大
  • 判断事务
mysql> use information_schema;
mysql> select * from INNODB_TRX \G   看所有未提交事务
trx_started: 2016-08-21 13:43:54  跟现在的时间比,时间长的话就是长事务。
trx_rows_modified: 0        看事务修改的行数,多的话就是大事务
  • 如何处理大事务?
    不能删除,因为这样的话会回滚很长时间
[root@localhost ~]# ps -ef|grep mysqld
[root@localhost ~]# kill -9 17789   可以杀死mysql进程,但是风险非常的大
  • 如何杀掉长事务?
    通过查看事务ID,然后
mysql> select * from INNODB_TRX \G
trx_mysql_thread_id: 13
mysql> kill ID;
mysql> show processlist;     看所有会话

6.检查表主键

select DISTINCT TABLE_NAME as 没有主键
from information_schema.`COLUMNS` where TABLE_SCHEMA='test' and TABLE_NAME not in(SELECT DISTINCT TABLE_NAME 
FROM information_schema.`COLUMNS` where COLUMN_KEY in ('MUL','PRI','uni') and TABLE_SCHEMA='test');

注意:没有主键产生的坑,挺多的。

相关文章

  • 数据库问题手册一

    批量sql 查看io 数据库日常巡检脚本 系统优化 numa关闭 1.bios方式进入时设置。改成节点交叉存取 2...

  • lumen框架 A facade root has not be

    问题: 使用Lumen时,需要执行数据库的 INSERT 操作,按照手册上边的方法写好代码之后,执行一直报错A f...

  • php常用mysql函数

    一、使用数据库 1、连接数据库: 注:其他参数详见php用户手册 2、选择数据库: 3、 设置数据库字符集: 4、...

  • mysql快速回顾

    MySQL 8.0参考手册 1、连接数据库 2、显示所有数据库 3、创建数据库 4、使用数据库 5、删除数据库 6...

  • Oracle Database(2) 例子

    本文摘抄自Oracle官方手册,操作 HR 数据库的例子。 Unlock HR User Oracle 数据库在安...

  • 1 通用信息

    1.1 关于本手册 这是MySQL数据库系统8.0版到8.0.15版的参考手册。 MySQL 8.0的次要版本之间...

  • NONCODE:综合性的lncRNA数据库

    欢迎关注”生信修炼手册”! NONCODE数据库是一个综合的非编码RNA数据库,该数据库中包含了除tRNA和rRN...

  • CI框架(Codeigniter)总结

    数据库使用见手册「Driver Reference > Database Class」 执行SQL row_arr...

  • 数据库的最简单实现

    所有应用软件之中,数据库可能是最复杂的。 MySQL的手册有3000多页,PostgreSQL的手册有2000多页...

  • 数据库最简单的实现

    所有应用软件之中,数据库可能是最复杂的。 MySQL的手册有3000多页,PostgreSQL的手册有2000多页...

网友评论

      本文标题:数据库问题手册一

      本文链接:https://www.haomeiwen.com/subject/tlxwextx.html