Обсуждение: clearing the buffer cache
Hi, I was doing some performance checks and wondered what the best way to clear out the shared buffers is? With the recent improvements in buffer management it appears a simple SELECT * FROM large_table; doesn't help here! I was hoping for a function I could call, or maybe some variable I write to, that would cause the contents to be invalidated. -- Sam http://samason.me.uk/
On Fri, 2009-02-13 at 17:49 +0000, Sam Mason wrote: > Hi, > > I was doing some performance checks and wondered what the best way to > clear out the shared buffers is? With the recent improvements in buffer > management it appears a simple SELECT * FROM large_table; doesn't help > here! > > I was hoping for a function I could call, or maybe some variable I write > to, that would cause the contents to be invalidated. Restart the database. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
On Fri, Feb 13, 2009 at 12:52:21PM -0500, Brad Nicholson wrote: > On Fri, 2009-02-13 at 17:49 +0000, Sam Mason wrote: > > I was doing some performance checks and wondered what the best way to > > clear out the shared buffers is? With the recent improvements in buffer > > management it appears a simple SELECT * FROM large_table; doesn't help > > here! > > > > I was hoping for a function I could call, or maybe some variable I write > > to, that would cause the contents to be invalidated. Isn't there anything faster than this? I was hoping to run many thousands of small (i.e. 100ms or less) queries and if it takes a couple of seconds to restart the database I'm not going to be able to do this. -- Sam http://samason.me.uk/
[ Sorry Brad, I didn't mean to delete your response! lets try again. ] On Fri, Feb 13, 2009 at 12:52:21PM -0500, Brad Nicholson wrote: > On Fri, 2009-02-13 at 17:49 +0000, Sam Mason wrote: > > I was doing some performance checks and wondered what the best way to > > clear out the shared buffers is? With the recent improvements in buffer > > management it appears a simple SELECT * FROM large_table; doesn't help > > here! > > > > I was hoping for a function I could call, or maybe some variable I write > > to, that would cause the contents to be invalidated. > > Restart the database. Isn't there anything faster than this? I was hoping to run many thousands of small (i.e. 100ms or less) queries and if it takes a couple of seconds to restart the database I'm not going to be able to do this. -- Sam http://samason.me.uk/
> Isn't there anything faster than this? > > I was hoping to run many thousands of small (i.e. 100ms or less) queries > and if it takes a couple of seconds to restart the database I'm not > going to be able to do this. Are you forgetting the OS's file system cache? That will also have a huge effect on performance, and so you'll need to clear it as well. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
On Fri, Feb 13, 2009 at 11:13:36AM -0700, Scott Ribe wrote: > > I was hoping to run many thousands of small (i.e. 100ms or less) queries > > and if it takes a couple of seconds to restart the database I'm not > > going to be able to do this. > > Are you forgetting the OS's file system cache? That will also have a huge > effect on performance, and so you'll need to clear it as well. With recent versions of Linux you can flush the system's buffer cache by doing: # echo 3 > /proc/sys/vm/drop_caches This is pretty quick; I do this, wait a small amount of time just in case and then start a new psql session. I'm currently having to put a restart of postgres before this flush as well which is slowing things down a lot. I'm not showing any statistically significant difference between the first run and subsequent runs which would suggest that I'm either all OK, or all wrong! -- Sam http://samason.me.uk/
On Fri, 13 Feb 2009, Sam Mason wrote: > I was hoping for a function I could call, or maybe some variable I write > to, that would cause the contents to be invalidated. For most people, just restarting the server is acceptable overhead, which is why nobody has bothered to write such a thing. It wouldn't be too difficult for someone familiar with the basic internals involved to hack in such a function, getting it accepted into the standard distribution would be quite another problem though. As you noticed, just "select * from t" where t is a dummy table doesn't help here anymore. What you could do is write a quick PL/pgSQL function that simulates that in a way that isn't optimized away. Create a table that's bigger than shared_buffers with a primary key, then iterate over that table record at a time using that key until you've scanned records to wipe out the cache. You can use contrib/pg_buffercache to confirm the function is doing what you expect. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
> With recent versions of Linux you can flush the system's buffer cache by > doing: > > # echo 3 > /proc/sys/vm/drop_caches You can also try something like this: http://wiki.services.openoffice.org/wiki/Cold-start-simulator Shutting down the server and running fillmem has worked for me, but that might take too long in your situation of thousands of small queries. But I guess "SELECT * FROM large_table" would have taken just as long anyway. --Chris
On Mon, Feb 16, 2009 at 11:09:59AM -0500, Chris Mayfield wrote: > >With recent versions of Linux you can flush the system's buffer cache by > >doing: > > > > # echo 3 > /proc/sys/vm/drop_caches > > You can also try something like this: > http://wiki.services.openoffice.org/wiki/Cold-start-simulator > > Shutting down the server and running fillmem has worked for me, but that > might take too long in your situation of thousands of small queries. Why is this better than asking the kernel to drop its caches? The kernel could reasonably assume that because you only touched the data in a page once it could swap the "fillmem"s data out in preference to the page cache. Even if it did, you need to restart the server which is what prevents testing going quickly in the first place. > But I guess "SELECT * FROM large_table" would have taken just as long > anyway. This doesn't work on several levels any more; PG is smart enough to recognize that this is what's happening and only uses a small buffer for this (256KB at the moment). Linux can be smart enough as well which is why you need to ask it to drop the page cache. -- Sam http://samason.me.uk/
On Mon, 16 Feb 2009, Sam Mason wrote: > On Mon, Feb 16, 2009 at 11:09:59AM -0500, Chris Mayfield wrote: >> You can also try something like this: >> http://wiki.services.openoffice.org/wiki/Cold-start-simulator >> might take too long in your situation of thousands of small queries. > > Why is this better than asking the kernel to drop its caches? fillmem/flushdisk also work with kernels before 2.6.16, which means that it's not avaialble on still common platforms (RHEL4 for example). If you've got drop_caches, it's the better approach, that pages gives an answer if you don't too. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD