Thread: SELECT Query on DB table preventing inserts
Hi, I have a server running PostgreSQL 8.4 (Scientific Linux release 6.0). I'm running a process which receives messages from a remote server and logs them into a table. Here is the table structure: CREATE TABLE messages.message_log ( message_id text, message_timestamp timestamp with time zone, message_type text, message text, message_pkid bigserial NOT NULL, CONSTRAINT hld_log_pk PRIMARY KEY (message_pkid) ) I'm receiving a few messages per second. I have a problem when I query the table and try and parse the message (a complex, read-only, function which takes quite a long time to process) the insert process is unable to insert new rows into the database and I end up losing some messages. Perhaps because I'm locking the table with my query? But I wouldn't have thought that a read-only query would lock the table for insert operations. The insert process is run as the owner of the DB. The query is run as me (and other users who have select permission on the table). I'd like to be able to change the priority of the inserts (or my user's query), or adjust the table locks so that the inserts occur in preference to the parsing query. I'd like to ensure that the DB stops whatever else it's doing so that the insert can occur. What is the best way of doing this? Thanks, Dan Scott http://danieljamesscott.org
On Aug 30, 2011, at 8:22 AM, Dan Scott wrote: > Perhaps because I'm locking the table with my query? Do you mean you're explicitly locking the table? If so, why??? -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Dan Scott wrote: > the insert process is unable to insert new rows into the database You should probably provide the error message on insert or otherwise describe how it's not working. Normally reading does not unintentionally prevent writing in a concurrent session. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Tue, Aug 30, 2011 at 13:52, Daniel Verite <daniel@manitou-mail.org> wrote: > Dan Scott wrote: > >> the insert process is unable to insert new rows into the database > > You should probably provide the error message on insert or otherwise describe > how it's not working. Normally reading does not unintentionally prevent > writing in a concurrent session. I've investigated a little further and it seems that they don't really 'fail'. Just start taking significantly longer to insert, and the messages start backing up and eventually stop being sent because they're not being acknowledged. I can see a few "WARNING: pgstat wait timeout" messages around the time that this is happening in the syslog. Thanks, Dan
On Tue, Aug 30, 2011 at 13:21, Scott Ribe <scott_ribe@elevated-dev.com> wrote: > On Aug 30, 2011, at 8:22 AM, Dan Scott wrote: > >> Perhaps because I'm locking the table with my query? > > Do you mean you're explicitly locking the table? If so, why??? No, not explicitly. I just thought of it as a possible explanation. If reading from the table does not lock the table for writing, then that's not the problem. Thanks, Dan > > -- > Scott Ribe > scott_ribe@elevated-dev.com > http://www.elevated-dev.com/ > (303) 722-0567 voice > > > > >
On 31 Srpen 2011, 1:07, Dan Scott wrote: > On Tue, Aug 30, 2011 at 13:52, Daniel Verite <daniel@manitou-mail.org> > wrote: >> Dan Scott wrote: >> >>> the insert process is unable to insert new rows into the database >> >> You should probably provide the error message on insert or otherwise >> describe >> how it's not working. Normally reading does not unintentionally prevent >> writing in a concurrent session. > > I've investigated a little further and it seems that they don't really > 'fail'. Just start taking significantly longer to insert, and the > messages start backing up and eventually stop being sent because What messages are you talking about? > they're not being acknowledged. I can see a few "WARNING: pgstat wait > timeout" messages around the time that this is happening in the > syslog. The pgstat messages are a typical symptom of I/O bottleneck - it just means you'ro doing a lot of writes, more than the drives can take. Enable checkpoint logging (log_checkpoints=on) and watch the system stats (e.g. using 'iostat -x' or vmstat), my bet is this is a checkpoint or pdflush issue. Anyway we need more info about your system - Pg version, amount of RAM, shared buffers, checkpoint settings (segments, completion) and page cache config (/proc/sys/vm/). A few lines of vmstat/iostat output would help too. Tomas
Hi,
Sorry for the late response on this.
On Wed, Aug 31, 2011 at 09:40, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 31 Srpen 2011, 1:07, Dan Scott wrote:
>> On Tue, Aug 30, 2011 at 13:52, Daniel Verite <daniel@manitou-mail.org>
>> wrote:
>>> Dan Scott wrote:
>>>
>>>> the insert process is unable to insert new rows into the database
>>>
>>> You should probably provide the error message on insert or otherwise
>>> describe
>>> how it's not working. Normally reading does not unintentionally prevent
>>> writing in a concurrent session.
>>
>> I've investigated a little further and it seems that they don't really
>> 'fail'. Just start taking significantly longer to insert, and the
>> messages start backing up and eventually stop being sent because
>
> What messages are you talking about?
I guess I need to explain the system a little better. The purpose of
this database is to log HL7 messages received from another system. We
then parse these messages to extract the data. So the 'messages' in
this case are the messages that I'm receiving from the remote system,
they are my data which I'm inserting into the database.
>> they're not being acknowledged. I can see a few "WARNING: pgstat wait
>> timeout" messages around the time that this is happening in the
>> syslog.
>
> The pgstat messages are a typical symptom of I/O bottleneck - it just
> means you'ro doing a lot of writes, more than the drives can take. Enable
> checkpoint logging (log_checkpoints=on) and watch the system stats (e.g.
> using 'iostat -x' or vmstat), my bet is this is a checkpoint or pdflush
> issue.
Checkpoint logging is enabled and I've setup monitoring using munin.
iostat -x 10 shows the following (vdb is the data drive):
avg-cpu: %user %nice %system %iowait %steal %idle
0.15 0.00 0.10 0.10 0.00 99.65
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
vda 0.00 0.00 0.00 0.70 0.00 5.60
8.00 0.00 1.14 0.86 0.06
vdb 0.00 3.30 0.00 5.40 0.00 69.60
12.89 0.05 8.57 1.52 0.82
dm-0 0.00 0.00 0.00 0.70 0.00 5.60
8.00 0.00 1.14 0.86 0.06
dm-1 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.30 0.00 0.10 0.15 0.00 99.45
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
vda 0.00 0.00 0.00 1.50 0.00 12.00
8.00 0.00 1.67 0.47 0.07
vdb 0.00 3.50 0.00 4.60 0.00 64.80
14.09 0.02 3.96 1.50 0.69
dm-0 0.00 0.00 0.00 1.50 0.00 12.00
8.00 0.00 1.67 0.47 0.07
dm-1 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.05 0.00 0.05 0.15 0.00 99.75
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
vda 0.00 0.00 0.00 0.80 0.00 6.40
8.00 0.00 0.88 0.37 0.03
vdb 0.00 1.30 0.00 2.80 0.00 32.80
11.71 0.01 3.79 1.75 0.49
dm-0 0.00 0.00 0.00 0.80 0.00 6.40
8.00 0.00 0.88 0.37 0.03
dm-1 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.05 0.00 0.05 0.00 0.00 99.90
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
vda 0.00 0.00 0.00 0.40 0.00 3.20
8.00 0.00 1.75 0.75 0.03
vdb 0.00 0.10 0.00 0.70 0.00 6.40
9.14 0.00 0.71 0.71 0.05
dm-0 0.00 0.00 0.00 0.40 0.00 3.20
8.00 0.00 1.75 0.75 0.03
dm-1 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
Which column should I be looking at?
> Anyway we need more info about your system - Pg version, amount of RAM,
> shared buffers, checkpoint settings (segments, completion) and page cache
> config (/proc/sys/vm/). A few lines of vmstat/iostat output would help
> too.
The server is running Scientific Linux 6.0, PostgreSQL 9.4.9, 2GB ram.
The PostgreSQL configuration is fairly standard:
[root@aorta ~]# grep shared /data/pgsql/data/postgresql.conf
# Note: Increasing max_connections costs ~400 bytes of shared memory per
shared_buffers = 24MB # min 128kB
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
#shared_preload_libraries = '' # (change requires restart)
# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
[root@aorta ~]# grep check /data/pgsql/data/postgresql.conf
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
log_checkpoints = on
#check_function_bodies = on
[root@aorta ~]# grep page /data/pgsql/data/postgresql.conf
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#full_page_writes = on # recover from partial page writes
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
Thanks for all your help,
Dan