Managing Disk space by deleting the older records when the disk is 60% full

Moderator: NorbertKrupa

rishireddy.bokka
Newbie
Newbie
Posts: 5
Joined: Mon Aug 07, 2017 4:24 pm

Managing Disk space by deleting the older records when the disk is 60% full

Post by rishireddy.bokka » Mon Aug 07, 2017 4:26 pm

Hi,
I am using vertica for an application which has continuous flow of data. So I want to manage the disk space by creating a cron job which will delete the older records once the disk is 60% full. Could someone please help me to achieve the same.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1822
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Managing Disk space by deleting the older records when the disk is 60% full

Post by JimKnicely » Tue Aug 08, 2017 6:35 pm

How many database tables do you need to delete data from?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

rishireddy.bokka
Newbie
Newbie
Posts: 5
Joined: Mon Aug 07, 2017 4:24 pm

Re: Managing Disk space by deleting the older records when the disk is 60% full

Post by rishireddy.bokka » Wed Aug 09, 2017 8:43 pm

We have 6 tables and we want to delete data from all the tables appropriately.

sKwa
Newbie
Newbie
Posts: 15
Joined: Wed Aug 02, 2017 3:12 pm

Re: Managing Disk space by deleting the older records when the disk is 60% full

Post by sKwa » Wed Aug 09, 2017 9:29 pm

Hi!

Pseudo algorithm:

    size  = get_disk_size()
    used  = get_disk_usage()
    ratio = used / size
    while ratio > 0.6:
        for each table in db drop an oldest partition
        used  = get_disk_usage()
        ratio = used / size


Dropping Partitions:

rishireddy.bokka
Newbie
Newbie
Posts: 5
Joined: Mon Aug 07, 2017 4:24 pm

Re: Managing Disk space by deleting the older records when the disk is 60% full

Post by rishireddy.bokka » Thu Aug 10, 2017 4:08 pm

Thanks for the reply. Could you please help me out how to get oldest partition in a table.

sKwa
Newbie
Newbie
Posts: 15
Joined: Wed Aug 02, 2017 3:12 pm

Re: Managing Disk space by deleting the older records when the disk is 60% full

Post by sKwa » Thu Aug 10, 2017 7:39 pm

Hi!
rishireddy.bokka wrote:
Thu Aug 10, 2017 4:08 pm
Could you please help me out how to get oldest partition in a table.

No problem, but I need more info:
  • How do you determine older records?
  • Can you post a tail of (some) table definition? (tail means all after columns definition)
For example:

dbadmin=> select export_tables('', 'MyTable');
                export_tables
-----------------------------------------------

CREATE TABLE public.MyTable
(
    id int NOT NULL,
    cmd varchar(255),
    pid int,
    start timestamp NOT NULL DEFAULT "sysdate"()
)
PARTITION BY ((((date_part('year', MyTable.start))::int * 100) + (date_part('month', MyTable.start))::int));

so "tail" in my case is:
PARTITION BY ((((date_part('year', MyTable.start))::int * 100) + (date_part('month', MyTable.start))::int));

rishireddy.bokka
Newbie
Newbie
Posts: 5
Joined: Mon Aug 07, 2017 4:24 pm

Re: Managing Disk space by deleting the older records when the disk is 60% full

Post by rishireddy.bokka » Fri Aug 11, 2017 9:23 pm

Thanks for the reply. We have continuous flow of data in our application. so we are planning to partition based on "PARTITION BY EXTRACT(MONTH FROM eventTime)*100 + EXTRACT(DAY FROM eventTime);" where eventTime is dateTime format. Please let me know if you need more information.

Post Reply

Return to “Vertica SQL”