Re: large InnoDB table partitioning without explicit PK
Posted by:
Miko M
Date: November 14, 2014 03:05AM
MariaDB [(none)]> show global status;
+---------------------------------------+---------------+
| Variable_name | Value |
+---------------------------------------+---------------+
| Aborted_clients | 39 |
| Aborted_connects | 7 |
| Access_denied_errors | 7 |
| Aria_pagecache_blocks_not_flushed | 0 |
| Aria_pagecache_blocks_unused | 15736 |
| Aria_pagecache_blocks_used | 15736 |
| Aria_pagecache_read_requests | 57167611 |
| Aria_pagecache_reads | 297059 |
| Aria_pagecache_write_requests | 13086940 |
| Aria_pagecache_writes | 425775 |
| Aria_transaction_log_syncs | 18 |
| Binlog_bytes_written | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Busy_time | 0.000000 |
| Bytes_received | 1028886898925 |
| Bytes_sent | 378232440749 |
| Com_admin_commands | 2217 |
| 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 | 5089 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 22762 |
| Com_change_db | 72 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 2 |
| 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 | 22782 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 2 |
| 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 | 22808 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1881655938 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 28 |
| 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 | 22808 |
| 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 | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 2631416 |
| Com_set_option | 1257979 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_client_statistics | 0 |
| Com_show_collations | 308785 |
| 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 | 6 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 82 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 3 |
| Com_show_events | 10 |
| Com_show_errors | 0 |
| Com_show_fields | 384 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 1 |
| Com_show_index_statistics | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 518 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_statistics | 0 |
| Com_show_table_status | 4 |
| Com_show_tables | 117 |
| Com_show_triggers | 0 |
| Com_show_user_statistics | 0 |
| Com_show_variables | 308797 |
| Com_show_warnings | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 22782 |
| Com_stmt_execute | 22808 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 22808 |
| 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 | 0 |
| Com_update_multi | 15188 |
| 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 | 454305 |
| Cpu_time | 0.000000 |
| Created_tmp_disk_tables | 15205 |
| Created_tmp_files | 5089 |
| Created_tmp_tables | 814996 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Empty_queries | 78494 |
| Flush_commands | 1 |
| Handler_commit | 1880849465 |
| Handler_delete | 2 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 919141 |
| Handler_read_key | 7301513434 |
| Handler_read_next | 1252369305810 |
| Handler_read_prev | 366357702 |
| Handler_read_rnd | 1213626 |
| Handler_read_rnd_next | 342974078 |
| Handler_rollback | 965608 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 7282810139 |
| Handler_write | 3825949873 |
| Innodb_buffer_pool_pages_data | 1881386 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 34084311 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_misc | 215765 |
| Innodb_buffer_pool_pages_total | 2097151 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 1579244 |
| Innodb_buffer_pool_read_ahead_evicted | 25285 |
| Innodb_buffer_pool_read_requests | 1308132126313 |
| Innodb_buffer_pool_reads | 3732307 |
| Innodb_buffer_pool_wait_free | 7 |
| Innodb_buffer_pool_write_requests | 11754841467 |
| Innodb_data_fsyncs | 1728224 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 87027044352 |
| Innodb_data_reads | 3759062 |
| Innodb_data_writes | 5083562 |
| Innodb_data_written | 1828452827648 |
| Innodb_dblwr_pages_written | 34084311 |
| Innodb_deadlocks | 0 |
| Innodb_dblwr_writes | 355280 |
| Innodb_dict_tables | 278 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 1446406008 |
| Innodb_log_writes | 428337 |
| Innodb_os_log_fsyncs | 591101 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 711494432768 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 30384454 |
| Innodb_pages_read | 5311575 |
| Innodb_pages_written | 34084311 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 1880672435 |
| Innodb_rows_read | 1252509907626 |
| Innodb_rows_updated | 30376 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 205 |
| Key_blocks_used | 4 |
| Key_blocks_warm | 0 |
| Key_read_requests | 111460 |
| Key_reads | 4 |
| Key_write_requests | 12 |
| Key_writes | 8 |
| Last_query_cost | 0.000000 |
| Maria_pagecache_blocks_not_flushed | 0 |
| Maria_pagecache_blocks_unused | 15736 |
| Maria_pagecache_blocks_used | 15736 |
| Maria_pagecache_read_requests | 57167611 |
| Maria_pagecache_reads | 297059 |
| Maria_pagecache_write_requests | 13086940 |
| Maria_pagecache_writes | 425775 |
| Maria_transaction_log_syncs | 18 |
| Max_used_connections | 35 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 24 |
| Open_streams | 0 |
| Open_table_definitions | 20 |
| Open_tables | 22 |
| Opened_files | 110890 |
| Opened_table_definitions | 5109 |
| Opened_tables | 14004 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 8371240 |
| Qcache_hits | 5 |
| Qcache_inserts | 115 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2631224 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Queries | 1886927178 |
| Questions | 1886542538 |
| Rows_read | 1236650152620 |
| Rows_sent | 1997307528 |
| Rpl_status | NULL |
| Select_full_join | 15188 |
| Select_full_range_join | 0 |
| Select_range | 114123 |
| Select_range_check | 0 |
| Select_scan | 693434 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 6960 |
| Sort_merge_passes | 7 |
| Sort_range | 6 |
| Sort_rows | 1229862 |
| Sort_scan | 120102 |
| 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 | |
| Syncs | 48152 |
| Table_locks_immediate | 221565221010 |
| Table_locks_waited | 108 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 4096 |
| Tc_log_page_waits | 0 |
| Threads_cached | 10 |
| Threads_connected | 7 |
| Threads_created | 46 |
| Threads_running | 2 |
| Uptime | 4556154 |
| Uptime_since_flush_status | 4556154 |
+---------------------------------------+---------------+
Subject
Views
Written By
Posted
4942
November 12, 2014 07:41PM
2462
November 13, 2014 06:40PM
2297
November 14, 2014 03:02AM
1737
November 15, 2014 12:05AM
1767
November 16, 2014 07:39PM
1737
November 17, 2014 04:21PM
1788
November 17, 2014 07:48PM
1770
November 18, 2014 02:00AM
1845
November 18, 2014 11:35PM
1830
November 22, 2014 06:44AM
1706
November 18, 2014 01:13AM
2158
November 19, 2014 12:27AM
1924
November 19, 2014 06:01PM
2063
November 22, 2014 08:39AM
2017
November 14, 2014 03:04AM
Re: large InnoDB table partitioning without explicit PK
2056
November 14, 2014 03:05AM
2052
November 15, 2014 12:32AM
2088
November 16, 2014 08:31PM
1853
November 19, 2014 12:12AM
1749
November 22, 2014 09:07AM
1781
November 23, 2014 09:00PM
1939
December 01, 2014 11:16AM
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.