0

I run .py files using Django ORM, that connected to Postgresql server on another server. Both servers working on Ubuntu 20.04

when i run the same file it takes the following time:

  • 2-3 seconds on server with postgresql
  • 8-12 seconds on another server.

When run .py file with more processes, it can take 20 seconds. If I run the same script at the same time on the postgresql server, it takes 2-3 seconds anyway

I tried:

  • Turn off firewall on both servers (sudo ufw disable)
  • Change postgres configs and then restart postgres server
  • Use pgBouncer

I checked internet speed on the servers and its normal

This is postgresql.conf

# Generated by PGConfig 2.0 beta
## http://pgconfig.org

# Memory Configuration
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 41MB
maintenance_work_mem = 512MB

# Checkpoint Related Configuration
min_wal_size = 512MB
max_wal_size = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Network Related Configuration
listen_addresses = '*'
max_connections = 1000

# Storage Configuration
random_page_cost = 1.1
effective_io_concurrency = 200

# Worker Processes
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Logging configuration for pgbadger
logging_collector = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
lc_messages = 'C'

# Adjust the minimum time to collect data
log_min_duration_statement = '10s'
log_autovacuum_min_duration = 0

# 'csvlog' format configuration
log_destination = 'csvlog'

In pg_hba i just insert 1 string

#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

host    all             all             all                     md5

Is it normal to have this speed or i can configure that?

Фёдор Яковлев
  • 9
  • 2
  • Can/Will you post the `.py` script ? How much data is being read by this script ? – Luuk Oct 10 '21 at 15:14
  • 1
    How and where are you measuring the time? – Adrian Klaver Oct 10 '21 at 15:58
  • How much data does that script retrieve (many rows or rows with large values)? – Bruno Oct 10 '21 at 16:15
  • 2
    "I checked internet speed on the servers and its normal" This has no meaning. The normal speed of the internet could be anything. – jjanes Oct 10 '21 at 19:40
  • 1
    This may indicate that your script/python code has N+1 queries issues. This is why it could be faster on a local server than on a distant server. You should try using the Django debug toolbar to see how many queries are made, and possibly https://docs.djangoproject.com/en/3.2/ref/models/querysets/#select-related and https://docs.djangoproject.com/en/3.2/ref/models/querysets/#prefetch-related to remove those – Agate Oct 10 '21 at 21:46

0 Answers0