[gold-users] Gold system getting bogged down

Hazlewood, Victor Gene vhazlewo at utk.edu
Tue Dec 8 12:22:07 MST 2009


So I also updated the postgres configuration items max_fsm_pages and
max_fsm_relations to the numbers suggested by the output of the vacuum
command.   These changes required a restart of the (gold and) postgres
server to take effect.

Results from glsuser and glsproject now return in a second or less.  So
that is a visible improvement in user response time to these commands.
Should help with the gcharge commands also.

-Victor

-----Original Message-----
From: Scott Jackson [mailto:scottmo at adaptivecomputing.com] 
Sent: Tuesday, December 08, 2009 1:27 PM
To: Hazlewood, Victor Gene
Cc: gold-users at supercluster.org
Subject: Re: [gold-users] Gold system getting bogged down

Victor,

(I know I gave you a similar response to the email forwarded via Steve. 
This is being posted for the sake of the gold-users list).

I have not seen times like this unless the site did not do vacuums or 
did not have the gold indexes in place. So, assuming you are using 
postgres, please do a VACUUM; and a VACUUM ANALYZE; (don't forget the 
semicolon!).
Also, please verify to me that you have put in the indexes. I think we 
are up to about 111 now and it makes a big difference. [BTW these 
indexes come in the newer Gold versions.]

[scottmo at keko gold-prod]$ echo "\di" | psql gold
List of relations
Schema | Name | Type | Owner | Table
--------+------------------------------------------+-------+---------+--
------------------------ 

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

[scottmo at keko gold-prod]$ grep INDEX bank.sql
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 
(g_transaction_id);
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 
(g_transaction_id);
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 
(g_transaction_id);
CREATE INDEX g_account_organization_account_idx ON 
g_account_organization (g_account);
CREATE INDEX g_account_organization_name_idx ON g_account_organization 
(g_name);
CREATE INDEX g_account_organization_deleted_idx ON 
g_account_organization (g_deleted);
CREATE INDEX g_account_organization_txnid_idx ON g_account_organization 
(g_transaction_id);
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, 
g_end_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, 
g_end_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_allocation_reservation_idx ON 
g_reservation_allocation (g_reservation);
CREATE INDEX g_reservation_allocation_id_idx ON g_reservation_allocation

(g_id);
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_reservation_acct_where_idx ON g_reservation_allocation 
(g_account) WHERE g_deleted!='True';
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

(g_type);
CREATE INDEX g_quotation_charge_rate_name_idx ON g_quotation_charge_rate

(g_name);
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 
(g_transaction_id);
[scottmo at keko gold-prod]$ grep INDEX bank.sql|wc -l
111

I would hope/expect these to get down to well below a half second a 
piece (and hopefully around .2s or .1s apiece).

Now, it is possible that if you do have significant overlapping gold 
requests that some unexpected lock contention may be occurring. I have 
not really seen this, but I cannot preclude it as a possibility. Before 
we get deeper into this, first let me know if the vacuuming or index 
ideas make any difference. [Please forgive me if you have already done 
this with me.]

Thanks,

Scott

Hazlewood, Victor Gene wrote:
>
> Hi!
>
> Got a gold issue... We have a Cray XT5 system which is starting to run

> about 7000 jobs a day. I post process the Torque accounting log each 
> night and create gcharge records for posting to gold. With 7,000 
> gcharge records and each taking approximately 5-8 seconds each it is 
> taking 15 or more hours to process the accounting records. (bad!). The

> gold database and gold daemon are on an infrastructure server and the 
> gcharge is done on the Cray directly.
>
> What can be done to the post processing process, gold daemon or the 
> gold database to speed up the processing of these gcharge records? I 
> wish these gcharge commands would take less than a second. Maybe they 
> have always processed in more than 1 second, maybe even more but when 
> we had 1000-2000 or less jobs is wasn't really noticed.
>
> These records in Gold are being used to copy information to another 
> postgres database using gold commands to collect the info and then 
> subsequently these records are posted to an off site database. With 
> these three things going on (charge posting, processing charges into 
> the other database, and sending the charges offsite) gold is doing 
> quite a bit and seems to get bogged down with these requests. Also 
> gold is getting queried at the job submission to make sure there is 
> time available in the account. All this is putting pressure on gold 
> and it seems to be slowing significantly (or noticeably I guess) under

> the pressure.
>
> Thanks for any help you can provide.
>
> -Victor
>
> Victor Hazlewood, CISSP
>
> Senior HPC Systems Analyst
>
> National Institute for Computational Science
>
> University of Tennessee
>
> http://www.nics.tennessee.edu/ <http://www.nics.utk.edu/>
>
>
------------------------------------------------------------------------
>
> _______________________________________________
> 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