Regarding db performance improvement

[Durgamahesh Manne]

Hi to PGDG TEAM


kernel.shmmax=18446744073692774399

kernel.shmall=18446744073692774399

 I am using linux ubuntu server(16.04) to manage pgsql. So  kernel default
value for kernel.shmax was 18446744073692774399 & kernel default value for
kernel.shmall was 18446744073692774399

I am using postgresql 12.1 version and ram was 32gb and 8 cpu's

1) Do i need to change the default values for kernel.shmmax &kernel.shmall
parameters by debugging this below script


Does pgsql have performance improvement well by changing the kernel.shmmax
and kernel.shmall default values ?
 #!/bin/bash
# simple shmsetup script
page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`
shmall=`expr $phys_pages / 2`
shmmax=`expr $shmall \* $page_size`
echo kernel.shmmax = $shmmax
echo kernel.shmall = $shmall



Regards
Durgamahesh Manne

Why is a check constraint not working ?

[David Gauthier]

psql (9.6.7, server 11.3)

I have a table...

dvdb=# \d+ dvm_events;

 Table "dvm.dvm_events"
  Column  |   Type   | Modifiers | Storage  | Stats
target | Description

--+--+---+--+--+--
 dvm_id   | integer  | not null  | plain|
   |
 project  | character varying| not null  | extended |
   |
 status   | character varying|   | extended |
   |
Check constraints:
"dvm_events_status_check" CHECK (status::text = ANY
(ARRAY['passed'::character varying, 'failed'::character varying,
NULL::character varying]::text[]))

(There's a lot more to the table than what you see here, but I took it out
because it didn't look relevant)

I believe it should disallow an insert with a status of "foo", but...

dvdb=# insert into dvm_events (dvm_id,project,status) values
(,'mero','foo');
INSERT 0 1

This is outside of a transaction and definitely not in a transaction with
deferred constraint checking.

Check constraints seem to work fine if I create a test table having a
constrained column.  But this existing table seems to have constraints
disabled for some reason.

What could cause this ?

Re: Why is a check constraint not working ?

[David G. Johnston]

On Wed, Apr 29, 2020 at 8:17 AM David Gauthier 
wrote:

>
> Check constraints:
> "dvm_events_status_check" CHECK (status::text = ANY
> (ARRAY['passed'::character varying, 'failed'::character varying,
> NULL::character varying]::text[]))
>
>
> What could cause this ?
>

NULL in the array.

"status = NULL" yields NULL which is a pass for a check constraint.

David J.

Re: Mixed Locales and Upgrading

[Don Seiler]

On Tue, Apr 7, 2020 at 11:41 AM Don Seiler  wrote:

>
> Follow-up question, the locale setting on the host would still be set to
> en_US (as would the postgres and template0 databases). Should I look to
> change that locale on the system to en_US.UTF-8, or even just for the
> postgres user that the DB cluster runs as? What are the ramification for
> doing (or not doing) so?
>

One more question around the GUC settings for locale. It is currently set
to this:

# select name,setting from pg_settings where name like 'lc%';
name | setting
-+-
 lc_collate  | en_US
 lc_ctype| en_US
 lc_messages |
 lc_monetary | C
 lc_numeric  | C
 lc_time | C

Since I'm not changing the postgres or template0 databases (leaving those
as en_US/LATIN1), do I keep lc_collate/lc_ctype as en_US? It's just the
template1 and application database that I've set to en_US.UTF-8.

I'm also struggling to see how lc_messages is an empty string. It is
commented out in postgresql.conf but suggests 'C' will be the default. The
OS locale LC_MESSAGES is set to en_US on the primary but I also see it is
set to en_US on the newer replica hosts. What value would be used for
lc_messages? I'm trying to create an empty DB with these same settings but
if I omit --lc-messages it uses the OS locale value, and I can't set it to
an empty string.

Don.

-- 
Don Seiler
www.seiler.us

Re: Mixed Locales and Upgrading

[Adrian Klaver]

On 4/29/20 1:10 PM, Don Seiler wrote:
On Tue, Apr 7, 2020 at 11:41 AM Don Seiler > wrote:



Follow-up question, the locale setting on the host would still be
set to en_US (as would the postgres and template0 databases). Should
I look to change that locale on the system to en_US.UTF-8, or even
just for the postgres user that the DB cluster runs as? What are the
ramification for doing (or not doing) so?


One more question around the GUC settings for locale. It is currently 
set to this:


# select name,setting from pg_settings where name like 'lc%';
     name     | setting
-+-
  lc_collate  | en_US
  lc_ctype    | en_US
  lc_messages |
  lc_monetary | C
  lc_numeric  | C
  lc_time     | C

Since I'm not changing the postgres or template0 databases (leaving 
those as en_US/LATIN1), do I keep lc_collate/lc_ctype as en_US? It's 
just the template1 and application database that I've set to en_US.UTF-8.


I'm also struggling to see how lc_messages is an empty string. It is 
commented out in postgresql.conf but suggests 'C' will be the default. 


Are you sure?:

https://www.postgresql.org/docs/12/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT
"lc_messages (string)

Sets the language in which messages are displayed. Acceptable 
values are system-dependent; see Section 23.1 for more information. If 
this variable is set to the empty string (which is the default) then the 
value is inherited from the execution environment of the server in a 
system-dependent way. ..."


The OS locale LC_MESSAGES is set to en_US on the primary but I also see 
it is set to en_US on the newer replica hosts. What value would be used 
for lc_messages? I'm trying to create an empty DB with these same 
settings but if I omit --lc-messages it uses the OS locale value, and I 
can't set it to an empty string.


Don.

--
Don Seiler
www.seiler.us 



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Mixed Locales and Upgrading

[Tom Lane]

Adrian Klaver  writes:
> On 4/29/20 1:10 PM, Don Seiler wrote:
>> On Tue, Apr 7, 2020 at 11:41 AM Don Seiler > > wrote:
>> Since I'm not changing the postgres or template0 databases (leaving 
>> those as en_US/LATIN1), do I keep lc_collate/lc_ctype as en_US? It's 
>> just the template1 and application database that I've set to en_US.UTF-8.

I believe that those settings only affect locale-dependent processing
in the postmaster and/or background processes, which there should be
little or none of anyway.  Processes connected to specific databases will
adopt the settings defined for those databases.

>> I'm also struggling to see how lc_messages is an empty string. It is 
>> commented out in postgresql.conf but suggests 'C' will be the default. 

> Are you sure?:
>  Sets the language in which messages are displayed. Acceptable 
> values are system-dependent; see Section 23.1 for more information. If 
> this variable is set to the empty string (which is the default) then the 
> value is inherited from the execution environment of the server in a 
> system-dependent way. ..."

The "system-dependent way" is "adopt whatever the LANG/LC_foo environment
variables say at server startup", at least on non-Windows machines.
I think that C is the fallback if none of those variables are set, though.

Short answer is you shouldn't need to mess with these.

regards, tom lane