Parameter value from (mb/gb) to bytes

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Parameter value from (mb/gb) to bytes

Raul Kaubi-2

Hi

 

Is there a simple way to dynamically get for example parameter „shared buffers“ value (megabytes or gigabytes) to bytes, for monitoring perspective..?

 

At the moment, this gives me value in GB.

 

# psql -U postgres -Atc "show shared_buffers;"

1GB

 

This value may as well be in MB. So I am looking a way to dynamically get the value in bytes.

 

Regards

Raul

Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Thomas Kellerer-4
Raul Kaubi schrieb am 14.10.2020 um 12:22:

> Is there a simple way to dynamically get for example parameter
> „shared buffers“ value (megabytes or gigabytes) to bytes, for
> monitoring perspective..?>
>  
>
> At the moment, this gives me value in GB.
>
> # psql -U postgres -Atc "show shared_buffers;"
> 1GB
>
> This value may as well be in MB. So I am looking a way to dynamically get the value in bytes.

Instead of using "show" you can use a SELECT with pg_size_bytes():

  select pg_size_bytes(setting)
  from pg_settings
  where name = 'shared_buffers';







Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Thomas Kellerer-4
Thomas Kellerer schrieb am 14.10.2020 um 15:55:

> Raul Kaubi schrieb am 14.10.2020 um 12:22:
>> Is there a simple way to dynamically get for example parameter
>> „shared buffers“ value (megabytes or gigabytes) to bytes, for
>> monitoring perspective..?>
>>  
>>
>> At the moment, this gives me value in GB.
>>
>> # psql -U postgres -Atc "show shared_buffers;"
>> 1GB
>>
>> This value may as well be in MB. So I am looking a way to dynamically get the value in bytes.
>
> Instead of using "show" you can use a SELECT with pg_size_bytes():
>
>   select pg_size_bytes(setting)
>   from pg_settings
>   where name = 'shared_buffers';

Ah, forgot that shared_buffers is in 8K pages.

So you actually need:

   select pg_size_bytes(setting) * 8192
   from pg_settings
   where name = 'shared_buffers';


Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Magnus Hagander-2


On Wed, Oct 14, 2020 at 3:57 PM Thomas Kellerer <[hidden email]> wrote:
Thomas Kellerer schrieb am 14.10.2020 um 15:55:
> Raul Kaubi schrieb am 14.10.2020 um 12:22:
>> Is there a simple way to dynamically get for example parameter
>> „shared buffers“ value (megabytes or gigabytes) to bytes, for
>> monitoring perspective..?>
>>  
>>
>> At the moment, this gives me value in GB.
>>
>> # psql -U postgres -Atc "show shared_buffers;"
>> 1GB
>>
>> This value may as well be in MB. So I am looking a way to dynamically get the value in bytes.
>
> Instead of using "show" you can use a SELECT with pg_size_bytes():
>
>   select pg_size_bytes(setting)
>   from pg_settings
>   where name = 'shared_buffers';

Ah, forgot that shared_buffers is in 8K pages.

So you actually need:

   select pg_size_bytes(setting) * 8192
   from pg_settings
   where name = 'shared_buffers';

Actually, it doesn't have to be in 8k pages, that depends on the build options. So if you want to be perfectly correct, you should probably multiply with current_setting('block_size') instead of a hardcoded 8192 :)

--
Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Tom Lane-2
Magnus Hagander <[hidden email]> writes:
> On Wed, Oct 14, 2020 at 3:57 PM Thomas Kellerer <[hidden email]> wrote:
>> select pg_size_bytes(setting) * 8192
>> from pg_settings
>> where name = 'shared_buffers';

> Actually, it doesn't have to be in 8k pages, that depends on the build
> options. So if you want to be perfectly correct, you should probably
> multiply with current_setting('block_size') instead of a hardcoded 8192 :)

It's fairly annoying that this doesn't work:

regression=# select pg_size_bytes(setting||' '||unit) from pg_settings where name = 'shared_buffers';
ERROR:  invalid size: "16384 8kB"
DETAIL:  Invalid size unit: "8kB".
HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".

Maybe we should teach pg_size_bytes to cope with that.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Pavel Stehule


st 14. 10. 2020 v 17:10 odesílatel Tom Lane <[hidden email]> napsal:
Magnus Hagander <[hidden email]> writes:
> On Wed, Oct 14, 2020 at 3:57 PM Thomas Kellerer <[hidden email]> wrote:
>> select pg_size_bytes(setting) * 8192
>> from pg_settings
>> where name = 'shared_buffers';

> Actually, it doesn't have to be in 8k pages, that depends on the build
> options. So if you want to be perfectly correct, you should probably
> multiply with current_setting('block_size') instead of a hardcoded 8192 :)

It's fairly annoying that this doesn't work:

regression=# select pg_size_bytes(setting||' '||unit) from pg_settings where name = 'shared_buffers';
ERROR:  invalid size: "16384 8kB"
DETAIL:  Invalid size unit: "8kB".
HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".

Maybe we should teach pg_size_bytes to cope with that.

"8kB" is strange unit - maybe we can introduce new - "page"



                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Magnus Hagander-2
In reply to this post by Tom Lane-2


