Thread: How i can empty the buffers of a db
How can i empty the buffers of a database during an open session to it
-or not?
SWTHRHS TOYRTOYNHS
(tourtoun@csd.uch.gr)
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:
>
> > 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
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]. 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. 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, 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
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> 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
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
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? :-)