7

My /tmp partition keeps running out of space on a very large SQL query. How can I make the /tmp partition bigger?

Thanks Tom

EDIT:

Here are the outputs from the requested commands

blkid
/dev/sda1: UUID="e8604436-53ea-4a05-9459-6c498cf19a1a" SEC_TYPE="ext2" TYPE="ext3" 
/dev/sda5: UUID="21bd6739-89c8-4196-986d-bec9474a4b1a" SEC_TYPE="ext2" TYPE="ext3" 
/dev/sda6: UUID="1a80f7c6-d524-4974-82ee-c5bd734802b9" SEC_TYPE="ext2" TYPE="ext3" 
/dev/sda7: TYPE="swap" 
/dev/sda8: UUID="edee40d5-9337-4705-b152-7bc2b9b35336" SEC_TYPE="ext2" TYPE="ext3" 
/dev/sda9: UUID="5c586547-8d78-4dd1-86c9-92f83b4d8fb5" SEC_TYPE="ext2" TYPE="ext3"

fdisk -l

Disk /dev/sda: 160.0 GB, 160041885696 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0x000504d7

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1           18663       19201     4329517+  83  Linux
/dev/sda2              35       18662   149629410    5  Extended
/dev/sda5              35         642     4883728+  83  Linux
/dev/sda6             643        1007     2931831   83  Linux
/dev/sda7            1008        1337     2650693+  82  Linux swap / Solaris
/dev/sda8            1338        1386      393561   83  Linux
/dev/sda9            1387       18662   138769438+  83  Linux

Partition table entries are not in disk order

cat /etc/mtab
/dev/sda1 / ext3 rw,errors=remount-ro 0 0
tmpfs /lib/init/rw tmpfs rw,nosuid,mode=0755 0 0
proc /proc proc rw,noexec,nosuid,nodev 0 0
sysfs /sys sysfs rw,noexec,nosuid,nodev 0 0
procbususb /proc/bus/usb usbfs rw 0 0
udev /dev tmpfs rw,mode=0755 0 0
tmpfs /dev/shm tmpfs rw,nosuid,nodev,size=1000m 0 0
devpts /dev/pts devpts rw,noexec,nosuid,gid=5,mode=620 0 0
/dev/sda9 /home ext3 rw 0 0
/dev/sda5 /usr ext3 rw 0 0
/dev/sda6 /var ext3 rw 0 0
rpc_pipefs /var/lib/nfs/rpc_pipefs rpc_pipefs rw 0 0
//192.168.254.12/SHARE1 /mnt/archive cifs rw,mand 0 0
/dev/sda8 /tmp ext3 rw 0 0


pvs
-su: pvs: command not found
pvs
-su: pvs: command not found
lvs
-su: lvs: command not found

5 Answers5

6

It may be easier to replace /tmp with a plain file.

dd if=/dev/zero of=/var/mytmp bs=1024 count=<desired size in K>

mkfs -t ext3 /var/mytmp

umount /tmp

mount -o loop /var/mytmp /tmp

Then, create a matching mount line in your /etc/fstab file.

kmarsh
  • 3,103
  • 15
  • 22
  • 2
    This should work well, and be faster+safer to implement (as there are no complex filesystem rearrangement operations to perform) than my gparted suggestion, though remember that using a loopback device like this is going to be less efficient than a separate partition. Also, you need to make sure that during the boot process the partition you put the file on for loopback use is mounted earlier in the process than the attempt to mount `/tmp` using the file. – David Spillett Sep 16 '09 at 21:05
  • You can also use different units for the `bs` parameter like so: `dd if=/dev/zero of=/var/mytmp bs=1GB count=`. Just notice that `dd` will try to allocate the value you put in `bs` at the start of the program. Secondly, in most modern linux distributions, systemd will try to order the mounts automatically to avoid the problem explained by @David Spillett – Marcelo Lacerda Apr 27 '18 at 15:02
3

Why not just tell MySQL to do its work elsewhere? Either set TMPDIR in MySQL's environment, or set tmpdir = /some/non/tmp/path in my.cnf.

Gerald Combs
  • 6,331
  • 23
  • 35
2

Exactly how to do this depends how your drive(s) are currently arranged. If you add the output of the following commands to your question that would help:

fdisk -l
cat /etc/mtab
pvs
vgs
lvs

(the last three commands may not be present if you are not using LVM)

As an alternative, you could try moving the location that your SQL server is using for its temporary scratch space. You'd have to give us more details about the database setup (i.e. which database server are you using? mySQL/Postgres/...?) for help on that.

If your databases are on the same drive as your /tmp partition then you may find moving the scratch area off onto another drive will greatly increase the speed of queries this complex.

It might also be worth posting the SQL query (and info on the structures it is querying) over on StackOverflow to see if a passing SQL expert can see any obvious parts that are crying out for an easy optimisation.

Edit (after partition information posted

As you are not using LVM at all and the change isn't going to impact your / or /boot, if you can afford a chunk of downtime while the operations are performed I would suggest the gparted "live CD". Boot with that, tell it to:

  1. shrink sda9 (your /home) a bit
  2. move it up the disk to make a gap between it and /sda8 (your /tmp)
  3. grow sda8 to fill teh new space after it

If you can afford to lose a bit of space then this instead will be quicker (as there is no move full operation on /home, just a resize operation and any data moves needed by that):

  1. shrink /home by as much as you want the new /tmp to be
  2. create a new partition in the space now after /home
  3. you can then mount this as /tmp
  4. you don't actually lose the space sda8 uses, as you can mount it as something else, but it just might not be all that convenient to use.

Yet another option would be to shrink the swap space that lives ahead of sda8, and move than resize sda8 into the extra space. How good an idea this is depends on the amount of RAM you have and what you are running on the machine. This option will be quicker as you are not moving or resizing /home. It will actually be safer too as you are not touching /home - gparted wiil just be acting on /tmp and swap space so nothing that matters if the resize operation goes wrong and stuff in the affected partitions ends up corrupted.

I have messed with partitions like this a number of times with gparted and had absolutely no issues, but it goes without saying that before using it you should take a full backup of all your important files and your current partition table. I have heard reports of problems if you let gparted mess with your partition containing /boot but I've always kept /boot in its own partition at the start of the disk so have never had to risk it myself.

David Spillett
  • 22,534
  • 42
  • 66
  • I've added the outputs from the commands you requested. Thanks for you help –  Sep 16 '09 at 13:12
1

Depending on how your box is, you can mount /tmp in ram, google it. Solaris does this by default, and I've done it on Linux, (and OS/2, in fact), if you have the ram, it can really speed things up. It will push out to swap of course. This would probably fix it for you, as your swap is 2.6GB vs 400mb for /tmp, right?

Heck, here is how to mount it on the fly on top of your existing /tmp ! http://www.funtoo.org/en/articles/linux/ffg/3/

That was a quick google, it used to be that there could be issues on boot (need stuff in your initrd to make it work).

Probably need to reboot to unmount then, as you will probably have files open in the /tmp on disk and the /tmp on swap (though you could use lsof to find them and kill the processes that have them open, so you could unmount it).

Just use this for the line in your /etc/fstab

tmpfs /tmp tmpfs defaults,noatime,mode=1777 0 0

I would say that you could remove the noatime, or replace it with relatime

Ronald Pottol
  • 1,683
  • 1
  • 11
  • 19
0

In this case, it's probably easiest to just umount /tmp, remove /dev/sda8 with fdisk, create a new sda8 that's bigger, mkfs on it and mount it again. Might cost you a moment of (application) downtime though.

Next time, build your filesystems upon LVM, which makes this a kazillion times easier.

wzzrd
  • 10,269
  • 2
  • 32
  • 47