Massive insert vs heavy contention in LWLock:buffer_content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm working on an application which performs a lot of inserts in 2 large
tables.
Previously we didn't know about lwlocks, but we're now testing in Amazon RDS
Aurora - PostgreSQL (9.6.3).
In previous load tests, both local servers and classic Amazon RDS, there was
some scalability limit we couldn't find - CPU / memory / IO were all low,
but still there was contention that wasn't visible in PostgreSQL views.
Now with Aurora it shows that most of the sessions are blocking on
LWLock:buffer_content.

I would like some insights, as we have 2 tables with ~35 million rows each,
and they have several indexes (shown below).
This request is a crucial operation for our system, and each application
request must insert on those 2 large tables in a single transaction, plus
some other selects.

I've searched a lot and found nothing on how to mitigate this issue. Just
found that it might be related to inserts.

Any tips?

For reference, here are the descriptions of both tables:

\d transactions
                                                            Tabela
"public.transactions"
                      Coluna                       |            Tipo            
|                           Modificadores                           
---------------------------------------------------+-----------------------------+-------------------------------------------------------------------
 id                                                | bigint                     
| não nulo valor padrão de nextval('transactions_id_seq'::regclass)
 subclass                                          | character varying(31)      
| 
 amount                                            | numeric                    
| não nulo
 authorization_status                              | character varying(255)     
| 
 date                                              | timestamp without time
zone | não nulo
 description                                       | text                       
| 
 transaction_feedback_expiration_notified          | boolean                    
| 
 transaction_feedback_expiration_reminder_notified | boolean                    
| 
 transaction_feedback_reminder_notified            | boolean                    
| 
 by_id                                             | bigint                     
| 
 channel_id                                        | bigint                     
| não nulo
 feedback_id                                       | bigint                     
| 
 from_user_id                                      | bigint                     
| 
 next_authorization_level_id                       | bigint                     
| 
 to_user_id                                        | bigint                     
| 
 type_id                                           | bigint                     
| não nulo
 order_id                                          | bigint                     
| 
 status                                            | character varying(255)     
| 
 received                                          | boolean                    
| 
 principal_type_id                                 | bigint                     
| 
 access_client_id                                  | bigint                     
| 
 original_transfer_id                              | bigint                     
| 
 show_to_receiver                                  | boolean                    
| 
 expiration_date                                   | timestamp without time
zone | 
 scheduled                                         | boolean                    
| 
 first_installment_immediate                       | boolean                    
| 
 installments_count                                | integer                    
| 
 process_date                                      | timestamp without time
zone | 
 comments                                          | text                       
| 
 transaction_id                                    | bigint                     
| 
 sms_code                                          | character varying(255)     
| 
 external_principal_value                          | character varying(255)     
| 
 external_principal_type_id                        | bigint                     
| 
 received_by_id                                    | bigint                     
| 
 from_name                                         | character varying(255)     
| 
 to_name                                           | character varying(255)     
| 
 next_occurrence_date                              | timestamp without time
zone | 
 occurrences_count                                 | integer                    
| 
 occurrence_interval_amount                        | integer                    
| 
 occurrence_interval_field                         | character varying(255)     
| 
 last_occurrence_failure_id                        | bigint                     
| 
 last_occurrence_success_id                        | bigint                     
| 
 by_self                                           | boolean                    
| 
 from_system                                       | boolean                    
| 
 to_system                                         | boolean                    
| 
 ticket_number                                     | character varying(255)     
| 
 cancel_url                                        | character varying(255)     
| 
 success_url                                       | character varying(255)     
| 
 transaction_number                                | character varying(255)     
| 
 expiration_date_comments                          | text                       
| 
Índices:
    "transactions_pkey" PRIMARY KEY, btree (id)
    "ix_external_principal_value" btree (external_principal_value) WHERE
external_principal_value IS NOT NULL
    "ix_recurring_next_occurrence_date" btree (next_occurrence_date) WHERE
next_occurrence_date IS NOT NULL
    "ix_ticket_number" btree (lower(ticket_number::text)) WHERE
