Page 1 of 2
Managing Disk space by deleting the older records when the disk is 60% full
Posted: Mon Aug 07, 2017 4:26 pm
by rishireddy.bokka
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.
Re: Managing Disk space by deleting the older records when the disk is 60% full
Posted: Tue Aug 08, 2017 6:35 pm
by JimKnicely
How many database tables do you need to delete data from?
Re: Managing Disk space by deleting the older records when the disk is 60% full
Posted: Wed Aug 09, 2017 8:43 pm
by rishireddy.bokka
We have 6 tables and we want to delete data from all the tables appropriately.
Re: Managing Disk space by deleting the older records when the disk is 60% full
Posted: Wed Aug 09, 2017 9:29 pm
by sKwa
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:
Re: Managing Disk space by deleting the older records when the disk is 60% full
Posted: Thu Aug 10, 2017 4:08 pm
by rishireddy.bokka
Thanks for the reply. Could you please help me out how to get oldest partition in a table.
Re: Managing Disk space by deleting the older records when the disk is 60% full
Posted: Thu Aug 10, 2017 7:39 pm
by sKwa
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));
Re: Managing Disk space by deleting the older records when the disk is 60% full
Posted: Fri Aug 11, 2017 9:23 pm
by rishireddy.bokka
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.