Thread: RES: Lock Problem
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 perminute in columns STATE and STATEDATE. 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 read about "DIRTY Transaction", is it the way? I have another solution? Thanks. Att. André Guergolet -----Mensagem original----- De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]Em nome de Andrew Sullivan Enviada em: quinta-feira, 24 de agosto de 2006 17:53 Para: pgsql-sql@postgresql.org Assunto: Re: [SQL] Lock Problem On Thu, Aug 24, 2006 at 05:27:58PM -0300, André José Guergolet wrote: > Hello all, I'm using the Postgres 8.0 and my product creates 7 > connections at the server. > > One of my connections do an update at a simple table with 360 rows > and I've got many table locks. How I can take more scability? It's pretty hard to tell, given what you're telling us. The UPDATE will take a write-blocking lock on each of the rows for the duration of the transaction. You shouldn't have any table locks, unless you're not telling us something. You should post more detail. Why do you think you have table locks, to begin with? A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
On Thu, 2006-08-24 at 16:12, André José Guergolet wrote: > 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 updatesper minute in columns STATE and STATEDATE. > > I list this states with a webpage. This webpage updates the list every 10 seconds. My page executes only "select * frommachinestates". > > If I stop the updates, I never get my page stopped at the select command. > > I read about "DIRTY Transaction", is it the way? > I have another solution? I'm guessing you've got a different problem. Generally speaking, in an MVCC database like PostgreSQL, readers don't block writers, and writers don't block readers. We need more info on how you're doing this. SQL queries for the updates etc... Are you vacuuming the database often enough? Is this table suffering from bloat?
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
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Thu, 2006-08-24 at 16:12, André José Guergolet wrote:
>> My system, checks if my script is running in each machine at this table, this table has 360 rows and has 50-100
updatesper minute in columns STATE and STATEDATE.
> Are you vacuuming the database often enough? Is this table suffering
> from bloat?
That's my bet. With numbers like those, that table has to get vacuumed
every few minutes to keep performance from going into the tank.
regards, tom lane
On Thu, Aug 24, 2006 at 06:12:32PM -0300, André José Guergolet wrote: > > 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. How often are you vacuuming it? Long-running UPDATEs with that many updates per minure makes me think that maybe you have a lot of dead rows. Anyway, none of this supports your claim that you're getting table locks. The place to see what locks you are getting is pg_locks. A -- Andrew Sullivan | ajs@crankycanuck.ca In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland