On 19.04.2008, at 19:11, Christopher Browne wrote:
> Martha Stewart called it a Good Thing when spreng@socket.ch (Thomas
> Spreng) wrote:
>> On 16.04.2008, at 17:42, Chris Browne wrote:
>> What I meant is if there are no INSERT's or UPDATE's going on it
>> shouldn't affect SELECT queries, or am I wrong?
>
> Yes, that's right. (Caveat: VACUUM would be a form of update, in this
> context...)

thanks for pointing that out, at the moment we don't run autovacuum but
VACUUM ANALYZE VERBOSE twice a day.

>>> 2. On the other hand, if you're on 8.1 or so, you may be able to
>>> configure the Background Writer to incrementally flush checkpoint
>>> data
>>> earlier, and avoid the condition of 1.
>>>
>>> Mind you, you'd have to set BgWr to be pretty aggressive, based on
>>> the
>>> "10s periodicity" that you describe; that may not be a nice
>>> configuration to have all the time :-(.
>>
>> I've just seen that the daily vacuum tasks didn't run,
>> apparently. The DB has almost doubled it's size since some days
>> ago. I guess I'll have to VACUUM FULL (dump/restore might be faster,
>> though) and check if that helps anything.
>
> If you're locking out users, then it's probably a better idea to use
> CLUSTER to reorganize the tables, as that simultaneously eliminates
> empty space on tables *and indices.*
>
> In contrast, after running VACUUM FULL, you may discover you need to
> reindex tables, because the reorganization of the *table* leads to
> bloating of the indexes.

I don't VACUUM FULL but thanks for the hint.

> Pre-8.3 (I *think*), there's a transactional issue with CLUSTER where
> it doesn't fully follow MVCC, so that "dead, but still accessible, to
> certain transactions" tuples go away. That can cause surprises
> (e.g. - queries missing data) if applications are accessing the
> database concurrently with the CLUSTER. It's safe as long as the DBA
> can take over the database and block out applications. And at some
> point, the MVCC bug got fixed.

I think I'll upgrade PostgreSQL to the latest 8.3 version in the next
few days anyway, along with a memory upgrade (from 1.5GB to 4GB) and a
new 2x RAID-1 (instead of RAID-5) disk configuration. I hope that this
has already a noticeable impact on the performance.

> Note that you should check the output of a VACUUM VERBOSE run, and/or
> use the contrib function pgsstattuples() to check how sparse the
> storage usage is. There may only be a few tables that are behaving
> badly, and cleaning up a few tables will be a lot less intrusive than
> cleaning up the whole database.

That surely is the case because about 90% of all data is stored in one
big table and most of the rows are deleted and newly INSERT'ed every
night.

cheers,

tom

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On Tue, Apr 22, 2008 at 7:42 AM, Thomas Spreng <spreng@socket.ch> wrote:
>
> I think I'll upgrade PostgreSQL to the latest 8.3 version in the next
> few days anyway, along with a memory upgrade (from 1.5GB to 4GB) and a
> new 2x RAID-1 (instead of RAID-5) disk configuration. I hope that this
> has already a noticeable impact on the performance.

Note that if you have a good RAID controller with battery backed cache
and write back enabled, then you're probably better or / at least as
well off using four disks in a RAID-10 than two separate RAID-1 sets
(one for xlog and one for data).

Test to see. I've had better performance in general with the RAID-10 setup.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Hi everyone,

I want to know how can you turn off logging in Psql ???

On 22.04.2008, at 17:25, Scott Marlowe wrote:
> On Tue, Apr 22, 2008 at 7:42 AM, Thomas Spreng <spreng@socket.ch>
> wrote:
>>
>> I think I'll upgrade PostgreSQL to the latest 8.3 version in the next
>> few days anyway, along with a memory upgrade (from 1.5GB to 4GB)
>> and a
>> new 2x RAID-1 (instead of RAID-5) disk configuration. I hope that
>> this
>> has already a noticeable impact on the performance.
>
> Note that if you have a good RAID controller with battery backed cache
> and write back enabled, then you're probably better or / at least as
> well off using four disks in a RAID-10 than two separate RAID-1 sets
> (one for xlog and one for data).

I just wanted to let you know that upgrading Postgres from 8.1 to 8.3,
RAM from 1.5GB to 4GB and changing from a 3 disk RAID5 to 2x RAID1 (OS &
WAL, Tablespace) led to a significant speed increase. What's especially
important is that those randomly slow queries seem to be gone (for now).

Thanks for all the help.

Cheers,

Tom

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance