[PERFORM] Massive insert vs heavy contention in LWLock:buffer_content
| От | luisfpg |
|---|---|
| Тема | [PERFORM] Massive insert vs heavy contention in LWLock:buffer_content |
| Дата | |
| Msg-id | 1509467408126-0.post@n3.nabble.com обсуждение исходный текст |
| Список | pgsql-performance |
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 |
charactervarying(31)
| amount | numeric
| não nuloauthorization_status | character varying(255)
| date | timestamp without time
zone | não nulodescription | 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 nulofeedback_id | bigint
| from_user_id | bigint
| next_authorization_level_id | bigint
| to_user_id | bigint
| type_id | bigint
| não nuloorder_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_idIS 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"
FOREIGNKEY
(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 nulodate | timestamp
withouttime zone | não nuloemission_date | timestamp without time zone | expiration_date
| timestamp without time zone | from_id | bigint | não nuloparent_id
| bigint | to_id | bigint |
nãonulotype_id | bigint | não nulocharged_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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
В списке pgsql-performance по дате отправления: