Обсуждение: problems with memory

Hi,

We are running postgres-6.5.3 on a dual pentium 300 machine, 0.5Gb RAM under
Linux Redhat 6.0 (kernel 2.2.14). The machine we are using sits on a 100Mb
network and the nics are 3com3c590's. We are also using a DPT Raid
controller in a raid5 configuration set up as 1 logical drive.

We are try to insert a large amount of data into the database. What happens
is that when we first start loading data everything is fine. Over a period
of time (1.5hrs) there is a marked decrease in performance in terms of both
memory and cpu usage. At this time cpu usage has crept up to 45-50% and
memory usage is 100Mb and rising slowly and there is only one connection to
the database.

All the statistics are gleaned from using 'top'.


Consequently, the database gets slower and slower until it loads at the rate
of <3KBps at which point it becomes more exciting to watch paint dry 8-)).

Postgres is the only program running (except for normal system programs)
that uses a significant amount of memory.

Is Postgres known to leak memory? What causes the high cpu usage?

Any ideas would be greatly appreciated.

Dean

* Dean Browett <dbrowett@bizonline.net> [000218 15:55] wrote:
> Hi,
>
> We are running postgres-6.5.3 on a dual pentium 300 machine, 0.5Gb RAM under
> Linux Redhat 6.0 (kernel 2.2.14). The machine we are using sits on a 100Mb
> network and the nics are 3com3c590's. We are also using a DPT Raid
> controller in a raid5 configuration set up as 1 logical drive.
>
> We are try to insert a large amount of data into the database. What happens
> is that when we first start loading data everything is fine. Over a period
> of time (1.5hrs) there is a marked decrease in performance in terms of both
> memory and cpu usage. At this time cpu usage has crept up to 45-50% and
> memory usage is 100Mb and rising slowly and there is only one connection to
> the database.
>
> All the statistics are gleaned from using 'top'.
>
>
> Consequently, the database gets slower and slower until it loads at the rate
> of <3KBps at which point it becomes more exciting to watch paint dry 8-)).
>
> Postgres is the only program running (except for normal system programs)
> that uses a significant amount of memory.
>
> Is Postgres known to leak memory? What causes the high cpu usage?
>
> Any ideas would be greatly appreciated.

You really haven't given very much information on the rules and constraints
in your tables, one problem that I had was that a constraint on a table
of mine caused extreme slowdown because each row inserted needed to be
validated through a constraint, as the table grew the amount of data that
needed to be scanned for each insert grew exponentially.

hope this helps,
-Alfred

> * Dean Browett <dbrowett@bizonline.net> [000218 15:55] wrote:
> > Hi,
> >
> > We are running postgres-6.5.3 on a dual pentium 300 machine, 0.5Gb RAM under
> > Linux Redhat 6.0 (kernel 2.2.14). The machine we are using sits on a 100Mb
> > network and the nics are 3com3c590's. We are also using a DPT Raid
> > controller in a raid5 configuration set up as 1 logical drive.
> >
> > We are try to insert a large amount of data into the database. What happens
> > is that when we first start loading data everything is fine. Over a period
> > of time (1.5hrs) there is a marked decrease in performance in terms of both
> > memory and cpu usage. At this time cpu usage has crept up to 45-50% and
> > memory usage is 100Mb and rising slowly and there is only one connection to
> > the database.
> >
> > All the statistics are gleaned from using 'top'.
> >
> >
> > Consequently, the database gets slower and slower until it loads at the rate
> > of <3KBps at which point it becomes more exciting to watch paint dry 8-)).
> >
> > Postgres is the only program running (except for normal system programs)
> > that uses a significant amount of memory.
> >
> > Is Postgres known to leak memory? What causes the high cpu usage?
> >
> > Any ideas would be greatly appreciated.
>
> You really haven't given very much information on the rules and constraints
> in your tables, one problem that I had was that a constraint on a table
> of mine caused extreme slowdown because each row inserted needed to be
> validated through a constraint, as the table grew the amount of data that
> needed to be scanned for each insert grew exponentially.
>
> hope this helps,
> -Alfred

The same is true of any type of index. Make sure you don't have
indices defined unitl after the insert. Also, do a COPY instead of
INSERT if possible.

--Gene