Content-Type: multipart/alternative;
 boundary="------------niwtTtewKkjk0041AkU8dK06"
Message-ID: <21a62556-f7dd-ee9f-3a9b-d4221f87d3e2@gmail.com>
Date: Mon, 5 Dec 2022 21:08:39 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.5.0
Subject: Re: need suggestion on querying big tables
To: pgsql-admin@lists.postgresql.org
References: 
 <CAJuN1jj6NQDUjbMH=n4zry10JMJEZ8bRi=j6uA-3_108-Tq9cQ@mail.gmail.com>
Content-Language: en-US
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: 
 <CAJuN1jj6NQDUjbMH=n4zry10JMJEZ8bRi=j6uA-3_108-Tq9cQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/21a62556-f7dd-ee9f-3a9b-d4221f87d3e2%40gmail.com>
Precedence: bulk

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

On 12/5/22 01:29, Ebin Jozer wrote:
> Hi Team,
> in postgresql 11 version we have two tables of size 435 GB and 347 GB.
> if we query on single table or if we are doing inner join on both the 
> big tables, it is not displacing any output, it keeps running
>
> We can see the wait event is IO and directDatafile .
>
> Server Spec : 8 cores and  64GB RAM
> PG config : 53 GB(effective_cache), 12 GB(shared buffer)
>
> can you please suggest some ideas , how we can query on big tables and 
> fasten them to get the output??
>
> Thanks & Regards,
> /Ebin/


The first thing you should realize is that your server is really small. 
Things like NVME, more memory and more cores should help. It's a 
business decision. If your application is important to your business, 
you should invest some money in the appropriate hardware. You should 
also consider partitioning both tables and upgrading to at least 
Postgres 14. The fear of the latest version is understandable and I feel 
it myself. Newer versions behave much better with respect to 
partitioning. Be aware, though, that you will have to add partitioning 
key as a leading column to any global index. Also, parallelism in a 
query helps.

64 GB and 8 cores is a desktop class machine. Here is my desktop machine:

[mgogala@umajor ~]$ tail -20 /proc/cpuinfo
cache size    : 512 KB
physical id    : 0
siblings    : 16
_*core id        : 7*__*
*__*cpu cores    : 8*_
apicid        : 15
initial apicid    : 15
fpu        : yes
fpu_exception    : yes
cpuid level    : 13
wp        : yes
flags        : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca 
cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt 
pdpe1gb rdtscp lm constant_tsc rep_good nopl nonstop_tsc cpuid 
extd_apicid aperfmperf rapl pni pclmulqdq monitor ssse3 fma cx16 sse4_1 
sse4_2 movbe popcnt aes xsave avx f16c rdrand lahf_lm cmp_legacy svm 
extapic cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw skinit wdt 
tce topoext perfctr_core perfctr_nb bpext perfctr_llc mwaitx cpb 
hw_pstate ssbd ibpb vmmcall fsgsbase bmi1 avx2 smep bmi2 rdseed adx smap 
clflushopt sha_ni xsaveopt xsavec xgetbv1 xsaves clzero irperf 
xsaveerptr arat npt lbrv svm_lock nrip_save tsc_scale vmcb_clean 
flushbyasid decodeassists pausefilter pfthreshold avic v_vmsave_vmload 
vgif overflow_recov succor smca sev sev_es
bugs        : sysret_ss_attrs null_seg spectre_v1 spectre_v2 
spec_store_bypass retbleed
bogomips    : 7385.36
TLB size    : 2560 4K pages
clflush size    : 64
cache_alignment    : 64
address sizes    : 43 bits physical, 48 bits virtual
power management: ts ttp tm hwpstate cpb eff_freq_ro [13] [14]

[mgogala@umajor ~]$ head /proc/meminfo
MemTotal:       65781164 kB
MemFree:        48364252 kB
MemAvailable:   47110532 kB
Buffers:           14812 kB
Cached:         10199584 kB
SwapCached:            0 kB
Active:          3949732 kB
Inactive:        9434600 kB
Active(anon):       4304 kB
Inactive(anon):  3211636 kB

It's faster than your DB server and it has a NVME drive. And this is a 
$2K machine from the late 2018. Today, I would go for a DDR5 machine, 
with at least 64 cores, 1TB RAM and at least 20TB of NVME drives. Such a 
machine would cost around $50K.

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

