我:
SELECT ai.`code`, c.mobilePhone
,co.`name`,cc.createdTime
FROM
ClientCoupon AS cc
INNER JOIN ActivityInfo AS ai ON ai.sn = cc.activitySn
INNER JOIN Client AS c ON cc.clientSn = c.sn
INNER JOIN Coupon AS co ON co.sn = cc.couponSn ,
ClientCoupon
WHERE 1 = 1
AND co.sn not in (3, 5, 6)
AND ai.code in ('10000006', '10000007', '10000008', '10000001', '10000002')
AND ai.partStartTime <= '2015-12-18 16:42:00'
AND ai.partEndTime >= '2015-12-18 16:42:00'
ORDER BY co.amount DESC, cc.createdTime DESC
limit 0, 10000;
mysql 排序导致内存使用高,且磁盘不断增大;
jrhdpt02:/root# free -m
total used free shared buffers cached
Mem: 15948 11981 3966 0 304 7918
-/+ buffers/cache: 3758 12189
Swap: 4095 0 4095
jrhdpt02:/root# free -m
total used free shared buffers cached
Mem: 15948 11981 3966 0 304 7918
-/+ buffers/cache: 3758 12189
Swap: 4095 0 4095
为什么呢？
jrhdpt02:/tmp# ls -ltrh
total 1.7G
srwxrwxrwx 1 root root 0 Mar 19 2015 qtsingleapp-aegiss-a5d2-0
srwxr-x--- 1 root root 0 Mar 19 2015 Aegis-<Guid(5A2C30A2-A87D-490A-9281-6765EDAD7CBA)>
srwxr-x--- 1 root root 0 Mar 19 2015 qtsingleapp-aegisG-46d2-0
-rw-rw-r-- 1 zabbix zabbix 5.4K Dec 13 04:04 zabbix_agentd.log
-rw-rw---- 1 mysql mysql 1.0K Dec 19 09:38 #sql_3d9c_0.MYI
-rw-rw---- 1 mysql mysql 1.7G Dec 19 09:39 #sql_3d9c_0.MYD
mysql> show variables like '%tmp%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| default_tmp_storage_engine | InnoDB |
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+----------------------------+----------+
5 rows in set (0.01 sec)
tmpdir 系统变量执行一个专用的系统 有大的空闲空间,变量值可以列出几个路径以循环的方式。
排序前:
jrhdpt02:/# ls -ltr | grep -v data | awk '{print $NF}' | while read A; do du -sh $A; done
du: cannot access `4194416': No such file or directory
4.0K srv
4.0K mnt
4.0K media
16K lost+found
4.0K selinux
2.0G usr
653M var
24M boot
4.1G swapfile
14M opt
2.1G software
180K dev
du: cannot access `proc/32283/task/32283/fd/4': No such file or directory
du: cannot access `proc/32283/task/32283/fdinfo/4': No such file or directory
du: cannot access `proc/32283/fd/4': No such file or directory
du: cannot access `proc/32283/fdinfo/4': No such file or directory
0 proc
0 sys
20K home
148M lib
22M lib64
15M sbin
7.6M bin
27M etc
352M root
20K tmp
You have new mail in /var/spool/mail/root
排序后:
jrhdpt02:/# ls -ltr | grep -v data | awk '{print $NF}' | while read A; do du -sh $A; done
du: cannot access `4194416': No such file or directory
4.0K srv
4.0K mnt
4.0K media
16K lost+found
4.0K selinux
2.0G usr
653M var
24M boot
4.1G swapfile
14M opt
2.1G software
180K dev
du: cannot access `proc/31588/task/31588/fd/4': No such file or directory
du: cannot access `proc/31588/task/31588/fdinfo/4': No such file or directory
du: cannot access `proc/31588/fd/4': No such file or directory
du: cannot access `proc/31588/fdinfo/4': No such file or directory
0 proc
0 sys
20K home
148M lib
22M lib64
15M sbin
7.6M bin
27M etc
352M root
1.7G tmp