- 浏览: 568289 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (262)
- oracle (43)
- 打包 (3)
- linux (33)
- nginx (20)
- ftp (1)
- tomcat (8)
- mysql (22)
- plsql (4)
- jms (3)
- nosql (2)
- 运维监控 (5)
- 数据采集 (0)
- 测试 (2)
- jvm (5)
- 自动化数据库迁移 (2)
- 密码 (1)
- java代码 (7)
- java多线程,线程安全 (8)
- xml (1)
- spring (6)
- svn (2)
- oracle备份 (1)
- oracle 诊断 (3)
- oracle 归档 (4)
- java 虚拟机(JVM) (2)
- hibernate (5)
- activemq (2)
- http (2)
- linux_shell (9)
- python (3)
- ant (1)
- oracle rac (1)
- php (2)
- js (1)
- mongo (1)
- java 反射 (1)
- jira (1)
- 打包 maven (1)
- zabbix (2)
- lvs+keepalive (1)
- 防火墙 (1)
- memcache (2)
- redis (1)
最新评论
-
di1984HIT:
学习了~~~~
服务器大量TIME_WAIT -
springdata_spring:
可以参考最新的文档:如何在eclipse jee中检出项目并转 ...
maven常用命令 -
李小斌_2014:
我也遇到了,现在完美解决。
ORA-01422: 实际返回的行数超出请求的行数 -
啸风8023:
...
tomcat启动报错 -
fke153:
这个问题确实很坑人啊,学习了
ORA-01422: 实际返回的行数超出请求的行数
遇到MySQL硬盘的问题,解决方法来自:http://blog.itpub.net/26355921/viewspace-1273862/
摘抄如下:
摘抄如下:
突然接到开发打来电话说MySQL数据库执行SQL报错,让排查一下原因。 登陆MySQL服务器看了一眼错误日志: 140904 12:06:20 [ERROR] /usr/local/mysql5.5/bin/mysqld: Incorrect key file for table '/tmp/#sql_5608_1.MYI'; try to repair it 140904 12:06:20 [ERROR] Got an error from unknown thread, /tmp/mysql5.5_install/tmp/mysql-5.5.30/storage/myisam/mi_write.c:223 看到/tmp下的问题,先看一眼tmpdir: mysql> show variables like 'tmpdir'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | tmpdir | /tmp | +---------------+-------+ 1 row in set (0.00 sec) 说/tmp下有表需要修复,通过tmpdir可知,是临时排序表所致。在网上查了一下,说产生这个问题是由于tmpdir目录下空间不足。 查看一下磁盘空间: [root@localhost data]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 15G 31G 33% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data /tmp属于/ 根目录下,空间还有31G,觉得不可能。 查看慢日志,找到一条很可疑的语句,拿50万的数据来排序,还有的语句拿200多万的数据来排序,而且从时间上也与开发说的时间很吻合: # Time: 140904 16:13:50 # User@Host: staff_reader[staff_reader] @ [10.35.15.68] # Query_time: 133.653564 Lock_time: 0.000186 Rows_sent: 5384178 Rows_examined: 20886933 use hotel_inventory; SET timestamp=1409818430; SELECT col1,col2,col3....,col7 FROM table1 nolock WHERE available_time >= '2014-09-03' AND available_time <= DATE_ADD('2014-09-03', interval 91 day) order by hotel_id,room_type_id,available_time; 看一下该语句访问的表的具体情况: 表行数为20886994: mysql> select count(*) from table1; +----------+ | count(*) | +----------+ | 20886994 | +----------+ 表大小为7G: [root@localhost]# du -sch *|grep sum_inv_room.ibd 6.9G sum_inv_room.ibd 该机器上有4个实例: [root@192.168.97.149 hotel_inventory]# ps -ef|grep mysqld_safe root 21185 1 0 2013 ? 00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6025/etc/my6025.cnf root 22525 1 0 2013 ? 00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6027/etc/my6027.cnf root 24275 1 0 2013 ? 00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6024/etc/my6024.cnf root 37864 1 0 2013 ? 00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6026/etc/my6026.cnf root 60463 5879 0 16:18 pts/6 00:00:00 grep mysqld_safe 总大小有30G左右,都是同时抽取数据: [root@192.168.97.149 hotel_inventory]# du -sch /data/mysql602*/data/hotel_inventory/* |grep sum_inv_room.ibd 6.9G /data/mysql6024/data/hotel_inventory/sum_inv_room.ibd 7.1G /data/mysql6025/data/hotel_inventory/sum_inv_room.ibd 7.9G /data/mysql6026/data/hotel_inventory/sum_inv_room.ibd 6.5G /data/mysql6027/data/hotel_inventory/sum_inv_room.ibd 既然看到这些现象,那么我肯定就是这条语句同时执行所致。这时候给开发反馈,而开发说[staff_reader] @ [10.35.15.68] 这个用户不是他们那边的。没办法了,继续找。我就手动那上面那条语句去执行,发现根本没产生多少临时文件。这时候可以断定不是这个语句造成的了。 所以这个时候只能等问题再次重现。看了一下磁盘空间,正常情况下,/ 根目录可用空间是31G,然后我就写了个脚本一直监测可用空间是否是31G,如果不是,就给我QQ邮箱发邮件。 过了一会,果然收到邮件了,这时候上服务器看磁盘空间,在持续增长: 查看磁盘空间情况: [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 30G 17G 65% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 33G 14G 71% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 35G 11G 77% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# du -sch * 88K mha4mysql-node-0.53 2.1G #sql_5608_0.MYD 4.0K #sql_5608_0.MYI 3.6G #sql_5608_1.MYD 4.0K #sql_5608_1.MYI 1.9G #sql_5b44_0.MYD 4.0K #sql_5b44_0.MYI 4.9G #sql_5b44_1.MYD 4.0K #sql_5b44_1.MYI 2.0G #sql_621a_0.MYD 4.0K #sql_621a_0.MYI 749M #sql_621a_1.MYD 4.0K #sql_621a_1.MYI 2.2G #sql_9753_0.MYD 4.0K #sql_9753_0.MYI 4.2G #sql_9753_1.MYD 4.0K #sql_9753_1.MYI 22G total [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 37G 9.5G 80% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 41G 5.2G 89% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 46G 29M 100% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 46G 22M 100% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 46G 0 100% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data 空间满的同时,看到/data/mysql6025/log/mysqld.err中又出现错误: 140904 16:03:59 [ERROR] /usr/local/mysql5.5/bin/mysqld: Incorrect key file for table '/tmp/#sql_5608_1.MYI'; try to repair it 140904 16:03:59 [ERROR] Got an error from unknown thread, /tmp/mysql5.5_install/tmp/mysql-5.5.30/storage/myisam/mi_write.c:223 通过慢日志来看,又出现了上面那条很可疑的语句。可是之前已经排除了其的可能性,那就只能继续找,发现一条很简短的语句,看起来没什么,但是执行时间很长: SELECT MIN(sum_inv_room_id), MAX(sum_inv_room_id) FROM (select s.*, 20140909 as dw_insert_dt_wid from sum_inv_room s WHERE (1 = 1) ) AS t1; 出错的这段时间,基本上就这两条SQL在跑。 报错之后,磁盘空间逐渐恢复: ........................................................ [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 38G 8.6G 82% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 log]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 15G 31G 33% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data 最后空间使用率恢复正常。 于是手动拿这个语句执行了一下: SELECT MIN(col1), MAX(col1) FROM (select s.*, 20140909 as dw_insert_dt_wid from table1 s WHERE (1 = 1) ) AS t1; 果然看到磁盘空间立马飙升,找到原因所在。单条语句执行,看到其产生的临时文件最大到了10多G。一共31G的空间,4个实例,都产生10多G,当然扛不住。 (至于具体为什么这样的语句会产生这么大的临时文件,本人对其中的原理不是太理解,先做此记录。) 经过与开发沟通,开发说那个是定时抽取数据。而且机器上的4个实例是同时开始的。 所以给出以下解决办法: 1.将MySQL中参数tmpdir设置到大的目录下去,但是这个需要参数是read_only变量,需要重启数据库 2.将这4个语句串行执行 3.修改抽取数据的SQL 最后开发选择了第2种方法,将定点抽取数据的SQL串行执行,问题暂时得到解决,至少半个多月都没再出现这个问题了。
发表评论
-
my.cnf 详解
2016-03-03 18:56 662MySQL 5.5.13 参数说明: ... -
mysql 导入导出
2015-11-10 10:38 565CREATE DATABASE p_ticket DE ... -
mysql备份
2015-05-28 14:47 574#!/bin/bash TARGETDIR=/BACKU ... -
./mysql_install_db FATAL ERROR: Could not find ./bin/my_print_defaults
2015-05-18 10:01 1440./mysql_install_db --user=mys ... -
mysql update sql
2015-01-22 16:55 530select userno,sum(small_postt ... -
mysqldump 操作
2015-01-15 10:24 973mysql导出某个表的表结构 mysqldump ... -
mysql 删除从库
2014-08-11 13:56 1371mysql> stop slave; Q ... -
mysql 主从文件配置,my.cnf
2014-04-01 15:49 2656主库 [client] port = 1231 so ... -
Lock wait timeout exceeded; try restarting transaction一些信息
2013-09-16 09:29 1421引用自 http://blog.csdn.net/beenin ... -
ERROR 2002 (HY000): Can't connect to local
2013-06-11 19:04 1121错误ERROR 2002 (HY000): Can't con ... -
mysql 源代码安装
2013-06-11 16:11 931http://www.cnblogs.com/fly1988h ... -
mysq理解
2013-06-07 18:42 939http://www.cnblogs.com/hustcat/ ... -
mysql 执行计划
2013-06-07 14:59 833附件是mysql的执行计划 -
mysql 添加,删除,调整字段
2013-05-22 09:57 1093ALTER TABLE — 更改表属性 添加字段: ... -
ERROR 1201 (HY000): Could not initialize master info structure; more error messa
2013-04-07 17:57 2597mysql主从复制时从库执行 change master to ... -
mysql参数
2013-02-27 15:57 918当前的连接数: mysql> show statu ... -
mysql常见命令
2012-11-19 14:17 1014mysql>show variables like ... -
ERROR 1130 (HY000): Host 'localhost'
2012-11-19 11:47 1069ERROR 1130 (HY000): Host 'local ... -
mysql命令
2012-07-11 14:51 945mysql rpm 安装 /usr/bin/mysqla ... -
mysql通过二进制恢复删除记录
2012-05-16 11:02 1527首先确定是否开启二进制 my.cnf / my.ini文件 ...
相关推荐
在MySQL数据库管理中,"Incorrect key file for table" 是一种常见的错误信息,它通常表示某个数据表的索引文件可能已经损坏或不匹配。这种问题可能是由于硬盘故障、不正常的数据库关闭、复制过程中数据损坏等原因...
//#define CONFIG_IPS_LEVEL_2 1 //enable this to set default IPS mode to IPS_LEVEL_2 #endif #define SUPPORT_HW_RFOFF_DETECTED 1 #define CONFIG_LPS 1 #define CONFIG_BT_COEXIST 1 //befor link #...
In file included from ./include/alsa/pcm_macros.h:13:0, from ./include/alsa/pcm.h:116, from ./include/alsa/asoundlib.h:48, from record.c:13: /home/rootroot/sogou/toolchain/include/sys/poll.h:1:2: ...
SCGCQ00289585 Defect Command line generated along with press any key in help file operation SCGCQ00315805 Defect Change the UI fro "adpBBucmd getbbumodes a0" command SCGCQ00317547 Defect MegaCLI ...
MySQL中的`tmp_table_size`参数是用来设定在内存中创建的临时表的最大大小。临时表通常在执行复杂的SQL查询,如JOIN、GROUP BY、DISTINCT等操作时生成,以辅助数据处理。当`tmp_table_size`设置得较低时,如果查询...
1/ Update for Delphi XE6 _______________________________________________________________________________ Update 1.12 1/ Update for Delphi XE5 2/ New component TRxPanel added ______________________...
Added TFlexFileFormat.StreamSupport property (default True), which difines, is it necessary to open file stream or give the file name only. - FIX: If the TCustomProp.Assign method calls when the ...
3. There is no point in arguing about it, because it is ______ a question of procedure. 4. The ______ of working process freed the workers from heavy labor. **答案**: simple; simplified; simply; ...
异常详细信息: EnterpriseDT.Net.Ftp.FTPException: plan/??_SV_201011121502.DNJH: The filename, directory name, or volume label syntax is incorrect
Fixed an issue which made it impossible to use the search box in the formatting styles editor for some users. SP-7549 : Fixed an issue that would cause suggestions to break if using USE HINT within ...
1. Add EXTI8, EXTI9 on PC8, PC9, only for EXTI test. 2. Modify some comments in LCD5110_lib. 3. (in main.c) Modifiy tasks' priority LED_TASK_Prio 1 -> 5 START_TASK_Prio 2 -> 10 4. (in main.c) ...
3. There is no point in arguing about it, because it is ______ a question of procedure. - 答案:simply 4. The ______ of working process freed the workers from heavy labor. - 答案:simplification ##...
Unofficial version Rx library for Delphi 2005/2006/2007/2009/2010/XE/XE2/XE3 DISCLAIMER: * This software is provided "as is" and is without warranty of any kind. The author(s) of this software does...
1. **错误代码:530 Login incorrect.** **解决方法**:检查虚拟用户的家目录是否正确配置。 ```bash # vi /etc/passwd virtual:x:1001:1001::/home/ftpsite:/bin/bash ``` 2. **错误代码:500 OOPS: ...
Support for formatting scripts containing SQLCMD mode and "GO 10" syntax New format action for adding/removing AS keyword for table/view alias definitions Simplified the parentheses "Place on new line...
Fix for incorrect indentation when using UNION Fix for missing space in ORDER BY between function call and DESC keyword Fix for extra space being inserted between " Fix for extra space being inserted ...
for Delphi 2005/2006/2007/2009/2010/XE/XE2/XE3 DISCLAIMER: * This software is provided "as is" and is without warranty of any kind. The author(s) of this software does not warrant, guarantee or make...
Added info.gam (effective number of parameters) field for rbf_fs_2 and rbf_rr_2 methods. Some typos fixed in manual. Version 2.1 June 15 1999 Fixed some bugs in meth/rbf_fs_2.m. Could cause ...
MySQL错误1033 "Incorrect information in file: 'xxx.frm'" 是一个常见的数据库问题,通常发生在尝试打开或恢复MySQL表时。此错误表明数据库系统无法识别或解析表的`.frm`文件,`.frm`文件存储了表的结构信息。在本...