Received: by 10.50.42.225 with SMTP id r1mr7313190igl.1.1350761421933;
Sat, 20 Oct 2012 12:30:21 -0700 (PDT)
X-BeenThere: codership-team@googlegroups.com
Received: by 10.50.0.208 with SMTP id 16ls6323600igg.1.canary; Sat, 20 Oct
2012 12:30:21 -0700 (PDT)
Received: by 10.50.213.99 with SMTP id nr3mr7311124igc.2.1350761421302;
Sat, 20 Oct 2012 12:30:21 -0700 (PDT)
Received: by 10.50.213.99 with SMTP id nr3mr7311123igc.2.1350761421288;
Sat, 20 Oct 2012 12:30:21 -0700 (PDT)
Return-Path: <henrik.i...@gmail.com>
Received: from mail-ie0-f169.google.com (mail-ie0-f169.google.com [209.85.223.169])
by gmr-mx.google.com with ESMTPS id uz16si1094586igb.3.2012.10.20.12.30.21
(version=TLSv1/SSLv3 cipher=OTHER);
Sat, 20 Oct 2012 12:30:21 -0700 (PDT)
Received-SPF: pass (google.com: domain of henrik.i...@gmail.com designates 209.85.223.169 as permitted sender) client-ip=209.85.223.169;
Authentication-Results: gmr-mx.google.com; spf=pass (google.com: domain of henrik.i...@gmail.com designates 209.85.223.169 as permitted sender) smtp.mail=henrik.i...@gmail.com; dkim=pass header...@gmail.com
Received: by mail-ie0-f169.google.com with SMTP id 10so2713584ied.0
for <codership-team@googlegroups.com>; Sat, 20 Oct 2012 12:30:21 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s=20120113;
h=mime-version:reply-to:sender:in-reply-to:references:date
:x-google-sender-auth:message-id:subject:from:to:cc:content-type;
bh=3BB+0qlNqVV0oOmlGKiOWzwAsq7Y2pVaV+xkwd8qgrE=;
b=JKj6rMy40nnyWNn3RuimFGyGf+lDqLJ2DxeOrG+QeGkB8Xr7tV4IABvASL7hCqLE+U
Kvkrt55YGN0d8mpYNa5W3cWH1nCjHlx3uAAyKPsTsgnz8S7b0Ez9p/RRFDl/fJ/HBkMq
BRy7ZMF3zceN9IS7QQxvE7PPj5mURgxOmec7KXG5hHJduoYXnm87GhK9BGjGHygpXYOk
uUrcSidegtQqbgtG25hSzBny90HtV3gAivwjNFKGoVYLdQoFpXQtM6fgc+GIWXWEHo8C
sR1Rw3B7Di6fthoEyIWO7LJ/WEMjhJbxT1pfLt0M6i73HkE6TA9NrXSWAMbe0Z55/LiX
oz3A==
MIME-Version: 1.0
Received: by 10.50.37.168 with SMTP id z8mr12338678igj.1.1350761421135; Sat,
20 Oct 2012 12:30:21 -0700 (PDT)
Reply-To: henrik.i...@avoinelama.fi
Sender: henrik.i...@gmail.com
Received: by 10.64.48.204 with HTTP; Sat, 20 Oct 2012 12:30:21 -0700 (PDT)
In-Reply-To: <d085fa5e-cf1b-43e8-980f-5b1f27e1442f@googlegroups.com>
References: <d085fa5e-cf1b-43e8-980f-5b1f27e1442f@googlegroups.com>
Date: Sat, 20 Oct 2012 22:30:21 +0300
Message-ID: <CAKHykesj_r3k1N7fD91RxpNPNLj6jaSfvbNx7q_nJNVSvn6...@mail.gmail.com>
Subject: Re: [codership-team] Delete_rows_log_event::find_row() on node2
preventing writes on node1
From: Henrik Ingo <henrik.i...@avoinelama.fi>
To: Freejack <freej...@gmail.com>
Cc: codership-team@googlegroups.com
Content-Type: text/plain; charset=ISO-8859-1
Actually, both Mikkel and Jay have asked about similar issue. Based on those:
Does the table in question have a primary key?
How many rows will the query delete? Is there any way to delete in
smaller chunks btw?
Jay had the theory that keeping wsrep_slave_threads=1 will fix such
issues, but you seem to have it at =1 and experience it nevertheless.
henrik
On Sat, Oct 20, 2012 at 7:04 PM, Freejack <freej...@gmail.com> wrote:
> Hi all. Just deployed a second galera cluster to our production environment
> and we're seeing an issue where replicating a large delete query to the
> secondary node will prevent writes on the first node. node2 seems to be
> stuck running Delete_rows_log_event::find_row(137577) while the write
> queries are piling up on node1 to the point where all connections are used
> up and the db is no longer accessible.
>
> Based on another similar thread, this may possibly be due to my innodb
> settings although I can't rule out galera at this point. Please see below.
> Any suggestions/tips would be hugely appreciated! Thanks!
>
> +-------------------------------------------+------------------------+
> | Variable_name | Value |
> +-------------------------------------------+------------------------+
> | have_innodb | YES |
> | ignore_builtin_innodb | OFF |
> | innodb_adaptive_flushing | ON |
> | innodb_adaptive_flushing_method | estimate |
> | innodb_adaptive_hash_index | ON |
> | innodb_adaptive_hash_index_partitions | 1 |
> | innodb_additional_mem_pool_size | 8388608 |
> | innodb_autoextend_increment | 8 |
> | innodb_autoinc_lock_mode | 2 |
> | innodb_blocking_buffer_pool_restore | OFF |
> | innodb_buffer_pool_instances | 1 |
> | innodb_buffer_pool_restore_at_startup | 0 |
> | innodb_buffer_pool_shm_checksum | ON |
> | innodb_buffer_pool_shm_key | 0 |
> | innodb_buffer_pool_size | 134217728 |
> | innodb_change_buffering | all |
> | innodb_checkpoint_age_target | 0 |
> | innodb_checksums | ON |
> | innodb_commit_concurrency | 0 |
> | innodb_concurrency_tickets | 500 |
> | innodb_corrupt_table_action | assert |
> | innodb_data_file_path | ibdata1:10M:autoextend |
> | innodb_data_home_dir | |
> | innodb_dict_size_limit | 0 |
> | innodb_disallow_writes | OFF |
> | innodb_doublewrite | ON |
> | innodb_doublewrite_file | |
> | innodb_fake_changes | OFF |
> | innodb_fast_checksum | OFF |
> | innodb_fast_shutdown | 1 |
> | innodb_file_format | Barracuda |
> | innodb_file_format_check | ON |
> | innodb_file_format_max | Barracuda |
> | innodb_file_per_table | ON |
> | innodb_flush_log_at_trx_commit | 1 |
> | innodb_flush_method | |
> | innodb_flush_neighbor_pages | area |
> | innodb_force_load_corrupted | OFF |
> | innodb_force_recovery | 0 |
> | innodb_ibuf_accel_rate | 100 |
> | innodb_ibuf_active_contract | 1 |
> | innodb_ibuf_max_size | 67092480 |
> | innodb_import_table_from_xtrabackup | 0 |
> | innodb_io_capacity | 5000 |
> | innodb_kill_idle_transaction | 0 |
> | innodb_large_prefix | OFF |
> | innodb_lazy_drop_table | 0 |
> | innodb_lock_wait_timeout | 50 |
> | innodb_locks_unsafe_for_binlog | ON |
> | innodb_log_block_size | 512 |
> | innodb_log_buffer_size | 8388608 |
> | innodb_log_file_size | 5242880 |
> | innodb_log_files_in_group | 2 |
> | innodb_log_group_home_dir | ./ |
> | innodb_max_dirty_pages_pct | 75 |
> | innodb_max_purge_lag | 0 |
> | innodb_merge_sort_block_size | 1048576 |
> | innodb_mirrored_log_groups | 1 |
> | innodb_old_blocks_pct | 37 |
> | innodb_old_blocks_time | 0 |
> | innodb_open_files | 300 |
> | innodb_page_size | 16384 |
> | innodb_purge_batch_size | 20 |
> | innodb_purge_threads | 1 |
> | innodb_random_read_ahead | OFF |
> | innodb_read_ahead | linear |
> | innodb_read_ahead_threshold | 56 |
> | innodb_read_io_threads | 64 |
> | innodb_recovery_stats | OFF |
> | innodb_recovery_update_relay_log | OFF |
> | innodb_replication_delay | 0 |
> | innodb_rollback_on_timeout | OFF |
> | innodb_rollback_segments | 128 |
> | innodb_show_locks_held | 10 |
> | innodb_show_verbose_locks | 0 |
> | innodb_spin_wait_delay | 6 |
> | innodb_stats_auto_update | 1 |
> | innodb_stats_method | nulls_equal |
> | innodb_stats_on_metadata | ON |
> | innodb_stats_sample_pages | 8 |
> | innodb_stats_update_need_lock | 1 |
> | innodb_strict_mode | OFF |
> | innodb_support_xa | ON |
> | innodb_sync_spin_loops | 30 |
> | innodb_table_locks | ON |
> | innodb_thread_concurrency | 0 |
> | innodb_thread_concurrency_timer_based | OFF |
> | innodb_thread_sleep_delay | 10000 |
> | innodb_use_global_flush_log_at_trx_commit | ON |
> | innodb_use_native_aio | ON |
> | innodb_use_sys_malloc | ON |
> | innodb_use_sys_stats_table | OFF |
> | innodb_version | 1.1.8-rel28.1 |
> | innodb_write_io_threads | 64 |
> +-------------------------------------------+------------------------+
>
> --
>
>
--
henrik.i...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc
My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559
Message from discussion Delete_rows_log_event::find_ro w() on node2 preventing writes on node1
| Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy |
| ©2013 Google |