Re: MySQL 5.7 performance issue with 300 GB Innodb
show global status;
Aborted_clients 59
Aborted_connects 1
Binlog_cache_disk_use 0
Binlog_cache_use 0
Binlog_stmt_cache_disk_use 0
Binlog_stmt_cache_use 0
Bytes_received 2264712862
Bytes_sent 2485585780
Com_admin_commands 827
Com_assign_to_keycache 0
Com_alter_db 0
Com_alter_db_upgrade 0
Com_alter_event 0
Com_alter_function 0
Com_alter_procedure 0
Com_alter_server 0
Com_alter_table 7
Com_alter_tablespace 0
Com_alter_user 0
Com_analyze 0
Com_begin 13
Com_binlog 0
Com_call_procedure 28865
Com_change_db 25131
Com_change_master 0
Com_change_repl_filter 0
Com_check 0
Com_checksum 0
Com_commit 9581
Com_create_db 0
Com_create_event 0
Com_create_function 0
Com_create_index 0
Com_create_procedure 4
Com_create_server 0
Com_create_table 7169
Com_create_trigger 0
Com_create_udf 0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 2013
Com_delete 13211
Com_delete_multi 30589
Com_do 0
Com_drop_db 0
Com_drop_event 0
Com_drop_function 0
Com_drop_index 0
Com_drop_procedure 4
Com_drop_server 0
Com_drop_table 3909
Com_drop_trigger 0
Com_drop_user 0
Com_drop_view 0
Com_empty_query 0
Com_execute_sql 2072
Com_explain_other 0
Com_flush 0
Com_get_diagnostics 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 21408
Com_insert_select 98354
Com_install_plugin 0
Com_kill 6
Com_load 17423
Com_lock_tables 0
Com_optimize 2
Com_preload_keys 0
Com_prepare_sql 2080
Com_purge 0
Com_purge_before_date 0
Com_release_savepoint 0
Com_rename_table 0
Com_rename_user 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_resignal 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 13
Com_rollback_to_savepoint 0
Com_savepoint 0
Com_select 659708
Com_set_option 24447
Com_signal 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 663
Com_show_create_db 0
Com_show_create_event 0
Com_show_create_func 0
Com_show_create_proc 20793
Com_show_create_table 56
Com_show_create_trigger 0
Com_show_databases 1
Com_show_engine_logs 0
Com_show_engine_mutex 0
Com_show_engine_status 0
Com_show_events 0
Com_show_errors 0
Com_show_fields 76
Com_show_function_code 0
Com_show_function_status 0
Com_show_grants 0
Com_show_keys 162848
Com_show_master_status 0
Com_show_open_tables 0
Com_show_plugins 0
Com_show_privileges 0
Com_show_procedure_code 0
Com_show_procedure_status 0
Com_show_processlist 0
Com_show_profile 0
Com_show_profiles 0
Com_show_relaylog_events 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_table_status 2
Com_show_tables 40
Com_show_triggers 0
Com_show_variables 679
Com_show_warnings 7428
Com_show_create_user 0
Com_shutdown 0
Com_slave_start 0
Com_slave_stop 0
Com_group_replication_start 0
Com_group_replication_stop 0
Com_stmt_execute 2072
Com_stmt_close 2052
Com_stmt_fetch 0
Com_stmt_prepare 2080
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 32187
Com_uninstall_plugin 0
Com_unlock_tables 0
Com_update 70332
Com_update_multi 219834
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Com_stmt_reprepare 0
Connection_errors_accept 0
Connection_errors_internal 0
Connection_errors_max_connections 0
Connection_errors_peer_address 0
Connection_errors_select 0
Connection_errors_tcpwrap 0
Connections 709
Created_tmp_disk_tables 5348
Created_tmp_files 5565
Created_tmp_tables 330529
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 806525
Handler_delete 12956183
Handler_discover 0
Handler_external_lock 2490998
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 897577
Handler_read_key 352138684
Handler_read_last 887
Handler_read_next 11558664488
Handler_read_prev 0
Handler_read_rnd 79206275
Handler_read_rnd_next 20949494738
Handler_rollback 108
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 162134020
Handler_write 145164514
Innodb_buffer_pool_dump_status not started
Innodb_buffer_pool_load_status Buffer pool(s) load completed at 170913 22:19:38
Innodb_buffer_pool_resize_status not started
Innodb_buffer_pool_pages_data 6162943
Innodb_buffer_pool_bytes_data 100973658112
Innodb_buffer_pool_pages_dirty 11196
Innodb_buffer_pool_bytes_dirty 183435264
Innodb_buffer_pool_pages_flushed 9289732
Innodb_buffer_pool_pages_free 16373
Innodb_buffer_pool_pages_misc 374268
Innodb_buffer_pool_pages_total 6553584
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 597674
Innodb_buffer_pool_read_ahead_evicted 0
Innodb_buffer_pool_read_requests 70923752197
Innodb_buffer_pool_reads 11092729
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 871171799
Innodb_data_fsyncs 3130809
Innodb_data_pending_fsyncs 2
Innodb_data_pending_reads 2
Innodb_data_pending_writes 0
Innodb_data_read 193682330112
Innodb_data_reads 11822231
Innodb_data_writes 10737471
Innodb_data_written 343332310528
Innodb_dblwr_pages_written 8803629
Innodb_dblwr_writes 921708
Innodb_log_waits 0
Innodb_log_write_requests 110874274
Innodb_log_writes 327731
Innodb_os_log_fsyncs 339049
Innodb_os_log_pending_fsyncs 1
Innodb_os_log_pending_writes 0
Innodb_os_log_written 46865440256
Innodb_page_size 16384
Innodb_pages_created 1032830
Innodb_pages_read 11821743
Innodb_pages_written 9291173
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 281410
Innodb_row_lock_time_avg 3126
Innodb_row_lock_time_max 81480
Innodb_row_lock_waits 90
Innodb_rows_deleted 12956178
Innodb_rows_inserted 57142810
Innodb_rows_read 32518792842
Innodb_rows_updated 77342226
Innodb_num_open_files 894
Innodb_truncated_status_writes 0
Innodb_available_undo_logs 128
Key_blocks_not_flushed 0
Key_blocks_unused 6590
Key_blocks_used 6698
Key_read_requests 118467287
Key_reads 8509
Key_write_requests 3260513
Key_writes 19889
Locked_connects 0
Max_execution_time_exceeded 0
Max_execution_time_set 0
Max_execution_time_set_failed 0
Max_used_connections 45
Max_used_connections_time 2017-09-14 10:20:50
Not_flushed_delayed_rows 0
Ongoing_anonymous_transaction_count 0
Open_files 65
Open_streams 0
Open_table_definitions 1399
Open_tables 1998
Opened_files 149592
Opened_table_definitions 41828
Opened_tables 48255
Performance_schema_accounts_lost 0
Performance_schema_cond_classes_lost 0
Performance_schema_cond_instances_lost 0
Performance_schema_digest_lost 0
Performance_schema_file_classes_lost 0
Performance_schema_file_handles_lost 0
Performance_schema_file_instances_lost 0
Performance_schema_hosts_lost 0
Performance_schema_index_stat_lost 0
Performance_schema_locker_lost 0
Performance_schema_memory_classes_lost 0
Performance_schema_metadata_lock_lost 0
Performance_schema_mutex_classes_lost 0
Performance_schema_mutex_instances_lost 0
Performance_schema_nested_statement_lost 0
Performance_schema_prepared_statements_lost 0
Performance_schema_program_lost 0
Performance_schema_rwlock_classes_lost 0
Performance_schema_rwlock_instances_lost 0
Performance_schema_session_connect_attrs_lost 0
Performance_schema_socket_classes_lost 0
Performance_schema_socket_instances_lost 0
Performance_schema_stage_classes_lost 0
Performance_schema_statement_classes_lost 0
Performance_schema_table_handles_lost 0
Performance_schema_table_instances_lost 0
Performance_schema_table_lock_stat_lost 0
Performance_schema_thread_classes_lost 0
Performance_schema_thread_instances_lost 0
Performance_schema_users_lost 0
Prepared_stmt_count 3
Qcache_free_blocks 1
Qcache_free_memory 1031832
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
Qcache_not_cached 474651
Qcache_queries_in_cache 0
Qcache_total_blocks 1
Queries 3165601
Questions 806869
Select_full_join 38746
Select_full_range_join 1534
Select_range 14976
Select_range_check 9
Select_scan 569221
Slave_open_temp_tables 0
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 16765
Sort_range 6305
Sort_rows 36366261
Sort_scan 68073
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects 0
Ssl_connect_renegotiates 0
Ssl_ctx_verify_depth 0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_server_not_after
Ssl_server_not_before
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size 0
Ssl_session_cache_timeouts 0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries 0
Ssl_verify_depth 0
Ssl_verify_mode 0
Ssl_version
Table_locks_immediate 23364
Table_locks_waited 0
Table_open_cache_hits 1585886
Table_open_cache_misses 44350
Table_open_cache_overflows 7087
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 10
Threads_connected 35
Threads_created 63
Threads_running 14
Uptime 45073
Uptime_since_flush_status 45073
Subject
Views
Written By
Posted
3251
September 13, 2017 03:52AM
863
September 13, 2017 11:25AM
759
September 13, 2017 10:35PM
1066
September 13, 2017 10:53PM
Re: MySQL 5.7 performance issue with 300 GB Innodb
765
September 13, 2017 11:02PM
751
September 14, 2017 01:58AM
751
September 14, 2017 11:03AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.