[SQL] How to reduce a database
Hi list, I have an openNMS server that uses a Postgres database. For those who are not familiar, openNMS is an open source network management product. Anyway, the openNMS database is very large now, more than 25GB (considering all tables) and I am starting to have disk space issues. The openNMS product has a vacuumdb procedure that runs every 24 hours and reads a vacuumd-configuration.xml file for parameters on what to do. The problem is that this process is not reducing the database size. What I need to do is to delete some records based on timestamp fileds or something like that. I don't know how to do it though. Can you guys help me with some command line examples? There is this table, called EVENTS, that have the following structure: eventid | integer | not null eventuei| character varying(256) | not null nodeid | integer | eventtime | timestamp without time zone | not null eventhost | character varying(256) | eventsource | character varying(128) | not null ipaddr | character varying(16) | eventdpname | character varying(12) | not null eventsnmphost | character varying(256) | serviceid | integer | eventsnmp | character varying(256) | eventparms | text| eventcreatetime | timestamp without time zone | not null eventdescr | character varying(4000) | eventloggroup | character varying(32) | eventlogmsg | character varying(256) | eventseverity | integer | not null eventpathoutage | character varying(1024) | eventcorrelation| character varying(1024) | eventsuppressedcount| integer | eventoperinstruct | character varying(1024) | eventautoaction | character varying(256) | eventoperaction | character varying(256) | eventoperactionmenutext | character varying(64) | eventnotification | character varying(128) | eventtticket| character varying(128) | eventtticketstate | integer | eventforward| character varying(256) | eventmouseovertext | character varying(64) | eventlog| character(1)| not null eventdisplay| character(1)| not null eventackuser| character varying(256) | eventacktime| timestamp without time zone | I was thinking about using a DELETE FROM EVENTS WHERE eventtime = ..but I am kind of worried on what this could cause on other tables, if there is some relations between them or something. Here is the vacuumd-configuration.xml file: DELETE FROM node WHERE node.nodeType = 'D'; DELETE FROM ipInterface WHERE ipInterface.isManaged = 'D'; DELETE FROM if Services WHERE ifServices.status = 'D'; DELETE FROM even ts WHERE NOT EXISTS (SELECT svclosteventid FROM outages WHERE svclosteventid = events.eventid UNION SELECT svcregainedeventid FROM out ages WHERE svcregainedeventid = events.eventid UNION SELECT eventid FROM notifications WHERE eventid = events.eventid) AND eventtime & lt; now() - interval '6 weeks'; Any help is appreciated. Thank you. Mario __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[SQL] deleting records from a table
Hi all, Simple question: once I execute the delete statement, does it free disk space immediatelly? Does Postgres uses something like a datafile with a pre-defined size like Oracle does?? Please advise. Thank you. Mario Get your own web address. Have a HUGE year through Yahoo! Small Business. http://smallbusiness.yahoo.com/domains/?p=BESTDEAL
[SQL] vacuum process taking more than 33 hours
Hi all, I've executed a VACUUM FULL on a database 33GB in size. The process was going fine until it reached a index (I guess it's an index) and there it stopped for more than 30 hours...the whole weekend.. I've canceled it but I desperately need to free some space at the server's disk. I was thinking about using the TRUNCATE statement at the table I know to be the largest one. I have some questions though: Will the TRUNCATE free disk space?Can I run this command and let the openNMS software running at the same time? The ONMS product will certainly try to execute INCLUDES and such.If the TRUNCATE indeed free disk space, what should I do next, reindex, vacuum ?? Also, what else can I do to avoid this kind of problem? Would a REINDEX fix the problem and let the VACUUM command finish? Please advise. Thank you. Best regards, Mario Behring TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. http://tv.yahoo.com/
[SQL] Droping indexes
Hi all, Please, if I drop all indexes from a table, can I recreate them after performing a vacuum full at this table? I mean, I do not know details about the indexes, so what I am asking is if I issue a REINDEX on this table, will it create the proper indexes again? Please advise. Thanks in advance. Mario Behring Never miss an email again! Yahoo! Toolbar alerts you the instant new Mail arrives. http://tools.search.yahoo.com/toolbar/features/mail/
Re: [SQL] Droping indexes
Hi Tomas, Thank you. Please help me here.I am not a database guy...how do I use this CLUSTER command and what does it do? Please keep in mind that I do not have disk space left. Also, please check the table I am talking about below: opennms=# \d events Tabela "public.events" Colunm |Type | Modifyers -+-+--- eventid | integer | not null eventuei| character varying(256) | not null nodeid | integer | eventtime | timestamp without time zone | not null eventhost | character varying(256) | eventsource | character varying(128) | not null ipaddr | character varying(16) | eventdpname | character varying(12) | not null eventsnmphost | character varying(256) | serviceid | integer | eventsnmp | character varying(256) | eventparms | text| eventcreatetime | timestamp without time zone | not null eventdescr | character varying(4000) | eventloggroup | character varying(32) | eventlogmsg | character varying(256) | eventseverity | integer | not null eventpathoutage | character varying(1024) | eventcorrelation| character varying(1024) | eventsuppressedcount| integer | eventoperinstruct | character varying(1024) | eventautoaction | character varying(256) | eventoperaction | character varying(256) | eventoperactionmenutext | character varying(64) | eventnotification | character varying(128) | eventtticket| character varying(128) | eventtticketstate | integer | eventforward| character varying(256) | eventmouseovertext | character varying(64) | eventlog| character(1)| not null eventdisplay| character(1)| not null eventackuser| character varying(256) | ndices:ktime| timestamp without time zone | "pk_eventid"primary key, btree (eventid) "events_acktime_idx" btree (eventacktime) "events_ackuser_idx" btree (eventackuser) "events_display_idx" btree (eventdisplay) "events_ipaddr_idx" btree (ipaddr) "events_log_idx" btree (eventlog) "events_nodeid_idx" btree (nodeid) "events_serviceid_idx" btree (serviceid) "events_severity_idx" btree (eventseverity) "events_time_idx" btree (eventtime) "events_uei_idx" btree (eventuei) Restrictions of foreing key: "fk_nodeid6" FOREIGN KEY (nodeid) REFERENCES node(nodeid) ON DELETE CASCADE Thanks. Mario Behring - Original Message From: Tomas Vondra <[EMAIL PROTECTED]> To: Mario Behring <[EMAIL PROTECTED]> Sent: Tuesday, January 16, 2007 12:29:59 PM Subject: Re: [SQL] Droping indexes > Hi all, > > Please, if I drop all indexes from a table, can I recreate them after > performing a vacuum full at this table? I mean, I do not know details > about the indexes, so what I am asking is if I issue a REINDEX on this > table, will it create the proper indexes again? > > Please advise. No, if you drop them they're lost and you'll have to recreate them using CREATE INDEX. Being in your situation, I'd use CLUSTER instead of VACUUM + REINDEX, as it basically does the same thing, plus it has several advantages related to performance. Just think carefully which index would you use to cluster the table - the index associated with the primary key is generally a good choice. Tomas PS: Don't forget to analyze the table after that! Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091
Re: [SQL] Droping indexes
Hi Alvaro, Thank you for your advise..I was thinking about doing exactly that, I wasn't sure on how to do it though, meaning, considering the info below, how should I use the CREATE INDEX command to create these indexes?? opennms=# \d events Tabela "public.events" Colunm |Type | Modifyers -+-+--- eventid | integer | not null eventuei| character varying(256) | not null nodeid | integer | eventtime | timestamp without time zone | not null eventhost | character varying(256) | eventsource | character varying(128) | not null ipaddr | character varying(16) | eventdpname | character varying(12) | not null eventsnmphost | character varying(256) | serviceid | integer | eventsnmp | character varying(256) | eventparms | text| eventcreatetime | timestamp without time zone | not null eventdescr | character varying(4000) | eventloggroup | character varying(32) | eventlogmsg | character varying(256) | eventseverity | integer | not null eventpathoutage | character varying(1024) | eventcorrelation| character varying(1024) | eventsuppressedcount| integer | eventoperinstruct | character varying(1024) | eventautoaction | character varying(256) | eventoperaction | character varying(256) | eventoperactionmenutext | character varying(64) | eventnotification | character varying(128) | eventtticket| character varying(128) | eventtticketstate | integer | eventforward| character varying(256) | eventmouseovertext | character varying(64) | eventlog| character(1)| not null eventdisplay| character(1)| not null eventackuser| character varying(256) | ndices:ktime| timestamp without time zone | "pk_eventid"primary key, btree (eventid) "events_acktime_idx" btree (eventacktime) "events_ackuser_idx" btree (eventackuser) "events_display_idx" btree (eventdisplay) "events_ipaddr_idx" btree (ipaddr) "events_log_idx" btree (eventlog) "events_nodeid_idx" btree (nodeid) "events_serviceid_idx" btree (serviceid) "events_severity_idx" btree (eventseverity) "events_time_idx" btree (eventtime) "events_uei_idx" btree (eventuei) Restrictions of foreing key: "fk_nodeid6" FOREIGN KEY (nodeid) REFERENCES node(nodeid) ON DELETE CASCADE Thank you. Best regards, Mario Behring - Original Message From: Alvaro Herrera <[EMAIL PROTECTED]> To: Mario Behring <[EMAIL PROTECTED]> Cc: Tomas Vondra <[EMAIL PROTECTED]>; [email protected] Sent: Tuesday, January 16, 2007 1:08:55 PM Subject: Re: [SQL] Droping indexes Mario Behring wrote: > Hi Tomas, > > Thank you. > > Please help me here.I am not a database guy...how do I use this > CLUSTER command and what does it do? Please keep in mind that I do not have > disk space left. If the disk is full you cannot use CLUSTER anyway. Suggestion: make a note of the indexes that exist. Drop them. Do a VACUUM FULL of the table. Create the indexes again. After that's done: 1. read the CLUSTER documentation, as it may help you in the future 2. consider restructuring the table so that redundant information is kept only on one place (for example, the eventackuser could probably be put on a separate table and on this one store just an integer ID). This will make the table and the index on that column a lot smaller. 3. add more disks to your installation 4. research a more effective VACUUM policy -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index
Re: [SQL] Droping indexes RESOLVED
Hi all, Thank you very much for your help. I've found a create.sql script among the millions of files from openNMS installation that has all SQL statements for creating all tables, constraints, indexes and such. :-) I've just executed a drop table at the guilty table (32GB in size...), recreated it along with all constraints and indexesand everything is fine now !! Thank a lot for your support. All the best, Mario Behring - Original Message From: Frank Bax <[EMAIL PROTECTED]> To: [email protected] Sent: Tuesday, January 16, 2007 2:30:31 PM Subject: Re: [SQL] Droping indexes At 11:27 AM 1/16/07, Frank Bax wrote: >At 10:42 AM 1/16/07, Mario Behring wrote: >>Thank you for your advise..I was thinking about doing exactly >>that, I wasn't sure on how to do it though, meaning, considering the info >>below, how should I use the CREATE INDEX command to create these indexes?? > > >This might provide a clue: > >pg_dump -t events | grep ^CREATE Sorry, perhaps this would be better pg_dump -s -t events | grep ^CREATE ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match 8:00? 8:25? 8:40? Find a flick in no time with the Yahoo! Search movie showtime shortcut. http://tools.search.yahoo.com/shortcuts/#news
