You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Ming <ov...@sina.com> on 2018/07/22 12:48:04 UTC

Is there a simple way to force the use of scratch file?

Hi, all,

 

I am trying to reproduce an issue in STFS. So I need to use a query to use
the STFS.

I tried to generate 10 Million rows in a table and do an ORDER BY, but it
doesn't use scratch file. I think I need to generate more rows maybe.

 

But is there a simpler way to simulate that? No matter what query it is, I
am fine, I just need that query can use scratch file.  I am using a
development machine, with very small disk and memory. So I hope I can find a
simple way to simulate.

 

Any idea will be very appreciated!

 

thanks,

Ming

RE: Is there a simple way to force the use of scratch file?

Posted by "Liu, Ming (Ming)" <mi...@esgyn.cn>.
thank you Selva and Prashanth, this helps me A LOT !

Ming

-----Original Message-----
From: Selva Govindarajan <se...@esgyn.com> 
Sent: Tuesday, July 24, 2018 12:33 AM
To: dev@trafodion.apache.org
Subject: RE: Is there a simple way to force the use of scratch file?

You can also try setting the CQD BMO_MEMORY_LIMIT_UPPER_BOUND to 200. This means it is limited to 200MB per operator instance.  If you want to reduce this further you need to set both 
BMO_MEMORY_LIMIT_LOWER_BOUND_SORT and BMO_MEMORY_LIMIT_UPPER_BOUND to a value less than 200 MB.

Selva
-----Original Message-----
From: Prashanth Vasudev <pr...@esgyn.com> 
Sent: Monday, July 23, 2018 8:03 AM
To: dev@trafodion.apache.org
Subject: RE: Is there a simple way to force the use of scratch file?

You can reduce the number of buffers and size of each buffer used by sort operator to minimal value. This will cause sort to run out of buffers and use scratch files.

The following cqds can be reduced to minimal values. 
GEN_SORT_MAX_BUFFER_SIZE
GEN_SORT_MAX_NUM_BUFFERS
GEN_SORT_NUM_BUFFERS

Regards,
Prashanth

-----Original Message-----
From: Ming <ov...@sina.com> 
Sent: Sunday, July 22, 2018 5:48 AM
To: dev@trafodion.apache.org
Subject: Is there a simple way to force the use of scratch file?

Hi, all,

 

I am trying to reproduce an issue in STFS. So I need to use a query to use the STFS.

I tried to generate 10 Million rows in a table and do an ORDER BY, but it doesn't use scratch file. I think I need to generate more rows maybe.

 

But is there a simpler way to simulate that? No matter what query it is, I am fine, I just need that query can use scratch file.  I am using a development machine, with very small disk and memory. So I hope I can find a simple way to simulate.

 

Any idea will be very appreciated!

 

thanks,

Ming

RE: Is there a simple way to force the use of scratch file?

Posted by Selva Govindarajan <se...@esgyn.com>.
You can also try setting the CQD BMO_MEMORY_LIMIT_UPPER_BOUND to 200. This means it is limited to 200MB per operator instance.  If you want to reduce this further you need to set both 
BMO_MEMORY_LIMIT_LOWER_BOUND_SORT and BMO_MEMORY_LIMIT_UPPER_BOUND to a value less than 200 MB.

Selva
-----Original Message-----
From: Prashanth Vasudev <pr...@esgyn.com> 
Sent: Monday, July 23, 2018 8:03 AM
To: dev@trafodion.apache.org
Subject: RE: Is there a simple way to force the use of scratch file?

You can reduce the number of buffers and size of each buffer used by sort operator to minimal value. This will cause sort to run out of buffers and use scratch files.

The following cqds can be reduced to minimal values. 
GEN_SORT_MAX_BUFFER_SIZE
GEN_SORT_MAX_NUM_BUFFERS
GEN_SORT_NUM_BUFFERS

Regards,
Prashanth

-----Original Message-----
From: Ming <ov...@sina.com> 
Sent: Sunday, July 22, 2018 5:48 AM
To: dev@trafodion.apache.org
Subject: Is there a simple way to force the use of scratch file?

Hi, all,

 

I am trying to reproduce an issue in STFS. So I need to use a query to use the STFS.

I tried to generate 10 Million rows in a table and do an ORDER BY, but it doesn't use scratch file. I think I need to generate more rows maybe.

 

But is there a simpler way to simulate that? No matter what query it is, I am fine, I just need that query can use scratch file.  I am using a development machine, with very small disk and memory. So I hope I can find a simple way to simulate.

 

Any idea will be very appreciated!

 

thanks,

Ming

RE: Is there a simple way to force the use of scratch file?

Posted by Prashanth Vasudev <pr...@esgyn.com>.
You can reduce the number of buffers and size of each buffer used by sort operator to minimal value. This will cause sort to run out of buffers and use scratch files.

The following cqds can be reduced to minimal values. 
GEN_SORT_MAX_BUFFER_SIZE
GEN_SORT_MAX_NUM_BUFFERS
GEN_SORT_NUM_BUFFERS

Regards,
Prashanth

-----Original Message-----
From: Ming <ov...@sina.com> 
Sent: Sunday, July 22, 2018 5:48 AM
To: dev@trafodion.apache.org
Subject: Is there a simple way to force the use of scratch file?

Hi, all,

 

I am trying to reproduce an issue in STFS. So I need to use a query to use the STFS.

I tried to generate 10 Million rows in a table and do an ORDER BY, but it doesn't use scratch file. I think I need to generate more rows maybe.

 

But is there a simpler way to simulate that? No matter what query it is, I am fine, I just need that query can use scratch file.  I am using a development machine, with very small disk and memory. So I hope I can find a simple way to simulate.

 

Any idea will be very appreciated!

 

thanks,

Ming