Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e9ZQg-0008Lc-QV for pgsql-performance@arkaria.postgresql.org; Tue, 31 Oct 2017 16:30:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e9ZQf-0006CW-SI for pgsql-performance@arkaria.postgresql.org; Tue, 31 Oct 2017 16:30:17 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e9ZQf-0006BO-3D for pgsql-performance@postgresql.org; Tue, 31 Oct 2017 16:30:17 +0000 Received: from n3.nabble.com ([162.255.23.22]) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e9ZQW-00080o-VI for pgsql-performance@postgresql.org; Tue, 31 Oct 2017 16:30:15 +0000 Received: from n3.nabble.com (localhost [127.0.0.1]) by n3.nabble.com (Postfix) with ESMTP id 2CD2F92FADA5 for ; Tue, 31 Oct 2017 09:30:08 -0700 (MST) Date: Tue, 31 Oct 2017 09:30:08 -0700 (MST) From: luisfpg To: pgsql-performance@postgresql.org Message-ID: <1509467408126-0.post@n3.nabble.com> Subject: Massive insert vs heavy contention in LWLock:buffer_content MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org 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=20=20= =20=20=20=20=20=20=20=20=20=20 | Modificadores=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------+-----------------------= ------+------------------------------------------------------------------- id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 | n=C3=A3o nulo valor padr=C3=A3o de nextval('transactions_id_seq'::regclas= s) subclass | character varying(31)= =20=20=20=20=20=20 |=20 amount | numeric=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 | n=C3=A3o nulo authorization_status | character varying(255)= =20=20=20=20=20 |=20 date | timestamp without time zone | n=C3=A3o nulo description | text=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 transaction_feedback_expiration_notified | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 transaction_feedback_expiration_reminder_notified | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 transaction_feedback_reminder_notified | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 by_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 channel_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 | n=C3=A3o nulo feedback_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 from_user_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 next_authorization_level_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 to_user_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 type_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 | n=C3=A3o nulo order_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 status | character varying(255)= =20=20=20=20=20 |=20 received | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 principal_type_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 access_client_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 original_transfer_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 show_to_receiver | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 expiration_date | timestamp without time zone |=20 scheduled | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 first_installment_immediate | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 installments_count | integer=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 process_date | timestamp without time zone |=20 comments | text=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 transaction_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 sms_code | character varying(255)= =20=20=20=20=20 |=20 external_principal_value | character varying(255)= =20=20=20=20=20 |=20 external_principal_type_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 received_by_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 from_name | character varying(255)= =20=20=20=20=20 |=20 to_name | character varying(255)= =20=20=20=20=20 |=20 next_occurrence_date | timestamp without time zone |=20 occurrences_count | integer=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 occurrence_interval_amount | integer=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 occurrence_interval_field | character varying(255)= =20=20=20=20=20 |=20 last_occurrence_failure_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 last_occurrence_success_id | bigint=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 by_self | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 from_system | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 to_system | boolean=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 ticket_number | character varying(255)= =20=20=20=20=20 |=20 cancel_url | character varying(255)= =20=20=20=20=20 |=20 success_url | character varying(255)= =20=20=20=20=20 |=20 transaction_number | character varying(255)= =20=20=20=20=20 |=20 expiration_date_comments | text=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 |=20 =C3=8Dndices: "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=C3=A7=C3=B5es 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 |=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 Modificadores=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20 ----------------------------------+-----------------------------+----------= ------------------------------------------------------ id | bigint | n=C3=A3o = nulo valor padr=C3=A3o de nextval('transfers_id_seq'::regclass) subclass | character varying(31) |=20 amount | numeric | n=C3=A3o = nulo date | timestamp without time zone | n=C3=A3o = nulo emission_date | timestamp without time zone |=20 expiration_date | timestamp without time zone |=20 from_id | bigint | n=C3=A3o = nulo parent_id | bigint |=20 to_id | bigint | n=C3=A3o = nulo type_id | bigint | n=C3=A3o = nulo charged_back_by_id | bigint |=20 user_account_fee_log_id | bigint |=20 chargeback_of_id | bigint |=20 transaction_id | bigint |=20 scheduled_payment_installment_id | bigint |=20 transfer_fee_id | bigint |=20 number | integer |=20 by_id | bigint |=20 transaction_number | character varying(255) |=20 =C3=8Dndices: "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=C3=A7=C3=B5es 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-f205008= 1.html --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance