Thread: BUG #3680: memory leak when excuting a SQL "select count(id) from chinatelecom;"

The following bug has been logged online:

Bug reference:      3680
Logged by:          ssurui
Email address:      ssurui@gmail.com
PostgreSQL version: 8.2.3
Operating system:   Redhat LINUX Advance server 3
Description:        memory leak when excuting a SQL "select count(id) from
chinatelecom;"
Details:

I use postgresql 8.2.3 to build a charger system.  chinatelecom is a table
name,use such command to create it :
CREATE TABLE chinatelecom
(
  id serial NOT NULL,
  company_id char(2) DEFAULT 'XX'::bpchar,
  service_id char(2) DEFAULT 'XX'::bpchar,
  inner_flag char(2) DEFAULT 'XX'::bpchar,
  terminal_id char(8) DEFAULT '00000000'::bpchar,
  worker_id char(8) DEFAULT '00000000'::bpchar,
  contract_id varchar(30) DEFAULT '0'::character varying,
  phone_number varchar(30) DEFAULT '0'::character varying,
  money numeric DEFAULT 0,
  trade_code varchar(3) DEFAULT '000'::character varying,
  record_date date DEFAULT ('now'::text)::date,
  la_serial_number varchar(12) DEFAULT '0'::character varying,
  serial_number varchar(12) DEFAULT '0'::character varying,
  record_time char(30),
  CONSTRAINT chinatelecom_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE chinatelecom OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
chinatelecom TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
chinatelecom TO public;

after the system runs about 4 months, the table has 2,000,000 records.

I use pgAdmin III to excute a simple SQL :
select count(*) from chinatelecom;

use top command observe the available memory, I find about 500MB memory is
vanished after the SQL is executed.

please tell me why it happened?

I'm not so sure you are correctly interpreting the memory information
provided by "top".  In a virtual memory system, it is not uncommon
for the amount of RAM in use to cycle up and down, as programs grab
and release memory, and the system reclaims free memory as needed,
but not necessarily immediately.

If the amount of committed memory in used by the postmaster process
continues to go up indefinitely, threatening to exhaust all available
system memory, that may be a leak.  What you are probably seeing is
just normal use of RAM resources.  You weren't really specific about
what numbers you were using to make your determination, so I can only
speculate.

On Oct 16, 2007, at 8:43 PM, ssurui wrote:

>
> The following bug has been logged online:
>
> Bug reference:      3680
> Logged by:          ssurui
> Email address:      ssurui@gmail.com
> PostgreSQL version: 8.2.3
> Operating system:   Redhat LINUX Advance server 3
> Description:        memory leak when excuting a SQL "select count
> (id) from
> chinatelecom;"
> Details:
>
> I use postgresql 8.2.3 to build a charger system.  chinatelecom is
> a table
> name,use such command to create it :
> CREATE TABLE chinatelecom
> (
>   id serial NOT NULL,
>   company_id char(2) DEFAULT 'XX'::bpchar,
>   service_id char(2) DEFAULT 'XX'::bpchar,
>   inner_flag char(2) DEFAULT 'XX'::bpchar,
>   terminal_id char(8) DEFAULT '00000000'::bpchar,
>   worker_id char(8) DEFAULT '00000000'::bpchar,
>   contract_id varchar(30) DEFAULT '0'::character varying,
>   phone_number varchar(30) DEFAULT '0'::character varying,
>   money numeric DEFAULT 0,
>   trade_code varchar(3) DEFAULT '000'::character varying,
>   record_date date DEFAULT ('now'::text)::date,
>   la_serial_number varchar(12) DEFAULT '0'::character varying,
>   serial_number varchar(12) DEFAULT '0'::character varying,
>   record_time char(30),
>   CONSTRAINT chinatelecom_pkey PRIMARY KEY (id)
> )
> WITHOUT OIDS;
> ALTER TABLE chinatelecom OWNER TO postgres;
> GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
> chinatelecom TO postgres;
> GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
> chinatelecom TO public;
>
> after the system runs about 4 months, the table has 2,000,000 records.
>
> I use pgAdmin III to excute a simple SQL :
> select count(*) from chinatelecom;
>
> use top command observe the available memory, I find about 500MB
> memory is
> vanished after the SQL is executed.
>
> please tell me why it happened?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Douglas Toltzman
doug@oakstreetsoftware.com
(910) 526-5938

>>> On Tue, Oct 16, 2007 at  3:43 PM, in message
<200710162043.l9GKhGeF091013@wwwmaster.postgresql.org>, "ssurui"
<ssurui@gmail.com> wrote:=20
=20
> use top command observe the available memory
=20
top is not the best way to look at RAM usage.  Try free or other tools.
=20
> I find about 500MB memory is
> vanished after the SQL is executed.
=20
Meaning that it is used by the OS cache?  That would make sense,
given that you just read all the data pages for the table.
=20
-Kevin
=20

Today I examine this case in Oracle database by Oracle client tools
executing same SQL.
After 2 minutes, the SQL is executed over, 300M memory is vanished. But
after 5 minutes, the free memory is increased to the original level (I also
use top command of Linux).
Same experiments I done for Postgresql. After long time, the free memory is
not increased, it still 300M memory vanished, so I want to know why it
happened?
                                                ssurui@mailst.xjtu.edu.cn
=20
  _____=20=20

=B7=A2=BC=FE=C8=CB: pgsql-bugs-owner@postgresql.org [mailto:pgsql-bugs-owne=
r@postgresql.
org] =B4=FA=B1=ED Douglas Toltzman
=B7=A2=CB=CD=CA=B1=BC=E4: 2007=C4=EA10=D4=C217=C8=D5 05:01 =C9=CF=CE=E7
=CA=D5=BC=FE=C8=CB: pgsql-bugs@postgresql.org
=D6=F7=CC=E2: Re: [BUGS] BUG #3680: memory leak when excuting a SQL "select
count(id) from chinatelecom;"
=20
I'm not so sure you are correctly interpreting the memory information
provided by "top". In a virtual memory system, it is not uncommon for the
amount of RAM in use to cycle up and down, as programs grab and release
memory, and the system reclaims free memory as needed, but not necessarily
immediately.
=20
If the amount of committed memory in used by the postmaster process
continues to go up indefinitely, threatening to exhaust all available system
memory, that may be a leak. What you are probably seeing is just normal use
of RAM resources. You weren't really specific about what numbers you were
using to make your determination, so I can only speculate.
=20
On Oct 16, 2007, at 8:43 PM, ssurui wrote:



=20
The following bug has been logged online:
=20
Bug reference: 3680
Logged by: ssurui
Email address: ssurui@gmail.com
PostgreSQL version: 8.2.3
Operating system: Redhat LINUX Advance server 3
Description: memory leak when excuting a SQL "select count(id) from
chinatelecom;"
Details:=20
=20
I use postgresql 8.2.3 to build a charger system. chinatelecom is a table
name,use such command to create it :=20
CREATE TABLE chinatelecom=20
(
id serial NOT NULL,
company_id char(2) DEFAULT 'XX'::bpchar,
service_id char(2) DEFAULT 'XX'::bpchar,
inner_flag char(2) DEFAULT 'XX'::bpchar,
terminal_id char(8) DEFAULT '00000000'::bpchar,
worker_id char(8) DEFAULT '00000000'::bpchar,
contract_id varchar(30) DEFAULT '0'::character varying,
phone_number varchar(30) DEFAULT '0'::character varying,
money numeric DEFAULT 0,
trade_code varchar(3) DEFAULT '000'::character varying,
record_date date DEFAULT ('now'::text)::date,
la_serial_number varchar(12) DEFAULT '0'::character varying,
serial_number varchar(12) DEFAULT '0'::character varying,
record_time char(30),
CONSTRAINT chinatelecom_pkey PRIMARY KEY (id)
)=20
WITHOUT OIDS;
ALTER TABLE chinatelecom OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
chinatelecom TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
chinatelecom TO public;
=20
after the system runs about 4 months, the table has 2,000,000 records.
=20
I use pgAdmin III to excute a simple SQL :
select count(*) from chinatelecom;
=20
use top command observe the available memory, I find about 500MB memory is
vanished after the SQL is executed.
=20
please tell me why it happened?
=20
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
=20
Douglas Toltzman
doug@oakstreetsoftware.com
(910) 526-5938
=20



=20

>>> On Wed, Oct 17, 2007 at  2:49 AM, in message
<00e001c81092$4b5aca50$2300a8c0@ssuruieda>, "ssurui"
<ssurui@mailst.xjtu.edu.cn> wrote:=20
=20
> After long time, the free memory is
> not increased, it still 300M memory vanished
=20
I think the one thing everyone knows as a fact is that the memory did
not vanish.  Your latest message gives us a clue that what interests
you is why free (unused, idle, not giving you any benefit) memory
went down.  If that is the question, it is because PostgreSQL goes
through the OS file systems, so RAM which is serving no other purpose
will automatically be used to cache data.  If you need memory for
something else, the cache will be reduced to compensate.
=20
-Kevin
=20