[gold-users] Gold system getting bogged down

Scott Jackson scottmo at adaptivecomputing.com
Tue Dec 8 12:21:43 MST 2009


Victor,

Great! I look forward to hearing your results.

Scott


Hazlewood, Victor Gene wrote:
> Scott,
>
> That seemed to make a difference.  I'll know for sure when over a
> thousand gcharges get run tonite.  After your pointer and reading
> chapters 17 and 22 of the postgres manual that covers server
> configuration and maintenance tasks I did the following three things to
> each of my databases (gold and amie):
>
> $ vacuumdb --verbose gold;
> $ vacuumdb --analyze gold;
> $ psql gold gold
> gold> reindex database gold;
>
> IT made a significant difference in turnaround time for the command.  I
> have been running gold for over a year and this is the first time I did
> the vacuum to reclaim space and a reindex to clean out/up the indexes.
>
> Thanks very much for this pointer.  I'll let you and Steve know the
> results of the gcharges that run overnight.
>
> -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