慢查询监控

2017-12-11 03:55:03.572843 [INFO] SQL_SLOW_PROCESS(client_ip={Not IP address [0]:0}, server_ip={10.125.224.5:2659}, route_type=ROUTE_TYPE_PARTITION_UNMERGE_LOCAL, use_compress_protocol=true, sql=SELECT /*+READ_CONSISTENCY(WEAK)*/ name, value FROM ob_all_proxy_kv_table WHERE name IN (‘ob1.obtest.config_version’,’ob_proxy_vip_tenant_version’,’ob1.obtest.upgrade_switch’,’ob1.obtest.upgrade_start_time’,’ob1.obtest.upgrade_duration_minutes’,’ob1.obtest.new_binary_name_6u’,’ob1.obtest.new_binary_md5_6u’,’all_proxy.upgrade_switch’,’all_proxy.upgrade_start_time’,’all_proxy.upgrade_duration_minutes’,’all_proxy.new_binary_name_6u’,’all_proxy.new_binary_md5_6u’) LIMIT 9223372036854775807, cmd_size_stats={client_request_bytes:496, server_request_bytes:514, server_response_bytes:393, client_response_bytes:393}, cmd_time_stats={client_transaction_idle_time_us=0, client_request_read_time_us=76, client_request_analyze_time_us=75, cluster_resource_create_time_us=0, pl_lookup_time_us=122, pl_process_time_us=142, congestion_control_time_us=28, congestion_process_time_us=6, do_observer_open_time_us=254, server_connect_time_us=191, server_sync_session_variable_time_us=18041, server_send_saved_login_time_us=613, server_send_use_database_time_us=7077, server_send_session_variable_time_us=10351, server_send_all_session_variable_time_us=0, server_send_last_insert_id_time_us=0, server_send_start_trans_time_us=0, build_server_request_time_us=137, plugin_compress_request_time_us=0, prepare_send_request_to_server_time_us=19412, server_request_write_time_us=37, server_process_request_time_us=10464, server_response_read_time_us=53, plugin_decompress_response_time_us=0, server_response_analyze_time_us=150, ok_packet_trim_time_us=52, client_response_write_time_us=286, request_total_time_us=30503})

2017-12-10 21:27:58.458102 [INFO] SQL_SLOW_QUERY(client_ip={10.125.224.4:51099}, server_ip={10.244.4.27:2661}, route_type=ROUTE_TYPE_NONPARTITION_UNMERGE_LOCAL, use_compress_protocol=true, sql=SET sql_mode=’STRICT_ALL_TABLES,STRICT_TRANS_TABLES’, cmd_size_stats={client_request_bytes:57, server_request_bytes:75, server_response_bytes:11, client_response_bytes:11}, cmd_time_stats={client_transaction_idle_time_us=0, client_request_read_time_us=26, client_request_analyze_time_us=24, cluster_resource_create_time_us=0, pl_lookup_time_us=0, pl_process_time_us=0, congestion_control_time_us=0, congestion_process_time_us=0, do_observer_open_time_us=4, server_connect_time_us=0, server_sync_session_variable_time_us=0, server_send_saved_login_time_us=0, server_send_use_database_time_us=0, server_send_session_variable_time_us=0, server_send_all_session_variable_time_us=0, server_send_last_insert_id_time_us=0, server_send_start_trans_time_us=0, build_server_request_time_us=9, plugin_compress_request_time_us=0, prepare_send_request_to_server_time_us=70, server_request_write_time_us=14, server_process_request_time_us=8632887, server_response_read_time_us=11, plugin_decompress_response_time_us=0, server_response_analyze_time_us=79, ok_packet_trim_time_us=85, client_response_write_time_us=134, request_total_time_us=8633219})

2017-12-10 21:27:58.458175 [INFO] SQL_SLOW_TRX(client_ip={10.125.224.4:51099}, server_ip={10.244.4.27:2661}, sql=SET sql_mode=’STRICT_ALL_TABLES,STRICT_TRANS_TABLES’, sql_cmd=”Query”, route_type=ROUTE_TYPE_NONPARTITION_UNMERGE_LOCAL, use_compress_protocol=true, trans_stats_={client_requests:1, server_responses:1, pl_lookup_retries:0, server_retries:0, client_request_bytes:57, server_request_bytes:75, server_response_bytes:11, client_response_bytes:11, client_transaction_idle_time_us=0, client_process_request_time_us=41, client_request_read_time_us=26, client_request_analyze_time_us=24, cluster_resource_create_time_us=0, pl_lookup_time_us=0, pl_process_time_us=0, congestion_control_time_us=0, congestion_process_time_us=0, do_observer_open_time_us=4, server_connect_time_us=0, sync_session_variable_time_us=0, send_saved_login_time_us=0, send_use_database_time_us=0, send_session_vars_time_us=0, send_all_session_vars_time_us=0, send_last_insert_id_time_us=0, send_start_trans_time_us=0, build_server_request_time_us=9, plugin_compress_request_time_us=0, prepare_send_request_to_server_time_us=70, server_request_write_time_us=14, server_process_request_time_us=8632887, server_response_read_time_us=11, plugin_decompress_response_time_us=0, server_response_analyze_time_us=79, ok_packet_trim_time_us=85, client_response_write_time_us=134, trans_time_us=8633326})

error包监控

2017-12-11 03:46:54.806037 [WARN] SQL_ERROR_RESP(client_ip={*Not IP address [0]*:0}, server_ip={10.125.224.5:2659}, proxy_user_name=proxyro@sys#ob1.obtest, database_name=, sql=, sql_cmd=”Login”, sql_error=”OB_UNCOMMON_ERROR”, msg={is_trans_completed:true, is_resp_completed:true, ending_type:2, is_partition_hit:true, has_new_sys_var:false, has_proxy_idc_name_user_var:false, is_server_db_reset:false, reserved_len:0, connection_id:0, scramble_buf:””, is_resultset_resp:false, server_capabilities_lower_.capability:0, ok_packet_action_type:2, last_ok_pkt_len:12, rewritten_last_ok_pkt_len:0, error_pkt:field_count:255, errcode:4666, sqlstate:”HY000”, message:”cluster name is not match to ‘test.jianhua’”})

tenant级别QPS/TPS统计

2017-12-10 21:26:35.424132 [INFO] TENANT_PREF_STATS(cluster_name=MetaDataBase, tenant_name=sys, database_name=opstest, select_count=1, insert_count=0, update_count=0, replace_count=0, delete_count=0, api_count=0, login_count=0, quit_count=0, failed_count=43, total_request_count=211, total_transaction_count=211)

说明

说明: total_request_count == query(dql/dml) + login + quit + others api_count在proxy中没意义，恒为0。 quit_count记录用户发送com_quit指令个数。 failed_count记录总request中失败的个数。

全局级别统计

网络

2017-12-11 03:55:59.476685 [INFO] NET_STATE(net_handler_run=448878090,read_bytes=651367,write_bytes=892599,client_connections_currently_open=0,global_client_connections_currently_open=0,global_connections_currently_open=1,global_accepts_currently_open=2,calls_to_readfromnet=2251,calls_to_read=2251,calls_to_read_nodata=775,calls_to_writetonet=2295,calls_to_write=1162,calls_to_write_nodata=0,inactivity_cop_lock_acquire_failure=0,keep_alive_lru_timeout_total=0,keep_alive_lru_timeout_count=0,default_inactivity_timeout=1,)

锁冲突

2017-12-11 03:55:59.476620 [INFO] LOCK_STATE(net_vc_lock_conflict_count=0,net_handler_lock_conflict_count=4,ethread_lock_conflict_count=0,congestion_entry_lock_conflict_count=0,congestion_table_lock_conflict_count=0,mysql_proxy_lock_conflict_count=0,client_vc_lock_conflict_count=774,cache_cleaner_lock_conflict_count=0,table_entry_map_lock_conflict_count=0,common_lock_conflict_count=0,)

黑名单

2017-12-11 03:55:59.476631 [INFO] CONGESTION_STATE(dead_congested=0,alive_congested=3,)

路由表

2017-12-11 03:55:59.476643 [INFO] PROCESSOR_STATE(get_pl_total=279,get_pl_from_thread_cache_hit=216,get_pl_from_global_cache_hit=52,get_pl_from_global_cache_dirty_stat=1,get_pl_by_all_dummy=131,get_pl_from_remote=10,get_pl_from_remote_succ=5,get_pl_from_remote_fail=12,get_pl_by_last_session_succ=204,get_pl_by_rs_list_succ=0,pl_delay_update_count=0,set_delay_update_count=3,gc_table_entry_from_global_cache=0,gc_table_entry_from_thread_cache=7,kick_out_table_entry_from_global_cache=0,get_part_info_from_remote=0,get_part_info_from_remote_succ=0,get_part_info_from_remote_fail=0,get_first_part_from_remote=0,get_first_part_from_remote_succ=0,get_first_part_from_remote_fail=0,get_sub_part_from_remote=0,get_sub_part_from_remote_succ=0,get_sub_part_from_remote_fail=0,get_partition_entry_from_thread_cache_hit=0,get_partition_entry_from_global_cache_hit=0,get_partition_entry_from_global_cache_dirty=0,get_partition_entry_from_remote=0,get_partition_entry_from_remote_succ=0,get_partition_entry_from_remote_fail=0,gc_partition_entry_from_global_cache=0,gc_partition_entry_from_thread_cache=0,kick_out_partition_entry_from_global_cache=0,update_route_entry_by_congestion=0,get_congestion_total=498,get_congestion_from_thread_cache_hit=466,get_congestion_from_global_cache_hit=32,get_congestion_from_global_cache_miss=0,)

主流程

2017-12-11 03:55:59.476673 [INFO] MYSQL_STATE(total_transaction_count=544,total_user_transaction_count=0,total_query_count=385,total_client_request_reread_count=0,total_server_response_reread_count=5,client_request_total_size=757043,client_response_total_size=196861,server_request_total_size=776404,server_response_total_size=179296,total_transactions_time=6462097580,total_user_transactions_time=0,total_client_request_read_time=71033460,total_client_response_write_time=152371145,total_client_request_analyze_time=36386541,total_client_transaction_idle_time=17361143,total_ok_packet_trim_time=29763337,total_server_process_request_time=3599176753,total_server_response_read_time=27118688,total_server_response_analyze_time=55862921,total_send_saved_login_time=96792039,total_send_all_session_vars_time=0,total_send_use_database_time=432591955,total_send_changed_session_vars_time=815896047,total_send_last_insert_id_time=0,total_send_start_trans_time=0,total_pl_lookup_time=96203645,total_congestion_control_time=18942744,total_server_connect_time=42279865,client_requests=563,client_large_requests=0,client_internal_requests=122,local_session_state_requests=5,client_missing_pk_requests=204,client_completed_requests=575,client_connection_abort_count=59,client_select_requests=194,client_insert_requests=70,client_update_requests=0,client_delete_requests=0,client_other_requests=89,request_size_100_count=364,request_size_1K_count=200,request_size_3K_count=1,request_size_5K_count=0,request_size_10K_count=68,request_size_1M_count=1,request_size_inf_count=0,response_size_100_count=445,response_size_1K_count=134,response_size_3K_count=50,response_size_5K_count=5,response_size_10K_count=0,response_size_1M_count=0,response_size_inf_count=0,client_speed_bytes_per_sec_100=0,client_speed_bytes_per_sec_1K=0,client_speed_bytes_per_sec_10K=6,client_speed_bytes_per_sec_100K=94,client_speed_bytes_per_sec_1M=231,client_speed_bytes_per_sec_10M=222,client_speed_bytes_per_sec_100M=9,server_speed_bytes_per_sec_100=0,server_speed_bytes_per_sec_1K=0,server_speed_bytes_per_sec_10K=0,server_speed_bytes_per_sec_100K=1,server_speed_bytes_per_sec_1M=190,server_speed_bytes_per_sec_10M=123,server_speed_bytes_per_sec_100M=92,server_connect_count=224,server_connect_retries=107,server_pl_lookup_count=229,server_pl_lookup_retries=49,broken_server_connections=13,server_requests=984,server_responses=1315,server_error_responses=159,server_resultset_responses=210,server_ok_responses=605,server_other_responses=328,send_saved_login_requests=152,send_all_session_vars_requests=0,send_use_database_requests=148,send_changed_session_vars_requests=147,send_last_insert_id_requests=0,send_start_trans_requests=0,vip_to_tenant_cache_hit=0,vip_to_tenant_cache_miss=0,current_client_connections=2,current_active_client_connections=0,current_client_transactions=0,current_server_transactions=31,dummy_entry_expired_count=0,total_client_connections=28,total_internal_client_connections=62,total_client_connections_ipv4=28,total_client_connections_ipv6=0,total_server_connections=328,current_server_connections=1,avg_transactions_per_client_connection=0,avg_transactions_per_server_connection=1,)

集群

2017-12-11 03:55:59.476627 [INFO] CLUSTER_RESOURCE_STATE(create_cluster_resource_count=5,create_cluster_resource_time=22748605,get_cluster_resource_fail_count=0,parallel_create_cluster_resource_count=0,current_cluster_resource_count=2,create_cluster_resource_succ_count=5,delete_cluster_resource_count=3,free_cluster_resource_count=3,create_cluster_client_pool_succ_count=14,delete_cluster_client_pool_count=11,free_cluster_client_pool_count=11,create_async_common_task_count=354,destroy_async_common_task_count=354,)

日志

2017-12-11 03:55:59.476612 [INFO] WARNING_STATE(current_core_count=0,last_warn_log_time_us=1512935729182447,last_error_log_time_us=0,total_warn_log_count=841,total_error_log_count=0,alloced_log_item_count=1024,active_log_item_count=0,released_log_item_count=0,alloced_large_log_item_count=512,active_large_log_item_count=0,released_large_log_item_count=0,default_log_write_size=146681287,default_log_write_count=629125,default_large_log_write_count=424,xflush_log_write_size=893965,xflush_log_write_count=945,xflush_large_log_write_count=0,dropped_error_log_count=0,dropped_warn_log_count=0,dropped_info_log_count=0,dropped_trace_log_count=0,dropped_debug_log_count=0,async_flush_log_speed=3,)