--------------niwtTtewKkjk0041AkU8dK06
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 12/5/22 01:29, Ebin Jozer wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAJuN1jj6NQDUjbMH=n4zry10JMJEZ8bRi=j6uA-3_108-Tq9cQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hi Team,
        <div>in postgresql 11 version we have two tables of size 435 GB
          and 347 GB. </div>
        <div>if we query on single table or if we are doing inner join
          on both the big tables, it is not displacing any output, it
          keeps running </div>
        <div><br>
        </div>
        <div>We can see the wait event is IO and directDatafile .</div>
        <div><br>
        </div>
        <div>Server Spec : 8 cores and  64GB RAM</div>
        <div>PG config : 53 GB(effective_cache), 12 GB(shared buffer) </div>
        <div><br>
        </div>
        <div>can you please suggest some ideas , how we can query on big
          tables and fasten them to get the output??</div>
        <div><br>
        </div>
        <div>
          <div>
            <div>
              <div dir="ltr" class="gmail_signature"
                data-smartmail="gmail_signature"><span
                  style="FONT-FAMILY:georgia,serif"><span
                    style="COLOR:rgb(102,102,102)"><font size="2">Thanks
                      &amp; Regards,<br>
                      <i>Ebin</i></font></span></span><br>
              </div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>The first thing you should realize is that your server is really
      small. Things like NVME, more memory and more cores should help.
      It's a business decision. If your application is important to your
      business, you should invest some money in the appropriate
      hardware. You should also consider partitioning both tables and
      upgrading to at least Postgres 14. The fear of the latest version
      is understandable and I feel it myself. Newer versions behave much
      better with respect to partitioning. Be aware, though, that you
      will have to add partitioning key as a leading column to any
      global index. Also, parallelism in a query helps.</p>
    <p>64 GB and 8 cores is a desktop class machine. Here is my desktop
      machine:</p>
    <p><font face="monospace" color="blue">[mgogala@umajor ~]$ tail -20
        /proc/cpuinfo<br>
        cache size    : 512 KB<br>
        physical id    : 0<br>
        siblings    : 16<br>
        <u><b>core id        : 7</b></u><u><b><br>
          </b></u><u><b>cpu cores    : 8</b></u><br>
        apicid        : 15<br>
        initial apicid    : 15<br>
        fpu        : yes<br>
        fpu_exception    : yes<br>
        cpuid level    : 13<br>
        wp        : yes<br>
        flags        : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr
        pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx
        mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_good nopl
        nonstop_tsc cpuid extd_apicid aperfmperf rapl pni pclmulqdq
        monitor ssse3 fma cx16 sse4_1 sse4_2 movbe popcnt aes xsave avx
        f16c rdrand lahf_lm cmp_legacy svm extapic cr8_legacy abm sse4a
        misalignsse 3dnowprefetch osvw skinit wdt tce topoext
        perfctr_core perfctr_nb bpext perfctr_llc mwaitx cpb hw_pstate
        ssbd ibpb vmmcall fsgsbase bmi1 avx2 smep bmi2 rdseed adx smap
        clflushopt sha_ni xsaveopt xsavec xgetbv1 xsaves clzero irperf
        xsaveerptr arat npt lbrv svm_lock nrip_save tsc_scale vmcb_clean
        flushbyasid decodeassists pausefilter pfthreshold avic
        v_vmsave_vmload vgif overflow_recov succor smca sev sev_es<br>
        bugs        : sysret_ss_attrs null_seg spectre_v1 spectre_v2
        spec_store_bypass retbleed<br>
        bogomips    : 7385.36<br>
        TLB size    : 2560 4K pages<br>
        clflush size    : 64<br>
        cache_alignment    : 64<br>
        address sizes    : 43 bits physical, 48 bits virtual<br>
        power management: ts ttp tm hwpstate cpb eff_freq_ro [13] [14]<br>
        <br>
      </font></p>
    <p><font face="monospace" color="blue">[mgogala@umajor ~]$ head
        /proc/meminfo<br>
        MemTotal:       65781164 kB<br>
        MemFree:        48364252 kB<br>
        MemAvailable:   47110532 kB<br>
        Buffers:           14812 kB<br>
        Cached:         10199584 kB<br>
        SwapCached:            0 kB<br>
        Active:          3949732 kB<br>
        Inactive:        9434600 kB<br>
        Active(anon):       4304 kB<br>
        Inactive(anon):  3211636 kB</font><br>
    </p>
    <p>It's faster than your DB server and it has a NVME drive. And this
      is a $2K machine from the late 2018. Today, I would go for a DDR5
      machine, with at least 64 cores, 1TB RAM and at least 20TB of NVME
      drives. Such a machine would cost around $50K.<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>

--------------niwtTtewKkjk0041AkU8dK06--