Message-ID: <40C90778.4090308@vasoftware.com>
Date: Thu, 10 Jun 2004 18:14:32 -0700
From: Mark Striebeck <mstriebeck@vasoftware.com>
User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207)
MIME-Version: 1.0
To: pgsql-admin@postgresql.org, pgsql-general@postgresql.org
Subject: out of memory error
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

we are using Postgres with a J2EE application (JBoss) and get 
intermittent "out of memory" errors on the Postgres database. We are 
running on a fairly large Linux server (Dual 3GHz, 2GB Ram) with the 
following parameters:

shared_buffers = 8192
sort_mem = 8192
effective_cache_size = 234881024
random_page_cost = 2

(everything else is default)

The error message in the log is:

Jun 10 17:20:04 cruisecontrol-rhea postgres[6856]: [6-1] ERROR:  53200: 
out of memory
Jun 10 17:20:04 cruisecontrol-rhea postgres[6856]: [6-2] DETAIL:  Failed 
on request of size 208.
Jun 10 17:20:04 cruisecontrol-rhea postgres[6856]: [6-3] LOCATION:  
AllocSetAlloc, aset.c:700

All failures are with the following query (again, it only fails every 
now and then). The query returns only a few results:
STATEMENT:  SELECT
    Tracker_Artifact0.id AS id,
    Tracker_Artifact0.priority AS priority,
    Tracker_Artifact_extension_f1.path AS projectPathString,
    Tracker_Artifact_extension_f1.title AS projectTitle,
    Tracker_Artifact_extension_f0.project_id AS projectId,
    Tracker_Artifact_extension_f0.path AS folderPathString,
    Tracker_Artifact_extension_f0.title AS folderTitle,
    Tracker_Artifact_extension0.folder_id AS folderId,
    Tracker_Artifact_extension0.title AS title,
    Tracker_Artifact_extension0.name AS name,
    Tracker_Artifact0.description AS description,
    Tracker_Artifact_group0.value AS artifactGroup,
    Tracker_Artifact_status0.value AS status,
    Tracker_Artifact_status0.value_class AS statusClass,
    Tracker_Artifact_category0.value AS category,
    Tracker_Artifact_customer0.value AS customer,
    Tracker_Artifact_extension_c0.username AS submittedByUsername,
    Tracker_Artifact_extension_c0.full_name AS submittedByFullname,
    Tracker_Artifact_extension0.date_created AS submittedDate,
    Tracker_Artifact0.close_date AS closeDate,
    Tracker_Artifact_ArtifactAss0.username AS assignedToUsername,
    Tracker_Artifact_ArtifactAss0.full_name AS assignedToFullname,
    Tracker_Artifact_extension0.date_last_modified AS lastModifiedDate,
    Tracker_Artifact0.estimated_hours AS estimatedHours,
    Tracker_Artifact0.actual_hours AS actualHours,
    Tracker_Artifact_extension0.version AS version
FROM
    field_value Tracker_Artifact_group0,
    item Tracker_Artifact_extension0,
    relationship Tracker_Artifact_relation_Ar0,
    sfuser Tracker_Artifact_ArtifactAss0,
    field_value Tracker_Artifact_status0,
    field_value Tracker_Artifact_category0,
    field_value Tracker_Artifact_customer0,
    folder Tracker_Artifact_extension_f0,
    artifact Tracker_Artifact0,
    project Tracker_Artifact_extension_f1,
    sfuser Tracker_Artifact_extension_c0
WHERE
    Tracker_Artifact0.id=Tracker_Artifact_extension0.id
    AND 
Tracker_Artifact_extension0.folder_id=Tracker_Artifact_extension_f0.id
    AND 
Tracker_Artifact_extension_f0.project_id=Tracker_Artifact_extension_f1.id
    AND Tracker_Artifact0.group_fv=Tracker_Artifact_group0.id
    AND Tracker_Artifact0.status_fv=Tracker_Artifact_status0.id
    AND Tracker_Artifact0.category_fv=Tracker_Artifact_category0.id
    AND Tracker_Artifact0.customer_fv=Tracker_Artifact_customer0.id
    AND 
Tracker_Artifact_extension0.created_by_id=Tracker_Artifact_extension_c0.id
    AND Tracker_Artifact_relation_Ar0.is_deleted=false
    AND 
Tracker_Artifact_relation_Ar0.relationship_type_name='ArtifactAssignment'
    AND 
Tracker_Artifact_relation_Ar0.origin_id=Tracker_Artifact_ArtifactAss0.id
    AND Tracker_Artifact0.id=Tracker_Artifact_relation_Ar0.target_id
    AND (Tracker_Artifact_extension_f1.path='projects' OR 
Tracker_Artifact_extension_f1.path LIKE 'projects.%')
    AND Tracker_Artifact_extension0.is_deleted=false
    AND Tracker_Artifact_status0.value_class='Open'
    AND Tracker_Artifact_ArtifactAss0.username='foundr12622313'
ORDER BY
    priority ASC,
    lastModifiedDate DESC

Can anyone see anything dangerous about this query? What's the best way 
to analyze this further?

Thanks
    MarkS