MYSQL
文章平均质量分 74
昱桑~
敢问路在何方~
展开
-
聊一聊搞技术那点事吧~
问题,当时也之间想到了字符集问题,也是很快就解决了,但在自动化工具面前,自己又给自己好好的上了一课。北京4月的疫情起来后就居家了,直到6月底才回来,因为一些其他事,这事最近又被提上日程了,但之前的困惑还是没有解决,而这事又被催促了多次,自己推脱的已经没有任何借口可说了(不过平时是真的有点小忙),这周五决定还是把他解决掉吧。说了这么多,举个简单的例子来说就是好比你是一个狙击手,你练了很多年的静止射靶,堪称枪枪10环,但直到有一天,你的射击目标不在静止了,你发现你就不会了~...原创 2022-07-17 23:10:04 · 113 阅读 · 1 评论 -
mysql 强同步退化成异步的问题
某日收到线上告警短信,有一实例主备同步发生退化(强同步变成异步),经过一番排查,除该时段有一些稍高于往日的sql请求,其他并未有异常。因为该主备mysql同步也符合原生同步的特性,所以排查了网络。使用sar命令查到了网络流量的异常:从绑定网卡bond1的接收和发送来看,都远高于前后数据,这里看下2-5列字段含义:rxpck/s 每秒接收到的包数txpck/s 每秒传输的包数rxkB/s 每秒收到的KB数txkB/s 每秒传输的KB数...原创 2022-06-15 08:33:19 · 315 阅读 · 0 评论 -
Slave failed to initialize relay log info structure from the repository, Error_code: MY-013124
架构:3节点MGR,单主模式现象:在第三个节点启动组复制时报出:Slave failed to initialize relay log info structure from the repository, Error_code: MY-013124错误,errlog日志显示如下:022-02-28T11:48:27.033637Z 8 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatic原创 2022-02-27 21:18:35 · 1557 阅读 · 0 评论 -
ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed‘, data_len: 67, event_type: 35
这是一个使用错误的mysql版本的工具导致的低级问题:我的mysql版本是8.0.17,由于需要单独安装mysql-devel,为了省事,结果从iso镜像里安装了低版本的rpm包,结果rpm安装后影响了原有的8.0.17版本的环境变量,不知不觉的任何mysql命令都是低版本的了:[root@mgr1 data]# mysql -VLogging to file '/mysqldata/citicsql/tee.log'mysql Ver 14.14 Distrib 5.1.73, for re.原创 2021-08-06 17:34:53 · 702 阅读 · 0 评论 -
innodb_buffer_pool_instances
The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,by reducing contention as different threads read and w...原创 2021-08-04 13:56:16 · 5427 阅读 · 0 评论 -
max_allowed_packet && net_buffer_length
The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default is 4MB.一个数据包或任何生成/中间字符串的最大大小,或mysql_stmt_send_long_data()C API函数发送的任何参数。默认值为4MB。The packet m...原创 2021-08-04 12:24:24 · 537 阅读 · 0 评论 -
删除主键导致使用普通索引失效(覆盖索引失效) 干货篇
本文用实验验证删除主键导致普通索引失效。1、查看实验表结构及当前数据行数:2、监控实验表当前索引使用情况:3、走普通索引查询:可以看到,索引modelid计数加1:4、走覆盖索引查询:可以看到,索引modelid计数再次加1:5、删除主键,观察索引使用情况变化:主键 删除共用26秒多,删除的过程中,索引监控视图中会显示一个时刻在滚动的数字,这个索引没有名字,但经过多次测试发现数字的最大值就是表的总行数(),而后消失,最终...原创 2021-07-21 16:54:34 · 508 阅读 · 0 评论 -
Metadata Locking
MySQL uses metadata locking to manage concurrent access to database objects and to ensuredata consistency. Metadata locking applies not just to tables, but also to schemas, stored programs (procedures, functions, triggers, and scheduled events), and table原创 2021-07-11 19:55:38 · 176 阅读 · 0 评论 -
InnoDB Limits
This section describes limits forInnoDBtables, indexes, tablespaces, and other aspects of theInnoDBstorage engine.本节介绍InnoDB表、索引、表空间和InnoDB存储引擎的其他方面的限制。A table can contain a maximum of 1017 columns (raised in MySQL 5.6.9 from the earlier limit of 1...原创 2021-06-28 23:45:28 · 166 阅读 · 0 评论 -
Limits on Table Column Count and Row Size
This section describes limits on the number of columns in tables and the size of individual rows.本节介绍对表中列数和单行大小的限制。 Column Count Limits Row Size Limits Column Count LimitsMySQL has hard limit of 4096 columns per table, but the effective max原创 2021-06-28 22:27:27 · 127 阅读 · 0 评论 -
Limits on Table Size
The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. For up-to-date information operating system file size limits, refer to the documentation specific to you原创 2021-06-28 12:31:43 · 108 阅读 · 0 评论 -
innodb_flush_log_at_trx_commit
Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but th原创 2021-06-26 16:19:58 · 2494 阅读 · 0 评论 -
mysql 3节点MGR升级
mysql 3节点mgr升级mgr1:备节点,mgr2:主节点,mgr3:备节点 (先升级mgr1和mgr3)升级目标:8.0.16--->8.0.17升级过程中,mgr1节点的mysqld1.log日志输出如下:2021-03-06T09:49:35.822817Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the原创 2021-03-05 18:34:55 · 592 阅读 · 1 评论 -
Mysql Optimizing SQL Statements (一)
8.2 Optimizing SQL Statements The core logic of a database application is performed through SQL statements, whether issued directly through an interpreter or submitted behind the scenes through an API. The tuning guidelines in this section help to sp..原创 2021-02-01 16:23:16 · 972 阅读 · 0 评论 -
Mysql Optimization Overview
8.1 Optimization Overview Database performance depends on several factors at the database level, such as tables, queries,and configuration settings. These software constructs result in CPU and I/O operations at the hardware level, which you must mini...原创 2021-01-10 19:09:17 · 168 阅读 · 0 评论 -
Mysql Optimizing Memory Use
8.12.4 Optimizing Memory Use8.12.4.1 How MySQL Uses Memory MySQL allocates buffers and caches to improve performance of database operations. The default configuration is designed to permit a MySQL server to start on a virtual machine that has approxi..原创 2021-01-10 13:58:26 · 346 阅读 · 0 评论 -
Optimizing the MySQL Server
8.12 Optimizing the MySQL ServerThis section discusses optimization techniques for the database server, primarily dealing with system configuration rather than tuning SQL statements. The information in this section is appropriate forDBAs who want to ens.原创 2021-01-10 11:11:06 · 299 阅读 · 0 评论 -
Mysql OPTIMIZE TABLE
13.7.2.4 OPTIMIZE TABLE SyntaxOPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessin原创 2021-01-08 23:08:45 · 2351 阅读 · 0 评论 -
Mysql undo
14.6.3.4 Undo Tablespaces Undo tablespaces contain undo logs, which are collections of undo log records that contain information about how to undo the latest change by a transaction to a clustered index record.Undo logs exist within undo log segments, ..原创 2021-01-07 13:53:26 · 368 阅读 · 0 评论