On Wed, Oct 14, 2020 at 5:10 PM Tom Lane <[hidden email]> wrote:
Magnus Hagander <[hidden email]> writes:
> On Wed, Oct 14, 2020 at 3:57 PM Thomas Kellerer <[hidden email]> wrote:
>> select pg_size_bytes(setting) * 8192
>> from pg_settings
>> where name = 'shared_buffers';

> Actually, it doesn't have to be in 8k pages, that depends on the build
> options. So if you want to be perfectly correct, you should probably
> multiply with current_setting('block_size') instead of a hardcoded 8192 :)

It's fairly annoying that this doesn't work:

regression=# select pg_size_bytes(setting||' '||unit) from pg_settings where name = 'shared_buffers';
ERROR:  invalid size: "16384 8kB"
DETAIL:  Invalid size unit: "8kB".
HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".

Maybe we should teach pg_size_bytes to cope with that.

Actually thinking though, surely *this* particular case can be spelled as:
SELECT  pg_size_bytes(current_setting('shared_buffers'))

Or if doing it off pg_settings:

SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where name='shared_buffers'

I'm not sure having pg_size_bytes() parse "16384 8kB" is reasonable, I have a feeling that could lead to a lot of accidental entries giving the wrong results.

--
Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Christoph Moench-Tegeder
In reply to this post by Magnus Hagander-2
## Magnus Hagander ([hidden email]):

> Actually, it doesn't have to be in 8k pages, that depends on the build
> options. So if you want to be perfectly correct, you should probably
> multiply with current_setting('block_size') instead of a hardcoded 8192 :)

More self-contained:
  select pg_size_bytes(setting) * pg_size_bytes(unit)
  from pg_settings
  where name = 'shared_buffers';

I guess that's what that unit column is there for.

Regards,
Christoph

--
Spare Space


Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Tom Lane-2
In reply to this post by Magnus Hagander-2
Magnus Hagander <[hidden email]> writes:
> On Wed, Oct 14, 2020 at 5:10 PM Tom Lane <[hidden email]> wrote:
>> It's fairly annoying that this doesn't work:
>> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
>> where name = 'shared_buffers';

> Actually thinking though, surely *this* particular case can be spelled as:
> SELECT  pg_size_bytes(current_setting('shared_buffers'))

Yeah, that might be the most recommendable way.

> Or if doing it off pg_settings:
> SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
> name='shared_buffers'

No, because that will fail for any unit other than '8kB', eg

regression=# select pg_size_bytes('MB');
ERROR:  invalid size: "MB"

> I'm not sure having pg_size_bytes() parse "16384 8kB" is reasonable, I have
> a feeling that could lead to a lot of accidental entries giving the wrong
> results.

Yeah, that's definitely a risk.  Given that current_setting() already does
what's needed, that's probably a better answer.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Raul Kaubi
Hi

Thanks for all the replies.

So at first, I did this:
select (pg_size_bytes(setting) * (select setting from pg_settings where name = 'block_size')::int) as shared_buffers from pg_settings where name = 'shared_buffers';

But as I understood, that the preferred way would be this, correct..?  (at least, it seems a lot simpler, I agree)
SELECT pg_size_bytes(current_setting('shared_buffers'))  

Regards
Raul

Kontakt Tom Lane (<[hidden email]>) kirjutas kuupäeval K, 14. oktoober 2020 kell 18:23:
Magnus Hagander <[hidden email]> writes:
> On Wed, Oct 14, 2020 at 5:10 PM Tom Lane <[hidden email]> wrote:
>> It's fairly annoying that this doesn't work:
>> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
>> where name = 'shared_buffers';

> Actually thinking though, surely *this* particular case can be spelled as:
> SELECT  pg_size_bytes(current_setting('shared_buffers'))

Yeah, that might be the most recommendable way.

> Or if doing it off pg_settings:
> SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
> name='shared_buffers'

No, because that will fail for any unit other than '8kB', eg

regression=# select pg_size_bytes('MB');
ERROR:  invalid size: "MB"

> I'm not sure having pg_size_bytes() parse "16384 8kB" is reasonable, I have
> a feeling that could lead to a lot of accidental entries giving the wrong
> results.

Yeah, that's definitely a risk.  Given that current_setting() already does
what's needed, that's probably a better answer.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Parameter value from (mb/gb) to bytes

Magnus Hagander-2
In reply to this post by Tom Lane-2


On Wed, Oct 14, 2020 at 5:23 PM Tom Lane <[hidden email]> wrote:
Magnus Hagander <[hidden email]> writes:
> On Wed, Oct 14, 2020 at 5:10 PM Tom Lane <[hidden email]> wrote:
>> It's fairly annoying that this doesn't work:
>> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
>> where name = 'shared_buffers';

> Actually thinking though, surely *this* particular case can be spelled as:
> SELECT  pg_size_bytes(current_setting('shared_buffers'))

Yeah, that might be the most recommendable way.

> Or if doing it off pg_settings:
> SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
> name='shared_buffers'

No, because that will fail for any unit other than '8kB', eg

regression=# select pg_size_bytes('MB');
ERROR:  invalid size: "MB"

Right, but it would certainly work for *this* case using pg_asettings, is what I meant.

That said, I think it'd then actually be better to teach pg_size_bytes to know that "MB" is the same as "1MB" and parse that. That might be something that would actually be useful in other cases as well -- basically as a way to get conversion units in general. Basically if the string is "unit only" then consider that as "1 unit".

--