[gold-users] speeding up gold

Brock Palen brockp at umich.edu
Tue Oct 30 16:34:31 MDT 2007


We have gold connected to moab,  It is not enforcing at the moment,  
for the most part we use it to collect stats.
We would like to add 2 more clusters (about 400 Running jobs) soon  
and a grid (400+ hosts serial jobs)  latter.  So the speed of gold is  
very important.

We have added indexes to the postgre database, this has speed up many  
of the queries (using log_statement='all')

We have one last query that for the life of me (we dont have DB  
admins) cant speed up any more than we already have.

The current speed is about 400-500ms. (.5 seconds)

Here is the query ran with explain analyze
Any postgree guru help would be wonderful:

gold=> explain analyze SELECT  
g_reservation_allocation.g_id,g_reservation_allocation.g_amount FROM
  g_reservation, g_reservation_allocation
WHERE ( g_reservation.g_id=g_reservation_allocation.g_reservation AND  
g_reservation.g_start_time<='1193782690' AND  
g_reservation.g_end_time>'1193782690' AND  
g_reservation_allocation.g_account='2' )
AND g_reservation.g_deleted!='True' AND  
g_reservation_allocation.g_deleted!='True';
                                                                      
QUERY PLAN
------------------------------------------------------------------------ 
------------------------------------------------------------------------ 
---
Merge Join  (cost=14058.50..19042.41 rows=331684 width=12) (actual  
time=460.943..461.362 rows=82 loops=1)
    Merge Cond: ("outer".g_reservation = "inner".g_id)
    ->  Sort  (cost=3107.09..3111.41 rows=1730 width=16) (actual  
time=382.068..382.130 rows=85 loops=1)
          Sort Key: g_reservation_allocation.g_reservation
          ->  Bitmap Heap Scan on g_reservation_allocation   
(cost=12.08..3014.04 rows=1730 width=16) (actual  
time=381.366..381.811 rows=85 loops=1)
                Recheck Cond: (g_account = 2)
                Filter: ((g_deleted)::text <> 'True'::text)
                ->  Bitmap Index Scan on g_account_idx   
(cost=0.00..12.08 rows=1738 width=0) (actual time=116.691..116.691  
rows=346817 loops=1)
                      Index Cond: (g_account = 2)
    ->  Sort  (cost=10951.41..11047.28 rows=38345 width=4) (actual  
time=78.854..78.921 rows=82 loops=1)
          Sort Key: g_reservation.g_id
          ->  Bitmap Heap Scan on g_reservation   
(cost=1140.68..7646.10 rows=38345 width=4) (actual  
time=78.333..78.631 rows=82 loops=1)
                Recheck Cond: ((g_start_time <= 1193782690) AND  
(g_end_time > 1193782690))
                Filter: ((g_deleted)::text <> 'True'::text)
                ->  Bitmap Index Scan on g_time_idx   
(cost=0.00..1140.68 rows=38538 width=0) (actual time=78.276..78.276  
rows=194 loops=1)
                      Index Cond: ((g_start_time <= 1193782690) AND  
(g_end_time > 1193782690))
Total runtime: 461.867 ms
(17 rows)

Here is the indexes we have for g_reservation_allocation and  
g_reservation
g_reservation
Indexes:
     "g_deleted_idx" btree (g_deleted)
     "g_id_idx2" btree (g_id)
     "g_name_idx" btree (g_name)
     "g_time_idx" btree (g_start_time, g_end_time, g_deleted)
     "g_transaction_id_idx" btree (g_transaction_id)

g_reservation_allocation
     "g_account_idx" btree (g_account)
     "g_id_idx" btree (g_id)
     "g_reservation_allocation_deleted_idx" btree (g_deleted)
     "g_reservation_allocation_idx" btree (g_transaction_id, g_deleted)
     "g_reservation_idx" btree (g_reservation)


Thanks.  Turning on gold slowed moab from 25 Seconds per iteration to  
about 50, the indexes have sped it up to about 30 seconds,  Just  
looking to get that last query down to 100ms or less (if posable)

Brock Palen
Center for Advanced Computing
brockp at umich.edu
(734)936-1985




More information about the gold-users mailing list