[gold-users] speeding up gold reservations

Scott Jackson scottmo at clusterresources.com
Wed Apr 15 19:01:45 MDT 2009

Hi Stijn,

Sorry to take so long to reply .... you know the story.

Stijn De Weirdt wrote:
> hi all,
> i'm running a maui 3.2.6p21/torque 2.3.6 with GOLD as AM and we have
> some issues with users submitting large (as in 10+k) amounts of short
> (5-10 minutes) jobs to the queue and this has been choking up the system
> somewhat.
Wow. That's alot:)

> one factor in this whole process is that gold slows things down a lot. i
> see gold reservation requests (Successfully reserved X credits for job
> Y) when each job enter the maui queue (if i phrase this correctly), and
> one when the job is done.
> the first request is the most limiting one, as all new jobs in the queue
> are processed on entering (although i have MAXIJOB set rather low, so
> almost all of these jobs enter as blocked jobs anyway). each request
> takes approx 600-700ms, and because the jobs finish more quickly then
> the time needed for maui to add newly submitted jobs (not all of them,
> but still a lot), cluster usage is spiked. 
I would expect the gold calls to happen when the jobs are started, not 
when they are submitted. Some sites use a submit filter to check for a 
reasonable balance when the job is submitted to prevent it being held if 
it is later found to be out of credits, but this is entirely optional.

