MySQL Forums
Forum List  »  InnoDB

Re: Table freeze when size exceeds innodb buffer pool size
Posted by: newbie Shai
Date: September 27, 2017 01:05PM

Dear Peter,

For the final query I got error when I tried to run this.
show global table status;
then I tried show global status;

I would like to learn based on these 3 output what could we learn and further optimise any mysql system?

Variable_name Value
Aborted_clients 53
Aborted_connects 6
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 12109221041
Bytes_sent 14099691509
Com_admin_commands 0
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 15
Com_alter_tablespace 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_binlog 0
Com_call_procedure 0
Com_change_db 3075
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 3302814
Com_create_db 0
Com_create_event 0
Com_create_function 0
Com_create_index 0
Com_create_procedure 0
Com_create_server 0
Com_create_table 0
Com_create_trigger 0
Com_create_udf 0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 0
Com_delete 0
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_event 0
Com_drop_function 0
Com_drop_index 0
Com_drop_procedure 0
Com_drop_server 0
Com_drop_table 0
Com_drop_trigger 0
Com_drop_user 0
Com_drop_view 0
Com_empty_query 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 5922746
Com_insert_select 0
Com_install_plugin 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
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_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 63474
Com_rollback_to_savepoint 0
Com_savepoint 0
Com_select 37636305
Com_set_option 5320259
Com_show_authors 0
Com_show_binlog_events 0
Com_show_binlogs 48
Com_show_charsets 0
Com_show_collations 419316
Com_show_column_types 0
Com_show_contributors 0
Com_show_create_db 0
Com_show_create_event 0
Com_show_create_func 0
Com_show_create_proc 0
Com_show_create_table 757
Com_show_create_trigger 0
Variable_name Value
Com_show_databases 113
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 1100
Com_show_function_status 0
Com_show_grants 48
Com_show_keys 344
Com_show_master_status 48
Com_show_new_master 0
Com_show_open_tables 0
Com_show_plugins 44
Com_show_privileges 0
Com_show_procedure_status 0
Com_show_processlist 0
Com_show_profile 0
Com_show_profiles 0
Com_show_slave_hosts 0
Com_show_slave_status 48
Com_show_status 3
Com_show_storage_engines 0
Com_show_table_status 1792
Com_show_tables 46389
Com_show_triggers 0
Com_show_variables 419464
Com_show_warnings 1
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reprepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_uninstall_plugin 0
Com_unlock_tables 0
Com_update 21509263
Com_update_multi 0
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 428308
Created_tmp_disk_tables 1128
Created_tmp_files 5
Created_tmp_tables 889690
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 64576938
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 17818
Handler_read_key 127528716
Handler_read_next 6117004066
Handler_read_prev 957754869
Handler_read_rnd 1350374
Handler_read_rnd_next 1115408667
Handler_rollback 5676
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 21468590
Handler_write 71736657
Innodb_buffer_pool_pages_data 626394
Innodb_buffer_pool_pages_dirty 90
Innodb_buffer_pool_pages_flushed 7487086
Innodb_buffer_pool_pages_free 734766
Innodb_buffer_pool_pages_misc 15096
Innodb_buffer_pool_pages_total 1376256
Innodb_buffer_pool_read_ahead_rnd 5
Innodb_buffer_pool_read_ahead_seq 5938
Innodb_buffer_pool_read_requests 13423493396
Innodb_buffer_pool_reads 166543
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 196024932
Innodb_data_fsyncs 17104275
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 7296143360
Innodb_data_reads 179387
Innodb_data_writes 21863681
Innodb_data_written 269506016256
Innodb_dblwr_pages_written 7487086
Innodb_dblwr_writes 152835
Innodb_log_waits 0
Innodb_log_write_requests 40118188
Innodb_log_writes 16298581
Innodb_os_log_fsyncs 16385222
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Variable_name Value
Innodb_os_log_written 24123085824
Innodb_page_size 16384
Innodb_pages_created 278808
Innodb_pages_read 445188
Innodb_pages_written 7487086
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 5707547
Innodb_row_lock_time_avg 1309
Innodb_row_lock_time_max 120932
Innodb_row_lock_waits 4358
Innodb_rows_deleted 0
Innodb_rows_inserted 9979177
Innodb_rows_read 7847321639
Innodb_rows_updated 21468557
Key_blocks_not_flushed 0
Key_blocks_unused 6694
Key_blocks_used 0
Key_read_requests 0
Key_reads 0
Key_write_requests 0
Key_writes 0
Last_query_cost 0.000000
Max_used_connections 57
Not_flushed_delayed_rows 0
Open_files 47
Open_streams 0
Open_table_definitions 35
Open_tables 64
Opened_files 4727
Opened_table_definitions 79
Opened_tables 18625
Prepared_stmt_count 0
Qcache_free_blocks 0
Qcache_free_memory 0
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
Qcache_not_cached 0
Qcache_queries_in_cache 0
Qcache_total_blocks 0
Queries 75075704
Questions 75075704
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 289007
Select_range_check 0
Select_scan 907314
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 20084
Sort_merge_passes 0
Sort_range 316605
Sort_rows 54661893
Sort_scan 1860
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_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 62579590
Table_locks_waited 108
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 0
Threads_connected 21
Threads_created 428307
Threads_running 1
Uptime 898882
Uptime_since_flush_status 898882

Options: ReplyQuote




Sorry, only registered users may post in this forum.

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.