Обсуждение: Adding a concept of TEMPORARY TABLESPACE for the use in temp_tablespaces
Hi! I have read around the Internet a lot about the idea of using /dev/shm for a tablespace to put tables in and issues with that. But I still have not managed to get a good grasp why would that be a bad idea for using it for temporary objects. I understand that for regular tables this might prevent database startup and recovery because tables and all files associated with tables would be missing. While operations for those tables could reside in the oplog. (Not sure if this means that unlogged tables can be stored on such tablesspace.) I have experimented a bit and performance really improves if /dev/shm is used. I have experimented with creating temporary tables inside a regular (SSD backed) tablespace /dev/shm and I have seen at least 2x improvement in time it takes for a set of modification+select queries to complete. I have also tested what happens if I kill all processes with KILL and restart it. There is noise in logs about missing files, but it does start up. Dropping and recreating the tablespace works. So I wonder, should we add a TEMPORARY flag to a TABLESPACE which would mark a tablespace such that if at startup its location is empty, it is automatically recreated, without warnings/errors? (Maybe some other term could be used for this.) Ideally, such tablespace could be set as temp_tablespaces and things should work out: PostgreSQL should recreate the tablespace before trying to use temp_tablespaces for the first time. We could even make it so that only temporary objects are allowed to be created in a TEMPORARY TABLESPACE, to make sure user does not make a mistake. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Hi! On Sun, Jan 6, 2019 at 11:01 AM Mitar <mmitar@gmail.com> wrote: > I have experimented a bit and performance really improves if /dev/shm > is used. I have experimented with creating temporary tables inside a > regular (SSD backed) tablespace /dev/shm and I have seen at least 2x > improvement in time it takes for a set of modification+select queries > to complete. I also tried just to increase temp_buffers to half the memory, and things are better, but not to the same degree as using a /dev/shm tablespace. Why is that? (All my temporary objects in my experiments are small, few 10k rows, few MBs.) Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On Sun, Jan 6, 2019 at 11:01:52AM -0800, Mitar wrote:
> Hi!
>
> I have read around the Internet a lot about the idea of using /dev/shm
> for a tablespace to put tables in and issues with that. But I still
> have not managed to get a good grasp why would that be a bad idea for
> using it for temporary objects. I understand that for regular tables
> this might prevent database startup and recovery because tables and
> all files associated with tables would be missing. While operations
> for those tables could reside in the oplog. (Not sure if this means
> that unlogged tables can be stored on such tablesspace.)
>
> I have experimented a bit and performance really improves if /dev/shm
> is used. I have experimented with creating temporary tables inside a
> regular (SSD backed) tablespace /dev/shm and I have seen at least 2x
> improvement in time it takes for a set of modification+select queries
> to complete.
>
> I have also tested what happens if I kill all processes with KILL and
> restart it. There is noise in logs about missing files, but it does
> start up. Dropping and recreating the tablespace works.
>
> So I wonder, should we add a TEMPORARY flag to a TABLESPACE which
> would mark a tablespace such that if at startup its location is empty,
> it is automatically recreated, without warnings/errors? (Maybe some
> other term could be used for this.)
>
> Ideally, such tablespace could be set as temp_tablespaces and things
> should work out: PostgreSQL should recreate the tablespace before
> trying to use temp_tablespaces for the first time.
>
> We could even make it so that only temporary objects are allowed to be
> created in a TEMPORARY TABLESPACE, to make sure user does not make a
> mistake.
I wrote a blog entry about this:
https://momjian.us/main/blogs/pgblog/2017.html#June_2_2017
This is certainly an area we can improve, but it would require changes
in several parts of the system to handle cases where the tablespace
disappears.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Hi! On Fri, Jan 25, 2019 at 2:32 PM Bruce Momjian <bruce@momjian.us> wrote: > I wrote a blog entry about this: > > https://momjian.us/main/blogs/pgblog/2017.html#June_2_2017 > > This is certainly an area we can improve, but it would require changes > in several parts of the system to handle cases where the tablespace > disappears. Yes, I read the discussion thread you point at the end of your blog post. [1] This is why I posted an e-mail to the mailing list because some statements from that thread do not hold anymore. For example, in the thread it is stated: "Just pointing the tablespace to non'restart'safe storage will get you an installation that fails to boot after a restart, since there's a tree structure that is expected to survive, and when it's not found, postgres just fails to boot." This does not seem to be true (anymore?) based on my testing. You get noise in logs, but installation boots without a problem. So maybe we are closer to this than we realize? [1] https://www.postgresql.org/message-id/flat/20170529185308.GB28209%40momjian.us Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On Thu, Mar 14, 2019 at 12:53:02AM -0700, Mitar wrote: > Hi! > > On Fri, Jan 25, 2019 at 2:32 PM Bruce Momjian <bruce@momjian.us> wrote: > > I wrote a blog entry about this: > > > > https://momjian.us/main/blogs/pgblog/2017.html#June_2_2017 > > > > This is certainly an area we can improve, but it would require changes > > in several parts of the system to handle cases where the tablespace > > disappears. > > Yes, I read the discussion thread you point at the end of your blog > post. [1] This is why I posted an e-mail to the mailing list because > some statements from that thread do not hold anymore. For example, in > the thread it is stated: > > "Just pointing the tablespace to non'restart'safe storage will get you > an installation that fails to boot after a restart, since there's a > tree structure that is expected to survive, and when it's not found, > postgres just fails to boot." > > This does not seem to be true (anymore?) based on my testing. You get > noise in logs, but installation boots without a problem. > > So maybe we are closer to this than we realize? Interesting. What happens when you references objects that were in the tablespace? What would we want to happen? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +