MySQL Forums
Forum List  »  InnoDB

Re: Index exist on Deleted columns
Posted by: Ra Nala
Date: September 21, 2017 04:21AM

Hi Sir,

Please see results.

select
concat(round(sum(table_rows)/1000000,2),'m') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'g') data,
concat(round(sum(index_length)/(1024*1024*1024),2),'g') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'g') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
from information_schema.tables ;

rows data idx total_size idxfrac
------- ------- ------ ------------- ----------
275.45m 92.91g 49.77g 142.68g 0.54


show global status;
Variable_name Value
--------------------------------------------- ------------------------------------------------
Aborted_clients 1092
Aborted_connects 48479
Binlog_cache_disk_use 3442133
Binlog_cache_use 6053572
Binlog_stmt_cache_disk_use 0
Binlog_stmt_cache_use 301515
Bytes_received 535620504684
Bytes_sent 912518626511
Com_admin_commands 38771
Com_assign_to_keycache 0
Com_alter_db 0
Com_alter_db_upgrade 0
Com_alter_event 0
Com_alter_function 0
Com_alter_instance 0
Com_alter_procedure 0
Com_alter_server 0
Com_alter_table 1468
Com_alter_tablespace 0
Com_alter_user 0
Com_analyze 0
Com_begin 0
Com_binlog 0
Com_call_procedure 1
Com_change_db 3257
Com_change_master 0
Com_change_repl_filter 0
Com_check 0
Com_checksum 0
Com_commit 9891734
Com_create_db 4
Com_create_event 0
Com_create_function 0
Com_create_index 1196
Com_create_procedure 0
Com_create_server 0
Com_create_table 987
Com_create_trigger 0
Com_create_udf 0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 0
Com_delete 50202510
Com_delete_multi 0
Com_do 0
Com_drop_db 1
Com_drop_event 0
Com_drop_function 0
Com_drop_index 13
Com_drop_procedure 0
Com_drop_server 0
Com_drop_table 45
Com_drop_trigger 0
Com_drop_user 0
Com_drop_view 0
Com_empty_query 0
Com_execute_sql 0
Com_explain_other 0
Com_flush 28705
Com_get_diagnostics 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 241030420
Com_insert_select 0
Com_install_plugin 0
Com_kill 278
Com_load 0
Com_lock_tables 166358
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 28507
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 4112886
Com_rollback_to_savepoint 0
Com_savepoint 0
Com_select 2630764851
Com_set_option 177170643
Com_signal 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 5527413
Com_show_collations 5530302
Com_show_create_db 0
Com_show_create_event 0
Com_show_create_func 0
Com_show_create_proc 0
Com_show_create_table 585
Com_show_create_trigger 0
Com_show_databases 1053
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 11064
Com_show_function_code 0
Com_show_function_status 0
Com_show_grants 0
Com_show_keys 1150
Com_show_master_status 99
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 498
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 27190
Com_show_storage_engines 0
Com_show_table_status 15538
Com_show_tables 23920
Com_show_triggers 14
Com_show_variables 5558854
Com_show_warnings 5538055
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 436942054
Com_stmt_close 1111613
Com_stmt_fetch 204649643
Com_stmt_prepare 3021398
Com_stmt_reset 0
Com_stmt_send_long_data 472
Com_truncate 297840
Com_uninstall_plugin 0
Com_unlock_tables 166457
Com_update 412230786
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
Com_stmt_reprepare 2
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 5606149
Created_tmp_disk_tables 2999357
Created_tmp_files 130190
Created_tmp_tables 216816383
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 3208087275
Handler_delete 64700326
Handler_discover 0
Handler_external_lock 5000885436
Handler_mrr_init 0
Handler_prepare 1386673020
Handler_read_first 99940034
Handler_read_key 22468067595
Handler_read_last 176502
Handler_read_next 42266179773
Handler_read_prev 11070960
Handler_read_rnd 833609560
Handler_read_rnd_next 65838365195
Handler_rollback 3051
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 4506212354
Handler_write 8581870518
Innodb_buffer_pool_dump_status Dumping of buffer pool not started
Innodb_buffer_pool_load_status Buffer pool(s) load completed at 170614 10:53:39
Innodb_buffer_pool_resize_status
Innodb_buffer_pool_pages_data 1448113
Innodb_buffer_pool_bytes_data 23725883392
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_bytes_dirty 0
Innodb_buffer_pool_pages_flushed 164199463
Innodb_buffer_pool_pages_free 8193
Innodb_buffer_pool_pages_misc 51022
Innodb_buffer_pool_pages_total 1507328
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 8240959
Innodb_buffer_pool_read_ahead_evicted 22086
Innodb_buffer_pool_read_requests 200236540774
Innodb_buffer_pool_reads 59099652
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 28651069254
Innodb_data_fsyncs 29888982
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 1106363290112
Innodb_data_reads 67544700
Innodb_data_writes 184918769
Innodb_data_written 6316746196992
Innodb_dblwr_pages_written 144358814
Innodb_dblwr_writes 9226191
Innodb_log_waits 0
Innodb_log_write_requests 3312326087
Innodb_log_writes 8556931
Innodb_os_log_fsyncs 8829617
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 1254175159808
Innodb_page_size 16384
Innodb_pages_created 35560376
Innodb_pages_read 67528358
Innodb_pages_written 164651303
Innodb_row_lock_current_waits 2
Innodb_row_lock_time 77111830
Innodb_row_lock_time_avg 12046
Innodb_row_lock_time_max 51737
Innodb_row_lock_waits 6401
Innodb_rows_deleted 64700300
Innodb_rows_inserted 3487157917
Innodb_rows_read 111877049893
Innodb_rows_updated 3062478343
Innodb_num_open_files 2000
Innodb_truncated_status_writes 0
Innodb_available_undo_logs 128
Key_blocks_not_flushed 0
Key_blocks_unused 13390
Key_blocks_used 9
Key_read_requests 1554
Key_reads 205
Key_write_requests 0
Key_writes 0
Locked_connects 0
Max_execution_time_exceeded 0
Max_execution_time_set 0
Max_execution_time_set_failed 0
Max_used_connections 102
Max_used_connections_time 2017-07-01 07:20:11
Not_flushed_delayed_rows 0
Ongoing_anonymous_transaction_count 0
Open_files 21
Open_streams 0
Open_table_definitions 1400
Open_tables 1998
Opened_files 6064256
Opened_table_definitions 4332547
Opened_tables 6058803
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 0
Qcache_free_blocks 1
Qcache_free_memory 1031832
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
Qcache_not_cached 2505865045
Qcache_queries_in_cache 0
Qcache_total_blocks 1
Queries 3762718128
Questions 3758519160
Select_full_join 13520
Select_full_range_join 20
Select_range 60159
Select_range_check 0
Select_scan 113844656
Slave_open_temp_tables 0
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 180847
Sort_range 166605
Sort_rows 3131101807
Sort_scan 329131
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 Mar 5 21:54:04 2020 GMT
Ssl_server_not_before Mar 8 14:11:02 2016 GMT
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode Unknown
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 5560209
Table_locks_waited 0
Table_open_cache_hits 2621742819
Table_open_cache_misses 6058280
Table_open_cache_overflows 5334570
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 31
Threads_connected 4
Threads_created 24433
Threads_running 2
Uptime 8551874
Uptime_since_flush_status 8551874

353 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

[Executed: 9/21/17 10:21:01 AM GMT ] [Execution: 282/ms]

Options: ReplyQuote


Subject
Views
Written By
Posted
168
September 19, 2017 09:38AM
86
September 19, 2017 01:36PM
58
September 20, 2017 12:54AM
74
September 20, 2017 10:19AM
Re: Index exist on Deleted columns
60
September 21, 2017 04:21AM
74
September 21, 2017 09:27AM
72
September 22, 2017 05:12AM
80
September 22, 2017 09:04AM
72
September 24, 2017 07:14AM
75
September 24, 2017 11:13AM
54
September 24, 2017 09:29PM
62
September 24, 2017 09:55PM
71
September 25, 2017 12:25AM
63
September 25, 2017 11:03AM
60
September 26, 2017 05:30AM
71
September 26, 2017 12:12PM


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.