2011-07-12 16:11:00 mysql> show global status; +------------------------------------------+----------------------+ | Variable_name | Value | +------------------------------------------+----------------------+ | Aborted_clients | 2 | | 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 | 1426334076 | | Bytes_sent | 188373706 | | Com_admin_commands | 158 | | 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 | 490 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_begin | 420976 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 107 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 420976 | | Com_create_db | 98 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 784 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 0 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 3 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 1 | | 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 | 8 | | Com_execute_sql | 0 | | Com_flush | 6 | | Com_grant | 6 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 1852969 | | Com_insert_select | 0 | | Com_install_plugin | 0 | | Com_kill | 0 | | Com_load | 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_resignal | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_rollback_to_savepoint | 0 | | Com_savepoint | 0 | | Com_select | 70658 | | Com_set_option | 95970 | | Com_signal | 0 | | Com_show_authors | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 3 | | 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 | 14 | | Com_show_create_trigger | 0 | | Com_show_databases | 7 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 287 | | Com_show_events | 15 | | Com_show_errors | 0 | | Com_show_fields | 0 | | Com_show_function_status | 15 | | Com_show_grants | 0 | | Com_show_keys | 0 | | Com_show_master_status | 0 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 15 | | Com_show_processlist | 289 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_relaylog_events | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 287 | | Com_show_status | 582 | | Com_show_storage_engines | 3 | | Com_show_table_status | 27 | | Com_show_tables | 1 | | Com_show_triggers | 15 | | Com_show_variables | 289 | | Com_show_warnings | 0 | | 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 | 1 | | 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 | 51582 | | Created_tmp_disk_tables | 770 | | Created_tmp_files | 1001 | | Created_tmp_tables | 55286 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 6 | | Handler_commit | 2345362 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 33040 | | Handler_read_key | 43629361 | | Handler_read_last | 602 | | Handler_read_next | 16200058 | | Handler_read_prev | 136905 | | Handler_read_rnd | 1394533 | | Handler_read_rnd_next | 65815745 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 11198007 | | Handler_write | 3447551 | | Innodb_buffer_pool_pages_data | 11593 | | Innodb_buffer_pool_pages_dirty | 1349 | | Innodb_buffer_pool_pages_flushed | 3477554 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 18446744073709548214 | | Innodb_buffer_pool_pages_total | 8191 | | Innodb_buffer_pool_read_ahead | 259157 | | Innodb_buffer_pool_read_ahead_evicted | 326930 | | Innodb_buffer_pool_read_requests | 213886139 | | Innodb_buffer_pool_reads | 1732773 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 12213786 | | Innodb_data_fsyncs | 851943 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 19720040448 | | Innodb_data_reads | 2110464 | | Innodb_data_writes | 2345032 | | Innodb_data_written | 49821940224 | | Innodb_dblwr_pages_written | 1738777 | | Innodb_dblwr_writes | 64444 | | Innodb_have_atomic_builtins | ON | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 2958830 | | Innodb_log_writes | 513278 | | Innodb_os_log_fsyncs | 522649 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 1567484928 | | Innodb_page_size | 16384 | | Innodb_pages_created | 31536 | | Innodb_pages_read | 2076171 | | Innodb_pages_written | 1738777 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 433104 | | Innodb_row_lock_time_avg | 5 | | Innodb_row_lock_time_max | 20523 | | Innodb_row_lock_waits | 78145 | | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 527926 | | Innodb_rows_read | 95742459 | | Innodb_rows_updated | 329046 | | Innodb_truncated_status_writes | 0 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 6698 | | Key_blocks_used | 46 | | Key_read_requests | 544081 | | Key_reads | 1 | | Key_write_requests | 22115 | | Key_writes | 0 | | Last_query_cost | 0.000000 | | Max_used_connections | 175 | | Not_flushed_delayed_rows | 0 | | Open_files | 1 | | Open_streams | 0 | | Open_table_definitions | 400 | | Open_tables | 400 | | Opened_files | 18698 | | Opened_table_definitions | 4927 | | Opened_tables | 8260 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 | | Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_table_handles_lost | 0 | | Performance_schema_table_instances_lost | 0 | | Performance_schema_thread_classes_lost | 0 | | Performance_schema_thread_instances_lost | 0 | | 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 | 2916105 | | Questions | 2916080 | | Rpl_status | AUTH_MASTER | | Select_full_join | 0 | | Select_full_range_join | 9 | | Select_range | 56686 | | Select_range_check | 0 | | Select_scan | 13951 | | Slave_heartbeat_period | 0.000 | | Slave_open_temp_tables | 0 | | Slave_received_heartbeats | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 21 | | Sort_merge_passes | 1 | | Sort_range | 120 | | Sort_rows | 1016169 | | Sort_scan | 53509 | | 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 | 114796910 | | Table_locks_waited | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 0 | | Threads_connected | 88 | | Threads_created | 51580 | | Threads_running | 1 | | Uptime | 86201 | | Uptime_since_flush_status | 86201 | +------------------------------------------+----------------------+ 309 rows in set (0.00 sec) mysql> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 110712 15:12:54 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 50 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 82114 1_second, 82093 sleeps, 8083 10_second, 2639 background, 2638 flush srv_master_thread log flush and writes: 82654 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 95656, signal count 775626 Mutex spin waits 1815964, rounds 5801017, OS waits 32159 RW-shared spins 176872, rounds 1292380, OS waits 28005 RW-excl spins 231746, rounds 2233352, OS waits 29799 Spin rounds per wait: 3.19 mutex, 7.31 RW-shared, 9.64 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 26852B11 Purge done for trx's n:o < 26852ADA undo n:o < 0 History list length 4018 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 51580, query id 2918869 localhost thomas.parrott show engine innodb status ---TRANSACTION 26852A87, not started MySQL thread id 51650, query id 2918543 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852ADF, not started MySQL thread id 51649, query id 2918745 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852A0D, not started MySQL thread id 51648, query id 2918244 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852AEC, not started MySQL thread id 51647, query id 2918772 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852AEB, not started MySQL thread id 51646, query id 2918768 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268529DD, not started MySQL thread id 51644, query id 2918116 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268529D8, not started MySQL thread id 51643, query id 2918098 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268529D7, not started MySQL thread id 51642, query id 2918096 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852A23, not started MySQL thread id 51641, query id 2918296 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852991, not started MySQL thread id 51640, query id 2917919 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852990, not started MySQL thread id 51639, query id 2917915 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852A96, not started MySQL thread id 51638, query id 2918578 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 2685295A, not started MySQL thread id 51637, query id 2917769 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 2685297E, not started MySQL thread id 51636, query id 2917865 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852AE5, not started MySQL thread id 51635, query id 2918755 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852B01, not started MySQL thread id 51634, query id 2918831 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852ACC, not started MySQL thread id 51633, query id 2918699 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852B0F, not started MySQL thread id 51632, query id 2918864 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852AF9, not started MySQL thread id 51631, query id 2918809 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852AD1, not started MySQL thread id 51630, query id 2918713 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852A6B, not started MySQL thread id 51629, query id 2918480 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852A8B, not started MySQL thread id 51628, query id 2918547 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852A63, not started MySQL thread id 51627, query id 2918455 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852AB5, not started MySQL thread id 51626, query id 2918649 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852B10, not started MySQL thread id 51625, query id 2918868 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852A60, not started MySQL thread id 51624, query id 2918448 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 2685291C, not started MySQL thread id 51623, query id 2917575 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852AFE, not started MySQL thread id 51622, query id 2918823 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852B09, not started MySQL thread id 51621, query id 2918847 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268529C0, not started MySQL thread id 51619, query id 2918034 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268529BA, not started MySQL thread id 51620, query id 2918019 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852B0D, not started MySQL thread id 51618, query id 2918860 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852504, not started MySQL thread id 51578, query id 2916013 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268524F7, not started MySQL thread id 51577, query id 2915982 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268524F4, not started MySQL thread id 51575, query id 2915969 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268524CD, not started MySQL thread id 51573, query id 2915839 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268523B8, not started MySQL thread id 51564, query id 2915193 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 268524B1, not started MySQL thread id 51520, query id 2915797 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26851C17, not started MySQL thread id 51473, query id 2911261 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26851B9B, not started MySQL thread id 51467, query id 2910952 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 26852AFC, not started MySQL thread id 51464, query id 2918815 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 2685298F, not started MySQL thread id 51433, query id 2917904 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 2685257B, not started MySQL thread id 51426, query id 2916303 rps-coredb01.infinity.local 172.16.5.15 ict-import ---TRANSACTION 2684B66C, not started MySQL thread id 50225, query id 2867199 rps-build01.infinity.local 172.16.5.8 rob.norman -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 2111290 OS file reads, 2347877 OS file writes, 852927 OS fsyncs 18.76 reads/s, 8864 avg bytes/read, 57.58 writes/s, 20.12 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 95, seg size 97, 87687 merges merged operations: insert 149398, delete mark 9049, delete 8 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 13 buffer(s) 263.75 hash searches/s, 430.25 non-hash searches/s --- LOG --- Log sequence number 186936666807 Log flushed up to 186936666807 Last checkpoint at 186935724021 0 pending log writes, 0 pending chkp writes 523322 log i/o's done, 11.66 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 325743851 Buffer pool size 8191 Free buffers 0 Database pages 11616 Old database pages 4267 Modified db pages 797 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4022179, not young 0 54.74 youngs/s, 0.00 non-youngs/s Pages read 2076997, created 31571, written 1740974 18.76 reads/s, 0.72 creates/s, 44.22 writes/s Buffer pool hit rate 993 / 1000, young-making rate 23 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 6.44/s LRU len: 11616, unzip_LRU len: 1162 I/O sum[5047]:cur[6], unzip sum[5842]:cur[5] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 15490, id 1204738368, state: sleeping Number of rows inserted 528476, updated 329416, deleted 0, read 95761434 11.50 inserts/s, 7.66 updates/s, 0.00 deletes/s, 382.33 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ free total used free shared buffers cached Mem: 3145728 1748600 1397128 0 0 0 -/+ buffers/cache: 1748600 1397128 Swap: 0 0 0 vmstat 1 10 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 0 1 0 1397184 0 0 0 0 15 136 0 2 1 0 96 2 0 0 3 0 1397184 0 0 0 0 16 304 0 504 1 0 86 14 0 0 2 0 1397184 0 0 0 0 0 100 0 237 0 0 87 13 0 0 2 0 1397188 0 0 0 0 32 320 0 610 1 0 85 14 0 0 1 0 1397188 0 0 0 0 0 136 0 325 3 0 85 12 0 0 3 0 1397188 0 0 0 0 8 652 0 508 1 0 87 13 0 0 0 0 1397188 0 0 0 0 8 92 0 284 0 0 93 6 0 0 2 0 1397188 0 0 0 0 0 248 0 492 1 0 93 6 0 0 2 0 1397188 0 0 0 0 0 132 0 291 0 0 87 12 0 0 1 0 1397188 0 0 0 0 0 304 0 544 1 0 86 13 0