Re: delete is getting hung when there is a huge data in table
От | Fabio Ugo Venchiarutti |
---|---|
Тема | Re: delete is getting hung when there is a huge data in table |
Дата | |
Msg-id | 5545FDE7.4010108@vuole.me обсуждение исходный текст |
Ответ на | delete is getting hung when there is a huge data in table (Mitu Verma <mitu.verma@ericsson.com>) |
Список | pgsql-general |
> still this delete operation is not working and not a single row has been deleted from the table. This is an intended effect of ACID properties compliance = transactions are guaranteed to atomically show their effects upon completion or do nothing at all. This also applies to multiple data-changing statements if they're wrapped into a transaction. http://en.wikipedia.org/wiki/ACID. Postgres just fully implements that through http://en.wikipedia.org/wiki/Multiversion_concurrency_control As to why it takes forever, you're not providing enough information for a solid hypothesis. Speculation (8 million rows is not that big): - very slow and database-unfriendly storage stack (RAID 5 perhaps?) - missing indexes to match your clauses (on modern hardware a full table scan over 8 million rows should complete in the order of minutes. Try to run a select with the same clauses) - have you checked if there are other processes holding exclusive locks on the table/some of the records you're trying to delete? pg_lock and pg_stat_activity are your friends here - are there triggers on the table that could be slow/hitting locks in other tables? Regardless, you might want to split your statement into smaller chunks through less greedy clauses and see what happens. Regards F On 03/05/15 15:24, Mitu Verma wrote: > Hi, > > I am facing an issue with the deletion of huge data. > We have a cronscript which is used to delete the data of last 3 months from one of the tables. > Data in the table is large (8872597 as you can see the count below) since it is from last 3 months. > > fm_db_Server3=# select count(*) from audittraillogentry ; > > > count > --------- > 8872597 > (1 row) > > Now issue is that when this script for the deletion of data is launched , it is taking more than 7 days and doing nothingi.e not a single row has been deleted. > > Then we stopped the script,terminated the database sessions by using SELECT pg_terminate_backend(proc pid) and run thefollowing command > > delete from audittraillogentry where intime <= to_timestamp('2015-01-30 23:59:59.999', 'YYYY/MM/DD-HH24:MI:SS.FF3') ORouttime <= to_timestamp('2015-01-30 23:59:59.999', 'YYYY/MM/DD-HH24:MI:SS.FF3'); > still this delete operation is not working and not a single row has been deleted from the table. > > Now i have following questions - > > 1. If postgreSQL has some limitations for deletion of large data? > 2. If i should run the vacumm, after stopping the cron script ? because probably to get the "smaller" table? > 3. if dropping the indexes can help here? now sure. > 4.if i should think about partitioning , if there is any limitation while delaing with large data in postgreSQL? > > regards > Mitu > > _____ > ___________________________________ > >
В списке pgsql-general по дате отправления: