mysql多条件排序查询
概述数据库中的数据直接呈现出来一般不是我们想要的,所以我们上两节演示了如何对数据进行过滤的方法。除了对数据进行过滤,我们可能还需要对数据进行排序,比如想从列表中了解消费最高的项,就可能需要对金额字段做...
2024.11.12前段时间,有测试人员联系我,寻求帮助,让我帮忙看看,测试环境的一台mysql数据库不断的重启,导致他们的测试无法进行,时间拖的长了,会影响上线进度。
下面就来说说现象在一个测试环境,mysql5.7部署在centos7.4的系统上,测试人员用LoadRunner进行应用的一个压测测试,压测并发为128,启动LoadRunner过几秒,LoadRunner日志就报错,显示mysql服务断开了,过几秒又恢复,然后又断开,异常诡异。
排查定位于是就登录到服务器,看一下数据库服务器的内存,内存已经用完了,紧接着就看centos的系统日志(/var/log/messages),在这个日志里发现mysql服务因为OOM,导致进程被系统给kill掉了,然后因为mysql服务有守护进程存在,又被自动启动了。
一个经验丰富的mysql数据库运维人员,应该很快就知道什么原因导致OOM了。第一:mysql数据库的buffer pool内存参数配置不合理第二:mysql数据库的session初始化内存参数配置不合理第三:数据库连接总数配置不合理
测试环境的数据库服务器内存为4G,在这里就不谈,为什么测试人员要在这个服务器上做压测,其实做应用压测,应该要用准用的压测环境,而不是随便找个环境就压测,不展开说了。
模拟复盘测试环境mysql5.7,centos7.7,内存2G
为了更好的模拟复盘上述故障,这里关闭掉模拟环境的swap,关闭步骤如下所示
[root@localhost data]# free -mtotalusedfreesharedbuff/cacheavailableMem:2124 565 6409 9171398Swap:204702047[root@localhost data]# swapon -s文件名类型大小已用权限/dev/dm-1partition2097148 264 -2[root@localhost data]# swapoff /dev/dm-1[root@localhost data]# free -mtotalusedfreesharedbuff/cacheavailableMem:2124 254 9449 9251709Swap: 000从上面free -m结果中,可以看到swap已经变成0了。
释放cache占用的内存
[root@localhost data]# sync[root@localhost data]# echo 1 > /proc/sys/vm/drop_caches ;[root@localhost data]# free -mtotalusedfreesharedbuff/cacheavailableMem:2124 25417849841748Swap: 000可以看到目前剩余的内存为1784M,mysql数据库占用内存主要有2大块,第一是:buffer pool占用,第二是:初始化连接占用的内存
在这里设置mysql的buffer pool为1500M,会话的参数设置如下
read_buffer_size = 32Mread_rnd_buffer_size = 32Msort_buffer_size = 32Mtmp_table_size = 32Mmax_heap_table_size=32Mjoin_buffer_size=32M然后5个连接,开始做大查询操作,没过多久,mysql进程就因为OOM被kill了
Aug 31 05:37:40 localhost kernel: Out of memory: Kill process 2534 (mysqld) score 658 or sacrifice childAug 31 05:37:40 localhost kernel: Killed process 2534 (mysqld), UID 1001, total-vm:1825792kB, anon-rss:654388kB, file-rss:0kB, shmem-rss:0kBmysql守护进程就开始启动mysql服务
/u02/mysql/bin/mysqld_safe: 行 198:2534 已杀死nohup /u02/mysql/bin/mysqld --defaults-file=/u02/conf/my3308.cnf --basedir=/u02/mysql --datadir=/u02/data/3308 --plugin-dir=/u02/mysql/lib/plugin --user=mysql --log-error=/u02/log/3308/error.log --open-files-limit=65535 --pid-file=/u02/run/3308/mysqld.pid --socket=/u02/run/3308/mysql.sock --port=3308 < /dev/null > /dev/null 2>&12020-08-30T21:37:40.375749Z mysqld_safe Number of processes running now: 02020-08-30T21:37:40.407781Z mysqld_safe mysqld restarted2020-08-30T21:37:40.666886Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2020-08-30T21:37:40.667059Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled2020-08-30T21:37:40.667112Z 0 [Note] /u02/mysql/bin/mysqld (mysqld 5.7.26-log) starting as process 2954 ...2020-08-30T21:37:40.782412Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html2020-08-30T21:37:40.782684Z 0 [Note] InnoDB: PUNCH HOLE support available2020-08-30T21:37:40.782729Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2020-08-30T21:37:40.782754Z 0 [Note] InnoDB: Uses event mutexes2020-08-30T21:37:40.782772Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier2020-08-30T21:37:40.782788Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.112020-08-30T21:37:40.782841Z 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB2020-08-30T21:37:40.784518Z 0 [Note] InnoDB: Number of pools: 12020-08-30T21:37:40.784865Z 0 [Note] InnoDB: Using CPU crc32 instructions2020-08-30T21:37:40.789314Z 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M2020-08-30T21:37:40.834948Z 0 [Note] InnoDB: Completed initialization of buffer pool2020-08-30T21:37:40.843612Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().2020-08-30T21:37:40.859028Z 0 [Note] InnoDB: Highest supported file format is Barracuda.2020-08-30T21:37:40.863176Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 57073942292020-08-30T21:37:40.863221Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 57073942382020-08-30T21:37:40.863231Z 0 [Note] InnoDB: Database was not shutdown normally!2020-08-30T21:37:40.863239Z 0 [Note] InnoDB: Starting crash recovery.2020-08-30T21:37:40.901955Z 0 [Note] InnoDB: Last MySQL binlog file position 0 43848, file name binlog.0000252020-08-30T21:37:41.075805Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"2020-08-30T21:37:41.075860Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables2020-08-30T21:37:41.075952Z 0 [Note] InnoDB: Setting file ‘/u02/log/3308/iblog/ibtmp1‘ size to 12 MB. Physically writing the file full; Please wait ...2020-08-30T21:37:41.254016Z 0 [Note] InnoDB: File ‘/u02/log/3308/iblog/ibtmp1‘ size is now 12 MB.2020-08-30T21:37:41.255390Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.2020-08-30T21:37:41.255421Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.2020-08-30T21:37:41.256171Z 0 [Note] InnoDB: Waiting for purge to start2020-08-30T21:37:41.307237Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 57073942382020-08-30T21:37:41.308291Z 0 [Note] Plugin ‘FEDERATED‘ is disabled.2020-08-30T21:37:41.310625Z 0 [Note] InnoDB: Loading buffer pool(s) from /u02/log/3308/iblog/ib_buffer_pool2020-08-30T21:37:41.310785Z 0 [Note] InnoDB: Buffer pool(s) load completed at 2008315:37:41 (/u02/log/3308/iblog/ib_buffer_pool was empty)2020-08-30T21:37:41.314568Z 0 [Note] Recovering after a crash using /u02/log/3308/binlog/binlog2020-08-30T21:37:41.314730Z 0 [Note] Starting crash recovery...2020-08-30T21:37:41.314842Z 0 [Note] Crash recovery finished.2020-08-30T21:37:41.346280Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.2020-08-30T21:37:41.346337Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.2020-08-30T21:37:41.349079Z 0 [Warning] CA certificate ca.pem is self signed.2020-08-30T21:37:41.349341Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.2020-08-30T21:37:41.350297Z 0 [Note] Server hostname (bind-address): ‘0.0.0.0‘; port: 33082020-08-30T21:37:41.350399Z 0 [Note]- ‘0.0.0.0‘ resolves to ‘0.0.0.0‘;2020-08-30T21:37:41.350475Z 0 [Note] Server socket created on IP: ‘0.0.0.0‘.2020-08-30T21:37:41.376794Z 0 [Note] Failed to start slave threads for channel ‘‘2020-08-30T21:37:41.397237Z 0 [Note] Event Scheduler: Loaded 0 events2020-08-30T21:37:41.397480Z 0 [Note] /u02/mysql/bin/mysqld: ready for connections.Version: ‘5.7.26-log‘socket: ‘/u02/run/3308/mysql.sock‘port: 3308Source distribution正在连接的会话自动中断
ERROR 2013 (HY000): Lost connection to MySQL server during querymysql>在配置mysql参数,一定要考虑以下3个因素1.业务连接预期总数2.会话初始化内存3.buffer pool缓冲器大小
下面先用sql查询一下会话的内存总大小和数据库buffer pool大小
mysql> select (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;+--------------+| MEMORY_MB|+--------------+| 584.00000000 |+--------------+1 row in set (0.00 sec)mysql> select (@@read_buffer_size+@@read_rnd_buffer_size+@@sort_buffer_size+@@tmp_table_size+@@join_buffer_size+@@binlog_cache_size)/1024/1024 as MB;+--------------+| MB|+--------------+| 160.03125000 |+--------------+1 row in set (0.00 sec)mysql的buffer pool加上会话内存总大小不超过服务器内存总大小的80%。
假设,服务器内存为32G,mysql数据库内存设置就不要超过26G,如果buffer pool设置为20G,每个会话内存为160M,则6G能提供6 *1024 /160 =38 个安全连接,超过了,可能造成内存不够,用swap分区。
概述数据库中的数据直接呈现出来一般不是我们想要的,所以我们上两节演示了如何对数据进行过滤的方法。除了对数据进行过滤,我们可能还需要对数据进行排序,比如想从列表中了解消费最高的项,就可能需要对金额字段做...
2024.11.12有一台预上线的服务器最近在做压力测试,也引发了一系列的相关问题,排查思路可以提供参考。问题的起因就是收到同事提醒,根据监控报警提示,磁盘空间满了。上面有一个MySQL服务,现在已经写入不了数据了。>>...
2024.11.13概述分享一个我平时用来做mysql数据库巡检的脚本,该脚本主要用于大致诊断MYSQL主机和数据库配置及性能收集,然后会生成一个html报告,通过html来直观获得数据库相关信息。下面介绍下脚本内容,文...
2024.11.12正常情况可以多个like 用and or not条件进行查询,但对于一句话里面不相关词组合查询不好操作(当然es数据库天然就是可以解决这个问题但,倒排索引直接用于搜索)这里介绍like多查询条件语句:...
2024.11.12MySQL 的默认排序规则取决于字符集。对于拉丁字母表,MySQL 的默认排序规则是“latin1”,它是一种基于 ASCII 码的排序规则。对于 Unicode 字符,MySQL 的默认排序规则是“...
2024.11.15