Robert Haas wrote:
>On Fri, Oct 2, 2009 at 9:54 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
>> On Fri, Oct 2, 2009 at 4:18 AM, Michal Vitecek <fuf@xxxxxxxx> 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@xxxxxxxx)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