> maui is annoying that it doesn't make these request in parallel or
> doesn't make them at all since these will be blocked jobs anyway. if the
> gold requests where made when unblocking the jobs, at least the usage
> would be more optimal.
I am surprised by this behavior. I am assuming you are meaning that the 
jobs run shortly after being unblocked and that you would prefer Maui 
reserve the jobs in Gold at this time instead of at submit time. I to 
believe that is what it should be doing. Perhaps you can present the 
evidence of this behavior in the maui logs. Or perhaps I am 
misunderstanding your statement. It is true that neither Maui nor Moab 
currently batch the gold requests (probably primarily due to the fact 
that there is no current support in Gold for batched requests). [That 
might not be entirely true -- Gold may support it if you were to use the 
perl API, I'd have to look.]

> but does anyone have any tips to speedup individual gold queries?
Yes, Are you already using the new indexes? We've recently introduced 
indexes into the Gold tables which roughly speeds things up by 10x. 
Also, if your database has been in use for awhile (weeks or months), you 
will need to VACUUM it periodically to keep the queries quick (this also 
can make a very large difference).

$ grep INDEX bank.sql.in
CREATE INDEX g_object_name_idx ON g_object (g_name);
CREATE INDEX g_object_deleted_idx ON g_object (g_deleted);
CREATE INDEX g_object_txnid_idx ON g_object (g_transaction_id);
CREATE INDEX g_attribute_object_idx ON g_attribute (g_object);
CREATE INDEX g_attribute_name_idx ON g_attribute (g_name);
CREATE INDEX g_attribute_deleted_idx ON g_attribute (g_deleted);
CREATE INDEX g_attribute_txnid_idx ON g_attribute (g_transaction_id);
CREATE INDEX g_action_object_idx ON g_action (g_object);
CREATE INDEX g_action_name_idx ON g_action (g_name);
CREATE INDEX g_action_deleted_idx ON g_action (g_deleted);
CREATE INDEX g_action_txnid_idx ON g_action (g_transaction_id);
CREATE INDEX g_transaction_id_idx ON g_transaction (g_id);
CREATE INDEX g_transaction_created_idx ON g_transaction (g_creation_time);
CREATE INDEX g_transaction_account_idx ON g_transaction (g_account);
CREATE INDEX g_transaction_delta_idx ON g_transaction (g_delta);
CREATE INDEX g_transaction_deleted_idx ON g_transaction (g_deleted);
CREATE INDEX g_transaction_txnid_idx ON g_transaction (g_transaction_id);
CREATE INDEX g_system_name_idx ON g_system (g_name);
CREATE INDEX g_system_deleted_idx ON g_system (g_deleted);
CREATE INDEX g_system_txnid_idx ON g_system (g_transaction_id);
CREATE INDEX g_user_name_idx ON g_user (g_name);
CREATE INDEX g_user_deleted_idx ON g_user (g_deleted);
CREATE INDEX g_user_txnid_idx ON g_user (g_transaction_id);
CREATE INDEX g_role_name_idx ON g_role (g_name);
CREATE INDEX g_role_deleted_idx ON g_role (g_deleted);
CREATE INDEX g_role_txnid_idx ON g_role (g_transaction_id);
CREATE INDEX g_role_action_role_idx ON g_role_action (g_role);
CREATE INDEX g_role_action_name_idx ON g_role_action (g_name);
CREATE INDEX g_role_action_deleted_idx ON g_role_action (g_deleted);
CREATE INDEX g_role_action_txnid_idx ON g_role_action (g_transaction_id);
CREATE INDEX g_role_user_role_idx ON g_role_user (g_role);
CREATE INDEX g_role_user_name_idx ON g_role_user (g_name);
CREATE INDEX g_role_user_deleted_idx ON g_role_user (g_deleted);
CREATE INDEX g_role_user_txnid_idx ON g_role_user (g_transaction_id);
CREATE INDEX g_password_user_idx ON g_password (g_user);
CREATE INDEX g_password_deleted_idx ON g_password (g_deleted);
CREATE INDEX g_password_txnid_idx ON g_password (g_transaction_id);
CREATE INDEX g_key_generator_name_idx ON g_key_generator (g_name);
CREATE INDEX g_organization_name_idx ON g_organization (g_name);
CREATE INDEX g_organization_deleted_idx ON g_organization (g_deleted);
CREATE INDEX g_organization_txnid_idx ON g_organization (g_transaction_id);
CREATE INDEX g_project_name_idx ON g_project (g_name);
CREATE INDEX g_project_deleted_idx ON g_project (g_deleted);
CREATE INDEX g_project_txnid_idx ON g_project (g_transaction_id);
CREATE INDEX g_machine_name_idx ON g_machine (g_name);
CREATE INDEX g_machine_deleted_idx ON g_machine (g_deleted);
CREATE INDEX g_machine_txnid_idx ON g_machine (g_transaction_id);
CREATE INDEX g_project_user_project ON g_project_user (g_project);
CREATE INDEX g_project_user_name_idx ON g_project_user (g_name);
CREATE INDEX g_project_user_deleted_idx ON g_project_user (g_deleted);
CREATE INDEX g_project_user_txnid_idx ON g_project_user (g_transaction_id);
CREATE INDEX g_project_machine_project ON g_project_machine (g_project);
CREATE INDEX g_project_machine_name_idx ON g_project_machine (g_name);
CREATE INDEX g_project_machine_deleted_idx ON g_project_machine (g_deleted);
CREATE INDEX g_project_machine_txnid_idx ON g_project_machine 
CREATE INDEX g_account_id_idx ON g_account (g_id);
CREATE INDEX g_account_deleted_idx ON g_account (g_deleted);
CREATE INDEX g_account_txnid_idx ON g_account (g_transaction_id);
CREATE INDEX g_account_project_account_idx ON g_account_project (g_account);
CREATE INDEX g_account_project_name_idx ON g_account_project (g_name);
CREATE INDEX g_account_project_deleted_idx ON g_account_project (g_deleted);
CREATE INDEX g_account_project_txnid_idx ON g_account_project 
CREATE INDEX g_account_user_account_idx ON g_account_user (g_account);
CREATE INDEX g_account_user_name_idx ON g_account_user (g_name);
CREATE INDEX g_account_user_deleted_idx ON g_account_user (g_deleted);
CREATE INDEX g_account_user_txnid_idx ON g_account_user (g_transaction_id);
CREATE INDEX g_account_machine_account_idx ON g_account_machine (g_account);
CREATE INDEX g_account_machine_name_idx ON g_account_machine (g_name);
CREATE INDEX g_account_machine_deleted_idx ON g_account_machine (g_deleted);
CREATE INDEX g_account_machine_txnid_idx ON g_account_machine 
CREATE INDEX g_account_organization_account_idx ON 
g_account_organization (g_account);
CREATE INDEX g_account_organization_name_idx ON g_account_organization 
CREATE INDEX g_account_organization_deleted_idx ON 
g_account_organization (g_deleted);
CREATE INDEX g_account_organization_txnid_idx ON g_account_organization 
CREATE INDEX g_allocation_id_idx ON g_allocation (g_id);
CREATE INDEX g_allocation_account_idx ON g_allocation (g_account);
CREATE INDEX g_allocation_time_idx ON g_allocation (g_start_time, 
CREATE INDEX g_allocation_deleted_idx ON g_allocation (g_deleted);
CREATE INDEX g_allocation_txnid_idx ON g_allocation (g_transaction_id);
CREATE INDEX g_reservation_id_idx ON g_reservation (g_id);
CREATE INDEX g_reservation_name_idx ON g_reservation (g_name);
CREATE INDEX g_reservation_time_idx ON g_reservation (g_start_time, 
CREATE INDEX g_reservation_deleted_idx ON g_reservation (g_deleted);
CREATE INDEX g_reservation_txnid_idx ON g_reservation (g_transaction_id);
CREATE INDEX g_reservation_acct_where_idx ON g_reservation_allocation 
(g_account) WHERE g_deleted!='True';
CREATE INDEX g_reservation_allocation_reservation_idx ON 
g_reservation_allocation (g_reservation);
CREATE INDEX g_reservation_allocation_id_idx ON g_reservation_allocation 
CREATE INDEX g_reservation_allocation_account_idx ON 
g_reservation_allocation (g_account);
CREATE INDEX g_reservation_allocation_deleted_idx ON 
g_reservation_allocation (g_deleted);
CREATE INDEX g_reservation_allocation_txnid_idx ON 
g_reservation_allocation (g_transaction_id);
CREATE INDEX g_quotation_id_idx ON g_quotation (g_id);
CREATE INDEX g_quotation_time_idx ON g_quotation (g_start_time, g_end_time);
CREATE INDEX g_quotation_deleted_idx ON g_quotation (g_deleted);
CREATE INDEX g_quotation_txnid_idx ON g_quotation (g_transaction_id);
CREATE INDEX g_charge_rate_type_idx ON g_charge_rate (g_type);
CREATE INDEX g_charge_rate_name_idx ON g_charge_rate (g_name);
CREATE INDEX g_charge_rate_deleted_idx ON g_charge_rate (g_deleted);
CREATE INDEX g_charge_rate_txnid_idx ON g_charge_rate (g_transaction_id);
CREATE INDEX g_quotation_charge_rate_quotation_idx ON 
g_quotation_charge_rate (g_quotation);
CREATE INDEX g_quotation_charge_rate_type_idx ON g_quotation_charge_rate 
CREATE INDEX g_quotation_charge_rate_name_idx ON g_quotation_charge_rate 
CREATE INDEX g_quotation_charge_rate_deleted_idx ON 
g_quotation_charge_rate (g_deleted);
CREATE INDEX g_quotation_charge_rate_txnid_idx ON 
g_quotation_charge_rate (g_transaction_id);
CREATE INDEX g_job_id_idx ON g_job (g_id);
CREATE INDEX g_job_jobid_idx ON g_job (g_job_id);
CREATE INDEX g_job_deleted_idx ON g_job (g_deleted);
CREATE INDEX g_job_txnid_idx ON g_job (g_transaction_id);
CREATE INDEX g_account_account_account_idx ON g_account_account (g_account);
CREATE INDEX g_account_account_id_idx ON g_account_account (g_id);
CREATE INDEX g_account_account_deleted_idx ON g_account_account (g_deleted);
CREATE INDEX g_account_account_txnid_idx ON g_account_account 

> i have a tip myself: there is a certain SQL query (see bottom of mail)
> that is executed rather slowly with the default schema (it's not cached
> by the DB unlike almost almost all other SELECT SQL queries from gold).
> (it is actually the slowest of them all, taking approx 400-500ms of teh
> total 600-700ms).
> we first had teh MySQL as DB, but we switched to postgres 8.3.6 (this
> gave 10-15% speedup), but i found that adding another 2 partial indexes
> improved this query to approx 150-200ms).
> CREATE INDEX g_reservation_not_deleted_start_idx ON g_reservation
> (g_start_time) WHERE g_deleted!='True';
> CREATE INDEX g_resallo_not_deleted_id_idx ON g_reservation_allocation
> (g_id) WHERE g_deleted!='True';
> ANALYZE g_reservation;
> ANALYZE g_reservation_allocation;

Let me know if you can pinpoint anything else that can be improved and 
we can either address it or put it in as a feature request.


> hope this helps,
> stijn
> 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<='1236070726' AND g_reservation.g_en\
> d_time>'1236070726' AND  ( g_reservation_allocation.g_id='35' OR
> g_reservation_allocation.g_id='20' )  ) AND g_reservation.g_deleted!
> ='True' AND g_reservatio\
> n_allocation.g_deleted!='True'
> _______________________________________________
> gold-users mailing list
> gold-users at supercluster.org
> http://www.supercluster.org/mailman/listinfo/gold-users

More information about the gold-users mailing list