ticket_number IS NOT NULL
    "ix_transactions_amount" btree (amount)
    "ix_transactions_date" btree (date)
    "ix_transactions_fk_transactions_access_client_id" btree
(access_client_id) WHERE access_client_id IS NOT NULL
    "ix_transactions_fk_transactions_by_id" btree (by_id) WHERE by_id IS NOT
NULL
    "ix_transactions_fk_transactions_channel_id" btree (channel_id)
    "ix_transactions_fk_transactions_external_principal_type_id" btree
(external_principal_type_id) WHERE external_principal_type_id IS NOT NULL
    "ix_transactions_fk_transactions_feedback_id" btree (feedback_id) WHERE
feedback_id IS NOT NULL
    "ix_transactions_fk_transactions_from_user_id" btree (from_user_id)
WHERE from_user_id IS NOT NULL
    "ix_transactions_fk_transactions_last_occurrence_failure_id" btree
(last_occurrence_failure_id) WHERE last_occurrence_failure_id IS NOT NULL
    "ix_transactions_fk_transactions_last_occurrence_success_id" btree
(last_occurrence_success_id) WHERE last_occurrence_success_id IS NOT NULL
    "ix_transactions_fk_transactions_next_authorization_level_id" btree
(next_authorization_level_id) WHERE next_authorization_level_id IS NOT NULL
    "ix_transactions_fk_transactions_order_id" btree (order_id) WHERE
order_id IS NOT NULL
    "ix_transactions_fk_transactions_original_transfer_id" btree
(original_transfer_id) WHERE original_transfer_id IS NOT NULL
    "ix_transactions_fk_transactions_principal_type_id" btree
(principal_type_id) WHERE principal_type_id IS NOT NULL
    "ix_transactions_fk_transactions_received_by_id" btree (received_by_id)
WHERE received_by_id IS NOT NULL
    "ix_transactions_fk_transactions_to_user_id" btree (to_user_id) WHERE
to_user_id IS NOT NULL
    "ix_transactions_fk_transactions_transaction_id" btree (transaction_id)
WHERE transaction_id IS NOT NULL
    "ix_transactions_fk_transactions_type_id" btree (type_id)
    "ix_transactions_subclass" btree (subclass)
    "ix_transactions_transaction_number" btree
(lower(transaction_number::text)) WHERE transaction_number IS NOT NULL
    "next_occurrence_date" btree (next_occurrence_date)
Restrições de chave estrangeira:
    "fk_transactions_access_client_id" FOREIGN KEY (access_client_id)
REFERENCES access_clients(id)
    "fk_transactions_by_id" FOREIGN KEY (by_id) REFERENCES users(id)
    "fk_transactions_channel_id" FOREIGN KEY (channel_id) REFERENCES
channels(id)
    "fk_transactions_external_principal_type_id" FOREIGN KEY
(external_principal_type_id) REFERENCES principal_types(id)
    "fk_transactions_feedback_id" FOREIGN KEY (feedback_id) REFERENCES
refs(id)
    "fk_transactions_from_user_id" FOREIGN KEY (from_user_id) REFERENCES
users(id)
    "fk_transactions_last_occurrence_failure_id" FOREIGN KEY
(last_occurrence_failure_id) REFERENCES failed_payment_occurrences(id)
    "fk_transactions_last_occurrence_success_id" FOREIGN KEY
(last_occurrence_success_id) REFERENCES transfers(id)
    "fk_transactions_next_authorization_level_id" FOREIGN KEY
(next_authorization_level_id) REFERENCES authorization_levels(id)
    "fk_transactions_order_id" FOREIGN KEY (order_id) REFERENCES
ad_orders(id)
    "fk_transactions_original_transfer_id" FOREIGN KEY
(original_transfer_id) REFERENCES transfers(id)
    "fk_transactions_principal_type_id" FOREIGN KEY (principal_type_id)
REFERENCES principal_types(id)
    "fk_transactions_received_by_id" FOREIGN KEY (received_by_id) REFERENCES
users(id)
    "fk_transactions_to_user_id" FOREIGN KEY (to_user_id) REFERENCES
users(id)
    "fk_transactions_transaction_id" FOREIGN KEY (transaction_id) REFERENCES
transactions(id)
    "fk_transactions_type_id" FOREIGN KEY (type_id) REFERENCES
transfer_types(id)
Referenciada por:
    TABLE "amount_reservations" CONSTRAINT
"fk_amount_reservations_external_payment_id" FOREIGN KEY
(external_payment_id) REFERENCES transactions(id)
    TABLE "amount_reservations" CONSTRAINT
"fk_amount_reservations_scheduled_payment_id" FOREIGN KEY
(scheduled_payment_id) REFERENCES transactions(id)
    TABLE "amount_reservations" CONSTRAINT
"fk_amount_reservations_transaction_id" FOREIGN KEY (transaction_id)
REFERENCES transactions(id)
    TABLE "failed_payment_occurrences" CONSTRAINT
"fk_failed_payment_occurrences_recurring_payment_id" FOREIGN KEY
(recurring_payment_id) REFERENCES transactions(id)
    TABLE "refs" CONSTRAINT "fk_refs_transaction_id" FOREIGN KEY
(transaction_id) REFERENCES transactions(id)
    TABLE "scheduled_payment_installments" CONSTRAINT
"fk_scheduled_payment_installments_scheduled_payment_id" FOREIGN KEY
(scheduled_payment_id) REFERENCES transactions(id)
    TABLE "transaction_authorizations" CONSTRAINT
"fk_transaction_authorizations_transaction_id" FOREIGN KEY (transaction_id)
REFERENCES transactions(id)
    TABLE "transaction_custom_field_values" CONSTRAINT
"fk_transaction_custom_field_values_owner_id" FOREIGN KEY (owner_id)
REFERENCES transactions(id)
    TABLE "transactions" CONSTRAINT "fk_transactions_transaction_id" FOREIGN
KEY (transaction_id) REFERENCES transactions(id)
    TABLE "transfers" CONSTRAINT "fk_transfers_transaction_id" FOREIGN KEY
(transaction_id) REFERENCES transactions(id)
    TABLE "voucher_packs" CONSTRAINT "fk_voucher_packs_buy_id" FOREIGN KEY
(buy_id) REFERENCES transactions(id)
    TABLE "vouchers" CONSTRAINT "fk_vouchers_redeem_id" FOREIGN KEY
(redeem_id) REFERENCES transactions(id)


------------------------------------------------------------------

\d transfers
                                                    Tabela
"public.transfers"
              Coluna              |            Tipo             |                        
Modificadores                          
----------------------------------+-----------------------------+----------------------------------------------------------------
 id                               | bigint                      | não nulo
valor padrão de nextval('transfers_id_seq'::regclass)
 subclass                         | character varying(31)       | 
 amount                           | numeric                     | não nulo
 date                             | timestamp without time zone | não nulo
 emission_date                    | timestamp without time zone | 
 expiration_date                  | timestamp without time zone | 
 from_id                          | bigint                      | não nulo
 parent_id                        | bigint                      | 
 to_id                            | bigint                      | não nulo
 type_id                          | bigint                      | não nulo
 charged_back_by_id               | bigint                      | 
 user_account_fee_log_id          | bigint                      | 
 chargeback_of_id                 | bigint                      | 
 transaction_id                   | bigint                      | 
 scheduled_payment_installment_id | bigint                      | 
 transfer_fee_id                  | bigint                      | 
 number                           | integer                     | 
 by_id                            | bigint                      | 
 transaction_number               | character varying(255)      | 
Índices:
    "transfers_pkey" PRIMARY KEY, btree (id)
    "ix_transfers_amount" btree (amount)
    "ix_transfers_date" btree (date)
    "ix_transfers_fk_transfers_by_id" btree (by_id) WHERE by_id IS NOT NULL
    "ix_transfers_fk_transfers_chargeback_of_id" btree (chargeback_of_id)
WHERE chargeback_of_id IS NOT NULL
    "ix_transfers_fk_transfers_charged_back_by_id" btree
(charged_back_by_id) WHERE charged_back_by_id IS NOT NULL
    "ix_transfers_fk_transfers_from_id" btree (from_id)
    "ix_transfers_fk_transfers_parent_id" btree (parent_id) WHERE parent_id
IS NOT NULL
    "ix_transfers_fk_transfers_scheduled_payment_installment_id" btree
(scheduled_payment_installment_id) WHERE scheduled_payment_installment_id IS
NOT NULL
    "ix_transfers_fk_transfers_to_id" btree (to_id)
    "ix_transfers_fk_transfers_transaction_id" btree (transaction_id) WHERE
transaction_id IS NOT NULL
    "ix_transfers_fk_transfers_transfer_fee_id" btree (transfer_fee_id)
WHERE transfer_fee_id IS NOT NULL
    "ix_transfers_fk_transfers_type_id" btree (type_id)
    "ix_transfers_fk_transfers_user_account_fee_log_id" btree
(user_account_fee_log_id) WHERE user_account_fee_log_id IS NOT NULL
    "ix_transfers_transaction_number" btree
(lower(transaction_number::text)) WHERE transaction_number IS NOT NULL
Restrições de chave estrangeira:
    "fk_transfers_by_id" FOREIGN KEY (by_id) REFERENCES users(id)
    "fk_transfers_chargeback_of_id" FOREIGN KEY (chargeback_of_id)
REFERENCES transfers(id)
    "fk_transfers_charged_back_by_id" FOREIGN KEY (charged_back_by_id)
REFERENCES transfers(id)
    "fk_transfers_from_id" FOREIGN KEY (from_id) REFERENCES accounts(id)
    "fk_transfers_parent_id" FOREIGN KEY (parent_id) REFERENCES
transfers(id)
    "fk_transfers_scheduled_payment_installment_id" FOREIGN KEY
(scheduled_payment_installment_id) REFERENCES
scheduled_payment_installments(id)
    "fk_transfers_to_id" FOREIGN KEY (to_id) REFERENCES accounts(id)
    "fk_transfers_transaction_id" FOREIGN KEY (transaction_id) REFERENCES
transactions(id)
    "fk_transfers_transfer_fee_id" FOREIGN KEY (transfer_fee_id) REFERENCES
transfer_fees(id)
    "fk_transfers_type_id" FOREIGN KEY (type_id) REFERENCES
transfer_types(id)
    "fk_transfers_user_account_fee_log_id" FOREIGN KEY
(user_account_fee_log_id) REFERENCES user_account_fee_logs(id)
Referenciada por:
    TABLE "account_balances" CONSTRAINT "fk_account_balances_transfer_id"
FOREIGN KEY (transfer_id) REFERENCES transfers(id)
    TABLE "failed_payment_occurrences" CONSTRAINT
"fk_failed_payment_occurrences_transfer_id" FOREIGN KEY (transfer_id)
REFERENCES transfers(id)
    TABLE "transactions" CONSTRAINT
"fk_transactions_last_occurrence_success_id" FOREIGN KEY
(last_occurrence_success_id) REFERENCES transfers(id)
    TABLE "transactions" CONSTRAINT "fk_transactions_original_transfer_id"
FOREIGN KEY (original_transfer_id) REFERENCES transfers(id)
    TABLE "transfer_status_logs" CONSTRAINT
"fk_transfer_status_logs_transfer_id" FOREIGN KEY (transfer_id) REFERENCES
transfers(id)
    TABLE "transfers" CONSTRAINT "fk_transfers_chargeback_of_id" FOREIGN KEY
(chargeback_of_id) REFERENCES transfers(id)
    TABLE "transfers" CONSTRAINT "fk_transfers_charged_back_by_id" FOREIGN
KEY (charged_back_by_id) REFERENCES transfers(id)
    TABLE "transfers" CONSTRAINT "fk_transfers_parent_id" FOREIGN KEY
(parent_id) REFERENCES transfers(id)
    TABLE "transfers_transfer_status_flows" CONSTRAINT
"fk_transfers_transfer_status_flows_transfer_id" FOREIGN KEY (transfer_id)
REFERENCES transfers(id)





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux