Re: need suggestion on querying big tables

Mladen Gogala <gogala.mladen@xxxxxxxxx> · Mon, 5 Dec 2022 21:08:39 -0500



    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