From: | "Rainer Mager" <rmager(at)vgkk(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "PostgreSQL Admin" <pgsql-admin(at)postgresql(dot)org> |
Subject: | RE: High memory usage |
Date: | 2001-06-20 07:18:52 |
Message-ID: | NEBBJBCAFMMNIHGDLFKGCEFOEFAA.rmager@vgkk.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-jdbc pgsql-patches |
Hi,
Here is a query that demonstrates the problem. Running this takes about
60MB until it is done at which time it is freed (I was wrong when I said
otherwise earlier). Interestingly, the same amount of memory is used when
doing an EXPLAIN on this query. Also it happens to return 0 rows. Please
excuse the weird characters in the middle this is a Japanese (UTF8)
database. Also please excuse Outlook breaking the query, it is just one long
line.
SELECT DISTINCT product.product_id FROM product, pr_prop_str alias_table_0,
pr_prop_str alias_table_1, pr_prop_str alias_table_2, pr_prop_str
alias_table_3, pr_prop_str alias_table_4, pr_prop_str alias_table_5,
pr_prop_str alias_table_6, pr_prop_str alias_table_7, pr_prop_str
alias_table_8 WHERE product.product_id = alias_table_0.product_id AND
product.product_id = alias_table_1.product_id AND product.product_id =
alias_table_2.product_id AND product.product_id = alias_table_3.product_id
AND product.product_id = alias_table_4.product_id AND product.product_id =
alias_table_5.product_id AND product.product_id = alias_table_6.product_id
AND product.product_id = alias_table_7.product_id AND product.product_id =
alias_table_8.product_id AND ( alias_table_0.pr_property_id = 147 AND
alias_table_0.str = '3E362cb' ) AND ( alias_table_1.pr_property_id = 18 AND
alias_table_1.str > '000999999' ) AND ( alias_table_2.pr_property_id = 18
AND alias_table_2.str < '004999999' ) AND ( alias_table_3.pr_property_id =
51 AND alias_table_3.str = '蛬Oウ縷C~O縷Cウ縷C~I縷Cォ' ) AND (
alias_table_4.pr_property_id = 115 AND alias_table_4.str = '1' ) AND (
alias_table_5.pr_property_id = 68 AND alias_table_5.str = '05' ) AND (
alias_table_6.pr_property_id = 113 AND alias_table_6.str < '030001' ) AND
( alias_table_7.pr_property_id = 57 AND alias_table_7.str < '19980101' ) AND
( alias_table_8.pr_property_id = 158 AND alias_table_8.str = '1' );
So, why is so much memory used and are there any settings I can change to
reduce that (possibly at the expense of performance)? The problem is that
sometimes this query will be running 4 or 5 times simultaneously and using
all of the box's memory.
Thanks,
--Rainer
> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: Thursday, June 14, 2001 10:03 AM
> To: Rainer Mager
> Cc: PostgreSQL Admin
> Subject: Re: [ADMIN] High memory usage
>
>
> There's not a lot more we can say about it until you can identify the
> query that sucks up all the space. 7.1 in general is a lot better about
> memory consumption than prior releases, but it sounds like you may have
> found a leak that's not plugged yet. I would like to know about it when
> you identify the problem query.
>
> regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Ngoc-Khoi TO | 2001-06-20 07:32:18 | Re: interfacing ms-access with postgresql? |
Previous Message | Namrata | 2001-06-20 06:05:50 | SSL problem |
From | Date | Subject | |
---|---|---|---|
Next Message | Rainer Mager | 2001-06-20 08:01:22 | RE: High memory usage |
Previous Message | Rainer Mager | 2001-06-20 03:40:22 | RE: High memory usage [PATCH] |
From | Date | Subject | |
---|---|---|---|
Next Message | Rainer Mager | 2001-06-20 08:01:22 | RE: High memory usage |
Previous Message | Rainer Mager | 2001-06-20 03:40:22 | RE: High memory usage [PATCH] |