mysql 排序 tmp_mysql 排序 /tmp被撑爆

我:

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-

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.0Ksrv

4.0Kmnt

4.0Kmedia

16Klost+found

4.0Kselinux

2.0Gusr

653Mvar

24Mboot

4.1Gswapfile

14Mopt

2.1Gsoftware

180Kdev

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

0proc

0sys

20Khome

148Mlib

22Mlib64

15Msbin

7.6Mbin

27Metc

352Mroot

20Ktmp

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.0Ksrv

4.0Kmnt

4.0Kmedia

16Klost+found

4.0Kselinux

2.0Gusr

653Mvar

24Mboot

4.1Gswapfile

14Mopt

2.1Gsoftware

180Kdev

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

0proc

0sys

20Khome

148Mlib

22Mlib64

15Msbin

7.6Mbin

27Metc

352Mroot

1.7Gtmp