Обсуждение: Postgres slow after a couple of days

Поиск
Список
Период
Сортировка

Postgres slow after a couple of days

От
Edwin Pauli
Дата:
Hi,

A few days ago i've installed PostgreSQL 7.4.1
After the installation, Postgres was very fast.
But now, a few days later, it getting slower and slower.

I've looked in the data-dir and i was confused when i see the big
size from that directory.

A dump (created with pg_dump) is about 1MB.

pauli# cd /usr/local/pgsql/data
epauli# du -h
4.4M    ./base/1
4.4M    ./base/17141
  19M    ./base/17142
4.4M    ./base/17143
4.4M    ./base/17149
4.4M    ./base/17158
4.5M    ./base/29156
4.4M    ./base/29175
4.5M    ./base/29184
  55M    ./base
160K    ./global
  32M    ./pg_xlog
  98K    ./pg_clog
  87M    .


Does anybody know why this directory is 87MB while the database is
1MB?


--
Edwin Pauli

Re: Postgres slow after a couple of days

От
Chris
Дата:
Hi.

> A few days ago i've installed PostgreSQL 7.4.1
> After the installation, Postgres was very fast.
> But now, a few days later, it getting slower and slower.

1. Is the database designed well? For example: did you create indices
on fields used as search keys, ecc...

2. Do you vacuum you database once in a while? look for "vacuum
in the manual"...


> I've looked in the data-dir and i was confused when i see the big
> size from that directory.
>
> A dump (created with pg_dump) is about 1MB.
>
> pauli# cd /usr/local/pgsql/data
> epauli# du -h
> 4.4M    ./base/1

the template database is about 4MB

> 4.4M    ./base/17141
>   19M    ./base/17142

this is your database
i'll be more than 1 MB due to: not reclaimed free space (vacuum!), index
lookup tables, various overhead, ...

> 4.4M    ./base/17143
> 4.4M    ./base/17149
> 4.4M    ./base/17158
> 4.5M    ./base/29156
> 4.4M    ./base/29175
> 4.5M    ./base/29184

there are a few empty or almost empty databases;
see what they are with psql -l

>   55M    ./base
> 160K    ./global


>   32M    ./pg_xlog
>   98K    ./pg_clog

the transaction log files - always there after first use;
don't touch them, you'll need them!


Bye, Chris.


>   87M    .
>
>
> Does anybody know why this directory is 87MB while the database is
> 1MB?
>

Re: Postgres slow after a couple of days

От
Bill Moran
Дата:
Edwin Pauli wrote:
> Hi,
>
> A few days ago i've installed PostgreSQL 7.4.1
> After the installation, Postgres was very fast.
> But now, a few days later, it getting slower and slower.
>
> I've looked in the data-dir and i was confused when i see the big
> size from that directory.

How often do you run VACUUM?

>
> A dump (created with pg_dump) is about 1MB.
>
> pauli# cd /usr/local/pgsql/data
> epauli# du -h
> 4.4M    ./base/1
> 4.4M    ./base/17141
>  19M    ./base/17142
> 4.4M    ./base/17143
> 4.4M    ./base/17149
> 4.4M    ./base/17158
> 4.5M    ./base/29156
> 4.4M    ./base/29175
> 4.5M    ./base/29184
>  55M    ./base
> 160K    ./global
>  32M    ./pg_xlog
>  98K    ./pg_clog
>  87M    .
>
>
> Does anybody know why this directory is 87MB while the database is
> 1MB?
>
>


--
Bill Moran
Potential Technologies
http://www.potentialtech.com