Deterioration in performance when query executed in multi threads

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux