Обсуждение: Connection Taking time to authentication of database

Поиск
Список
Период
Сортировка

Connection Taking time to authentication of database

От
Vivek Gadge
Дата:
Hi All,

From few days I am facing an issue while connecting to PostgreSQL database. It took 20sec to make a connection from application  Currently I am using PostgreSQL v13.5. Could you please provide me some input on this.

Thanks and regards

Vivek Gadge.

Re: Connection Taking time to authentication of database

От
Samed YILDIRIM
Дата:
Hello Vivek,

- Have you checked logs? What do logs say?
- What is the programming language you use?
- Is there any pooling on application side or between application and database?
- Do you use ssl? Have you checked entropy on both sides?
- Are you sure that authentication takes long time? Would it be a network issue, such as running out of free port?
- what are operating systems on which application and postgresql run?

Best regards.
Samed YILDIRIM

On Wed, 18 Jan 2023, 10:42 Vivek Gadge, <vvkgadge56@gmail.com> wrote:
Hi All,

From few days I am facing an issue while connecting to PostgreSQL database. It took 20sec to make a connection from application  Currently I am using PostgreSQL v13.5. Could you please provide me some input on this.

Thanks and regards

Vivek Gadge.

Re: Connection Taking time to authentication of database

От
Laurenz Albe
Дата:
On Tue, 2023-01-17 at 23:02 +0530, Vivek Gadge wrote:
> From few days I am facing an issue while connecting to PostgreSQL database.
> It took 20sec to make a connection from application  Currently I am using
> PostgreSQL v13.5. Could you please provide me some input on this.

Before you investigate that with a network sniffer, see if you have
"pre_auth_delay" or "post_auth_delay" set on the PostgreSQL server.

Yours,
Laurenz Albe



Problems with unique constraints

От
Massimo Ortensi
Дата:
I had troubles with some unique constraints recently.

They simply didn't work, not seeing some records (select found only 47 
records with specific values instead of 65, insert could create 
duplicate records).

The constraint were on multiple columns. Drop constraint and create 
constraint fixed the problem.

This happened on two servers :

- a version 10 server,  where an abnormal poweroff happened 6 months 
ago, found 3 tables with the problem throughout last 6 months.

- a test server, found the problem on 1 table after upgrading version 10 
to 14


Anythng similar happened to anybody ?
How can I find out if similar problems are present ? constraint and 
index looked ok and were used





Re: Problems with unique constraints

От
Erik Wienhold
Дата:
> On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote:
>
> I had troubles with some unique constraints recently.
>
> They simply didn't work, not seeing some records (select found only 47
> records with specific values instead of 65, insert could create
> duplicate records).
>
> The constraint were on multiple columns. Drop constraint and create
> constraint fixed the problem.
>
> This happened on two servers :
>
> - a version 10 server,  where an abnormal poweroff happened 6 months
> ago, found 3 tables with the problem throughout last 6 months.
>
> - a test server, found the problem on 1 table after upgrading version 10
> to 14
>
> Anythng similar happened to anybody ?
> How can I find out if similar problems are present ? constraint and
> index looked ok and were used

Are the indexes on string types?  Changes to the locale data is one cause:
https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us

--
Erik



Re: Problems with unique constraints

От
Massimo Ortensi
Дата:

we had OS upgrades, but with the same locale.

Is there any chance to avoid rebuilding of all indexes/constraints by checking the correctness of indexes ?

Massimo Ortensi - Responsabile ICT
logo

Via Cristoforo Colombo, 21 - 40131, Bologna 
Tel.  051 4195069
Cell.  3351092560


mortensi@unimaticaspa.it

Questa e-mail e i suoi allegati contengono informazioni di proprietà di Unimatica-RGI S.p.A. e devono essere utilizzati esclusivamente dal destinatario in relazione alle finalità per le quali sono stati ricevuti. E’ vietata qualsiasi forma di riproduzione o di divulgazione senza l’esplicito consenso di Unimatica-RGI S.p.A. Qualora la presente e-mail fosse stata ricevuta per errore, si prega di informare tempestivamente il mittente e distruggere la copia in possesso.

Privacy: I dati personali contenuti in questa e-mail, nonché nei file ivi inclusi, risultano oggetto di tutela ai sensi del Reg. UE 2016/679 (GDPR). Il Titolare del trattamento dei suddetti dati è Unimatica-RGI S.p.A. Gli interessati potranno esercitare tutti i diritti ex artt. 15 e ss. del GDPR inviando un messaggio all’indirizzo privacy@pec.unimaticaspa.it. Qualsiasi trattamento effettuato da chi ha ricevuto per errore tali dati costituisce violazione delle disposizioni previste dal GDPR. In ogni momento è possibile proporre reclamo all’Autorità competente. Per maggiori informazioni si rinvia al sito www.unimaticaspa.it. Ambiente: Considera la responsabilità che hai verso l’ambiente prima di stampare questa e-mail


Il 26/01/2023 11:56, Erik Wienhold ha scritto:
On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote:

I had troubles with some unique constraints recently.

They simply didn't work, not seeing some records (select found only 47
records with specific values instead of 65, insert could create
duplicate records).

The constraint were on multiple columns. Drop constraint and create
constraint fixed the problem.

This happened on two servers :

- a version 10 server,  where an abnormal poweroff happened 6 months
ago, found 3 tables with the problem throughout last 6 months.

- a test server, found the problem on 1 table after upgrading version 10
to 14

Anythng similar happened to anybody ?
How can I find out if similar problems are present ? constraint and
index looked ok and were used
Are the indexes on string types?  Changes to the locale data is one cause:
https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us

