Deterioration in performance when query executed in multi threads - Mailing list pgsql-performance
| From | Anne Rosset |
|---|---|
| Subject | Deterioration in performance when query executed in multi threads |
| Date | |
| Msg-id | 9A6B86D66BD3C2438AFACFA09354890F20A2DE68@EXCH01.sp.corp.collab.net Whole thread Raw |
| List | pgsql-performance |
Hi all,
We are running a stress test that executes one select query with multiple threads.
The query executes very fast (10ms). It returns 100 rows. I see deterioration in the performance when we have multiple threads executing the query. With 100 threads, the query takes between 3s and 8s.
I suppose there is a way to tune our database. What are the parameters I should look into? (shared_buffer?, wal_buffer?)
srdb=> explain analyze SELECT
psrdb-> artifact.id AS id,
psrdb-> artifact.priority AS priority,
psrdb-> project.path AS projectPathString,
psrdb-> project.title AS projectTitle,
psrdb-> folder.project_id AS projectId,
psrdb-> folder.path AS folderPathString,
psrdb-> folder.title AS folderTitle,
psrdb-> item.folder_id AS folderId,
psrdb-> item.planning_folder_id AS planningFolderId,
psrdb-> item.title AS title,
psrdb-> item.name AS name,
psrdb-> artifact.description AS description,
psrdb-> field_value.value AS artifactGroup,
psrdb-> field_value2.value AS status,
psrdb-> field_value2.value_class AS statusClass,
psrdb-> field_value3.value AS category,
psrdb-> field_value4.value AS customer,
psrdb-> sfuser.username AS submittedByUsername,
psrdb-> sfuser.full_name AS submittedByFullname,
psrdb-> item.date_created AS submittedDate,
psrdb-> artifact.close_date AS closeDate,
psrdb-> sfuser2.username AS assignedToUsername,
psrdb-> sfuser2.full_name AS assignedToFullname,
psrdb-> item.date_last_modified AS lastModifiedDate,
psrdb-> artifact.estimated_effort AS estimatedEffort,
psrdb-> artifact.actual_effort AS actualEffort,
psrdb-> artifact.remaining_effort AS remainingEffort,
psrdb-> artifact.points AS points,
psrdb-> artifact.autosumming AS autosumming,
psrdb-> item.version AS version
psrdb-> FROM
psrdb-> field_value field_value2,
psrdb-> sfuser sfuser2,
psrdb-> field_value field_value3,
psrdb-> field_value field_value,
psrdb-> field_value field_value4,
psrdb-> item item,
psrdb-> project project,
psrdb-> relationship relationship,
psrdb-> artifact artifact,
psrdb-> sfuser sfuser,
psrdb-> folder folder
psrdb-> WHERE
psrdb-> artifact.id=item.id
psrdb-> AND item.folder_id=folder.id
psrdb-> AND folder.project_id=project.id
psrdb-> AND artifact.group_fv=field_value.id
psrdb-> AND artifact.status_fv=field_value2.id
psrdb-> AND artifact.category_fv=field_value3.id
psrdb-> AND artifact.customer_fv=field_value4.id
psrdb-> AND item.created_by_id=sfuser.id
psrdb-> AND relationship.is_deleted=false
psrdb-> AND relationship.relationship_type_name='ArtifactAssignment'
psrdb->
psrdb-> AND relationship.origin_id=sfuser2.id
psrdb-> AND artifact.id=relationship.target_id
psrdb-> AND item.is_deleted=false
psrdb-> AND folder.is_deleted=false
psrdb-> AND folder.project_id='proj1032'
psrdb-> AND item.folder_id='tracker1213'
psrdb-> AND folder.path='tracker.trackerName';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..117.32 rows=3 width=1272) (actual time=7.003..9.684 rows=100 loops=1)
-> Nested Loop (cost=0.00..116.69 rows=2 width=1271) (actual time=6.987..8.820 rows=100 loops=1)
Join Filter: ((item.created_by_id)::text = (sfuser.id)::text)
-> Seq Scan on sfuser (cost=0.00..7.65 rows=65 width=30) (actual time=0.013..0.053 rows=65 loops=1)
-> Materialize (cost=0.00..107.10 rows=2 width=1259) (actual time=0.005..0.100 rows=100 loops=65)
-> Nested Loop (cost=0.00..107.09 rows=2 width=1259) (actual time=0.307..5.667 rows=100 loops=1)
-> Nested Loop (cost=0.00..106.45 rows=2 width=1263) (actual time=0.294..4.841 rows=100 loops=1)
-> Nested Loop (cost=0.00..105.82 rows=2 width=1267) (actual time=0.281..3.988 rows=100 loops=1)
-> Nested Loop (cost=0.00..105.18 rows=2 width=1271) (actual time=0.239..3.132 rows=100 loops=1)
-> Nested Loop (cost=0.00..104.61 rows=2 width=1259) (actual time=0.223..2.457 rows=100 loops=1)
-> Nested Loop (cost=0.00..16.55 rows=1 width=1099) (actual time=0.095..0.096 rows=1 loops=1)
-> Index Scan using project_pk on project (cost=0.00..8.27 rows=1 width=1114) (actual time=0.039..0.039 rows=1 loops=1)
Index Cond: ((id)::text = 'proj1032'::text)
-> Index Scan using folder_pk on folder (cost=0.00..8.27 rows=1 width=67) (actual time=0.054..0.055 rows=1 loops=1)
Index Cond: ((folder.id)::text = 'tracker1213'::text)
Filter: ((NOT folder.is_deleted) AND ((folder.project_id)::text = 'proj1032'::text) AND (folder.path = 'tracker.trackerName'::text))
-> Nested Loop (cost=0.00..88.04 rows=2 width=169) (actual time=0.127..2.323 rows=100 loops=1)
-> Nested Loop (cost=0.00..63.19 rows=3 width=168) (actual time=0.090..1.309 rows=100 loops=1)
-> Index Scan using item_folder on item (cost=0.00..21.78 rows=5 width=77) (actual time=0.046..0.265 rows=100 loops=1)
Index Cond: ((folder_id)::text = 'tracker1213'::text)
Filter: (NOT is_deleted)
-> Index Scan using artifact_pk on artifact (cost=0.00..8.27 rows=1 width=91) (actual time=0.009..0.009 rows=1 loops=100)
Index Cond: ((artifact.id)::text = (item.id)::text)
-> Index Scan using relation_target on relationship (cost=0.00..8.27 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=100)
Index Cond: ((relationship.target_id)::text = (artifact.id)::text)
Filter: ((NOT relationship.is_deleted) AND ((relationship.relationship_type_name)::text = 'ArtifactAssignment'::text))
-> Index Scan using sfuser_pk on sfuser sfuser2 (cost=0.00..0.27 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=100)
Index Cond: ((sfuser2.id)::text = (relationship.origin_id)::text)
-> Index Scan using field_value_pk on field_value field_value3 (cost=0.00..0.30 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: ((field_value3.id)::text = (artifact.category_fv)::text)
-> Index Scan using field_value_pk on field_value (cost=0.00..0.30 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: ((field_value.id)::text = (artifact.group_fv)::text)
-> Index Scan using field_value_pk on field_value field_value4 (cost=0.00..0.30 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: ((field_value4.id)::text = (artifact.customer_fv)::text)
-> Index Scan using field_value_pk on field_value field_value2 (cost=0.00..0.30 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: ((field_value2.id)::text = (artifact.status_fv)::text)
Total runtime: 10.210 ms
(37 rows)
Thanks for your help,
Anne
pgsql-performance by date: