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@gmail.com> wrote:
>> On Fri, Oct 2, 2009 at 4:18 AM, Michal Vitecek <fuf@mageo.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@mageo.cz)