How i can empty the buffers of a db

On Fri, 2 Aug 2002, Tourtounis Sotiris wrote:

How can i empty the buffers of a database during an open session to it
-or not?

Not that I know the answer or anything but which buffers do you mean?
Stuff in shm? WAL?

Joshua b. Jore ; http://www.greentechnologist.org

i mean the amount of buffers that you state when you load the postmaster
on a machine - i don't know if i express it proper in order to understand
!!!

SWTHRHS TOYRTOYNHS
(tourtoun@csd.uch.gr)

On Fri, 2 Aug 2002, Josh Jore wrote:

On Fri, 2 Aug 2002, Tourtounis Sotiris wrote:

i mean the amount of buffers that you state when you load the postmaster
on a machine - i don't know if i express it proper in order to understand
!!!

I take it you mean the shared memory buffer then[1]B nbuffers Sets the number of shared buffers for use by the server processes. This value defaults to 64 buffers, where each buffer is 8 kB.. You stop PostgreSQL.
If you *still* need to clear the shared memory then you read ipcclean(1).

[1]: B nbuffers Sets the number of shared buffers for use by the server processes. This value defaults to 64 buffers, where each buffer is 8 kB.
Sets the number of shared buffers for use by the server processes. This
value defaults to 64 buffers, where each buffer is 8 kB.

Joshua b. Jore ; http://www.greentechnologist.org

Josh Jore <josh@greentechnologist.org> writes:

I take it you mean the shared memory buffer then[1]. You stop PostgreSQL.

Probably a more interesting question is why would you want to? What
is it you actually want to accomplish?

regards, tom lane

On Fri, 2 Aug 2002, Tom Lane wrote:

Josh Jore <josh@greentechnologist.org> writes:

I take it you mean the shared memory buffer then[1]. You stop PostgreSQL.

Probably a more interesting question is why would you want to? What
is it you actually want to accomplish?

I don't know about you but I'm prepared to be fully boggled by people's
wishes for functionality. My only guess is that he wanted to avoid initial
use of cache or something. Maybe to watch disk io or something like that.
Anyhow, I suppose that also means just do the work directly after a fresh
boot to avoid the filesystem buffer cache as well. It's all very wacky.

Joshua b. Jore ; http://www.greentechnologist.org

I am sorry for my lack of good knowledge of English but i have previously
asked how during a session with the database server to empty the memory
buffers after any commited select/insert/delete in order to have an as
much as possible indicative execution time and explain facility for each
of them. Thank you for your willingness of help !!!

SWTHRHS TOYRTOYNHS
(tourtoun@csd.uch.gr)

On Fri, 2 Aug 2002, Josh Jore wrote:

On Fri, Aug 02, 2002 at 06:29:33PM +0300, Tourtounis Sotiris wrote:

I am sorry for my lack of good knowledge of English but i have previously
asked how during a session with the database server to empty the memory
buffers after any commited select/insert/delete in order to have an as
much as possible indicative execution time and explain facility for each
of them. Thank you for your willingness of help !!!

Ah, then you don't want to empty the buffers. Just make sure fsync
is turned on (if it is, fsync _must_ be called before your
transaction COMMITs). To see some statistics, try turing on query
stats or executor stats. See

<http://www.postgresql.org/idocs/index.php?runtime-config.html#LOGGING&gt;

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Tourtounis Sotiris <tourtoun@csd.uoc.gr> writes:

I am sorry for my lack of good knowledge of English but i have previously
asked how during a session with the database server to empty the memory
buffers after any commited select/insert/delete in order to have an as
much as possible indicative execution time and explain facility for each
of them. Thank you for your willingness of help !!!

Ah. In that case Josh's guess was right: you want to reboot the machine
for each query. That's the only way AFAIK to flush the kernel's disk
caches. Since Postgres relies on the kernel's disk buffering quite as
much as its own buffering, just flushing Postgres' buffers wouldn't get
you back to a standing start anyway.

But I'm not sure that you'd be proving a lot by running your tests that
way --- it's not got a lot to do with realistic usage conditions. The
normal state of affairs for a database under load is that there is lots
of stuff in disk cache.

regards, tom lane

On Fri, 2 Aug 2002, Tom Lane wrote:

Tourtounis Sotiris <tourtoun@csd.uoc.gr> writes:

I am sorry for my lack of good knowledge of English but i have previously
asked how during a session with the database server to empty the memory
buffers after any commited select/insert/delete in order to have an as
much as possible indicative execution time and explain facility for each
of them. Thank you for your willingness of help !!!

Ah. In that case Josh's guess was right: you want to reboot the machine
for each query. That's the only way AFAIK to flush the kernel's disk
caches. Since Postgres relies on the kernel's disk buffering quite as
much as its own buffering, just flushing Postgres' buffers wouldn't get
you back to a standing start anyway.

But I'm not sure that you'd be proving a lot by running your tests that
way --- it's not got a lot to do with realistic usage conditions. The
normal state of affairs for a database under load is that there is lots
of stuff in disk cache.

Nah, just write something that allocates enough memory to make the kernel
give up it's disk cache, then give it right back. Don't do this on
production machines (I don't really need to add that do I? :-)

Tom Lane <tgl@sss.pgh.pa.us> writes:

Tourtounis Sotiris <tourtoun@csd.uoc.gr> writes:

I am sorry for my lack of good knowledge of English but i have previously
asked how during a session with the database server to empty the memory
buffers after any commited select/insert/delete in order to have an as
much as possible indicative execution time and explain facility for each
of them. Thank you for your willingness of help !!!

Ah. In that case Josh's guess was right: you want to reboot the machine
for each query. That's the only way AFAIK to flush the kernel's disk
caches. Since Postgres relies on the kernel's disk buffering quite as
much as its own buffering, just flushing Postgres' buffers wouldn't get
you back to a standing start anyway.

Short of rebooting, you could umount and remount the partition that
$PGDATA lives on, if no other daemons are using it (and it's not your
root partition). At least on Linux, that'll flush out all the cached
blocks from that partition.

-Doug