[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