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
4509
November 12, 2014 07:41PM
2301
November 13, 2014 06:40PM
2072
November 14, 2014 03:02AM
1605
November 15, 2014 12:05AM
1645
November 16, 2014 07:39PM
1584
November 17, 2014 04:21PM
1680
November 17, 2014 07:48PM
1649
November 18, 2014 02:00AM
1714
November 18, 2014 11:35PM
1703
November 22, 2014 06:44AM
1572
November 18, 2014 01:13AM
2008
November 19, 2014 12:27AM
1721
November 19, 2014 06:01PM
1940
November 22, 2014 08:39AM
1875
November 14, 2014 03:04AM
Re: large InnoDB table partitioning without explicit PK
1908
November 14, 2014 03:05AM
1921
November 15, 2014 12:32AM
1935
November 16, 2014 08:31PM
1721
November 19, 2014 12:12AM
1633
November 22, 2014 09:07AM
1644
November 23, 2014 09:00PM
1815
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.