Repost: postmaster growing to consume all memory

Sorry for the repost. Just wondering if anyone has a workaround for 7.4?

At 11:25 AM 1/27/2004 -0500, Tom Lane wrote:

Martijn van Oosterhout <kl*****@svana.org> writes:
I'm afraid I'll have to defer to someone else (Tom?) as why the

estimate was
out by three orders of magnitude.

I'd like to know that, too.
I'd suggest playing around with statistics and seeing if you can work out
why they were so bad.

Could we see the pg_stats row for the ip_saddr column? Also, does the
estimate get better if you increase the stats target for ip_saddr and
re-analyze?

regards, tom lane

OK - the statistics and various queries follow. If n_distinct is the
estimate of unique values then it is still off, but the plan does change.
How does postgresql do the analysis?

select version();
version
-------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
VACUUM full cust_ulog ;
VACUUM
Time: 96916.323 ms
analyze cust_ulog;
Time: 25969.562 ms
explain select ip_saddr,count(*),sum(ip_totlen) from cust_ulog
where true
group by ip_saddr order by sum(ip_totlen) desc limit 10
QUERY PLAN
---------------------------------------------------------------------------------------
Limit (cost=331739.22..331739.24 rows=10 width=13)
-> Sort (cost=331739.22..331743.03 rows=1523 width=13)
Sort Key: sum(ip_totlen)
-> HashAggregate (cost=331651.09..331658.71 rows=1523 width=13)
-> Seq Scan on cust_ulog (cost=0.00..262156.91
rows=9265891 width=13)
(5 rows)

Time: 163.512 ms

select * from pg_stats where tablename ='cust_ulog' and attname='ip_saddr';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
| most_common_freqs |
histogram_bounds | correlation
------------+-----------+----------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | cust_ulog | ip_saddr | 0 | 11 | 1523 |
{202.185.236.30,202.185.236.6,202.185.236.11,202.1 85.236.25,202.185.236.7,203.121.96.19,202.185.236. 23,202.75.129.102,192.228.128.20,202.188.1.5}
|
{0.243667,0.0926667,0.0553333,0.0433333,0.031,0.01 83333,0.0143333,0.0116667,0.011,0.011}
|
{4.2.49.2,61.149.58.98,66.128.175.107,69.56.63.214 ,195.92.67.209,202.185.231.40,202.185.236.47,203.7 0.228.182,208.41.95.194,212.253.48.34,221.232.160. 6}
| 0.00267716
(1 row)

alter table cust_ulog alter column ip_saddr set statistics 50;
ALTER TABLE
Time: 15.605 ms
analyze cust_ulog;
ANALYZE
Time: 51251.862 ms
select correlation,attname from pg_stats where tablename ='cust_ulog' and
attname='ip_saddr';
correlation | attname
-------------+----------
0.0286273 | ip_saddr

select n_distinct from pg_stats where tablename ='cust_ulog' and
attname='ip_saddr';
n_distinct
------------
6714
(1 row)
Time: 16.134 ms
alter table cust_ulog alter column ip_saddr set statistics 100;
ALTER TABLE
Time: 13.728 ms
analyze cust_ulog;
ANALYZE
Time: 55412.288 ms
select n_distinct from pg_stats where tablename ='cust_ulog' and
attname='ip_saddr';
n_distinct
------------
12939
(1 row)
Time: 26.338 ms

explain select ip_saddr,count(*),sum(ip_totlen) from cust_ulog
where true
group by ip_saddr order by sum(ip_totlen) desc limit 10
;

QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=2096080.34..2096080.37 rows=10 width=13)
-> Sort (cost=2096080.34..2096112.69 rows=12939 width=13)
Sort Key: sum(ip_totlen)
-> GroupAggregate (cost=2002684.66..2095196.65 rows=12939 width=13)
-> Sort (cost=2002684.66..2025796.48 rows=9244729 width=13)
Sort Key: ip_saddr
-> Seq Scan on cust_ulog (cost=0.00..261945.29
rows=9244729 width=13)
(7 rows)

Time: 29.819 ms

