[GENERAL] Unusually high IO for autovacuum worker

Hi everyone, We are experiencing a strange situation after upgrading our Postgres from 8.4 to 9.1 and our OS from Debian 6 to Ubuntu 12.04 LTS. We have an application where we are collecting timestamp-based data. Since a month of data goes over 30M rows (and growing each month), we decided to par

Re: [GENERAL] Unusually high IO for autovacuum worker

On Fri, Feb 1, 2013 at 1:44 PM, Vlad Bailescu wrote: > Hi everyone, > > We are experiencing a strange situation after upgrading our Postgres from > 8.4 to 9.1 and our OS from Debian 6 to Ubuntu 12.04 LTS. > > We have an application where we are collecting timestamp-based data. Since a > month of d

Re: [GENERAL] Unusually high IO for autovacuum worker

On Fri, Feb 1, 2013 at 11:01 AM, Pavan Deolasee wrote: > > Do you know for sure that its the master table that generating all the > vacuum traffic ? What about the partition tables ? Do they get any > updates/deletes ? It might be useful if you could turn autovacuum > logging ON and see which tabl

Re: [GENERAL] naming of wal-archives

Yes, it does indeed interleave and it seems to archive the backlog just before the files are about to be deleted. That explains it. Thanks for your help, Neil 2013/1/31 Jeff Janes > On Thu, Jan 31, 2013 at 12:50 AM, Neil Worden > wrote: > > > > The situation is as follows: > > > > All conc

Re [GENERAL] Deleting 173000 records takes forever

Alexander Farber wrote: delete from pref_users where id not in (select distinct id from pref_money); Hi, try instead delete from pref_users where id in (select id from pref_users except select id from pref_money); Regards Wolfgang Hamann -- Sent via pgsql-general mailing list (pgsql-general@

Re: [GENERAL] Deleting 173000 records takes forever, blocks async queries for unrelated records

Alexander Farber wrote: > in a Facebook game running on > PostgreSQL 8.4.13 and having so many players: > > # select count(*) from pref_users; > count > > 223964 > > I am trying to get rid of inactive users, > who just visited the canvas page, but > never played (I'm sure, Facebook ha

Re: [GENERAL] Deleting 173000 records takes forever, blocks async queries for unrelated records

On Fri, 1 Feb 2013 10:38:26 +0100, Alexander Farber wrote: > Hello, > > in a Facebook game running on > PostgreSQL 8.4.13 and having so many players: > > # select count(*) from pref_users; > count > > 223964 > > I am trying to get rid of inactive users, > who just visited the canvas

Re: [GENERAL] Deleting 173000 records takes forever, blocks async queries for unrelated records

Hi, What Albe said about PKs. I'm also not very fond of people using text in keys (even if it's a varchar or a char field). Test the same thing with a numeric key and you are likely to see a difference. Apart from making sure your design is ok, you might want to keep this stuff well in the backgr

Re: [GENERAL] Unusually high IO for autovacuum worker

On Fri, Feb 1, 2013 at 3:24 PM, Vlad Bailescu wrote: > > > Stats show only 3 tables get frequently autovacuumed > > Table Name Tuples inserted Tuples updated Tuples deleted Tuples HOT > updated Live Tuples Dead Tuples Last vacuum Last autovacuum Last analyze Last > autoanalyze Vacuum counter A

Re: [GENERAL] Unusually high IO for autovacuum worker

On Fri, Feb 1, 2013 at 1:08 PM, Pavan Deolasee wrote: > > > On Fri, Feb 1, 2013 at 3:24 PM, Vlad Bailescu wrote: > >> >> >> Stats show only 3 tables get frequently autovacuumed >> >> Table Name Tuples inserted Tuples updated Tuples deleted Tuples HOT >> updated Live Tuples Dead Tuples Last vac

Re: [GENERAL] Deleting 173000 records takes forever, blocks async queries for unrelated records

righto. You need a CTE to do that create table deletable ( id bigint not null primary key); create table condition ( id bigint not null primary key); insert into deletable select generate_series(1,500); insert into condition select generate_series(1,50); WITH target AS ( select id from dele

[GENERAL] Windows documentation

Hi, I just noticed that on this page: http://www.postgresql.org/docs/current/static/install-windows.html it states "Native builds of psql don't support command line editing" which is clearly not true. psql (at least with 9.2.x but I'm pretty sure this has been working in older ver

Re: [GENERAL] Unusually high IO for autovacuum worker

On Fri, Feb 1, 2013 at 5:19 PM, Vlad Bailescu wrote: > > > Pavan, it seems there's a small issue with copy-paste and column > text-align. Table sizes are: > > 4136 kB 2448 kB 2336 kB Ah OK. I see. > > 2012-12-05 00:44:23 EET LOG: automatic analyze of table > "fleet.fleet.vehicle_position"

[GENERAL] Postgres version number support

Which versions of Postgres support LOB? Which versions of Postgres does Hibernate 3.2.6..(Gavin left no clues in the Hibernate docs)?Thanks! Martin Gainty __ Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et

Re: [GENERAL] Unusually high IO for autovacuum worker

Vlad Bailescu wrote: > Because of our application stack (Hibernate ORM) we use a > before_insert trigger to insert rows into the right partition and > in the master table and then an after_insert trigger to delete > them from master table (we need the original insert to return the > inserted row

Re: [GENERAL] Unusually high IO for autovacuum worker

On Fri, Feb 1, 2013 at 2:40 PM, Pavan Deolasee wrote: > > > On Fri, Feb 1, 2013 at 5:19 PM, Vlad Bailescu wrote: > >> >> >> Pavan, it seems there's a small issue with copy-paste and column >> text-align. Table sizes are: >> >> 4136 kB 2448 kB 2336 kB > > > Ah OK. I see. > > >> >> 2012-12-05 00

Re: [GENERAL] [JDBC] JDBC connection test with SSL on PG 9.2.1 server

> On Tuesday, January 29, 2013 10:12 PM danap wrote: >> Hari Babu wrote: >>> On Monday, January 28, 2013 10:20 PM, Dave Cramer wrote: >>> >>> >>On Mon, Jan 28, 2013 at 9:03 AM, Hari Babu>> > wrote: >>> >>> >>While testing PostgreSQL JDBC java client to conn

Re: [GENERAL] [JDBC] JDBC connection test with SSL on PG 9.2.1 server

On 02/01/2013 06:06 AM, Hari Babu wrote: We tried the approach as suggested by you but still it is not working as shown in the below log (I had enabled logLevel as 1) keystore passowrd is qwerty 19:26:22.666 (1) PostgreSQL 9.2 JDBC4 (build 1002) 19:26:23.451 (1) Receive Buffer Size is 4380

Re: [GENERAL] Deleting 173000 records takes forever, blocks async queries for unrelated records

>># delete from pref_users >>where id not in (select distinct id from pref_money) limit 10; >>ERROR: syntax error at or near "limit" >>LINE 2: ...ere id not in (select distinct id from pref_money) limit 10; Or this? DELETE FROM pref_users WHERE id IN ( SELECT id FROM pref_users

[GENERAL] ATET, could it be made "more concurrent" ?

PostgreSQL 9.1.x I try to enable my trigger "ALTER TABLE cool_tbl ENABLE TRIGGER trg_for_cool_tbl;" I notice that it gets blocked by another backend doing "select xxx from cool_tbl;" (of course, my pain is if this transaction lasts for minutes, instead of seconds) I understand that [postgre

Re: [GENERAL] Parsing COPY ... WITH BINARY

On 2013-01-31 17:38:26 -0600, Merlin Moncure wrote: > On Thu, Jan 31, 2013 at 8:47 AM, Leonardo M. Ramé wrote: > > I'm using this: > > > > COPY( select field1, field2, field3 from table ) TO 'C://Program > > Files/PostgreSql//8.4//data//output.dat' WITH BINARY > > > > To export some fields to a f

Re: [GENERAL] trouble with upgrade from 9.0 (many schemas and tables)

On Wednesday, January 30, 2013, Groshev Andrey wrote: > > > 30.01.2013, 18:47, "Jeff Janes" >: > > > You would probably want to use the pg_dump from 9.2, as there are > > improvements in that version of pg_dump to speed up partial dumps. > > You can use pg_dump from 9.2 against server 9.0 and sti