Hack 99. Dont Run Out of Disk Space

Hack 99 Don t Run Out of Disk Space

A badly written query can fill up even the biggest disk drive. Get your system back onto its feet quickly.

There are two main reasons for running out of disk space. Either you have filled up the disk with real data, or an internal feature of the database system, such as the temporary space, is using disk space excessively. Temporary space is used in databases to hold such things as SQL execution workspace, intermediate indexes, and subquery information. It is (usually) released automatically once the data has been used, but problems can arise. Issues such as fragmentation, DBMS bugs, and badly written queries can cause temporary space to grow until eventually you run out of disk space.

In Oracle, having to manually housekeep the temp space can be quite a common activity. The Oracle temp space is flexible and powerful, but with this comes some administration overhead. The temp space in MySQL, SQL Server, and PostgreSQL is managed automatically provided there is sufficient disk space. This hack looks at temp space for Oracle and how to manage it. It also looks at the general problem of disk space management for SQL Server, MySQL, PostgreSQL, and Oracle.

12.4.1. Oracle: Out of Temp Space

Here we assume your database is in /u02/oradata/grussell/ and your temp space is called temp01.dbf. We also assume you have some free space in /u01/tmp to use as scratch space while solving this problem. You can do this as SYSDBA.

Find the name of the current default temp space:

SELECT property_value FROM database_properties
 WHERE property_name='DEFAULT_TEMP_TABLESPACE'

Create a new temporary space to allow the system to operate while you are making the changes:

create temporary tablespace t1
tempfile '/u01/tmp/t1.dbf'
size 512M reuse autoextend on next 10M maxsize unlimited
extent management local uniform size 256K;

Bring the new one online:

alter database
tempfile '/u01/tmp/t1.dbf'
ONLINE;

Change the default to the new space:

alter database default temporary tablespace t1;

Delete the old temp space which is using up your disk space:

drop tablespace temp including contents and datafiles;

You are running with the new temp space, but what you really want to do is re-create the old space in its old partition and use that. While you are doing this, you can restrict the size of the new space so that this does not happen again. Here you create the new space as 0.5 GB, and allow it to grow to 4 GB:

create temporary tablespace temp
tempfile '/u02/oradata/grussell/temp01.dbf'
size 512M reuse autoextend on next 10M maxsize 4096M
extent management local uniform size 256K;

Now switch everything back to using this final temp table space, and delete the temporary table space t1 in /u01:

alter database
tempfile '/u02/oradata/grussell/temp01.dbf'
ONLINE;

alter database default temporary tablespace temp;

drop tablespace t1 including contents and datafiles;

Oracle uses the temp space to good effect, allowing large query results to be collated without running out of computer memory. There is no temp space in MySQL, as the queries are built directly in memory. With MySQL, you won't need to worry about a SELECT query using up your disk space, but you might run out of RAM.

12.4.2. Large Data Tables

The shortage of disk space may be caused by a user with a large table. You need to be able to find that user and get rid of the table. This is not as easy as it sounds. If you are short on disk space you cannot always run a delete query such as the following:

DELETE FROM troublesomeTable;

The system needs to log each command so that it can be undone if required. A DELETE will double the space occupied by the table, at least temporarily.

The command DROP TABLE will work, but that destroys the actual table, not just the rows in the table. If you want to delete the data without getting rid of the table and without logging, you can use the trUNCATE command:

TRUNCATE TABLE troublesomeTable

 

12.4.2.1. Oracle

If you are running low on disk space, but the temp space is fine, maybe you are just storing a lot of data. One of the nice features of Oracle is that you can do most DBA work from the SQL prompt. To see what is using the most data among your users, run:

select OWNER,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 TABLESPACE_NAME,
 BYTES
from dba_segments
where TABLESPACE_NAME = 'USERS'
order by OWNER, BYTES

Most data you could ever want to know will be stored in a table or a view. This means that standard SQL will tell you just about everything to do with your database system. In our production server we see:

select OWNER, sum(bytes)
from dba_segments
where TABLESPACE_NAME = 'USERS'
group by OWNER

OWNER SUM(BYTES)
---------- ----------
DBRW 99483648
LINUXZOO 900333568
SCOTT 393216

So, user LINUXZOO is using about 0.9 GB of space.

12.4.2.2. MySQL

In MySQL, you can spot the greedy table using up your disk space via the command-line client. Here's how to look at all the tables starting with w:

mysql> SHOW TABLE STATUS FROM dbname LIKE 'w%';
+---------------+--------+-----+-------------+------------------+...
| Name | Engine | ... | Data_length | Max_data_length |
+---------------+--------+-----+-------------+------------------+
| webalizer | MyISAM | | 4380 | 3377699720527871 |
| weekBeginning | NULL | | NULL | NULL |
| widetable | MyISAM | | 21 | 5910974510923775 |
| wltm | MyISAM | | 60 | 281474976710655 |
| words | MyISAM | | 9366740 | 281474976710655 |
+---------------+--------+-----+-------------+------------------+ 

The Data_Length column shows the amount of disk space used by the data; the indexes may be using additional disk space.

12.4.2.3. SQL Server

SQL Server provides a stored procedure for displaying disk and table usage. In its basic form, sp_spaceused displays disk usage statistics:

C:>osql -E
1> sp_spaceused
2> go
 database_name database_size unallocated space
 ------------- --------------- ------------------
 master 19.44 MB 0.78 MB


 reserved data index_size unused
 ------------------ ------------------ ------------------ ------------------
 16544 KB 11528 KB 1456 KB 3560 KB

1>

The stored procedure can take a number of different parameters, including a table name. So, to get statistics on the bbc table:

1> use gisq
2> go
1> sp_spaceused 'bbc'
2> go

name rows reserved data index_size unused
bbc 261 72 KB 24 KB 48 KB 0 KB

If you had many different tables you could run this command for all user tables, using an internal procedure called sp_MSforeachtable:

1> sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
2> go
 name rows reserved data index_size unused
 stops 246 24 KB 8 KB 16 KB 0 KB
 concert 8 24 KB 8 KB 16 KB 0 KB
 route 1174 136 KB 64 KB 16 KB 56 KB
 ...