Same query taking less time in low configuration machine

On Jul 14, 2020, at 5:27 AM, Vishwa Kalyankar <vishwakalyankar8@gmail.com> wrote:

Hi,

I have two machines - one with 8GB RAM & 4core CPU and the other with 64GB Ram & 24 core CPU. Both machines have the same DB (Postgres 12 + Postgis 2.5.3). Same query is taking less time in low end machine whereas more time in high end machine. Any thoughts on where to look? I have tuned the db in both machines according to https://pgtune.leopard.in.ua/#/

Below I am pasting the output of query explain in both the machines.

-bash-4.2$ psql -p 5434
psql (12.3)
Type "help" for help.

postgres=# \c IPDS_KSEB;
You are now connected to database "IPDS_KSEB" as user "postgres".
IPDS_KSEB=# explain analyze select * from kseb_geometry_trace_with_barrier_partition(5,'kottarakara_version',437,'htline',2) ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Function Scan on kseb_geometry_trace_with_barrier_partition (cost=0.25..10.25 rows=1000 width=169) (actual time=11626.548..11626.568 rows=254 loops=1)
Planning Time: 0.212 ms
Execution Time: 11628.590 ms

-bash-4.2$ psql -p 5422
psql (12.3)
Type "help" for help.

postgres=# \c IPDS_KSEB;
You are now connected to database "IPDS_KSEB" as user "postgres".
IPDS_KSEB=# explain analyze select * from kseb_geometry_trace_with_barrier_partition(5,'kottarakara_version',437,'htline',2) ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Function Scan on kseb_geometry_trace_with_barrier_partition (cost=0.25..10.25 rows=1000 width=169) (actual time=22304.425..22304.448 rows=254 loops=1)
Planning Time: 0.219 ms
Execution Time: 22352.219 ms
(3 rows)

Hi Vishwa,
Is it possible that your data is in the cache on the low end machine but not on the high end machine? There’s both the Postgres cache and the OS disk cache to consider. You can see what’s in the Postgres cache with an extension like pg_buffercache. I don’t know of a way to see what’s in the OS cache; maybe others do.

Cheers
Philip

HI,

OS cache is updated and I had run the query few times with almost the same
result each time.

Regards,
Vishwa

On Tue, Jul 14, 2020 at 6:16 PM Philip Semanchuk <
philip@americanefficient.com> wrote:

On Tue, Jul 14, 2020 at 09:27:56PM +0530, Vishwa Kalyankar wrote:

HI,

OS cache is updated and I had run the query few times with almost the same
result each time.

Regards,
Vishwa

Hi Vishwa,

What are the CPU speeds, memory bandwidth, I/O bandwidth? Often the
lower core count CPUs have a faster clock speed. What is the CPU cache
size for both? Are you running the same OS and PostgreSQL build binaries
on both?

Regards,
Ken

Hi,

I am pasting the output of both server cpu speed and memory speed, and we
have same os (centos) on both the machines and i have downloaded the
postgres rpms from https://www.postgresql.org/

1) High end machine

[root@localhost ~]# dmidecode -t processor | grep Speed
Max Speed: 3600 MHz
Current Speed: 2666 MHz
Max Speed: 3600 MHz
Current Speed: 2666 MHz

Ram slots 16x4=64 GB

[root@localhost ~]# dmidecode --type 17
# dmidecode 3.1
Getting SMBIOS data from sysfs.
SMBIOS 2.6 present.

Handle 0x1100, DMI type 17, 28 bytes
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 16384 MB
Form Factor: DIMM
Set: 1
Locator: DIMM_A1
Bank Locator: Not Specified
Type: DDR3
Type Detail: Synchronous Registered (Buffered)
Speed: 1333 MT/s
Manufacturer: 00CE00B380CE
Serial Number: 35E9FC94
Asset Tag: 02131263
Part Number: M393B2G70BH0-YH9
Rank: 2

cache size : 12288 KB

2) Low End machine

[root@localhost ~]# dmidecode -t processor | grep Speed
Max Speed: 3800 MHz
Current Speed: 3200 MHz

Ram slots 4x2=8GB
[root@localhost ~]# dmidecode --type 17
# dmidecode 3.0
Getting SMBIOS data from sysfs.
SMBIOS 2.7 present.

Handle 0x0038, DMI type 17, 34 bytes
Memory Device
Array Handle: 0x0037
Error Information Handle: Not Provided
Total Width: 64 bits
Data Width: 64 bits
Size: 4096 MB
Form Factor: DIMM
Set: None
Locator: DIMM1
Bank Locator: Not Specified
Type: DDR3
Type Detail: Synchronous
Speed: 1600 MHz
Manufacturer: Hynix/Hyundai
Serial Number: 0BB0390C003C
Asset Tag: 9876543210
Part Number: HMT351U6EFR8C-PB
Rank: 2
Configured Clock Speed: 1600 MHz

cache size : 6144 KB

Any other details required? kindly let me know how to obtain those, i
will share you the same.

Regards,
Vishwa S Kalyankar

On Tue, Jul 14, 2020 at 10:12 PM Kenneth Marshall <ktm@rice.edu> wrote:

On Tue, Jul 14, 2020 at 9:27 PM Vishwa Kalyankar
<vishwakalyankar8@gmail.com> wrote:

Function Scan on kseb_geometry_trace_with_barrier_partition (cost=0.25..10.25 rows=1000 width=169) (actual time=11626.548..11626.568 rows=254 loops=1)

Function Scan on kseb_geometry_trace_with_barrier_partition (cost=0.25..10.25 rows=1000 width=169) (actual time=22304.425..22304.448 rows=254 loops=1)

I have no idea what that function does, but perhaps it runs more
queries, and you can't see the plans for those here. If you set up
auto_explain[1]https://www.postgresql.org/docs/current/auto-explain.html, and turn on auto_explain.log_nested_statements, then
you'll be able to see the query plans for the internal stuff happening
in that function.

I'd look at EXPLAIN (BUFFERS) or auto_explain.log_buffers to get more
information on cache hits and misses. I'd look for settings
differences with EXPLAIN (SETTINGS) to see if there's anything
accidentally set differently (maybe JIT or paralelism or something
like that). I'd look at pg_stat_activity repeatedly while it runs to
see what the processes are doing, especially the wait_event column.
I'd also look at the CPU and I/O on the systems with operating system
tools like iostat, htop, perf to try to find the difference.

[1]: https://www.postgresql.org/docs/current/auto-explain.html

On Thu, 16 Jul 2020 at 09:50, Thomas Munro <thomas.munro@gmail.com> wrote:

I'd look at EXPLAIN (BUFFERS) or auto_explain.log_buffers to get more
information on cache hits and misses. I'd look for settings
differences with EXPLAIN (SETTINGS) to see if there's anything
accidentally set differently (maybe JIT or paralelism or something
like that). I'd look at pg_stat_activity repeatedly while it runs to
see what the processes are doing, especially the wait_event column.
I'd also look at the CPU and I/O on the systems with operating system
tools like iostat, htop, perf to try to find the difference.

It might also be good to look at size of the tables and indexes that
are looked at within the function. If the smaller end machine was
loaded with data via pg_restore form a pg_dump taken from the larger
machine then the indexes might be in much better shape and the heap
may have less bloat. Of course, that's just speculation. We've not
seen what the function does yet.

Vishwa, it would be good if you could follow the guide here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions , in particular:

"Post the definitions of all tables and indexes referenced in the
query. If the query touches views or custom functions, we'll need
those definitions as well. Run psql command "\d table" with the
tables/views/indices referenced in the problem query."

David