select * from pg_stats where tablename ='cust_ulog' and attname='ip_saddr';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+-----------+----------+-----------+-----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------
-----
-
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------
-----
-
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public | cust_ulog | ip_saddr | 0 | 11 | 12939 |
{202.185.236.30,202.185.236.6,202.185.236.11,202.1 85.236.25,202.185.236.7,203.121.96.19,202.185.236. 23,202.185.236.47,192.228.128.20,202.75.129.102,20 2.188.1.5,202.185.236.3,202.75.129.101,203.121.96. 24,203.121.96.16,202.185.236.34,202.185.236.31,202 .188.0.132,202.188.0.133,202.185.236.12,202.12.28. 131,202.188.1.23,202.188.1.25,202.188.1.4,202.12.2 9.25,202.185.231.40,202.185.236.45,202.185.236.26, 192.228.128.11,193.0.0.193,202.129.169.40,202.146. 72.66,210.186.75.154,219.93.92.82,202.185.236.35,1 92.228.128.1
2,202
..
185.90.118,202.185.236.5,202.185.236.8,202.187.125 .12,211.97.56.133,103.221.179.184,202.185.66.41,20 2.186.47.155,202.186.140.50,202.188.172.163,216.39 .48.40,216.218.158.87,216.218.201.91,216.218.224.2 6,216.219.254.60,202.185.254.231,202.186.49.133,20 2.187.31.12,202.187.58.165,202.188.0.164,203.121.1 5.140,203.193.96.68,207.218.165.207,61.6.38.139,61 .175.132.228,62.62.139.253,63.163.210.227,64.61.25 .155,66.48.78.31}
|
{0.248767,0.0997333,0.0566667,0.0382667,0.0345667, 0.0147667,0.0138,0.0118,0.0107667,0.00983333,0.009 46667,0.0093,0.00926667,0.00903333,0.00793333,0.00 776667,0.0077,0.0074,0.00733333,0.0049,0.00443333, 0.0044,0.0044,0.00426667,0.00406667,0.0038,0.0031, 0.0028,0.00193333,0.0018,0.0016,0.00146667,0.0014, 0.00123333,0.00113333,0.0011,0.00103333,0.0008,0.0 008,0.000733333,0.0006,0.0006,0.000566667,0.000566 667,0.000566667,0.000533333,0.000533333,0.00046666 7,0.000433333,0.000433333,0.0004,0.000366667,0.000 366667,0.000
333,0
.. 000133333} |
{4.3.7.155,4.40.42.46,12.8.192.29,12.148.209.196,1 8.206.0.76,24.81.163.171,24.145.188.188,24.194.25. 199,24.228.82.49,61.6.98.138,61.6.154.62,61.48.54. 46,61.88.0.6,61.149.232.192,61.222.96.205,62.12.11 2.134,63.13.136.190,63.185.112.170,64.40.227.22,64 .146.63.170,64.218.231.6,64.231.230.239,65.43.172. 73,65.92.251.30,65.124.135.2,65.220.24.19,66.50.11 .33,66.92.93.137,66.142.230.36,66.190.201.9,66.250 .5.182,67.39.64.151,67.72.200.129,67.119.178.208,6 7.227.12.5,68.47.247.99,68.76.186.67,68.97.15.56,6 8.146.69.171
..22,2
0
7.206.201.250,208.188.24.184,209.122.233.245,210.3 .135.230,210.54.85.168,210.169.237.132,210.186.123 .51,210.187.228.241,210.201.230.58,211.24.46.39,21 1.104.217.229,211.173.53.3,212.162.76.197,213.152. 72.122,216.68.94.36,216.176.89.162,217.42.54.38,21 7.233.45.74,218.47.249.76,218.117.84.68,218.244.59 .57,219.92.84.8,219.93.108.1,219.95.1.248,219.95.1 64.90,219.109.238.196,219.252.67.13,220.201.96.35, 221.232.160.6}
| 0.0258759
(1 row)

Time: 4.600 ms

select count(*) from cust_ulog ;
count
---------
9461332
(1 row)

Time: 46468.211 ms
fwlogs=# explain select count(distinct(ip_saddr)) from cust_ulog;
QUERY PLAN
---------------------------------------------------------------------------
Aggregate (cost=285057.11..285057.11 rows=1 width=11)
-> Seq Scan on cust_ulog (cost=0.00..261945.29 rows=9244729 width=11)
(2 rows)

Time: 1.087 ms
fwlogs=# select count(distinct(ip_saddr)) from cust_ulog;
count
---------
1391110
(1 row)

Time: 226998.021 ms
***Version 7.3.4 statistics:
select * from pg_stats where tablename ='cust_ulog' and attname='ip_saddr'
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
| most_common_freqs |
histogram_bounds | correlation
------------+-----------+----------+-----------+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | hlbb_ulog | ip_saddr | 0 | 12 | 1643 |
{202.185.236.30,202.185.236.6,202.185.236.11,202.1 85.236.25,202.185.236.7,203.121.96.19,202.185.236. 23,192.228.128.20,202.185.236.47,202.188.0.133}
|
{0.238333,0.091,0.0536667,0.037,0.035,0.0163333,0. 015,0.012,0.0103333,0.00966667}
|
{4.2.49.3,61.6.158.105,66.44.2.9,74.16.241.113,192 .228.128.11,202.108.249.21,202.185.236.34,203.69.1 9.3,209.112.0.54,216.87.206.244,221.199.145.131}
| 0.0245979

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #1

Subscribe Post Reply

1527

Tom Lane

Lincoln Yeoh <ly***@pop.jaring.my> writes:

Sorry for the repost. Just wondering if anyone has a workaround for 7.4?

Brute force way is "set enable_hashagg = off"

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #2

Lincoln Yeoh

Sounds like that'll work. Sorry I didn't go look for it <sheepish grin>.

Thanks!

At 02:03 PM 2/8/2004 -0500, Tom Lane wrote:

Lincoln Yeoh <ly***@pop.jaring.my> writes:
Sorry for the repost. Just wondering if anyone has a workaround for 7.4?

Brute force way is "set enable_hashagg = off"

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #3

by: Aravind | last post by:

Unable to consume Process reserved memory Hi , This is a Windows form application which interacts with the unmanaged C++ codes . In unmanaged c++ code we allocate around 130MB on the heap for...

.NET Framework

postmaster(s) have high load average

by: Chris Webster | last post by:

I have one process which writes a single float into 300 columns once per second. I then run 4 process, from remote computers, to query a small subset of the latest row. I have even commented...

PostgreSQL Database

SSL and web services - Repost

by: Wade Redmond | last post by:

I'm trying to consume a .net web service with VC++.net. It works fine without SSL but will not work at all with SSL and no exception is thrown. Can someone please explain possible causes as to...

.NET Framework

aspnet_wp.exe memory usage keep growing

by: Clement | last post by:

hi, i have an asp.net site and is using SQL Server 2k. i realize the aspnet_wp.exe memory usage keep growing and i will receive an error for the pages that call the sql connection. others page...

ASP.NET

postmaster growing to consume all memory

by: Lincoln Yeoh | last post by:

Hi, I recently upgraded to postgresql 7.4 and I am having a problem with postmaster using lots of memory for a query (keeps growing even up to 400MB+ till I stop postgresql ). I don't recall...

PostgreSQL Database

postmaster out of memory....

by: Joe Maldonado | last post by:

Hello all! when asking postgres to aggregate totals accross 4.5 or so Million records. The visible effect is that the postmaster will grow to the 3GB process limit and die without a core :(. I...

PostgreSQL Database

restrict growing VM of a process

by: Navneet Kumar | last post by:

Hi, My program is non-leaky, I've checked on that. More virtual memory is allocated to it by the system then is required by it. I'm filling a linked list which grows in size to around 1GB (at this...

.NET Framework

ASP.NET Worker Process Memory Growing

by: Pablo Bianco SE | last post by:

Hi everyone, I am new in this group. I have a question and would like to have some support. A customer is experiencing memory problems with the asp.net worker process. The have an application...

.NET Framework

NOVICE: SQL Query on a Timer, Growing Memory Size

by: Joe C. | last post by:

hello, thanks for reading. this topic was probably covered in the past; if so, i apologize for the repost, and would you kindly redirect me to the topic? i have small app that monitors the...

Visual Basic .NET

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

Repost: postmaster growing to consume all memory

Similar topics