--
Erik
Вложения

Re: Problems with unique constraints

От
Erik Wienhold
Дата:
> On 26/01/2023 12:52 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote:
>
> we had OS upgrades, but with the same locale.

Which OS?  Locale may still be the same but collation information may have
changed, resulting in a different sort order.

https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html

Has libc version changed with the OS upgrades?

> Is there any chance to avoid rebuilding of all indexes/constraints by checking
> the correctness of indexes ?

Extension amcheck (functions bt_index_check and bt_index_parent_check) can
verify that btree entries for text are in lexical order.

https://www.postgresql.org/docs/current/amcheck.html

> Il 26/01/2023 11:56, Erik Wienhold ha scritto:
>
> > On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote:
> >
> > I had troubles with some unique constraints recently.
> >
> > They simply didn't work, not seeing some records (select found only 47
> > records with specific values instead of 65, insert could create
> > duplicate records).
> >
> > The constraint were on multiple columns. Drop constraint and create
> > constraint fixed the problem.
> >
> > This happened on two servers :
> >
> > - a version 10 server,  where an abnormal poweroff happened 6 months
> > ago, found 3 tables with the problem throughout last 6 months.
> >
> > - a test server, found the problem on 1 table after upgrading version 10
> > to 14
> >
> > Anythng similar happened to anybody ?
> > How can I find out if similar problems are present ? constraint and
> > index looked ok and were used
> >
> Are the indexes on string types?  Changes to the locale data is one cause:
> https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us

--
Erik



Re: Problems with unique constraints

От
Holger Jakobs
Дата:


Am 26.01.23 um 12:52 schrieb Massimo Ortensi:

we had OS upgrades, but with the same locale.

Is there any chance to avoid rebuilding of all indexes/constraints by checking the correctness of indexes ?

Massimo Ortensi - Responsabile ICT
logo

Via Cristoforo Colombo, 21 - 40131, Bologna 
Tel.  051 4195069
Cell.  3351092560


mortensi@unimaticaspa.it

In case of a change in the locale properties - albeit the name of the locale remained the same - index corruption is possible.

This has been reported on RedHat platforms. The reaction of the PostgreSQL project was to incorporate the locale libraries by ICU. They can be used on demand. If they change, the database server will automatically reindex if necessary.

If something in the OS libraries changes, the database server won't be informed.

When indexes are corrupt (and data are fine), the best solution is to reindex all indexes over varchar and text columns.

Kind Regards,

Holger


-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Вложения

Re: Problems with unique constraints

От
Scott Ribe
Дата:
> On Jan 26, 2023, at 5:42 AM, Holger Jakobs <holger@jakobs.com> wrote:
> 
> This has been reported on RedHat platforms. 

Happened to me on Ubuntu--I think it's Linux in general.



Re: Problems with unique constraints

От
Massimo Ortensi
Дата:
Confirmed, the local is the same but sorting changed.

Thanks

Il 26/01/2023 13:39, Erik Wienhold ha scritto:
>> On 26/01/2023 12:52 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote:
>>
>> we had OS upgrades, but with the same locale.
> Which OS?  Locale may still be the same but collation information may have
> changed, resulting in a different sort order.
>
> https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
>
> Has libc version changed with the OS upgrades?
>
>> Is there any chance to avoid rebuilding of all indexes/constraints by checking
>> the correctness of indexes ?
> Extension amcheck (functions bt_index_check and bt_index_parent_check) can
> verify that btree entries for text are in lexical order.
>
> https://www.postgresql.org/docs/current/amcheck.html
>
>> Il 26/01/2023 11:56, Erik Wienhold ha scritto:
>>
>>> On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote:
>>>
>>> I had troubles with some unique constraints recently.
>>>
>>> They simply didn't work, not seeing some records (select found only 47
>>> records with specific values instead of 65, insert could create
>>> duplicate records).
>>>
>>> The constraint were on multiple columns. Drop constraint and create
>>> constraint fixed the problem.
>>>
>>> This happened on two servers :
>>>
>>> - a version 10 server,  where an abnormal poweroff happened 6 months
>>> ago, found 3 tables with the problem throughout last 6 months.
>>>
>>> - a test server, found the problem on 1 table after upgrading version 10
>>> to 14
>>>
>>> Anythng similar happened to anybody ?
>>> How can I find out if similar problems are present ? constraint and
>>> index looked ok and were used
>>>
>> Are the indexes on string types?  Changes to the locale data is one cause:
>> https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us
> --
> Erik



Re: Problems with unique constraints

От
Laurenz Albe
Дата:
On Thu, 2023-01-26 at 11:11 +0100, Massimo Ortensi wrote:
> I had troubles with some unique constraints recently.
>
> They simply didn't work, not seeing some records (select found only 47
> records with specific values instead of 65, insert could create
> duplicate records).
>
> The constraint were on multiple columns. Drop constraint and create
> constraint fixed the problem.
>
> This happened on two servers :
>
> - a version 10 server,  where an abnormal poweroff happened 6 months
> ago, found 3 tables with the problem throughout last 6 months.
>
> - a test server, found the problem on 1 table after upgrading version 10
> to 14
>
>
> Anythng similar happened to anybody ?

Yes, there were quite a number of such reports.

> How can I find out if similar problems are present ? constraint and
> index looked ok and were used

This is almost certainly caused by an update to the library that provides your
collations.  The update must have changed the definition of those collations
and consequently the sort order, corrupting your collations.

These articles describe the problem and ways to a solution:

https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/

Yours,
Laurenz Albe