Re: Bypassing shared_buffers
От | Thomas Munro |
---|---|
Тема | Re: Bypassing shared_buffers |
Дата | |
Msg-id | CA+hUKG+OWGPMqNQeGW1+k=0qsmWebbJ819c7W4yKxazrCqLxzg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Bypassing shared_buffers (Vladimir Churyukin <vladimir@churyukin.com>) |
Список | pgsql-hackers |
On Thu, Jun 15, 2023 at 2:51 PM Vladimir Churyukin <vladimir@churyukin.com> wrote: > Do you foresee any difficulties in implementation of the "unwarm" operation? It requires a cache flush operation, > so I'm curious how complicated that is (probably there is a reason this is not supported by Postgres by now? mssql andoracle support stuff like that for a long time) If they have a way to kick individual relations out of the buffer pool, then I suspect they have an efficient way to find the relevant buffers. We'd have to scan the entire buffer pool, or (for small relations), probe for blocks 0..n (when we know that n isn't too high). We'll probably eventually get something tree-based, like operating system kernels and perhaps those other databases use for their own buffer pools, which is useful for I/O merging and for faster DROP, but until then you'll face the same problem while implementing unwarm, and you'd probably have to understand a lot of details about bufmgr.c and add some new interfaces. As Tom says, in the end it's going to work out much like restarting, which requires a pleasing zero lines of new code, perhaps explaining why no one has tried this before... Though of course you can be more selective about which tables are zapped. > Cluster restart is not an option for us unfortunately, as it will be required for each query pretty much, and there area lot of them. > An ideal solution would be, if it's possible, to test it in parallel with other activities... > Evicting all the other stuff using pg_prewarm is an interesting idea though (if a large prewarm operation really evictsall the previously stored data reliably). > It's a bit hacky, but thanks, I think it's possible to make this work with some effort. > It will require exclusive access just for that testing, which is not ideal but may work for us. You can use pg_buffercache to check the current contents of the buffer pool, to confirm that a relation you're interested in is gone. https://www.postgresql.org/docs/current/pgbuffercache.html#PGBUFFERCACHE-COLUMNS I guess another approach if you really want to write code to do this would be to introduce a function that takes a buffer ID and invalidates it, and then you could use queries of pg_buffercache to drive it. It would simplify things greatly if you only supported invalidating clean buffers, and then you could query pg_buffercache to see if any dirty buffers are left and if so run a checkpoint and try again or something like that... Another thing I have wondered about while hacking on I/O code is whether pg_prewarm should also have an unwarm-the-kernel-cache thing. There is that drop_cache thing, but that's holus bolus and Linux-only. Perhaps POSIX_FADV_WONTNEED could be used for this, though that would seem to require a double decker bus-sized layering violation.
В списке pgsql-hackers по дате отправления:
Следующее
От: Kyotaro HoriguchiДата:
Сообщение: Re: add non-option reordering to in-tree getopt_long