I'm a freshman with Postgresql, so any hints or tips are appreciated.
My program is very small. It receives data from socket, and then insert them into a table that has no index/foreign key/constrain/Text Field. In the fact, it has a few of numeric fields only.
My data from network is coming continuously at a rate about 100 to 1000 records per seconds. My Postgresql is not optimized at all, and every thing of it is running at the default values.
The trouble is The time consumption of every db-insert varys in a very wide range. The maximum time of a single insert might be 10s, and the minimum one might be 100us, while the average one is about 900us.
Even though I can increase the length of buffer, to avoid the buffer be exhausted when we wait for a too long insert to finish, I think this is NOT the correct solution, because regardless how long the buffer length is, we might encounter a more slower insert. I guesss it is abnormal in the world of Postgresql(10s for writing a tiny plain table), but I have no idea to solve it, so I ask it here to hope someone point out my mistake or some optimizing methods.
env: Centos 7.5 64bit, Postgresql 10, gcc 8.2.0 and latest pqxx lib.
This is my pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv4 incoming connections:
host all all all password
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
and the postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 100 # (change requires restart)
shared_buffers = 128MB # min 128kB
dynamic_shared_memory_type = posix # the default is the first option
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'log' # directory where log files are written,
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
log_line_prefix = '%m [%p] ' # special values:
log_timezone = 'PRC'
datestyle = 'iso, ymd'
timezone = 'PRC'
lc_messages = 'zh_CN.UTF-8' # locale for system error message
lc_monetary = 'zh_CN.UTF-8' # locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8' # locale for number formatting
lc_time = 'zh_CN.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.simple'
Sorry for my English, I'm a Chinese. I appreciate any patience to read my poor English. Thanks!