-
mysql优化——定位慢查询
1、show status 命令 命令使用方式：show [session|global] status like 'slow_queries' 如果你不写 [session|global] 默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global) 执行show status 显示的数据库变量大概有291个。变量对应说明： http://www.ttlsa.com/mysql/mysql_show_status_descriptsions/ http://blog.chinaunix.net/uid-20204919-id-1972099.html http://lxneng.iteye.com/blog/451985 show status 常用的命令： show status like ‘uptime’ ; show stauts like ‘com_select’ show stauts like ‘com_insert’ ...类推 update delete show status like ‘connections’; //显示慢查询次数 show status like ‘slow_queries’; 2、mysql默认的慢查询 默认情况下，mysql认为10秒才是一个慢查询. show variables like ‘long_query_time’ ; //可以显示当前慢查询时间 set long_query_time=1 ;//可以修改慢查询时间 3、定位慢查询 show variables like '%slow%'; #查看MySQL慢查询是否开启 set global slow_query_log=ON; #开启MySQL慢查询功能 show variables like "long_query_time"; #查看MySQL慢查询时间设置，默认10秒 set global long_query_time=5; #修改为记录5秒内的查询 select sleep(6); #测试MySQL慢查询 show variables like "%slow%"; #查看MySQL慢查询日志路径 show global status like '%slow%'; #查看MySQL慢查询状态 或者 vi /etc/my.cnf #编辑，在[mysqld]段添加以下代码 slow-query-log = on #开启MySQL慢查询功能 slow_query_log_file = /var/run/mysqld/mysqld-slow.log #设置MySQL慢查询日志路径 long_query_time = 5 #修改为记录5秒内的查询，……
SE_You
2024-04-07
10 0 0 -
show variables like xxx 详解mysql运行时参数
通过show variables like xxx 详解mysql运行时参数 本文参考以下网页： 1.http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.htm 2.http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html 3.http://www.ibm.com/developerworks/cn/linux/l-tune-lamp-3.html 4.http://www.day32.com/MySQL/tuning-primer.sh 具体数值主要参考此工具 1, 查看MySQL服务器配置信息 mysql> show variables; 2, 查看MySQL服务器运行的各种状态值 mysql> show global status; 3, 慢查询 mysql> show variables like '%slow%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | log_slow_queries | OFF | | slow_launch_time | 2 | +------------------+-------+ mysql> show global status like '%slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Slow_launch_threads | 0 | | Slow_queries | 279 | +---------------------+-------+ 配置中关闭了记录慢查询（最好是打开，方便优化），超过2秒即为慢查询，一共有279条慢查询 4, 连接数 mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 500 | +-----------------+-------+ mysql> show global status like 'max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 498 | +------------……
SE_You
2024-04-03
10 0 0 -
MySQL中间件之ProxySQL(15)：ProxySQL代理MySQL组复制
1.ProxySQL+组复制前言 在以前的ProxySQL版本中，要支持MySQL组复制(MGR,MySQL Group Replication)需要借助第三方脚本对组复制做健康检查并自动调整配置，但是从ProxySQL v1.4.0开始，已原生支持MySQL组复制的代理，在main库中也已提供mysql_group_replication_hostgroups表来控制组复制集群中的读、写组。 Admin> show tables ; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | ... | runtime_mysql_group_replication_hostgroups | ... | scheduler | +--------------------------------------------+ admin> show tables from monitor; +------------------------------------+ | tables | +------------------------------------+ | mysql_server_connect_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +------------------------------------+ 尽管已原生支持MGR，但仍然需要在MGR节点中创建一张额外的系统视图sys.gr_member_routing_candidate_status为ProxySQL提供监控指标。创建该视图的脚本addition_to_sys.zip我已上传。在后文需要创建该系统视图的地方，我会将这个脚本的内容贴出来。 本文先解释mysql_group_replication_hostgroups表中各字段的意义，然后按……
SE_You
2024-04-02
20 0 0 -
MySQL中间件之ProxySQL(14)：ProxySQL+PXC
1.ProxySQL+PXC 本文演示ProxySQL代理PXC(Percona XtraDB Cluster)的方法，不涉及原理，纯配置过程，所以如有不懂之处，请先掌握相关理论。 ProxySQL要代理PXC，需要使用ProxySQL额外提供的脚本/usr/share/proxysql/tools/proxysql_galera_checker.sh做健康检查并动态调整，通过Scheduler来调用实现。ProxySQL v2.0版本将原生支持Galera并增加相关表mysql_galera_hostgroups，到时就无需借用第三方脚本。 实验环境： Node HostName IP_address Node1 proxysql 192.168.100.21 Node2 pxc1 192.168.100.32 Node3 pxc2 192.168.100.33 Node4 pxc3 192.168.100.34 1.1 配置PXC 1.安装percona xtradb cluster 3个节点上(pxc1、pxc2、pxc3)都操作： 提供yum源： [percona] name=percona_repo baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch enabled = 1 gpgcheck = 0 ##安装 yum install Percona-XtraDB-Cluster-client-57 2.提供配置文件 第一个节点(pxc1)配置文件/etc/my.cnf： [mysqld] server-id=100 # 各节点不同 datadir=/data socket=/data/mysql.sock log-error=/data/error.log pid-file=/data/mysqld.pid log-bin=/data/master-bin log_slave_updates wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_name=pxc-cluster wsrep_cluster_address=gcomm://192.168.100.32,192.168.100.33,192.168.100.34 wsrep_node_name=pxc1 # 各节点不同 wsrep_node_address=192.168.100.32 # 各节点不同 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:P@ssword1! pxc_strict_mode=ENFORCING binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_loc……
SE_You
2024-04-01
50 0 0 -
MySQL中间件之ProxySQL(13)：ProxySQL集群
ProxySQL有原生的集群功能，但是这个原生的集群功能还正在试验阶段。本文会详细介绍这个原生集群的实现细节。 1.ProxySQL部署在哪 在拓扑结构中，ProxySQL部署在应用程序和MySQL集群的中间位置。应用程序向ProxySQL发起SQL语句，ProxySQL分析收到的SQL语句，进行匹配、重写等操作，然后路由给后端MySQL集群中的某实例。 如图： 上图描述的是多个application共用一个ProxySQL实例，但需求总是多变的。例如有些app比较繁忙，我们想要将这些繁忙的app使用的ProxySQL分离出来，让不同的application独立使用一个ProxySQL甚至一个ProxySQL集群，让那些不太繁忙的app共用一个ProxySQL。这种情形如下图： 还可以为每个app都配置一个ProxySQL，如下图。 这种配置的好处是明显的，没有单点故障，不需要额外的负载均衡，app+proxysql的节点可以轻松扩展。但是，也有缺点，各ProxySQL之间无法共享查询缓存。但无论如何，这是一种良好的配置方式。 此外，还可以使用多层结构，对ProxySQL群进行负载均衡。如下图： 上图几个注意点： 负载软件层，也可以使用ProxySQL对ProxySQL集群进行负载均衡，因为ProxySQL自身就是一个代理，而且是专门负责MySQL协议的代理。 在负载均衡层，要保证事务持久，不能让一个事务内的不同语句路由到不同的目标上，也就是说负载的目标是"有状态"的。因此在使用lvs/haproxy时，必须想办法保证事务持久性。通用性方法是，从多个ProxySQL节点中取出一个作为逻辑写节点(可以有backup)，haproxy/lvs通过端口(也可以用户名，但通常采用端口)代理的方式，强制将所有写语句路由到这个固定的ProxySQL节点上。 而通过ProxySQL来代理ProxySQL集群，因为ProxySQL内部支持事务持久(在mysql_users表中字段transaction_persistent控制持久性)。 综上分析，通过lvs/haproxy负载ProxySQL或者负载MySQL、Galera……
SE_You
2024-03-29
84 0 0 -
MySQL中间件之ProxySQL(12)：禁止多路路由
1.multiplexing multiplexing，作用是将语句分多路路由。开启了multiplexing开关，读/写分离、按规则路由才能进行。但有时候，有些语句要求路由到同一个主机组，甚至是同一个主机组中的同一个节点上。这时会自动禁用multiplexing。禁用multiplexing后，语句会根据同一个连接进行路由。 也就是说，在禁用multiplexing情况下，会将语句路由到同一个主机组中的同一个节点上。 什么情况下会禁用多路路由(disable multiplexing)？ 激活事务时 当某连接中激活了一个事务，则在该事务提交或回滚之前，multiplexing将一直被禁用。 表被锁 当执行了LOCK TABLE, LOCK TABLES 或 FLUSH TABLES WITH READ LOCK 时，将一直禁用multiplexing，直到执行UNLOCK TABLES。 使用GET_LOCK()时 如果执行了GET_LOCK()，将一直禁用multiplexing，且永不再次启用。 使用某些特定的会话/用户(session/user)变量 所有查询的query_digest部分包含了@的都将禁用multiplexing，且永不再次启用。 下面的命令是类似的情况，都会禁用multiplexing且不再次启用： SET SQL_SAFE_UPDATES=?,SQL_SELECT_LIMIT=?,MAX_JOIN_SIZE=? (mysql --safe-updates) SET FOREIGN_KEY_CHECKS SET UNIQUE_CHECKS SET AUTO_INCREMENT_INCREMENT (v 1.4.4+) SET AUTO_INCREMENT_OFFSET (v 1.4.4+) SET GROUP_CONCAT_MAX_LEN (v 1.4.4+) 但下面两个语句是例外，因为它们被硬编码为不禁用multiplexing： SELECT @@tx_isolation SELECT @@version 之所以要硬编码这两个例外，是因为很多应用程序在每个连接中都会执行这两个语句。 使用SQL_CALC_FOUND_ROWS 如果查询语句中包含了SQL_CALC_FOUND_ROWS，那么该连接上将禁用multiplexing且不再启用。 临时表 如果执行了CREATE TEMPORARY TABLE，那么该连接上将禁用multiplexing且不再启用。 使用PREPARE 如……
SE_You
2024-03-28
28 0 0 -
MySQL中间件之ProxySQL(11)：链式规则( flagIN 和 flagOUT )
1.理解链式规则 在mysql_query_rules表中，有两个特殊字段"flagIN"和"flagOUT"，它们分别用来定义规则的入口和出口，从而实现链式规则(chains of rules)。 链式规则的实现方式如下： 当入口值flagIN设置为0时，表示开始进入链式规则。如未显式指定规则的flagIN值，则默认都为0。 当语句匹配完当前规则后，将记下当前规则的flagOUT值，如果flagOUT值非空(NOT NULL)，则为该语句打上flagOUT标记。如果该规则的apply字段值不是1，则继续向下匹配。 如果语句的flagOUT标记和下一条规则的flagIN值不同，则跳过该规则，继续向下匹配。直到匹配到flagOUT=flagIN的规则，则匹配该规则。该规则是链式规则中的另一条规则。 直到某规则的apply字段设置为1，或者已经匹配完所有规则，则最后一次被评估的规则将直接生效，不再继续向下匹配。 通过下面两张图，应该很容易理解链式规则的生效方式。 必须注意，规则是按照rule_id的大小顺序进行的。且并非只有apply=1时才会应用规则，当无规则可匹配，或者某规则的flagIN和flagOUT值相同，都会应用最后一次被评估的规则。 以下几个示例，可以解释生效规则： # rule_id=3 生效 +---------+-------+--------+---------+ | rule_id | apply | flagIN | flagOUT | +---------+-------+--------+---------+ | 1 | 0 | 0 | 23 | | 2 | 0 | 23 | 23 | | 3 | 0 | 23 | NULL | +---------+-------+--------+---------+ # rule_id=2 生效 +---------+-------+--------+---------+ | rule_id | apply | flagIN | flagOUT | +---------+-------+--------+---------+ | 1 | 0 | 0 | 23 | | 2 | 0 | 23 | 23 | | 3 | 0 | 24 | NULL | +---------+-----……
SE_You
2024-03-27
19 0 0 -
MySQL中间件之ProxySQL(10)：读写分离方法论
1.不同类型的读写分离 数据库中间件最基本的功能就是实现读写分离，ProxySQL当然也支持。而且ProxySQL支持的路由规则非常灵活，不仅可以实现最简单的读写分离，还可以将读/写都分散到多个不同的组，以及实现分库sharding(分表sharding的规则比较难写，但也能实现)。 本文只描述通过规则制定的语句级读写分离，不讨论通过 ip/port, client, username, schemaname 实现的读写分离。 下面描述了ProxySQL能实现的常见读写分离类型。 1.1 最简单的读写分离 如图。 这种模式的读写分离，严格区分后端的master和slave节点，且slave节点必须设置选项read_only=1。在ProxySQL上，分两个组，一个写组HG=10，一个读组HG=20。同时在ProxySQL上开启monitor模块的read_only监控功能，让ProxySQL根据监控到的read_only值来自动调整节点放在HG=10(master会放进这个组)还是HG=20(slave会放进这个组)。 这种模式的读写分离是最简单的，只需在mysql_users表中设置用户的默认路由组为写组HG=10，并在mysql_query_rules中加上两条简单的规则(一个select for update，一个select)即可。 例如，下面实现的就是这种读写分离模式。 mysql_replication_hostgroups: +------------------+------------------+----------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+----------+ | 10 | 20 | cluster1 | +------------------+------------------+----------+ mysql_servers: +--------------+----------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+----------+------+--------+--------+ | 10 | master | 3306 | ONLINE | 1 | | 20 | slave1 | 3306 | O……
SE_You
2024-03-26
26 0 0 -
MySQL中间件之ProxySQL(9)：ProxySQL的查询缓存功能
ProxySQL支持查询缓存的功能，可以将后端返回的结果集缓存在自己的内存中，在某查询的缓存条目被清理(例如过期)之前，前端再发起同样的查询语句，将直接从缓存中取数据并返回给前端。如此一来，ProxySQL处理的性能会大幅提升，也会大幅减轻后端MySQL Server的压力。 1.开启query cache功能 ProxySQL的查询缓存功能由mysql_query_rules表中的cache_ttl字段控制，该字段设置每个规则对应的缓存时长，时间单位为毫秒。 当前端发送的SQL语句命中了某规则后(严格地说，是最后应用的那条规则，因为链式规则下会操作多个规则)，如果这个规则同时还设置了"cache_ttl"字段的值，则这个SQL语句返回的结果将会被缓存一定时间，过期后将等待专门的线程(purge线程)来清理。 例如： delete from mysql_query_rules; select * from stats_mysql_query_digest_reset where 1=0; insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,cache_ttl) values(1,1,1,10,"^select .* test1.t1",20000); load mysql query rules to runtime; save mysql query rules to disk; select rule_id,destination_hostgroup,match_pattern,cache_ttl from mysql_query_rules; 这表示匹配上述规则(查询test1.t1表)的查询结果集将在ProxySQL上缓存20秒。 可以执行下面的语句进行测试，每个语句循环执行10次： # 在bash下执行 proc="mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e" for ((i=0;i<10;i++));do $proc "select * from test1.t1;" $proc "select * from test1.t2;" $proc "select * from test2.t1;" $proc "select * from test2.t2;" done 再去查看规则统计表： Admin> select * from stats_mysql_query_rules; +---------+------+ | rule_id | ……
SE_You
2024-03-25
20 0 0 -
MySQL中间件之ProxySQL(8)：SQL语句的重写规则
1.为什么要重写SQL语句 ProxySQL在收到前端发送来的SQL语句后，可以根据已定制的规则去匹配它，匹配到了还可以去重写这个语句，然后再路由到后端去。 什么时候需要重写SQL语句？ 对于下面这种简单的读、写分离，当然用不上重写SQL语句。 这样的读写分离，实现起来非常简单。如下： mysql_replication_hostgroups: +------------------+------------------+----------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+----------+ | 10 | 20 | cluster1 | +------------------+------------------+----------+ mysql_servers: +--------------+----------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+----------+------+--------+--------+ | 10 | master | 3306 | ONLINE | 1 | | 20 | slave1 | 3306 | ONLINE | 1 | | 20 | slave2 | 3306 | ONLINE | 1 | +--------------+----------+------+--------+--------+ mysql_query_rules: +---------+-----------------------+----------------------+ | rule_id | destination_hostgroup | match_digest | +---------+-----------------------+----------------------+ | 1 | 10 | ^SELECT.*FOR UPDATE$ | | 2 | 20 | ^SELECT | +---------+-----------------------+----------------------+ 但是，复杂一点的，例如ProxySQL实现sharding功能。对db1库的select_1语句路由给hg=10的组，将db2库的select_2语句路由给hg=20的组，将db3库的select_3语句路由给hg=30的组。 ……
SE_You
2024-03-22
24 0 0
包含标签：MySQL 的文章
