out of memory error
Question posted by: Mark Striebeck
(Guest)
on
November 23rd, 2005 01:08 AM
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.i d
AND
Tracker_Artifact_extension0.folder_id=Tracker_Arti fact_extension_f0.id
AND
Tracker_Artifact_extension_f0.project_id=Tracker_A rtifact_extension_f1.id
AND Tracker_Artifact0.group_fv=Tracker_Artifact_group0 .id
AND Tracker_Artifact0.status_fv=Tracker_Artifact_statu s0.id
AND Tracker_Artifact0.category_fv=Tracker_Artifact_cat egory0.id
AND Tracker_Artifact0.customer_fv=Tracker_Artifact_cus tomer0.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_na me='ArtifactAssignment'
AND
Tracker_Artifact_relation_Ar0.origin_id=Tracker_Ar tifact_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='foundr1262 2313'
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
