ApsaraDB for POLARDB MySQL 8.0 互換版その 2 です。
今回は、バッファプールまわりの挙動を中心に確かめてみました。
前回の記事 : Alibaba Cloud の POLARDB を試してみる（１）MySQL 8.0 互換版起動編
※当初、PostgreSQL 11 互換版も試そうと思っていたのですが、長くなりそうだったので先送りしました。
確認する内容
- プライマリノードで
INSERTした直後、読み取り専用ノードのバッファプールにINSERTしたデータが反映されるか？ - ノード再起動後に当該ノードのバッファプール上のデータは残っているか？
- Switch Primary Node 実行時に各ノードのバッファプール上のデータは残っているか？
- プライマリノードで
UPDATE実行後、読み取り専用ノードのバッファプールに更新が反映されるか？
**結果だけ先に見たい方はこちら**
- 反映されない（Aurora と同じ）
- 消える（Aurora とは異なる）
- 新プライマリノードは昇格前のバッファプールが引き継がれ、降格した旧プライマリノードは消える（Aurora と同じ）
- 反映される（Aurora と同じ）
エンドポイントについて
バッファプールまわりの確認の前に、POLARDB MySQL 8.0 互換版の各種エンドポイントについて簡単に触れておきます。
前回の記事からリンクしている、
にある通り、POLARDB MySQL 互換版には、以下のようなものがあります。
- プライマリノードに接続するエンドポイント（プライマリエンドポイント）
- VPC 接続用がデフォルトで用意される。Public なエンドポイントはオプション
- プロキシ（PolarProxy）経由で各ノードに接続するクラスタエンドポイント
- デフォルトで読み書き可能クラスタエンドポイントが 1 つ用意される。プライマリ同様、Public なエンドポイントはオプション
- デフォルト以外にもカスタムクラスタエンドポイントを追加することが可能。読み取り専用クラスタエンドポイントも選択できる
- 読み書き可能クラスタエンドポイントでは、都度振り分け（結果整合性）とセッション一貫性振り分けの選択が可能（デフォルトは後者）。更新系 SQL が実行されるとプライマリノードに送られ、参照系 SQL はその他のノードも含めて振り分けられる
- 読み取り専用クラスタエンドポイントでは、都度振り分けのみ。設定時の振り分け対象にプライマリノードが含まれている場合はプライマリノードにも振り分けられる
Aurora とは違い、個別ノードに接続するためのエンドポイントは提供されないようです。
今回は、
- プライマリエンドポイント（polarprimary）
- デフォルト（読み書き可能）クラスタエンドポイント（polardefault）
- 読み取り専用クラスタエンドポイント（polarreadonly）
の 3 つのエンドポイントを用意して動作を確認しました。
**エンドポイント設定**
  各エンドポイントのIPアドレス
