Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

361
Visualizações
Debugging high PostgreSQL memory usage (per connection)

Is there any way to check how memory assigned to each connection is actually used?

After upgrading from PostgreSQL 9.3 to PG 12 the memory usage for each PostgreSQL connection doubled or even tripled. So I had to go from 32GB machine with: shared_buffers = 8GB memory used for connections = 8GB memory for disk buffers = 16GB To: 64GB machine with: shared_buffers = 8GB memory used for connections = 40GB memory for disk buffers = 16GB

And it is still not enough. It is not uncommon for single connection to reach use 170MB of private ram (Private in smaps, as described in https://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/ ), not shared with other processes.

What can be a cause for such high memory usage? As far I can tell it is persistent - memory isn't released until connection is closed. As I am using connection pools managed by Wildfly they are reused and it is rare for them to be closed and recreated.

Here is my datasource definition:

<datasource jta="true" jndi-name="java:/MainDS" pool-name="MainDCPool">
    <connection-url>jdbc:postgresql://dbhost/</connection-url>
    <driver-class>org.postgresql.Driver</driver-class>
    <driver>postgres</driver>
    <pool>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>30</max-pool-size>
    </pool>
    <security>
        <user-name>dbuser</user-name>
        <password>password</password>
    </security>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
        <validate-on-match>true</validate-on-match>
        <background-validation>false</background-validation>
    </validation>
    <statement>
        <share-prepared-statements>false</share-prepared-statements>
    </statement>
    <timeout>
        <idle-timeout-minutes>1</idle-timeout-minutes>
    </timeout>
</datasource>

As far I call tell setting 'idle-timeout-minutes=1' and min-pool-size=1 didn't have much if any impact. It looks like WildFly selects random connection from pool (when requested by application), so it is unlikely that any of them stays idle for extended period of time and pool size won't ever drop below ~20 connections

over 4 years ago · Santiago Trujillo
1 Respostas
Responde à pergunta

0

It turned out the reason was histogram (pg_stats). It is stored in memory per each connection, and in database with huge amount of partitions it causes significant overhead. Columns that contain large/long values have significant impact. Lowering STATISTICS attribute on 4 columns of single table and its partitions cut the memory usage (per connection) by 35%. Upgrade itself wasn't at fault, but - as part of upgrade procedure - whole database was ANALYZEd, appling new default_statistics_target value to all tables.

Checking histogram size: Finding the histogram size for a given table PostgreSQL

over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda