Re: updating a row in a table with only one row

Robert Haas wrote:
>On Fri, Oct 2, 2009 at 9:54 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Fri, Oct 2, 2009 at 4:18 AM, Michal Vitecek <fuf(at)mageo(dot)cz> wrote:
>>>  Hello everyone,
>>>
>>>  I'm using PostgreSQL 8.3.8 running on a server with 2 Xeon CPUs, 4GB
>>>  RAM, 4+2 disks in RAID 5 and CentOS 5.3. There's only one database
>>>  which dumped with pgdump takes ~0.5GB.
>>>
>>>  There are ~100 tables in the database and one of them (tableOne) always
>>>  contains only a single row. There's one index on it. However performing
>>>  update on the single row (which occurs every 60 secs) takes a
>>>  considerably long time -- around 200ms. The system is not loaded in any
>>>  way.
>>>
>>>  The table definition is:
>>>
>>>  CREATE TABLE tableOne (
>>>    value1      BIGINT NOT NULL,
>>>    value2      INTEGER NOT NULL,
>>>    value3      INTEGER NOT NULL,
>>>    value4      INTEGER NOT NULL,
>>>    value5      INTEGER NOT NULL,
>>>  );
>>>  CREATE INDEX tableOne_index1 ON tableOne (value5);
>>>
>>>  And the SQL query to update the _only_ row in the above table is:
>>>  ('value5' can't be used to identify the row as I don't know it at the
>>>  time)
>>>
>>>  UPDATE tableOne SET value1 = newValue1, value2 = newValue2, value5 = newValue5;
>>>
>>>  And this is what EXPLAIN says on the above SQL query:
>>>
>>>  DB=> EXPLAIN UPDATE tableOne SET value1 = newValue1, value2 = newValue2, value5 = newValue5;
>>>  LOG:  duration: 235.948 ms  statement: EXPLAIN UPDATE tableOne SET value1 = newValue1, value2 = newValue2, value5 = newValue5;
>>>                        QUERY PLAN
>>>  --------------------------------------------------------
>>>  Seq Scan on tableOne  (cost=0.00..1.01 rows=1 width=14)
>>>  (1 row)
>>>
>>>  What takes PostgreSQL so long? I guess I could add a fake 'id' column,
>>>  create an index on it to identify the single row, but still -- the time
>>>  seems quite ridiculous to me.
>>
>> it is ridiculous.  your problem is almost definitely dead rows.  I
>> can't recall (and I can't find the info anywhere) if the 'hot' feature
>> requires an index to be active -- I think it does.  If so, creating a
>> dummy field and indexing it should resolve the problem.   Can you
>> confirm the dead row issue by doing vacuum verbose and create the
>> index?  please respond with your results, I'm curious.  Also, is
>> autovacuum on?  Have you measured iowait?

Autovacuum is on. I have dropped the superfluous index on value5.

The following is a result of running vacuum verbose analyze on the
table after the database has been running for 3 days (it was restored
from pgdump 3 days ago).

DB=> vacuum verbose analyze tableOne;
INFO: vacuuming "public.tableOne"
INFO: "tableOne": found 82 removable, 1 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 141 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.tableOne"
INFO: "tableOne": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
LOG: duration: 23.833 ms statement: vacuum verbose analyze tableOne;
VACUUM

The problem occurs also on different tables but on tableOne this is
most striking as it is very simple. Also I should mention that the
problem doesn't occur every time -- but in ~1/6 cases.

Could the problem be the HW RAID card? There's ServerRAID 8k with 256MB
with write-back enabled. Could it be that its internal cache becomes
full and all disk I/O operations are delayed until it writes all
changes to hard drives?

Thanks,
--
Michal Vitecek (fuf(at)mageo(dot)cz)