[root@polartest ~]# ping polarprimary.mysql.polardb.rds.aliyuncs.com
PING polarprimary.mysql.polardb.rds.aliyuncs.com (10.0.0.200) 56(84) bytes of data.
64 bytes from 10.0.0.200 (10.0.0.200): icmp_seq=1 ttl=102 time=0.097 ms
64 bytes from 10.0.0.200 (10.0.0.200): icmp_seq=2 ttl=102 time=0.105 ms
64 bytes from 10.0.0.200 (10.0.0.200): icmp_seq=3 ttl=102 time=0.099 ms
64 bytes from 10.0.0.200 (10.0.0.200): icmp_seq=4 ttl=102 time=0.098 ms
^C
--- polarprimary.mysql.polardb.rds.aliyuncs.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3000ms
rtt min/avg/max/mdev = 0.097/0.099/0.105/0.012 ms
[root@polartest ~]# ping polardefault.rwlb.rds.aliyuncs.com
PING polardefault.rwlb.rds.aliyuncs.com (10.0.0.201) 56(84) bytes of data.
64 bytes from 10.0.0.201 (10.0.0.201): icmp_seq=1 ttl=102 time=0.103 ms
64 bytes from 10.0.0.201 (10.0.0.201): icmp_seq=2 ttl=102 time=0.118 ms
64 bytes from 10.0.0.201 (10.0.0.201): icmp_seq=3 ttl=102 time=0.113 ms
64 bytes from 10.0.0.201 (10.0.0.201): icmp_seq=4 ttl=102 time=0.105 ms
^C
--- polardefault.rwlb.rds.aliyuncs.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3000ms
rtt min/avg/max/mdev = 0.103/0.109/0.118/0.014 ms
[root@polartest ~]# ping polarreadonly.rwlb.rds.aliyuncs.com
PING polarreadonly.rwlb.rds.aliyuncs.com (10.0.0.202) 56(84) bytes of data.
64 bytes from 10.0.0.202 (10.0.0.202): icmp_seq=1 ttl=102 time=0.161 ms
64 bytes from 10.0.0.202 (10.0.0.202): icmp_seq=2 ttl=102 time=0.132 ms
64 bytes from 10.0.0.202 (10.0.0.202): icmp_seq=3 ttl=102 time=0.118 ms
64 bytes from 10.0.0.202 (10.0.0.202): icmp_seq=4 ttl=102 time=0.118 ms
^C
--- polarreadonly.rwlb.rds.aliyuncs.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3000ms
rtt min/avg/max/mdev = 0.118/0.132/0.161/0.019 ms
サーバ変数について
動作確認の前にサーバ変数を確認してみました。
プライマリエンドポイントで実行したSHOW VARIABLESの結果を記します。
**SHOW VARIABLES の結果（プライマリ）**
サーバ変数
mysql> SHOW VARIABLES;
+----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| activate_all_roles_on_login | OFF |
| add_filter_hint_to_super_user_command | OFF |
| auto_generate_certs | ON |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| avoid_temporal_upgrade | OFF |
| back_log | 3000 |
| basedir | /u01/polardb80_current/ |
| big_tables | OFF |
| bind_address | 0.0.0.0 |
| binlog_cache_size | 2097152 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_seconds | 1209600 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_io_cache_size | 16777216 |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | OFF |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| block_encryption_mode | aes-128-ecb |
| bulk_insert_buffer_size | 8388608 |
| caching_sha2_password_auto_generate_rsa_keys | ON |
| caching_sha2_password_private_key_path | private_key.pem |
| caching_sha2_password_public_key_path | public_key.pem |
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /u01/polardb80_current/share/charsets/ |
| check_proxy_users | OFF |
| client_endpoint_ip | 10.0.0.201 |
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
| completion_type | NO_CHAIN |
| concurrent_insert | AUTO |
| connect_timeout | 10 |
| core_file | ON |
| cost_threshold_for_parallelism | 50000 |
| cte_max_recursion_depth | 1000 |
| datadir | /674365-1/home/mysql/data/dbs/ |
| default_authentication_plugin | mysql_native_password |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
| default_password_lifetime | 0 |
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| disabled_storage_engines | |
| disconnect_on_expired_password | ON |
| div_precision_increment | 4 |
| end_markers_in_json | OFF |
| enforce_gtid_consistency | ON |
| eq_range_index_dive_limit | 10 |
| error_count | 0 |
| event_scheduler | ON |
| expire_logs_days | 0 |
| explicit_defaults_for_timestamp | OFF |
| external_user | |
| fix_control | oby_limit_optimize=on |
| flush | OFF |
| flush_time | 0 |
| force_maximum_parallel_degree | OFF |
| force_parallel_mode | OFF |
| foreign_key_checks | ON |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | OFF |
| general_log_file | /home/mysql/log/mysql/general.log |
| group_concat_max_len | 1024 |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| have_compress | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | YES |
| have_profiling | YES |
| have_query_cache | NO |
| have_rtree_keys | YES |
| have_ssl | YES |
| have_statement_timeout | YES |
| have_symlink | DISABLED |
| histogram_generation_max_mem_size | 20000000 |
| host_cache_size | 128 |
| hostname | i41e01113.cloud.et135 |
| identity | 0 |
| implicit_primary_key | ON |
| information_schema_stats_expiry | 86400 |
| init_connect | |
| init_file | |
| init_slave | |
| inner_schema_list | |
| inner_user_list | |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 12884901888 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | none |
| innodb_checksum_algorithm | crc32 |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compact_allocation | OFF |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:200M:autoextend |
| innodb_data_home_dir | /674365-1/home/mysql/data/log |
| innodb_deadlock_detect | ON |
| innodb_dedicated_server | OFF |
| innodb_default_row_format | dynamic |
| innodb_directories | |
| innodb_disable_sort_file_cache | ON |
| innodb_doublewrite | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | ALL_O_DIRECT |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_fsync_threshold | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 4000 |
| innodb_io_capacity_max | 8000 |
| innodb_lock_sys_rec_partition | 64 |
| innodb_lock_sys_table_partition | 64 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 33554432 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | OFF |
| innodb_log_file_size | 1073741824 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /674365-1/home/mysql/data/log |
| innodb_log_optimize_ddl | OFF |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 4096 |
| innodb_lru_scan_depth | 2048 |
| innodb_max_dirty_pages_pct | 60.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_lock_nowait | ON |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 3000 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 8 |
| innodb_page_size | 16384 |
| innodb_parallel_scan_estimate_max_pages | 128 |
| innodb_polar_auto_arrange | 0 |
| innodb_polar_checkpoint_on_new_replica | ON |
| innodb_polar_copy_page | ON |
| innodb_polar_copy_page_pool_size | 134217728 |
| innodb_polar_empty_page_or_log_check | OFF |
| innodb_polar_fil_extend_batch | 100 |
| innodb_polar_fil_extend_delay | 0 |
| innodb_polar_inactive_slave_clear_all | OFF |
| innodb_polar_inactive_slave_clear_by_id | 0 |
| innodb_polar_io_latency_warning | 300000 |
| innodb_polar_io_merge_enabled | ON |
| innodb_polar_keep_inactive_replica | ON |
| innodb_polar_keep_inactive_standby | ON |
| innodb_polar_kick_replica_max_delay | 18446744073709551614 |
| innodb_polar_log_auto_purge | OFF |
| innodb_polar_log_file_extend_method | fallocate |
| innodb_polar_log_file_max_reuse | 8 |
| innodb_polar_log_force_parse | OFF |
| innodb_polar_log_max_checkpoint_files | 2 |
| innodb_polar_log_max_reserved_files_no_slave | 1 |
| innodb_polar_log_wait_delay | 100 |
| innodb_polar_log_wait_loops | 10 |
| innodb_polar_min_log_files_keep | 1 |
| innodb_polar_notify_reader_after_sync | OFF |
| innodb_polar_notify_replica_after_sync | OFF |
| innodb_polar_purge_lsn_point_in_time_recovery | 0 |
| innodb_polar_purge_sys_delay_ms | 1000 |
| innodb_polar_repl_info_sync | OFF |
| innodb_polar_restore_old_version | OFF |
| innodb_primary_abort_ddl_wait_replica_timeout | 3600 |
| innodb_primary_accept_reconnect_max_log_delay | 107374182400 |
| innodb_primary_degrade_timeout | 5 |
| innodb_primary_dml_max_delay_microsec | 10000 |
| innodb_primary_flush_max_lsn_lag | 1509949440 |
| innodb_primary_log_write_before_erase_trx | OFF |
| innodb_primary_purge_max_id_lag | 18446744073709551614 |
| innodb_primary_purge_max_lsn_lag | 18446744073709551614 |
| innodb_primary_sync_no_slave | OFF |
| innodb_primary_sync_slave | no_wait |
| innodb_primary_sync_slave_timeout | 10000 |
| innodb_print_all_deadlocks | OFF |
| innodb_print_ddl_logs | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_rds_buffer_pool_file_del | OFF |
| innodb_rds_cleaner_max_lru_time | 1000 |
| innodb_rds_page_cleaner_adaptive_sleep | ON |
| innodb_read_ahead_threshold | 0 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_read_view_max_recorded_num | 512 |
| innodb_redo_log_encrypt | OFF |
| innodb_replica_log_addr_heap_max_size | 67108864 |
| innodb_replica_log_max_lag | 0 |
| innodb_replica_log_parse_buf_chunk_over_allocate | 2 |
| innodb_replica_log_parse_buf_size | 1677721600 |
| innodb_replica_promote_wait_log_done | OFF |
| innodb_replica_retry_page_read_times | 5 |
| innodb_replica_retry_read_wait | 100 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_slave_crash_on_hang | ON |
| innodb_slave_log_apply_batch_size | 16777216 |
| innodb_slave_log_apply_worker | 8 |
| innodb_slave_log_block_do_check | ON |
| innodb_slave_log_hash_per_worker | 16 |
| innodb_slave_log_parse_buf_chunk_size | 67108864 |
| innodb_slave_log_read_buf_count | 4 |
| innodb_slave_log_store_ack_point | after_write |
| innodb_slave_loose_view_check | disable |
| innodb_slave_max_cached_dummy_index | 64 |
| innodb_slave_parallel_apply_low_addrs | 16 |
| innodb_slave_purge_defer_timeout | 3600000 |
| innodb_slave_purge_method | 2 |
| innodb_slave_reset_table_space_recv_state | OFF |
| innodb_slave_show_correct_autoinc | OFF |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_standby_force_upgrade | OFF |
| innodb_standby_full_apply_startup | OFF |
| innodb_standby_remove_prealloc_log | OFF |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | OFF |
| innodb_sync_array_size | 16 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | /home/mysql/log/tmp/ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | /home/mysql/log/tmp |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | /674365-1/home/mysql/data/log |
| innodb_transaction_id | 0 |
| innodb_undo_directory | /674365-1/home/mysql/data/log |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | OFF |
| innodb_undo_tablespaces | 8 |
| innodb_use_native_aio | OFF |
| innodb_version | 8.0.13 |
| innodb_write_io_threads | 4 |
| insert_id | 0 |
| interactive_timeout | 7200 |
| internal_tmp_disk_storage_engine | InnoDB |
| internal_tmp_mem_storage_engine | MEMORY |
| join_buffer_size | 524288 |
| keep_files_on_create | OFF |
| key_buffer_size | 8388608 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| keyring_operations | ON |
| kill_idle_transaction_timeout | 0 |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| last_insert_id | 0 |
| lc_messages | en_US |
| lc_messages_dir | /u01/polardb80_current/share/ |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| lock_instance_mode | LOCK_NON |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| log_bin | OFF |
| log_bin_basename | ../log/mysql-bin |
| log_bin_index | /674365-1/home/mysql/data/log/master-log-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | ON |
| log_error | /home/mysql/log/mysql/master-error.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 3 |
| log_output | TABLE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | ON |
| log_slow_admin_statements | ON |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | SYSTEM |
| long_query_time | 1.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
| maintain_max_connections | 0 |
| maintain_user_list | root,aurora,replicator |
| mandatory_roles | |
| master_info_repository | FILE |
| master_verify_checksum | OFF |
| max_allowed_packet | 1073741824 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 524288000 |
| max_binlog_stmt_cache_size | 18446744073709498368 |
| max_connect_errors | 100 |
| max_connections | 5512 |
| max_delayed_threads | 20 |
| max_digest_length | 1024 |
| max_error_count | 64 |
| max_execution_time | 0 |
| max_heap_table_size | 67108864 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_parallel_degree | 0 |
| max_parallel_workers | 16 |
| max_points_in_geometry | 65536 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709500000 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_user_connections | 5512 |
| max_write_lock_count | 102400 |
| min_examined_row_limit | 0 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| mysql_native_password_proxy_users | OFF |
| mysqlx_bind_address | * |
| mysqlx_connect_timeout | 30 |
| mysqlx_document_id_unique_prefix | 0 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_max_allowed_packet | 67108864 |
| mysqlx_max_connections | 100 |
| mysqlx_min_worker_threads | 2 |
| mysqlx_port | 33060 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_socket | /tmp/mysqlx.sock |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_buffer_length | 16384 |
| net_compression_level | 6 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| ngram_token_size | 2 |
| offline_mode | OFF |
| old | OFF |
| old_alter_table | OFF |
| open_files_limit | 655350 |
| opt_enable_rds_priv_strategy | ON |
| opt_indexstat | ON |
| opt_readonly_trans_implicit_commit | OFF |
| opt_tablestat | ON |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,force_parallel_group_with_merge_sort=off |
| optimizer_trace | enabled=off,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
| original_commit_timestamp | 36028797018963968 |
| parallel_partition_factor | 100 |
| parser_max_mem_size | 18446744073709551615 |
| password_history | 0 |
| password_require_current | OFF |
| password_reuse_interval | 0 |
| performance_point_dbug_enabled | OFF |
| performance_point_enabled | ON |
| performance_point_iostat_interval | 2 |
| performance_point_iostat_volume_size | 10000 |
| performance_point_lock_rwlock_enabled | ON |
| performance_schema | OFF |
| performance_schema_accounts_size | 0 |
| performance_schema_digests_size | 0 |
| performance_schema_error_size | 4577 |
| performance_schema_events_parallel_query_history_size | 0 |
| performance_schema_events_stages_history_long_size | 0 |
| performance_schema_events_stages_history_size | 0 |
| performance_schema_events_statements_history_long_size | 0 |
| performance_schema_events_statements_history_size | 0 |
| performance_schema_events_transactions_history_long_size | 0 |
| performance_schema_events_transactions_history_size | 0 |
| performance_schema_events_waits_history_long_size | 0 |
| performance_schema_events_waits_history_size | 0 |
| performance_schema_hosts_size | 0 |
| performance_schema_max_cond_classes | 0 |
| performance_schema_max_cond_instances | 0 |
| performance_schema_max_digest_length | 0 |
| performance_schema_max_digest_sample_age | 60 |
| performance_schema_max_file_classes | 0 |
| performance_schema_max_file_handles | 0 |
| performance_schema_max_file_instances | 0 |
| performance_schema_max_index_stat | 0 |
| performance_schema_max_memory_classes | 0 |
| performance_schema_max_metadata_locks | 0 |
| performance_schema_max_mutex_classes | 0 |
| performance_schema_max_mutex_instances | 0 |
| performance_schema_max_parallel_operator_objects | 1024 |
| performance_schema_max_parallel_query_classes | 3 |
| performance_schema_max_parallel_query_objects | 4096 |
| performance_schema_max_prepared_statements_instances | 0 |
| performance_schema_max_program_instances | 0 |
| performance_schema_max_rwlock_classes | 0 |
| performance_schema_max_rwlock_instances | 0 |
| performance_schema_max_socket_classes | 0 |
| performance_schema_max_socket_instances | 0 |
| performance_schema_max_sql_text_length | 0 |
| performance_schema_max_stage_classes | 0 |
| performance_schema_max_statement_classes | 0 |
| performance_schema_max_statement_stack | 0 |
| performance_schema_max_table_handles | 0 |
| performance_schema_max_table_instances | 0 |
| performance_schema_max_table_lock_stat | 0 |
| performance_schema_max_thread_classes | 0 |
| performance_schema_max_thread_instances | 0 |
| performance_schema_session_connect_attrs_size | 0 |
| performance_schema_setup_actors_size | 0 |
| performance_schema_setup_objects_size | 0 |
| performance_schema_users_size | 0 |
| persisted_globals_load | ON |
| pid_file | /home/mysql/log/tmp/mysql.pid |
| plugin_dir | /u01/polardb80_current/lib/plugin/ |
| polar_binlog_packet_size | 1048576 |
| polar_compressed_protocol | OFF |
| polar_csv_log_table_basedir | /home/mysql/log/mysql |
| polar_dump_binlog | OFF |
| polar_enable_replica | OFF |
| polar_enforce_storage_engine | INNODB |
| polar_io_thread_max_wait_time | 86400 |
| polar_log_bin | OFF |
| polar_log_packet_size | 1048576 |
| polar_max_slaves | 128 |
| polar_node_basedir | /home/mysql/log/mysql |
| polar_persisted_system_basedir | /home/mysql/log/mysql |
| polar_reconnect_count | 8640 |
| polar_reconnect_sleep_time | 10000 |
| polar_recover_ignore_fail | OFF |
| polar_sock_recv_buf_size | 0 |
| polar_sock_send_buf_size | 0 |
| polar_temp_table_or_file_pbdname | 674365-1 |
| polarfs_host_id | 1 |
| polarfs_pangu_prefix | |
| polarfs_temp_pangu_prefix | |
| port | 3020 |
| preload_buffer_size | 32768 |
| primary_degrade_binlog_dump_timeout | 10 |
| primary_fast_lookup | ON |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| proxy_user | |
| pseudo_slave_mode | OFF |
| pseudo_thread_id | 16787194 |
| query_alloc_block_size | 8192 |
| query_memory_hard_limit | 18446744073709551615 |
| query_memory_soft_limit | 3436183552 |
| query_prealloc_size | 8192 |
| rand_seed1 | 0 |
| rand_seed2 | 0 |
| range_alloc_block_size | 4096 |
| range_optimizer_max_mem_size | 8388608 |
| rbr_exec_mode | STRICT |
| rds_audit_log_buffer_size | 167772160 |
| rds_audit_log_connection_policy | ALL |
| rds_audit_log_dir | |
| rds_audit_log_enabled | ON |
| rds_audit_log_event_buffer_size | 2048 |
| rds_audit_log_flush | OFF |
| rds_audit_log_format | PLAIN |
| rds_audit_log_policy | ALL |
| rds_audit_log_row_limit | 200000 |
| rds_audit_log_skip | OFF |
| rds_audit_log_statement_policy | ALL |
| rds_audit_log_strategy | ASYNCHRONOUS |
| rds_audit_log_version | MYSQL_V1 |
| rds_kill_connections | 20 |
| rds_kill_user_list | |
| rds_push_lsn_default_interval | 10 |
| rds_release_date | 20191104 |
| rds_result_skip_counter | 0 |
| rds_set_connection_id_enabled | ON |
| rds_version | 13 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| records_threshold_for_parallelism | 10000 |
| regexp_stack_limit | 8000000 |
| regexp_time_limit | 32 |
| relay_log | /home/mysql/log/mysql/slave-relay.log |
| relay_log_basename | /home/mysql/log/mysql/slave-relay |
| relay_log_index | /home/mysql/log/mysql/slave-relay-log.index |
| relay_log_info_file | /home/mysql/log/mysql/slave-relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 21474836480 |
| replica_lock_wait_timeout | 50 |
| report_host | |
| report_password | |
| report_port | 3020 |
| report_user | |
| require_secure_transport | OFF |
| resultset_metadata | FULL |
| rotate_log_table | OFF |
| rotate_log_table_last_name | |
| rpl_read_size | 8192 |
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
| schema_definition_cache | 256 |
| secure_file_priv | NULL |
| server_id | 10134283 |
| server_id_bits | 32 |
| server_uuid | efd98935-0d91-11ea-bb45-506b4b1c274a |
| session_track_gtids | OFF |
| session_track_lsn_change | ON |
| session_track_schema | ON |
| session_track_state_change | OFF |
| session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection |
| session_track_transaction_info | STATE |
| sha256_password_auto_generate_rsa_keys | ON |
| sha256_password_private_key_path | private_key.pem |
| sha256_password_proxy_users | OFF |
| sha256_password_public_key_path | public_key.pem |
| show_create_table_verbosity | OFF |
| show_ipk_info | OFF |
| show_old_temporals | OFF |
| skip_external_locking | ON |
| skip_name_resolve | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_io_thread_max_wait_time | 86400 |
| slave_load_tmpdir | /home/mysql/log/tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_max_expire_on_master_timeout | 0 |
| slave_net_timeout | 60 |
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 8 |
| slave_pending_jobs_size_max | 167772160 |
| slave_preserve_commit_order | OFF |
| slave_rows_search_algorithms | INDEX_SCAN,HASH_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_trans_sync_level | 0 |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /home/mysql/log/mysql/slow_query.log |
| socket | /home/mysql/log/tmp/mysql.sock |
| sort_buffer_size | 1048576 |
| sql_auto_is_null | OFF |
| sql_big_selects | ON |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_mode | |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_require_primary_key | OFF |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | 0 |
| sql_warnings | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
| stored_program_cache | 256 |
| stored_program_definition_cache | 256 |
| super_read_only | OFF |
| sync_binlog | 1 |
| sync_master_info | 10000 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
| system_time_zone | CST |
| table_definition_cache | 16384 |
| table_open_cache | 16384 |
| table_open_cache_instances | 32 |
| tablespace_definition_cache | 256 |
| temptable_max_ram | 1073741824 |
| thread_cache_size | 256 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
| time_zone | SYSTEM |
| timestamp | 1574475273.117658 |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| tmp_table_size | 2097152 |
| tmpdir | /home/mysql/log/tmp |
| transaction_alloc_block_size | 8192 |
| transaction_allow_batching | OFF |
| transaction_isolation | READ-COMMITTED |
| transaction_prealloc_size | 4096 |
| transaction_read_only | OFF |
| transaction_write_set_extraction | XXHASH64 |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| use_secondary_engine | ON |
| version | 8.0.13 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.11 |
| wait_timeout | 86400 |
| warning_count | 0 |
| windowing_use_high_precision | ON |
+----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
717 rows in set (0.01 sec)
- バッファプールのサイズはメモリ 16GB に対して 12GB 程度なので MySQL としては常識的な設定値
- ざっと見た感じで以下のような変数が追加されている（一部は ApsaraDB RDS for MySQL にも存在する模様）
force_maximum_parallel_degreeforce_parallel_mode-
innodb_polar_で始まるもの -
innodb_primary_で始まるもの -
innodb_rds_で始まるもの -
innodb_replica_で始まるもの -
innodb_slave_で始まるもの max_parallel_degreeparallel_partition_factorperformance_point-
polar_で始まるもの -
polarfs_で始まるもの primary_degrade_binlog_dump_timeoutprimary_fast_lookup-
rds_で始まるもの
各クラスタエンドポイントからの接続先について
読み書き可能クラスタエンドポイントからの接続は、プライマリノード・読み取り専用ノードのそれぞれに動的に振り分けられるようです。
途中で更新系の SQL が実行された場合、その時点からプライマリノードに接続されるようです（先のリンク先に書かれている通り、設定に合わせた整合性確認が行われる模様）。
読み取り専用クラスタエンドポイントからの接続は、プライマリ以外の読み取り専用ノードに優先的に振り分けられるようです。
※SHOW VARIABLES LIKE 'innodb_read_only'で確認。hostname・server_id・server_uuidで接続先ノードを確認することも可能。
バッファプール関連の挙動
簡単なテーブルを用意し、データを 100 万行程度INSERTしておき、各種操作の後に全行読み込みの時間を確認することで、
- メモリ（バッファプール）からの読み込み
- ディスク（ストレージノード）からの読み込み
のどちらが行われたのかを確認（推測）しました。
**テーブル定義（プライマリノードで実行）**
テーブル定義
mysql> CREATE DATABASE bptest;
Query OK, 1 row affected (0.01 sec)
mysql> USE bptest;
Database changed
mysql> CREATE TABLE bptest (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dummy_str1 VARCHAR(512) NOT NULL, val INT NOT NULL);
Query OK, 0 rows affected (0.57 sec)
**バッファプールにデータが載っているときの SELECT 実行時間**
バッファプールにデータが載っているときのSELECT実行時間
mysql> SELECT SUM(val) FROM bptest;
+----------+
| SUM(val) |
+----------+
| 1000000 |
+----------+
1 row in set (0.32 sec)
プライマリノードでINSERTした直後の読み取り専用ノード
ディスクからの読み込みになりました。当然と言えば当然です。
**プライマリノードで`INSERT`した直後の読み取り専用ノードで`SELECT`実行**
読み取り専用ノードでSELECT実行
mysql> SELECT SUM(val) FROM bptest;
+----------+
| SUM(val) |
+----------+
| 1000000 |
+----------+
1 row in set (12.03 sec)
ノード再起動後
ディスクからの読み込みとなりました。Aurora のような「DB を再起動してもバッファプールが維持される仕組み」は持たないようです。
なお、Aurora とは違い、ノードを再起動しても Primary / Read-only の切り替えは発生しないようです。
**プライマリノードで再起動後に`SELECT`実行**
プライマリノードでSELECT実行
mysql> SELECT SUM(val) FROM bptest;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> SELECT SUM(val) FROM bptest;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 96
Current database: bptest
+----------+
| SUM(val) |
+----------+
| 1000000 |
+----------+
1 row in set (11.07 sec)
Switch Primary Node 実行時
両ノードでSELECTを実行しバッファプールにデータが載っている状態で Switch Primary Node しました。
プライマリノードに昇格した側はバッファプールからの読み込みとなりました。
プライマリノードから降格した側はバッファプールが消えてディスクからの読み込みとなりました。
**Switch Primary Node**
  ※途中でクラスタを再作成したため、先のキャプチャとはエンドポイントやノードの ID が異なっています。**Switch Primary Node 後にプライマリノードで`SELECT`実行**
プライマリノードでSELECT実行
mysql> SELECT SUM(val) FROM bptest;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> SELECT SUM(val) FROM bptest;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1604
Current database: bptest
+----------+
| SUM(val) |
+----------+
| 1000000 |
+----------+
1 row in set (0.34 sec)
**Switch Primary Node 後に読み取り専用ノードで`SELECT`実行**
読み取り専用ノードでSELECT実行
mysql> SELECT SUM(val) FROM bptest;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> SELECT SUM(val) FROM bptest;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1604
Current database: bptest
+----------+
| SUM(val) |
+----------+
| 1000000 |
+----------+
1 row in set (10.93 sec)
プライマリノードでUPDATE実行後の読み取り専用ノード
読み取り専用ノードですでにバッファプールに載っているデータに対し、プライマリノードでUPDATEしてみました。
結果、読み取り専用ノードでもバッファプールからの読み込みとなりました。
Aurora 同様、DB ノード間でバッファプールの整合性を保つための処理が実装されているようです。
**プライマリノードで`UPDATE`実行（読み書き可能クラスタエンドポイントより）**
プライマリノードでUPDATE実行
mysql> SHOW VARIABLES LIKE '%read_only';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | ON |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
+-----------------------+-------+
4 rows in set (0.02 sec)
※この時点では読み取り専用ノードに接続されている
mysql> SELECT SUM(val) FROM bptest;
+----------+
| SUM(val) |
+----------+
| 1000000 |
+----------+
1 row in set (0.37 sec)
mysql> UPDATE bptest SET val = 2;
Query OK, 1000000 rows affected (6.35 sec)
※接続先がプライマリノードに変更され、無事 UPDATE された
mysql> SHOW VARIABLES LIKE '%read_only';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
+-----------------------+-------+
4 rows in set (0.00 sec)
**INSERT 直後の読み取り専用ノードで`SELECT`実行**
読み取り専用ノードでSELECT実行
mysql> SELECT SUM(val) FROM bptest;
+----------+
| SUM(val) |
+----------+
| 1000000 |
+----------+
1 row in set (10.24 sec)
※ここでプライマリノードに対し先の通り UPDATE 実行
mysql> SELECT SUM(val) FROM bptest;
+----------+
| SUM(val) |
+----------+
| 2000000 |
+----------+
1 row in set (0.32 sec)
参考：SHOW ENGINE INNODB STATUSについて
プライマリエンドポイントで実行した結果を記します。
**`SHOW ENGINE INNODB STATUS`の結果**
InnoDBステータス
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2019-11-23 10:17:09 0x7f3758b39700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2290 srv_active, 0 srv_shutdown, 12 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5564
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 5068
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 5499
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 4970
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 5624
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 5130
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 5415
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 5126
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 21161
RW-shared spins 3, rounds 6, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 2.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1989
Purge done for trx's n:o < 1988 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421350604282336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421350604281416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421350604280496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421350604279576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421350604278656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421350604277736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421350604276816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 1
14362 OS file reads, 255159 OS file writes, 3378 OS fsyncs
5.85 reads/s, 1174 avg bytes/read, 132.37 writes/s, 1.48 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 3187567, node heap has 2 buffer(s)
Hash table size 3187567, node heap has 2 buffer(s)
Hash table size 3187567, node heap has 3 buffer(s)
Hash table size 3187567, node heap has 0 buffer(s)
Hash table size 3187567, node heap has 0 buffer(s)
Hash table size 3187567, node heap has 0 buffer(s)
Hash table size 3187567, node heap has 2 buffer(s)
Hash table size 3187567, node heap has 3 buffer(s)
13.48 hash searches/s, 11.83 non-hash searches/s
---
LOG
---
Log sequence number 42318741
Log buffer assigned up to 42318741
Log buffer completed up to 42318741
Log written up to 42318741
Log flushed up to 42318741
Added dirty pages up to 42318741
Pages flushed up to 42318741
Last checkpoint at 42314975
246522 log i/o's done, 128.85 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 13359644672
Dictionary memory allocated 452734
Buffer pool size 786428
Free buffers 784407
Database pages 2009
Old database pages 0
Modified db pages 31
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1721, created 4857, written 3999
0.00 reads/s, 2.37 creates/s, 1.35 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2009, unzip_LRU len: 0
I/O sum[496]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98303
Free buffers 98053
Database pages 249
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 225, created 36, written 85
0.00 reads/s, 0.02 creates/s, 0.02 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 249, unzip_LRU len: 0
I/O sum[62]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 98303
Free buffers 98105
Database pages 196
Old database pages 0
Modified db pages 6
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 181, created 21, written 846
0.00 reads/s, 0.00 creates/s, 0.26 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 196, unzip_LRU len: 0
I/O sum[62]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 98303
Free buffers 98096
Database pages 205
Old database pages 0
Modified db pages 6
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 180, created 28, written 574
0.00 reads/s, 0.00 creates/s, 0.15 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 205, unzip_LRU len: 0
I/O sum[62]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 98303
Free buffers 98053
Database pages 248
Old database pages 0
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 229, created 397, written 86
0.00 reads/s, 2.35 creates/s, 0.04 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 248, unzip_LRU len: 0
I/O sum[62]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 98304
Free buffers 98088
Database pages 214
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 196, created 4152, written 170
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 214, unzip_LRU len: 0
I/O sum[62]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 98304
Free buffers 98025
Database pages 278
Old database pages 0
Modified db pages 9
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 197, created 81, written 1027
0.00 reads/s, 0.00 creates/s, 0.43 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 278, unzip_LRU len: 0
I/O sum[62]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 98304
Free buffers 97942
Database pages 361
Old database pages 0
Modified db pages 9
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 276, created 85, written 1085
0.00 reads/s, 0.00 creates/s, 0.41 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 361, unzip_LRU len: 0
I/O sum[62]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 98304
Free buffers 98045
Database pages 258
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 237, created 57, written 126
0.00 reads/s, 0.00 creates/s, 0.02 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 258, unzip_LRU len: 0
I/O sum[62]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3928, Main thread ID=139860245473024 , state=sleeping
Number of rows inserted 3083045, updated 418, deleted 163, read 2058006
1474.51 inserts/s, 0.02 updates/s, 0.00 deletes/s, 956.91 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
なお、読み書き可能なクラスタエンドポイントでは結果が表示され、読み取り専用のクラスタエンドポイントでは**Empty set**となりました。
参考：SHOW PROCESSLISTについて
プライマリエンドポイントからの接続で実行するとプライマリノードの結果が返り、クラスタエンドポイントからの接続で実行すると複数ノードの結果が合成されるようです…が、処理が甘いのかエラーが出ていますね。
**プライマリエンドポイントから実行**
プライマリエンドポイントでのPROCESSLIST
mysql> SHOW PROCESSLIST;
+------+-----------------+----------------------+------+----------------+------+-------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+----------------------+------+----------------+------+-------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 3184 | Waiting on empty queue | NULL |
| 24 | root | 127.0.0.1:59132 | NULL | Sleep | 6 | | NULL |
| 37 | root | 127.0.0.1:59197 | NULL | Sleep | 0 | | NULL |
| 84 | root | 127.0.0.1:59415 | NULL | Sleep | 5 | | NULL |
| 133 | root | 127.0.0.1:59567 | NULL | Sleep | 37 | | NULL |
| 153 | replicator | 11.198.20.195:44928 | NULL | Polar Log Dump | 0 | Reading log from innodb | NULL |
| 154 | replicator | 11.198.20.195:44929 | NULL | Polar Log Ack | 0 | Receiving from client | NULL |
| 486 | replicator | 11.194.242.232:51259 | NULL | Polar Log Dump | 0 | Reading log from innodb | NULL |
| 488 | replicator | 11.194.242.232:51261 | NULL | Polar Log Ack | 0 | Receiving from client | NULL |
| 528 | aurora | 11.112.240.32:54669 | NULL | Sleep | 1 | | NULL |
| 529 | aurora | 11.112.239.96:42283 | NULL | Sleep | 1 | | NULL |
| 530 | aurora | 11.112.240.32:54673 | NULL | RDS Push LSN | 2881 | starting | NULL |
| 531 | aurora | 11.112.239.96:42287 | NULL | RDS Push LSN | 2881 | starting | NULL |
| 3316 | polaradmin | 10.0.0.199:47360 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+------+-----------------+----------------------+------+----------------+------+-------------------------+------------------+
14 rows in set (0.00 sec)
**クラスタエンドポイントから実行**
クラスタエンドポイントでのPROCESSLIST
mysql> SHOW PROCESSLIST;
+----------+-----------------+----------------------+------+----------------+------+--------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+----------------------+------+----------------+------+--------------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 3589 | Waiting on empty queue | NULL |
| 24 | root | 127.0.0.1:59132 | NULL | Sleep | 1 | | NULL |
| 37 | root | 127.0.0.1:59197 | NULL | Sleep | 0 | | NULL |
| 84 | root | 127.0.0.1:59415 | NULL | Sleep | 0 | | NULL |
| 133 | root | 127.0.0.1:59567 | NULL | Sleep | 22 | | NULL |
| 153 | replicator | 11.198.20.195:44928 | NULL | Polar Log Dump | 0 | Reading log from innodb | NULL |
| 154 | replicator | 11.198.20.195:44929 | NULL | Polar Log Ack | 0 | Receiving from client | NULL |
| 486 | replicator | 11.194.242.232:51259 | NULL | Polar Log Dump | 0 | Reading log from innodb | NULL |
| 488 | replicator | 11.194.242.232:51261 | NULL | Polar Log Ack | 0 | Receiving from client | NULL |
| 528 | aurora | 11.112.240.32:54669 | NULL | Sleep | 1 | | NULL |
| 529 | aurora | 11.112.239.96:42283 | NULL | Sleep | 1 | | NULL |
| 530 | aurora | 11.112.240.32:54673 | NULL | RDS Push LSN | 3286 | starting | NULL |
| 531 | aurora | 11.112.239.96:42287 | NULL | RDS Push LSN | 3286 | starting | NULL |
| 3316 | polaradmin | 10.0.0.199:47360 | NULL | Sleep | 405 | | NULL |
| 33575989 | polaradmin | 10.0.0.199:40786 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 3 | system user | | NULL | Connect | 0 | Ack sender thread sending data | NULL |
| 9 | root | 127.0.0.1:54456 | NULL | Sleep | 7 | | NULL |
| 25 | root | 127.0.0.1:54608 | NULL | Sleep | 55 | | NULL |
| 79 | root | 127.0.0.1:55151 | NULL | Sleep | 9 | | NULL |
| 83 | root | 127.0.0.1:55170 | NULL | Sleep | 0 | | NULL |
| 292 | aurora | 11.112.240.32:22586 | NULL | Sleep | 1 | | NULL |
| 293 | aurora | 11.112.239.96:28498 | NULL | Sleep | 1 | | NULL |
| 294 | aurora | 11.112.240.32:22589 | NULL | RDS Push LSN | 3286 | starting | NULL |
| 295 | aurora | 11.112.239.96:28504 | NULL | RDS Push LSN | 3286 | starting | NULL |
| 33575989 | polaradmin | 10.0.0.199:40786 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----------+-----------------+----------------------+------+----------------+------+--------------------------------+------------------+
ERROR 2027 (HY000): Malformed packet
25 rows in set (0.00 sec)
…ところでRDS Push LSNしてるのは…お、auroraさん？
MySQLユーザ一覧
mysql> SELECT user, host FROM mysql.user;
+------------------+----------------+
| user | host |
+------------------+----------------+
| aurora | % |
| polaradmin | % |
| replicator | % |
| replicator | 11.194.244.101 |
| replicator | 11.198.18.167 |
| replicator | 11.198.19.78 |
| root | 127.0.0.1 |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+----------------+
11 rows in set (0.00 sec)
mysql> SHOW GRANTS FOR `aurora`@`%`;
+----------------------------------------------------------------------------------------------------+
| Grants for aurora@% |
+----------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `aurora`@`%` |
| GRANT ALL PRIVILEGES ON `mysql`.* TO `aurora`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `test`.* TO `aurora`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
次回以降、Advent Calendar 2019 （MySQL / PostgreSQL / alibabacloud）の記事として書いていく予定です。