- 投稿日:2019-11-24T23:54:49+09:00
Alibaba Cloud の POLARDB を試してみる(2)MySQL 8.0 互換版のバッファプールまわりを中心に
ApsaraDB for POLARDB MySQL 8.0 互換版その 2 です。
今回は、バッファプールまわりの挙動を中心に確かめてみました。
前回の記事 : Alibaba Cloud の POLARDB を試してみる(1)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_degree
force_parallel_mode
innodb_polar_
で始まるものinnodb_primary_
で始まるものinnodb_rds_
で始まるものinnodb_replica_
で始まるものinnodb_slave_
で始まるものmax_parallel_degree
parallel_partition_factor
performance_point
polar_
で始まるものpolarfs_
で始まるものprimary_degrade_binlog_dump_timeout
primary_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 後にプライマリノードで
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
についてプライマリエンドポイントからの接続で実行するとプライマリノードの結果が返り、クラスタエンドポイントからの接続で実行すると複数ノードの結果が合成されるようです…が、処理が甘いのかエラーが出ていますね。
プライマリエンドポイントから実行
プライマリエンドポイントでのPROCESSLISTmysql> 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)
クラスタエンドポイントから実行
クラスタエンドポイントでのPROCESSLISTmysql> 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)の記事として書いていく予定です。
- 投稿日:2019-11-24T22:51:45+09:00
AWSにdjango開発環境構築メモ(VSCode Remote SSH用)
前提
この構築のゴールは、AWS環境ではsshのみ開けて、サーバサイドの開発、デバックをClientPCのVSCodeでできるようにすることです。
Client
Windows10
サーバ
CentOS7(AWSで動作)※手順全部書いたつもりです。参考になるところあったら使ってください。
※毎回これするのも大変なのでansible化するかdokerイメージにしたい。
※VSCodeでのdjangoデバック環境は次の機会に。ssh 設定
.ssh/sshconfigにAWSで起動したCentOSののログイン情報を記載してSSHでパス無しでログインできるようにする。
LocalForwardの行は、Djangoのポートへの。PCでlocalhost:8000を開くとサーバ側に転送されて便利。# Read more about SSH config files: https://linux.die.net/man/5/ssh_config Host myserver HostName xxx.xxx.xxx.xxx User centos IdentityFile ~user/.ssh/xxxx.pem LocalForward 8000 localhost:8000VSCodeのRemote SSHで接続できる事を確認しておく
VSCodeにpythonのプラグインをインストールするサーバ側の変更
CentOS7のGitをVersion2に変更する
$ sudo yum -y remove git $ sudo yum -y install https://centos7.iuscommunity.org/ius-release.rpm $ sudo yum -y install git2uvenv環境を作ってdjangoをインストール。後で使う、mysqlclientもインストール
$ sudo pip3.6 install --upgrade pip $ cd ~/envs/activate $ python3 -m venv django $ source ~/envs/django/bin/a $ pip install django $ sudo yum install python-devel mysql-community-devel $ pip install mysqlclientdjangoのサンプルアプリ作成
$ cd ~/web/ $ django-admin startproject mysiteCent7のSQliteのバージョンだとdjangoが動かない。
SQLiteは諦めMysqlをインストール$ sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm $ yum info mysql-community-server $ yum -y install mysql-community-server $ sudo yum -y install mysql-community-server $ sudo systemctl enable mysqld.service $ sudo systemctl start mysqld.service $ sudo systemctl status mysqld.service $ sudo cat /var/log/mysqld.log|grep passwordMysqlのセキュリティ設定
[root@ip-172-31-33-248 ~]# mysql_secure_installation Securing the MySQL server deployment. Enter password for user root: The existing password for the user account root has expired. Please set a new password. New password: Re-enter new password: The 'validate_password' component is installed on the server. The subsequent steps will run with the existing configuration of the component. Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : No ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success. All done! [root@ip-172-31-33-248 centos]#MysqlのユーザとDB作成
mysql> create database django01; mysql> create user django@localhost identified by '************'; mysql> grant all on django01.* to django@localhost;venvの適用方法(メモ)
[centos@ip-172-31-33-248 web]$ source venv/bin/activate (venv) [centos@ip-172-31-33-248 web]$ python -V Python 3.6.2 (venv) [centos@ip-172-31-33-248 web]$ pip list Package Version ---------- ------- Django 2.2.7 pip 19.3.1 pytz 2019.3 setuptools 28.8.0 sqlparse 0.3.0djangoのDBをMysqlに変更
$ git diff diff --git a/mysite/settings.py b/mysite/settings.py index 94fdb96..cf0a816 100644 --- a/mysite/settings.py +++ b/mysite/settings.py @@ -75,8 +75,15 @@ WSGI_APPLICATION = 'mysite.wsgi.application' DATABASES = { 'default': { - 'ENGINE': 'django.db.backends.sqlite3', - 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'), + 'ENGINE': 'django.db.backends.mysql', + 'NAME': 'django01', + 'USER': 'django', + 'PASSWORD': '********', + 'HOST': 'localhost', + 'PORT': '3306', + 'OPTIONS': { + 'init_command': "SET sql_mode='STRICT_TRANS_TABLES'", + }, } }python manage.py migrate python manage.py runserverクライアントPCで
http://localhost:8000
を開くとdjangoサーバにつながります。