Content-Type: multipart/alternative;
 boundary="------------6IFgwHr0AaKjM9jT2c0WpmV7"
Message-ID: <44e64451-73a0-b8de-4d88-25d642c62f6d@gmail.com>
Date: Thu, 31 Mar 2022 22:40:25 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Subject: Re: Postgresql TPS Bottleneck
Content-Language: en-US
To: pgsql-performance@lists.postgresql.org
References: 
 <VI1PR04MB31338E811E0896D00D85B69B97E19@VI1PR04MB3133.eurprd04.prod.outlook.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: 
 <VI1PR04MB31338E811E0896D00D85B69B97E19@VI1PR04MB3133.eurprd04.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/44e64451-73a0-b8de-4d88-25d642c62f6d%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------6IFgwHr0AaKjM9jT2c0WpmV7
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 3/31/22 07:50, wakandavision@outlook.com wrote:
> Hi everyone,
>
> I am a bachelor's student and writing my thesis about the scaling and
> performance of an application. The application is using postgresql as a
> database but we can't scale any further currently as it seems postgres
> is hitting the limit.
>
> With the application, as well as with pgbench, we don't get more than
> (max) 70k TPS on postgres. But the servers' resources are not utilized
> completely (more below).

I would try monitoring using "perf top" and "atop -d" to see what is 
going on on the system. Also, try using sar to figure out what's going 
on. Are you paging, waiting for I/O or having some other kind of 
bottleneck. Once you figure where is your system spending time, you can 
address the problem. In addition to that, analyze the log files with 
pgbadger to find out which queries are time consuming and try optimizing 
them.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--------------6IFgwHr0AaKjM9jT2c0WpmV7
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 3/31/22 07:50,
      <a class="moz-txt-link-abbreviated" href="mailto:wakandavision@outlook.com">wakandavision@outlook.com</a> wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:VI1PR04MB31338E811E0896D00D85B69B97E19@VI1PR04MB3133.eurprd04.prod.outlook.com">
      <div>Hi everyone,<br>
      </div>
      <div><br>
      </div>
      <div>I am a bachelor's student and writing my thesis about the
        scaling and<br>
      </div>
      <div>performance of an application. The application is using
        postgresql as a<br>
      </div>
      <div>database but we can't scale any further currently as it seems
        postgres<br>
      </div>
      <div>is hitting the limit.<br>
      </div>
      <div><br>
      </div>
      <div>With the application, as well as with pgbench, we don't get
        more than<br>
      </div>
      <div>(max) 70k TPS on postgres. But the servers' resources are not
        utilized<br>
      </div>
      <div>completely (more below).</div>
    </blockquote>
    <p>I would try monitoring using "perf top" and "atop -d" to see what
      is going on on the system. Also, try using sar to figure out
      what's going on. Are you paging, waiting for I/O or having some
      other kind of bottleneck. Once you figure where is your system
      spending time, you can address the problem. In addition to that,
      analyze the log files with pgbadger to find out which queries are
      time consuming and try optimizing them.<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------6IFgwHr0AaKjM9jT2c0WpmV7--