Store and Postgres 8.1 on Linux

> I've got two installations of Postgres 8.1 on Linux here (FC5 and FC6)
> and I notice a significant performance degradation with just about any
> Store operation compared to my previous installs that didn't involve
> Postgres 8.1. Previously I used Postgres 7.4 (I think) on FC4 and even
> Postgres 8 RC on WinXP, which felt noticeably faster AFAICT. This is
> running VW on the same machine as the DB. I'm used to this setup to just
> zip by loading pretty much anything. Now it feels like the database is
> remote, not local.
>
> I didn't try to dig in to figure out what's going on yet, so I have no
> idea why (it may well be something wrong with my particular setup), but
> I notice that top shows the postgres daemon very busy at about 90%
> during Store ops. I believe I managed to turn on the auto-vacuuming
> feature and even vacuumed manually but didn't see much improvement.
> Actually now that I think of it, I suspect that all my previous setups
> used 32-bit OSes (certainly the Windows ones did), whereas my current
> FC5 and FC6 installs run in 64-bits, and also the postgres server is
> running in 64-bits. But I still use the 32-bit VW on those.
>
> Anyway, I'm curious if anyone else is using similar setup and what's the
> experience.
>
> Thanks,
>
> Martin
>
>

> OK, I've got an update that might be interesting for some. Desperate
> enough I decided to replicate the repository using the Glorp replicator.
> Less than a day later I've got another DB with the same contents and
> indeed this one is orders of magnitude faster. So I'm inclined to
> conclude that something is wrong with my backup or my backup procedures
> (since that's what I used to restore the repository).
> My backup procedure is pretty simple. I run this as a cronjob:
>
>     pg_dump -U mkobetic -Fc -b store > store.pgdump
>
> and to restore it I use
>
>     pg_restore -cd store store.pgdump
>
> I did use the backup several times (usually due to OS upgrades) and it
> always manages to create a "working" repository but it seems to get
> progressively slower as the DB gets bigger. I can continue fine relying
> on the replicator instead, but I'd be curious if somebody knows what I'm
> doing wrong with the backups.
>
> Thanks,
>
> Martin
>
> Martin Kobetic wrote:
>> I've got two installations of Postgres 8.1 on Linux here (FC5 and FC6)
>> and I notice a significant performance degradation with just about any
>> Store operation compared to my previous installs that didn't involve
>> Postgres 8.1. Previously I used Postgres 7.4 (I think) on FC4 and even
>> Postgres 8 RC on WinXP, which felt noticeably faster AFAICT. This is
>> running VW on the same machine as the DB. I'm used to this setup to
>> just zip by loading pretty much anything. Now it feels like the
>> database is remote, not local.
>>
>> I didn't try to dig in to figure out what's going on yet, so I have no
>> idea why (it may well be something wrong with my particular setup),
>> but I notice that top shows the postgres daemon very busy at about 90%
>> during Store ops. I believe I managed to turn on the auto-vacuuming
>> feature and even vacuumed manually but didn't see much improvement.
>> Actually now that I think of it, I suspect that all my previous setups
>> used 32-bit OSes (certainly the Windows ones did), whereas my current
>> FC5 and FC6 installs run in 64-bits, and also the postgres server is
>> running in 64-bits. But I still use the 32-bit VW on those.
>>
>> Anyway, I'm curious if anyone else is using similar setup and what's
>> the experience.
>>
>> Thanks,
>>
>> Martin
>>
>>
>
>

> Martin, what about "vacuum"-ing a database after restore? As I
> understand vacuuming a PostgreSQL database recalculate statistics about
> db tables which effects performance significantly.
>
> Best regards
> Janko
>
> Martin Kobetic wrote:
>> OK, I've got an update that might be interesting for some. Desperate
>> enough I decided to replicate the repository using the Glorp
>> replicator. Less than a day later I've got another DB with the same
>> contents and indeed this one is orders of magnitude faster. So I'm
>> inclined to conclude that something is wrong with my backup or my
>> backup procedures (since that's what I used to restore the repository).
>> My backup procedure is pretty simple. I run this as a cronjob:
>>
>>     pg_dump -U mkobetic -Fc -b store > store.pgdump
>>
>> and to restore it I use
>>
>>     pg_restore -cd store store.pgdump
>>
>> I did use the backup several times (usually due to OS upgrades) and it
>> always manages to create a "working" repository but it seems to get
>> progressively slower as the DB gets bigger. I can continue fine
>> relying on the replicator instead, but I'd be curious if somebody
>> knows what I'm doing wrong with the backups.
>>
>> Thanks,
>>
>> Martin
>>
>> Martin Kobetic wrote:
>>> I've got two installations of Postgres 8.1 on Linux here (FC5 and
>>> FC6) and I notice a significant performance degradation with just
>>> about any Store operation compared to my previous installs that
>>> didn't involve Postgres 8.1. Previously I used Postgres 7.4 (I think)
>>> on FC4 and even Postgres 8 RC on WinXP, which felt noticeably faster
>>> AFAICT. This is running VW on the same machine as the DB. I'm used to
>>> this setup to just zip by loading pretty much anything. Now it feels
>>> like the database is remote, not local.
>>>
>>> I didn't try to dig in to figure out what's going on yet, so I have
>>> no idea why (it may well be something wrong with my particular
>>> setup), but I notice that top shows the postgres daemon very busy at
>>> about 90% during Store ops. I believe I managed to turn on the
>>> auto-vacuuming feature and even vacuumed manually but didn't see much
>>> improvement. Actually now that I think of it, I suspect that all my
>>> previous setups used 32-bit OSes (certainly the Windows ones did),
>>> whereas my current FC5 and FC6 installs run in 64-bits, and also the
>>> postgres server is running in 64-bits. But I still use the 32-bit VW
>>> on those.
>>>
>>> Anyway, I'm curious if anyone else is using similar setup and what's
>>> the experience.
>>>
>>> Thanks,
>>>
>>> Martin
>>>
>>>
>>
>>
>

> OK, I've got an update that might be interesting for some. Desperate
> enough I decided to replicate the repository using the Glorp replicator.
> Less than a day later I've got another DB with the same contents and
> indeed this one is orders of magnitude faster. So I'm inclined to
> conclude that something is wrong with my backup or my backup procedures
> (since that's what I used to restore the repository).
> My backup procedure is pretty simple. I run this as a cronjob:
>
>     pg_dump -U mkobetic -Fc -b store > store.pgdump
>
> and to restore it I use
>
>     pg_restore -cd store store.pgdump
>
> I did use the backup several times (usually due to OS upgrades) and it
> always manages to create a "working" repository but it seems to get
> progressively slower as the DB gets bigger. I can continue fine relying
> on the replicator instead, but I'd be curious if somebody knows what I'm
> doing wrong with the backups.
>
> Thanks,
>
> Martin
>
> Martin Kobetic wrote:
>> I've got two installations of Postgres 8.1 on Linux here (FC5 and FC6)
>> and I notice a significant performance degradation with just about any
>> Store operation compared to my previous installs that didn't involve
>> Postgres 8.1. Previously I used Postgres 7.4 (I think) on FC4 and even
>> Postgres 8 RC on WinXP, which felt noticeably faster AFAICT. This is
>> running VW on the same machine as the DB. I'm used to this setup to
>> just zip by loading pretty much anything. Now it feels like the
>> database is remote, not local.
>>
>> I didn't try to dig in to figure out what's going on yet, so I have no
>> idea why (it may well be something wrong with my particular setup),
>> but I notice that top shows the postgres daemon very busy at about 90%
>> during Store ops. I believe I managed to turn on the auto-vacuuming
>> feature and even vacuumed manually but didn't see much improvement.
>> Actually now that I think of it, I suspect that all my previous setups
>> used 32-bit OSes (certainly the Windows ones did), whereas my current
>> FC5 and FC6 installs run in 64-bits, and also the postgres server is
>> running in 64-bits. But I still use the 32-bit VW on those.
>>
>> Anyway, I'm curious if anyone else is using similar setup and what's
>> the experience.
>>
>> Thanks,
>>
>> Martin
>>
>>
>
>