Re: Storage space lost during an UPDATE

#1

On Saturday 03 July 2004 6:37 am, Vitaly Belman wrote:

Quote:

I ran the following query on my database:

---------------
update bv_descriptions set description = REPLACE(description,
'\\n', '\n'); commit;
---------------

It finished fine but to my surprise when I ran "df" on the server
the drive usage jumped from 44% to 60% which is additional 650MB.

I tried to restart the server but it didn't do any good, eventually
I tried to do a FULL VACUUM on that table and it did the trick and
cleaned the lost space.

I am still confused about what happened... What took these
additional 650MB?

I could understand this phenomenon if it happened before I did
COMMIT... But why did it stay after COMMIT too?

This is expected. It has to do with MVCC, not commit. PostgreSQL
creates a duplicate record for each record that was updated. This is
how MVCC hangs on to "old" records that may still be in use by other
transactions.

A regular vacuum only checks for space within the file that can be
reused by new records (non-current records that are no longer held by
a transaction). Vacuum full actually reclaims physical space on the
disk. By way of example:

vacuum full sometable

Space used by table = x

update all records in sometable

Space used by table = 2x (well, not actually 2x but probably somewhere
around that)

vacuum

Space used by sometable still = 2x but there is reusable space within
the table

update all records

Space used by sometable still somewhere around 2x - updated records
filled unused space in file.

vacuum full sometable

space used by table = x

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#2

Hi,

Is there anyone who knows about the extended query language? Nobody
answered my question yesterday. Could anyone put me in touch with
the person who programmed that part in the worst case?

I send a "parse command." If there's a syntax error, I get an error
back as expected, otherwise I don't get anything back even though the
docs say I should receive a "parse complete" message back (and
hopefully a "ready for query" too even though that's not mentionned).

Is the extended query language operational yet? (PostgreSQL 7.4.2)

Thanks,

Marc

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#3

I'm sending this parse command after which the backend completely
stops responding (just the session, not the whole server):

Byte1('P'): "P"
Int32: 41
String: "mystatement" + null byte
String: "select * from mytable;" + null byte
Int16: 0

I have to close and re-open the frontends' TCP/IP connection to be
able to work again. PostgreSQL 7.4.2.

Marc

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#4

Ah, I understand. Thanks

.

Steve Crawford wrote:

Quote:

This is expected. It has to do with MVCC, not commit. PostgreSQL
creates a duplicate record for each record that was updated. This is
how MVCC hangs on to "old" records that may still be in use by other
transactions.

SNIP

Cheers,
Steve

#5

"M. Bastin" <marcbastin (AT) mindspring (DOT) com> writes:

Quote:

I'm sending this parse command after which the backend completely
stops responding (just the session, not the whole server):

Byte1('P'): "P"
Int32: 41
String: "mystatement" + null byte
String: "select * from mytable;" + null byte
Int16: 0

You haven't done anything to ensure a response message (viz, Flush or
Sync). See nearby discussion about brokenness of Tcl interface...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#6

Hi,

Is there a query on some system table that will list all names of
prepared statements?

(FYI: To those wondering about the extended query language I've got
it working now and it is impressive. E.g. for an uncomplicated query
like

SELECT * FROM population WHERE age > 12 AND age < 20;

the total execution time will be _twice as fast_ as with the simple
query language. When you look at only the PostgreSQL reaction time
it is more than 10 times faster, while the time for the reception and
parsing of the data by the frontend remains the same of course.

I haven't tried heavy, complicated queries yet but I can only imagine
that will give even more spectacular results.

The fact that you can work with parameters opens up a whole new
dimension as well, e.g.:

SELECT * FROM population WHERE age > $1 AND age < $2;

I only wish there were more documentation about the extended query
language. All I've found on the whole internet would hold on 2 to 3
printed pages. Did I miss something?)

Thanks,

Marc

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#7

"M. Bastin" <marcbastin (AT) mindspring (DOT) com> writes:

Quote:

Is there a query on some system table that will list all names of
prepared statements?

No, since they are never stored on disk. We could think of providing
some operation in the FE/BE protocol to list the available statements,
perhaps. (In retrospect I guess it was a mistake to have the Describe
message raise a hard error for no-such-statement. If it just sent
back a message without aborting your transaction, you could use Describe
to probe whether you'd prepared a given name yet.)

Same comments apply to Portals I suppose.

If you'd like to lobby to get this changed, pgsql-novice is not the
place; pgsql-interfaces or pgsql-hackers would be more appropriate.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#8

At 10:37 AM -0400 7/14/04, Tom Lane wrote:

Quote:

"M. Bastin" <marcbastin (AT) mindspring (DOT) com> writes:
Is there a query on some system table that will list all names of
prepared statements?

No, since they are never stored on disk.
Same comments apply to Portals I suppose.

If you'd like to lobby to get this changed

I don't believe this is a critical issue. People can keep the list
in their client app just the same since prepared statements only live
for the duration of a session.

Thank you very much Tom for all your help so far during my first
steps with the extended query protocol!

Marc

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#9

M. Bastin wrote:

Quote:

[...]
The fact that you can work with parameters opens up a whole new
dimension as well, e.g.:

SELECT * FROM population WHERE age > $1 AND age < $2;

I only wish there were more documentation about the extended query
language. All I've found on the whole internet would hold on 2 to 3
printed pages. Did I miss something?)

I'm not sure what exactly you are missing, but the page
documenting the PREPARE statement (in the official Postgres
docs) pretty much says it all. Also, it's easy to find
(Docs -> Reference -> SQL Commands -> PREPARE).

http://www.postgresql.org/docs/7.4/s...l-prepare.html

However, maybe we're actually talking about completely
different things -- in that case I would like to know what
you are talking about. :-)

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

Quote:

Can the denizens of this group enlighten me about what the
advantages of Python are, versus Perl ?
"python" is more likely to pass unharmed through your spelling

checker than "perl".
-- An unknown poster and Fredrik Lundh

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#10

Hi Oliver,

Quote:

I only wish there were more documentation about the extended query
language. All I've found on the whole internet would hold on 2 to 3
printed pages. Did I miss something?)

I'm not sure what exactly you are missing, but the page
documenting the PREPARE statement (in the official Postgres
docs) pretty much says it all. Also, it's easy to find
(Docs -> Reference -> SQL Commands -> PREPARE).

http://www.postgresql.org/docs/7.4/s...l-prepare.html

However, maybe we're actually talking about completely
different things

Yes and no. I'm looking at the subject from the frontend-backend
protocol 3.0 point of view which is separated in the Extended Query
language and the Simple Query language.

With the PREPARE and EXECUTE statements you're accessing the Extended
Query language through the Simple Query language, which requires more
CPU time since your EXECUTE query itself is going to be parsed as a
Simple Query first before PostgreSQL realizes it must execute a
prepared statement, while when you send the commands directly through
the Extended Query language you skip that parsing step.

Plus the Extended Query language is far richer than just preparing
and executing even though these 2 operations will probably cover most
needs. The Extended Query language is also much more complex to use.
E.g. you would do the EXECUTE by issuing about 5 commands--but it's
still much faster. That's where I had problems and wished for more
documentation and/or examples.

I hope this clears up the confusion.

Cheers,

Marc

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org