[gold-users] speeding up gold

Scott Jackson scott at clusterresources.com
Fri Nov 9 12:25:59 MST 2007


Brock,

I have recently received a writeup of the indexes that ASU put into Gold
which, for them, decreased average gold transaction times from about 10
seconds to about half a second. It sounds like the indexes are the way
to go on this. I am attaching the report done by Karl Lindekugel
indicating the indexes that were created that achieved this remarkable
improvement. I will be trying to include indexes in future versions of
Gold.

So that I am able to take advantage of your findings as well, would you
please send the indexes that you have created and indicate which indexes
you thing made the most difference.

Thank you!

Scott

On Tue, 2007-10-30 at 18:34 -0400, Brock Palen wrote:
> 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
> 
> 
> _______________________________________________
> gold-users mailing list
> gold-users at supercluster.org
> http://www.supercluster.org/mailman/listinfo/gold-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Gold DB.sxw
Type: application/vnd.sun.xml.writer
Size: 10007 bytes
Desc: not available
Url : http://www.supercluster.org/pipermail/gold-users/attachments/20071109/1d33634e/GoldDB.sxw


More information about the gold-users mailing list