pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: RES: Lock Problem
Date: Thu, 24 Aug 2006 23:00:55 -0400
Message-ID: <87d5apzfh4.fsf@wolfe.cbbrowne.com> (raw)
References: <3FFA1818C1550246933DEA311C39B07612304A@CGEXC03.compugraf.com.br>

Centuries ago, Nostradamus foresaw when AGuergolet@compugraf.com.br (André José Guergolet) would write:
> Sorry, I have a table with 360 rows, in this table I control the state of machines on network:
>
>
> Ip 			State		StateDate
> 172.20.0.39		Running	2006-08-23 00:00:00
> 172.20.0.59		Running	2006-08-23 00:00:00
> 172.20.0.72		Running	2006-08-23 00:00:00
> 172.20.0.84		Running	2006-08-23 00:00:00
> 172.20.0.35		Running	2006-08-23 00:00:00
> 172.20.0.17		Running	2006-08-23 00:00:00
> 172.20.0.28		Running	2006-08-23 00:00:00
> 172.20.0.39		Running	2006-08-23 00:00:00
> 172.20.0.14		Running	2006-08-23 00:00:00
> 172.20.0.33		Running	2006-08-23 00:00:00
> 172.20.0.19		Running	2006-08-23 00:00:00
>
> My system, checks if my script is running in each machine at this
> table, this table has 360 rows and has 50-100 updates per minute in
> columns STATE and STATEDATE.

Question: How often are you vacuuming this table?

If, as you indicate, you're updating about 1/4 of the table each
minute, you should probably VACUUM the table about once a minute.

If you only VACUUM it once an hour or once a day, those 360 tuples
will be spread across 200,000 pages, and need a VACUUM FULL and a
REINDEX to draw the table back down to a decent size.

You can see how badly the table has grown by running the SQL:
  VACUUM VERBOSE machinestates;

This will list various statistics; generally, if you have many more
pages than tuples, there's probably a problem with how often you're
vacuuming...

> I list this states with a webpage. This webpage updates the list
> every 10 seconds. My page executes only "select * from
> machinestates".
>
> If I stop the updates, I never get my page stopped at the select
> command.

I don't understand what you mean by that.

In PostgreSQL, readers don't block writers and writers don't block
readers (unless you expressly ask for them to do so).

> I read about "DIRTY Transaction", is it the way?
> I have another solution?

I'm not sure you have successfully described either the observed
phenomenon or the nature of the problem; I know I don't yet quite
understand what seems wrong.

Sometimes language gets in the way; that seems possible here.  I also
think you're trying to solve the problem before understanding what it
is.

Please try to explain further what phenomenon you are observing; as
details emerge, hopefully someone will recognize what is going on.
Trying to fix it isn't the right thing to do at this point; just try
to explain what you see happening.  Getting to the point where someone
recognizes what is happening is really the goal.
-- 
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/lsf.html
"Just because the code is intended to cause flaming death is no reason
to get sloppy and leave off  the casts." - Tim Smith, regarding sample
(F0 0F C7 C8) Pentium Death code on comp.os.linux.advocacy



view thread (5+ messages)  latest in thread

Message-ID: <87d5apzfh4.fsf@wolfe.cbbrowne.com>
Permalink:  ../87d5apzfh4.fsf@wolfe.cbbrowne.com/
Also on:    postgresql.org/message-id/87d5apzfh4.fsf@wolfe.cbbrowne.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: RES: Lock Problem
  In-Reply-To: <87d5apzfh4.fsf@wolfe.cbbrowne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox