[gold-users] Gold system getting bogged down

Scott Jackson scottmo at adaptivecomputing.com
Wed Dec 9 10:20:35 MST 2009


Victor,

Ahhh... Excellent. That's much better!

Scott


Hazlewood, Victor Gene wrote:
> This did the trick.  Accounting ran last night and 1922 gcharge commands
> were processed in 18 minutes yielding an average of .56 seconds per
> command.  This is a 16 times speedup from what I saw previously.  
>
> I'll put the vacuumdb in a cron once a week to keep this tidy.
>
> Thanks very much for the tip.  
>
> -Victor
>
> -----Original Message-----
> From: Scott Jackson [mailto:scottmo at adaptivecomputing.com] 
> Sent: Tuesday, December 08, 2009 2:22 PM
> To: Hazlewood, Victor Gene
> Cc: gold-users at supercluster.org
> Subject: Re: [gold-users] Gold system getting bogged down
>
> 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