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.
Managing Disk space by deleting the older records when the disk is 60% full
Moderator: NorbertKrupa
-
- Newbie
- Posts: 5
- Joined: Mon Aug 07, 2017 4:24 pm
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Managing Disk space by deleting the older records when the disk is 60% full
How many database tables do you need to delete data from?
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- 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
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
Hi!
Pseudo algorithm:
Dropping Partitions:
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:
-
- 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
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
Hi!
No problem, but I need more info:
PARTITION BY ((((date_part('year', MyTable.start))::int * 100) + (date_part('month', MyTable.start))::int));
rishireddy.bokka wrote: ↑Thu Aug 10, 2017 4:08 pmCould 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)
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));
-
- 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
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.