[ADMIN] ERROR: XX001 (Critical and Urgent)
Hello All, Getting *ERROR: XX001: could not read block 17 of relation base/16386/2619: read only 0 of 8192 bytes*, While vacuuming database Manual vacuuming and Auto vacuuming process *constantly* taking high CPU, not able to skip corrupted table for vacuuming and dump this message at regular interval. * fsync is off* , From strace, found that semop call was in infinite loop. I have tried with making fsync on, Now manual vacuum process is taking high CPU, Strace unable to show any results (may be dead lock situation) and not any error / warning from postgres daemon Postgres Version : 8.4.3 (Migrated data from 8.4.1) What can be issue ? Is it issue coming after database table corruption, Can fsync on can prevent such (corruption) scenarios ? Thanks, Siddharth
Re: [ADMIN] ERROR: XX001 (Critical and Urgent)
Siddharth Shah wrote: > * fsync is off* If you are running the database with fsync off and there is any sort of unusual termination, your database will probably be corrupted. I recommend restoring from your last good backup. If you don't have one, recovery is going to be painful; I recommend contracting with one of the many companies which off PostgreSQL support. (I'm not affiliated with any of them.) > I have tried with making fsync on That may help prevent further corruption, but will do nothing to help recover from the damage already done. > Postgres Version : 8.4.3 (Migrated data from 8.4.1) What do you mean by that? You installed 8.4.3 and reindexed hash indexes? > What can be issue ? Is it issue coming after database table > corruption Yes. > Can fsync on can prevent such (corruption) scenarios ? Yes. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] ERROR: XX001 (Critical and Urgent)
Thanks Kevin. Yes, I installed 8.4.3 then I have found that DDL and DML statements were getting failed to execute in some distributions So that's why taken call for reindexing What can be the method to verify that it's a database corruption ? xdb=# \dt; ERROR: index "pg_class_relname_nsp_index" contains unexpected zero page at block 33 HINT: Please REINDEX it. xdb=# analyse verbose pg_class_relname_nsp_index; ANALYZE xdb=# \dt; ERROR: index "pg_class_relname_nsp_index" contains unexpected zero page at block 33 HINT: Please REINDEX it. xdb=# reindex index pg_class_relname_nsp_index; Now INDEXing taking High CPU and postgres baffled. I don't have any backup available, Is there any way to fix this ? Kevin Grittner wrote: Siddharth Shah wrote: * fsync is off* If you are running the database with fsync off and there is any sort of unusual termination, your database will probably be corrupted. I recommend restoring from your last good backup. If you don't have one, recovery is going to be painful; I recommend contracting with one of the many companies which off PostgreSQL support. (I'm not affiliated with any of them.) I have tried with making fsync on That may help prevent further corruption, but will do nothing to help recover from the damage already done. Postgres Version : 8.4.3 (Migrated data from 8.4.1) What do you mean by that? You installed 8.4.3 and reindexed hash indexes? What can be issue ? Is it issue coming after database table corruption Yes. Can fsync on can prevent such (corruption) scenarios ? Yes. -Kevin
Re: [ADMIN] ERROR: XX001 (Critical and Urgent)
[rearranged to put the most critical point first] Siddharth Shah wrote: > I don't have any backup available, Is there any way to fix this ? I *strongly* recommend that you shut down the database and take a file copy of the whole data tree (everything under what -D points to on the server startup) which you should keep until long after you think everything is working OK again. Before you do anything else. You are at risk of losing everything in the database, and one misstep could put you over the edge. If this is a production database, tell the users that it is down until further notice. > What can be the method to verify that it's a database corruption ? > ERROR: index "pg_class_relname_nsp_index" contains unexpected > zero page at block 33 Getting an error like that indicates database corruption. > HINT: Please REINDEX it. > xdb=# reindex index pg_class_relname_nsp_index; > > Now INDEXing taking High CPU and postgres baffled. That is an index on the table which describes all your tables and indexes. It normally doesn't take a long time to reindex. You should consider doing your recovery in single-user mode (*AFTER* you make that copy): http://www.postgresql.org/docs/8.4/interactive/app-postgres.html After trying reindex in that context, please post again. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] help me on creation cube
hi, i am a student , and for my final project i have to work with postgresql ; my project subject is data warehousing & data mining, so i need to define cube; create dimention table & fact table & ... i had active cube in template1; i mean i run cube.sql and there are cube functions in my databases but i don't know how to create cube!!! please help me and explain me how to do these kind of things step by step!! With Best Regards -- Raha Sadeghi
[ADMIN] postgres invoked oom-killer
We have a postgres 8.3.8 on linux We get following messages int /var/log/messages: May 6 22:31:01 pgblade02 kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 May 6 22:31:01 pgblade02 kernel: May 6 22:31:01 pgblade02 kernel: Call Trace: May 6 22:31:19 pgblade02 kernel: [] out_of_memory+0x8e/0x2f5 May 6 22:31:19 pgblade02 kernel: [] __alloc_pages+0x22b/0x2b4 May 6 22:31:19 pgblade02 kernel: [] __do_page_cache_readahead+0x95/0x1d9 May 6 22:31:19 pgblade02 kernel: [] __wait_on_bit_lock+0x5b/0x66 May 6 22:31:19 pgblade02 kernel: [] :dm_mod:dm_any_congested+0x38/0x3f May 6 22:31:19 pgblade02 kernel: [] filemap_nopage+0x148/0x322 May 6 22:31:19 pgblade02 kernel: [] __handle_mm_fault+0x1f8/0xdf4 May 6 22:31:19 pgblade02 kernel: [] do_page_fault+0x4b8/0x81d May 6 22:31:19 pgblade02 kernel: [] thread_return+0x0/0xeb May 6 22:31:19 pgblade02 kernel: [] error_exit+0x0/0x84 May 6 22:31:27 pgblade02 kernel: May 6 22:31:28 pgblade02 kernel: Mem-info: May 6 22:31:28 pgblade02 kernel: Node 0 DMA per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 0 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: Node 0 DMA32 per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:27 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:54 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:23 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:49 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:12 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:14 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:50 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:60 May 6 22:31:29 pgblade02 kernel: Node 0 Normal per-cpu: May 6 22:31:29 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:5 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:48 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:11 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:39 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:14 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:57 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:94 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:36 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem per-cpu: empty May 6 22:31:29 pgblade02 kernel: Free pages: 41788kB (0kB HighMem) May 6 22:31:29 pgblade02 kernel: Active:974250 inactive:920579 dirty:0 writeback:0 unstable:0 free:10447 slab:11470 mapped-file:985 mapped-anon:1848625 pagetables:111027 May 6 22:31:29 pgblade02 kernel: Node 0 DMA free:11172kB min:12kB low:12kB high:16kB active:0kB inactive:0kB present:10816kB pages_scanned:0 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 3254 8052 8052 May 6 22:31:29 pgblade02 kernel: Node 0 DMA32 free:23804kB min:4636kB low:5792kB high:6952kB active:1555260kB inactive:1566144kB present:3332668kB pages_scanned:35703257 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 4797 4797 May 6 22:31:29 pgblade02 kernel: Node 0 Normal free:6812kB min:6836kB low:8544kB high:10252kB active:2342332kB inactive:2115836kB present:4912640kB pages_scanned:10165709 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 DMA: 3*4kB 5*8kB 3*16kB 6*32kB 4*64kB 3*128kB 0*256kB 0*512kB 2*1024kB 0*2048kB 2*4096kB = 11172kB May 6 22:31:29 pgblade02 kernel: Node 0 DMA32: 27*4kB 0*8kB 1*16kB 0*32kB 2*64kB 4*128kB 0*256kB 1*512kB 0*1024kB 1*2048kB 5*4096kB = 23804kB May 6 22:31:29 pgblade02 kernel: Node 0 Normal: 21*4kB 9*8kB 26*16kB 3*32kB 6*64kB 5*128kB 0*256kB 0*512kB 1*1024kB 0*2048kB 1*4096kB = 6812kB May 6 22:31:29 pgblade02 kernel: Node 0 HighMem: empty May 6 22:31:29 pgblade02 kernel: Swap cache: add 71286821, delete 71287152, find 207780333/216904318, race 1387+10506 May 6 22:31:29 pgblade02 kernel: Free swap = 0kB May 6 22:31:30 pgblade02 kernel: Total swap = 8388600kB May 6 22:31:30 pgblade02 kernel: Free swap:0kB May 6 22:31:30 pgblade02 kernel: 2293759 pag
Re: [ADMIN] ERROR: XX001 (Critical and Urgent)
Kevin Grittner wrote: [rearranged to put the most critical point first] Siddharth Shah wrote: I don't have any backup available, Is there any way to fix this ? I *strongly* recommend that you shut down the database and take a file copy of the whole data tree (everything under what -D points to on the server startup) which you should keep until long after you think everything is working OK again. Before you do anything else. You are at risk of losing everything in the database, and one misstep could put you over the edge. If this is a production database, tell the users that it is down until further notice. Yes Kevin, I have taken backup of DATADIR. What can be the method to verify that it's a database corruption ? ERROR: index "pg_class_relname_nsp_index" contains unexpected zero page at block 33 Getting an error like that indicates database corruption. HINT: Please REINDEX it. xdb=# reindex index pg_class_relname_nsp_index; Now INDEXing taking High CPU and postgres baffled. That is an index on the table which describes all your tables and indexes. It normally doesn't take a long time to reindex. You should consider doing your recovery in single-user mode (*AFTER* you make that copy): http://www.postgresql.org/docs/8.4/interactive/app-postgres.html After trying reindex in that context, please post again. postgres --single -P -D $DATADIR -p 5433 xdb Same behavior in single mode. -Kevin
Re: [ADMIN] ERROR: XX001 (Critical and Urgent)
One more point, This is observed two times while product firmware updates which updates Postgres 8.4.3 from 8.4.1 Abruptly shutdown never leads to this type of corruption while fsync is always off Thanks, Siddharth Siddharth Shah wrote: Kevin Grittner wrote: [rearranged to put the most critical point first] Siddharth Shah wrote: I don't have any backup available, Is there any way to fix this ? I *strongly* recommend that you shut down the database and take a file copy of the whole data tree (everything under what -D points to on the server startup) which you should keep until long after you think everything is working OK again. Before you do anything else. You are at risk of losing everything in the database, and one misstep could put you over the edge. If this is a production database, tell the users that it is down until further notice. Yes Kevin, I have taken backup of DATADIR. What can be the method to verify that it's a database corruption ? ERROR: index "pg_class_relname_nsp_index" contains unexpected zero page at block 33 Getting an error like that indicates database corruption. HINT: Please REINDEX it. xdb=# reindex index pg_class_relname_nsp_index; Now INDEXing taking High CPU and postgres baffled. That is an index on the table which describes all your tables and indexes. It normally doesn't take a long time to reindex. You should consider doing your recovery in single-user mode (*AFTER* you make that copy): http://www.postgresql.org/docs/8.4/interactive/app-postgres.html After trying reindex in that context, please post again. postgres --single -P -D $DATADIR -p 5433 xdb Same behavior in single mode. -Kevin
Re: [ADMIN] postgres invoked oom-killer
Silvio Brandani wrote: We have a postgres 8.3.8 on linux We get following messages int /var/log/messages: May 6 22:31:01 pgblade02 kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 May 6 22:31:01 pgblade02 kernel: May 6 22:31:01 pgblade02 kernel: Call Trace: May 6 22:31:19 pgblade02 kernel: [] out_of_memory+0x8e/0x2f5 May 6 22:31:19 pgblade02 kernel: [] __alloc_pages+0x22b/0x2b4 May 6 22:31:19 pgblade02 kernel: [] __do_page_cache_readahead+0x95/0x1d9 May 6 22:31:19 pgblade02 kernel: [] __wait_on_bit_lock+0x5b/0x66 May 6 22:31:19 pgblade02 kernel: [] :dm_mod:dm_any_congested+0x38/0x3f May 6 22:31:19 pgblade02 kernel: [] filemap_nopage+0x148/0x322 May 6 22:31:19 pgblade02 kernel: [] __handle_mm_fault+0x1f8/0xdf4 May 6 22:31:19 pgblade02 kernel: [] do_page_fault+0x4b8/0x81d May 6 22:31:19 pgblade02 kernel: [] thread_return+0x0/0xeb May 6 22:31:19 pgblade02 kernel: [] error_exit+0x0/0x84 May 6 22:31:27 pgblade02 kernel: May 6 22:31:28 pgblade02 kernel: Mem-info: May 6 22:31:28 pgblade02 kernel: Node 0 DMA per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 0 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: Node 0 DMA32 per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:27 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:54 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:23 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:49 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:12 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:14 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:50 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:60 May 6 22:31:29 pgblade02 kernel: Node 0 Normal per-cpu: May 6 22:31:29 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:5 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:48 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:11 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:39 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:14 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:57 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:94 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:36 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem per-cpu: empty May 6 22:31:29 pgblade02 kernel: Free pages: 41788kB (0kB HighMem) May 6 22:31:29 pgblade02 kernel: Active:974250 inactive:920579 dirty:0 writeback:0 unstable:0 free:10447 slab:11470 mapped-file:985 mapped-anon:1848625 pagetables:111027 May 6 22:31:29 pgblade02 kernel: Node 0 DMA free:11172kB min:12kB low:12kB high:16kB active:0kB inactive:0kB present:10816kB pages_scanned:0 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 3254 8052 8052 May 6 22:31:29 pgblade02 kernel: Node 0 DMA32 free:23804kB min:4636kB low:5792kB high:6952kB active:1555260kB inactive:1566144kB present:3332668kB pages_scanned:35703257 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 4797 4797 May 6 22:31:29 pgblade02 kernel: Node 0 Normal free:6812kB min:6836kB low:8544kB high:10252kB active:2342332kB inactive:2115836kB present:4912640kB pages_scanned:10165709 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 DMA: 3*4kB 5*8kB 3*16kB 6*32kB 4*64kB 3*128kB 0*256kB 0*512kB 2*1024kB 0*2048kB 2*4096kB = 11172kB May 6 22:31:29 pgblade02 kernel: Node 0 DMA32: 27*4kB 0*8kB 1*16kB 0*32kB 2*64kB 4*128kB 0*256kB 1*512kB 0*1024kB 1*2048kB 5*4096kB = 23804kB May 6 22:31:29 pgblade02 ker if it asks for more memory than is actually available. nel: Node 0 Normal: 21*4kB 9*8kB 26*16kB 3*32kB 6*64kB 5*128kB 0*256kB 0*512kB 1*1024kB 0*2048kB 1*4096kB = 6812kB May 6 22:31:29 pgblade02 kernel: Node 0 HighMem: empty May 6 22:31:29 pgblade02 kernel: Swap cache: add 71286821, delete 71287152, find 207780333/216904318, race 1387+10506 May 6 22:31:29 pgblade02 kernel: Free swap = 0kB May 6 22:31:30 pgblade02 kernel: Total swap = 8388600kB May 6 22:31:30 pgbla
Re: [ADMIN] ERROR: XX001 (Critical and Urgent)
Siddharth Shah wrote: >>> xdb=# reindex index pg_class_relname_nsp_index; >>> >>> Now INDEXing taking High CPU and postgres baffled. >> consider doing your recovery in single-user mode > postgres --single -P -D $DATADIR -p 5433 xdb > Same behavior in single mode. How long did you leave it running? Did you get any messages? Is there anything in the log? What do CPU usage and disk usage look like during the attempt? -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgres invoked oom-killer
On Fri, May 7, 2010 at 8:26 AM, Silvio Brandani wrote: > We have a postgres 8.3.8 on linux > > We get following messages int /var/log/messages: > > May 6 22:31:01 pgblade02 kernel: postgres invoked oom-killer: > gfp_mask=0x201d2, order=0, oomkilladj=0 > *** snip *** > May 6 22:31:30 pgblade02 kernel: Out of memory: Killed process 29076 > (postgres). > Silvio, Is this system a virtual machine? Greg
Re: [ADMIN] postgres invoked oom-killer
Lacey Powers ha scritto: Silvio Brandani wrote: We have a postgres 8.3.8 on linux We get following messages int /var/log/messages: May 6 22:31:01 pgblade02 kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 May 6 22:31:01 pgblade02 kernel: May 6 22:31:01 pgblade02 kernel: Call Trace: May 6 22:31:19 pgblade02 kernel: [] out_of_memory+0x8e/0x2f5 May 6 22:31:19 pgblade02 kernel: [] __alloc_pages+0x22b/0x2b4 May 6 22:31:19 pgblade02 kernel: [] __do_page_cache_readahead+0x95/0x1d9 May 6 22:31:19 pgblade02 kernel: [] __wait_on_bit_lock+0x5b/0x66 May 6 22:31:19 pgblade02 kernel: [] :dm_mod:dm_any_congested+0x38/0x3f May 6 22:31:19 pgblade02 kernel: [] filemap_nopage+0x148/0x322 May 6 22:31:19 pgblade02 kernel: [] __handle_mm_fault+0x1f8/0xdf4 May 6 22:31:19 pgblade02 kernel: [] do_page_fault+0x4b8/0x81d May 6 22:31:19 pgblade02 kernel: [] thread_return+0x0/0xeb May 6 22:31:19 pgblade02 kernel: [] error_exit+0x0/0x84 May 6 22:31:27 pgblade02 kernel: May 6 22:31:28 pgblade02 kernel: Mem-info: May 6 22:31:28 pgblade02 kernel: Node 0 DMA per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 0 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: Node 0 DMA32 per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:27 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:54 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:23 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:49 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:12 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:14 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:50 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:60 May 6 22:31:29 pgblade02 kernel: Node 0 Normal per-cpu: May 6 22:31:29 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:5 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:48 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:11 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:39 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:14 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:57 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:94 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:36 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem per-cpu: empty May 6 22:31:29 pgblade02 kernel: Free pages: 41788kB (0kB HighMem) May 6 22:31:29 pgblade02 kernel: Active:974250 inactive:920579 dirty:0 writeback:0 unstable:0 free:10447 slab:11470 mapped-file:985 mapped-anon:1848625 pagetables:111027 May 6 22:31:29 pgblade02 kernel: Node 0 DMA free:11172kB min:12kB low:12kB high:16kB active:0kB inactive:0kB present:10816kB pages_scanned:0 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 3254 8052 8052 May 6 22:31:29 pgblade02 kernel: Node 0 DMA32 free:23804kB min:4636kB low:5792kB high:6952kB active:1555260kB inactive:1566144kB present:3332668kB pages_scanned:35703257 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 4797 4797 May 6 22:31:29 pgblade02 kernel: Node 0 Normal free:6812kB min:6836kB low:8544kB high:10252kB active:2342332kB inactive:2115836kB present:4912640kB pages_scanned:10165709 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 DMA: 3*4kB 5*8kB 3*16kB 6*32kB 4*64kB 3*128kB 0*256kB 0*512kB 2*1024kB 0*2048kB 2*4096kB = 11172kB May 6 22:31:29 pgblade02 kernel: Node 0 DMA32: 27*4kB 0*8kB 1*16kB 0*32kB 2*64kB 4*128kB 0*256kB 1*512kB 0*1024kB 1*2048kB 5*4096kB = 23804kB May 6 22:31:29 pgblade02 ker if it asks for more memory than is actually available. nel: Node 0 Normal: 21*4kB 9*8kB 26*16kB 3*32kB 6*64kB 5*128kB 0*256kB 0*512kB 1*1024kB 0*2048kB 1*4096kB = 6812kB May 6 22:31:29 pgblade02 kernel: Node 0 HighMem: empty May 6 22:31:29 pgblade02 kernel: Swap cache: add 71286821, delete 71287152, find 207780333/216904318, race 1387+10506 May 6 22:31:29 pgblade02 kernel: Free swap = 0kB May 6 22:31:30 pgblade02 kernel: Total swap = 838
Re: [ADMIN] ERROR: XX001 (Critical and Urgent)
Kevin Grittner wrote: Siddharth Shah wrote: xdb=# reindex index pg_class_relname_nsp_index; Now INDEXing taking High CPU and postgres baffled. consider doing your recovery in single-user mode postgres --single -P -D $DATADIR -p 5433 xdb Same behavior in single mode. How long did you leave it running? Did you get any messages? Is there anything in the log? What do CPU usage and disk usage look like during the attempt? -Kevin Kevin, It start normally , I have successfully retrieved data from few tables But I am not able to do dS / dT or dt. As you said this is index file for Postgres tables and indexes Now when I taken call for reindex pg_class_relname_nsp_index it takes 99% CPU PID PPID USER STAT VSZ %MEM %CPU COMMAND 13419 13418 nobody R39172 8% 99% postgres --single -P -D /var/db -p 5433 xdb It's been running from 10 minutes still there is no output or logs.
Re: [ADMIN] postgres invoked oom-killer
Greg Spiegelberg ha scritto: On Fri, May 7, 2010 at 8:26 AM, Silvio Brandani mailto:silvio.brand...@tech.sdb.it>> wrote: We have a postgres 8.3.8 on linux We get following messages int /var/log/messages: May 6 22:31:01 pgblade02 kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 *** snip *** May 6 22:31:30 pgblade02 kernel: Out of memory: Killed process 29076 (postgres). Silvio, Is this system a virtual machine? Greg No is not, is up and runnig from 4 months , 60 G of data in 9 different databases. Lately we import a new schema in one of those databases . Silvio -- Silvio Brandani Infrastructure Administrator SDB Information Technology Phone: +39.055.3811222 Fax: +39.055.5201119 --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme. -- -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgres invoked oom-killer
Silvio Brandani ha scritto: Lacey Powers ha scritto: Silvio Brandani wrote: We have a postgres 8.3.8 on linux We get following messages int /var/log/messages: May 6 22:31:01 pgblade02 kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 May 6 22:31:01 pgblade02 kernel: May 6 22:31:01 pgblade02 kernel: Call Trace: May 6 22:31:19 pgblade02 kernel: [] out_of_memory+0x8e/0x2f5 May 6 22:31:19 pgblade02 kernel: [] __alloc_pages+0x22b/0x2b4 May 6 22:31:19 pgblade02 kernel: [] __do_page_cache_readahead+0x95/0x1d9 May 6 22:31:19 pgblade02 kernel: [] __wait_on_bit_lock+0x5b/0x66 May 6 22:31:19 pgblade02 kernel: [] :dm_mod:dm_any_congested+0x38/0x3f May 6 22:31:19 pgblade02 kernel: [] filemap_nopage+0x148/0x322 May 6 22:31:19 pgblade02 kernel: [] __handle_mm_fault+0x1f8/0xdf4 May 6 22:31:19 pgblade02 kernel: [] do_page_fault+0x4b8/0x81d May 6 22:31:19 pgblade02 kernel: [] thread_return+0x0/0xeb May 6 22:31:19 pgblade02 kernel: [] error_exit+0x0/0x84 May 6 22:31:27 pgblade02 kernel: May 6 22:31:28 pgblade02 kernel: Mem-info: May 6 22:31:28 pgblade02 kernel: Node 0 DMA per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 0 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: Node 0 DMA32 per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:27 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:54 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:23 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:49 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:12 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:14 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:50 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:60 May 6 22:31:29 pgblade02 kernel: Node 0 Normal per-cpu: May 6 22:31:29 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:5 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:48 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:11 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:39 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:14 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:57 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:94 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:36 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem per-cpu: empty May 6 22:31:29 pgblade02 kernel: Free pages: 41788kB (0kB HighMem) May 6 22:31:29 pgblade02 kernel: Active:974250 inactive:920579 dirty:0 writeback:0 unstable:0 free:10447 slab:11470 mapped-file:985 mapped-anon:1848625 pagetables:111027 May 6 22:31:29 pgblade02 kernel: Node 0 DMA free:11172kB min:12kB low:12kB high:16kB active:0kB inactive:0kB present:10816kB pages_scanned:0 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 3254 8052 8052 May 6 22:31:29 pgblade02 kernel: Node 0 DMA32 free:23804kB min:4636kB low:5792kB high:6952kB active:1555260kB inactive:1566144kB present:3332668kB pages_scanned:35703257 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 4797 4797 May 6 22:31:29 pgblade02 kernel: Node 0 Normal free:6812kB min:6836kB low:8544kB high:10252kB active:2342332kB inactive:2115836kB present:4912640kB pages_scanned:10165709 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 DMA: 3*4kB 5*8kB 3*16kB 6*32kB 4*64kB 3*128kB 0*256kB 0*512kB 2*1024kB 0*2048kB 2*4096kB = 11172kB May 6 22:31:29 pgblade02 kernel: Node 0 DMA32: 27*4kB 0*8kB 1*16kB 0*32kB 2*64kB 4*128kB 0*256kB 1*512kB 0*1024kB 1*2048kB 5*4096kB = 23804kB May 6 22:31:29 pgblade02 ker if it asks for more memory than is actually available. nel: Node 0 Normal: 21*4kB 9*8kB 26*16kB 3*32kB 6*64kB 5*128kB 0*256kB 0*512kB 1*1024kB 0*2048kB 1*4096kB = 6812kB May 6 22:31:29 pgblade02 kernel: Node 0 HighMem: empty May 6 22:31:29 pgblade02 kernel: Swap cache: add 71286821, delete 71287152, find 207780333/216904318, race 1387+10506 May 6 22:31:29 pgblade02 kernel: Free swap = 0kB May 6 22:31:30 pgbl
Re: [ADMIN] postgres invoked oom-killer
Silvio Brandani ha scritto: Greg Spiegelberg ha scritto: On Fri, May 7, 2010 at 8:26 AM, Silvio Brandani mailto:silvio.brand...@tech.sdb.it>> wrote: We have a postgres 8.3.8 on linux We get following messages int /var/log/messages: May 6 22:31:01 pgblade02 kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 *** snip *** May 6 22:31:30 pgblade02 kernel: Out of memory: Killed process 29076 (postgres). Silvio, Is this system a virtual machine? Greg No is not, is up and runnig from 4 months , 60 G of data in 9 different databases. Lately we import a new schema in one of those databases . Silvio No is not, is up and runnig from 4 months , 60 G of data in 9 different databases. Lately we import a new schema in one of those databases . Silvio --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme. -- -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] ERROR: XX001 (Critical and Urgent)
Siddharth Shah writes: > PID PPID USER STAT VSZ %MEM %CPU COMMAND > 13419 13418 nobody R39172 8% 99% postgres --single -P -D > /var/db -p 5433 xdb > It's been running from 10 minutes still there is no output or logs. What does "strace" show that process is doing? regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgres invoked oom-killer
Silvio Brandani wrote: Lacey Powers ha scritto: Silvio Brandani wrote: We have a postgres 8.3.8 on linux We get following messages int /var/log/messages: May 6 22:31:01 pgblade02 kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 May 6 22:31:01 pgblade02 kernel: May 6 22:31:01 pgblade02 kernel: Call Trace: May 6 22:31:19 pgblade02 kernel: [] out_of_memory+0x8e/0x2f5 May 6 22:31:19 pgblade02 kernel: [] __alloc_pages+0x22b/0x2b4 May 6 22:31:19 pgblade02 kernel: [] __do_page_cache_readahead+0x95/0x1d9 May 6 22:31:19 pgblade02 kernel: [] __wait_on_bit_lock+0x5b/0x66 May 6 22:31:19 pgblade02 kernel: [] :dm_mod:dm_any_congested+0x38/0x3f May 6 22:31:19 pgblade02 kernel: [] filemap_nopage+0x148/0x322 May 6 22:31:19 pgblade02 kernel: [] __handle_mm_fault+0x1f8/0xdf4 May 6 22:31:19 pgblade02 kernel: [] do_page_fault+0x4b8/0x81d May 6 22:31:19 pgblade02 kernel: [] thread_return+0x0/0xeb May 6 22:31:19 pgblade02 kernel: [] error_exit+0x0/0x84 May 6 22:31:27 pgblade02 kernel: May 6 22:31:28 pgblade02 kernel: Mem-info: May 6 22:31:28 pgblade02 kernel: Node 0 DMA per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 0 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 1 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 2 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 hot: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: cpu 3 cold: high 0, batch 1 used:0 May 6 22:31:28 pgblade02 kernel: Node 0 DMA32 per-cpu: May 6 22:31:28 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:27 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:54 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:23 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:49 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:12 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:14 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:50 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:60 May 6 22:31:29 pgblade02 kernel: Node 0 Normal per-cpu: May 6 22:31:29 pgblade02 kernel: cpu 0 hot: high 186, batch 31 used:5 May 6 22:31:29 pgblade02 kernel: cpu 0 cold: high 62, batch 15 used:48 May 6 22:31:29 pgblade02 kernel: cpu 1 hot: high 186, batch 31 used:11 May 6 22:31:29 pgblade02 kernel: cpu 1 cold: high 62, batch 15 used:39 May 6 22:31:29 pgblade02 kernel: cpu 2 hot: high 186, batch 31 used:14 May 6 22:31:29 pgblade02 kernel: cpu 2 cold: high 62, batch 15 used:57 May 6 22:31:29 pgblade02 kernel: cpu 3 hot: high 186, batch 31 used:94 May 6 22:31:29 pgblade02 kernel: cpu 3 cold: high 62, batch 15 used:36 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem per-cpu: empty May 6 22:31:29 pgblade02 kernel: Free pages: 41788kB (0kB HighMem) May 6 22:31:29 pgblade02 kernel: Active:974250 inactive:920579 dirty:0 writeback:0 unstable:0 free:10447 slab:11470 mapped-file:985 mapped-anon:1848625 pagetables:111027 May 6 22:31:29 pgblade02 kernel: Node 0 DMA free:11172kB min:12kB low:12kB high:16kB active:0kB inactive:0kB present:10816kB pages_scanned:0 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 3254 8052 8052 May 6 22:31:29 pgblade02 kernel: Node 0 DMA32 free:23804kB min:4636kB low:5792kB high:6952kB active:1555260kB inactive:1566144kB present:3332668kB pages_scanned:35703257 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 4797 4797 May 6 22:31:29 pgblade02 kernel: Node 0 Normal free:6812kB min:6836kB low:8544kB high:10252kB active:2342332kB inactive:2115836kB present:4912640kB pages_scanned:10165709 all_unreclaimable? yes May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no May 6 22:31:29 pgblade02 kernel: lowmem_reserve[]: 0 0 0 0 May 6 22:31:29 pgblade02 kernel: Node 0 DMA: 3*4kB 5*8kB 3*16kB 6*32kB 4*64kB 3*128kB 0*256kB 0*512kB 2*1024kB 0*2048kB 2*4096kB = 11172kB May 6 22:31:29 pgblade02 kernel: Node 0 DMA32: 27*4kB 0*8kB 1*16kB 0*32kB 2*64kB 4*128kB 0*256kB 1*512kB 0*1024kB 1*2048kB 5*4096kB = 23804kB May 6 22:31:29 pgblade02 ker if it asks for more memory than is actually available. nel: Node 0 Normal: 21*4kB 9*8kB 26*16kB 3*32kB 6*64kB 5*128kB 0*256kB 0*512kB 1*1024kB 0*2048kB 1*4096kB = 6812kB May 6 22:31:29 pgblade02 kernel: Node 0 HighMem: empty May 6 22:31:29 pgblade02 kernel: Swap cache: add 71286821, delete 71287152, find 207780333/216904318, race 1387+10506 May 6 22:31:29 pgblade02 kernel: Free swap = 0kB May 6 22:31:30 pgblade02
Re: [ADMIN] help me on creation cube
raha sadeghi wrote: > i had active cube in template1; i mean i run cube.sql and there > are cube functions in my databases but i don't know how to create > cube!!! > please help me and explain me how to do these kind of things step > by step!! Before anyone can make useful suggestions, I think you need to provide a bit more detail about what you have, what you're trying to do, and what problems you're having. Even though you're not reporting a problem, exactly, some of the suggestions here may help: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin