I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5.1 ) and run the same applications. In server A, the row number of the table ap_statistic is about 22512379, in server B, the row number of the table is 41438751. Of course the query running on server A is faster than server B, but what is strange is the query on server B is extreme slow, it takes more than 3 hours where in server A it just takes 10 minutes.
I use some tool to monitor system status and find that when the query is running in server A, system IO read speed is about 20~30M/s, but in server B it's 2~3M/s. I've tried to clean linux cache and restart mysql server, all is the same result. And I tried to restored DB from server B to server A, so the query in Server A is very very slow and io read speed is very slow. I want to know why this happen?
the ap_statistic table data in server A is generated by normally running and table data in server B is generated by a stored procedure. the table scheme is:
CREATE TABLE `ap_statistic` (
`ap_statisticId` BIGINT(20) UNSIGNED NOT NULL,
`deviceId` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`macaddress` VARCHAR(100) DEFAULT NULL,
`check_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`ap_count` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`wlan` VARCHAR(64) DEFAULT NULL,
`radio` VARCHAR(50) DEFAULT NULL,
`bssid` VARCHAR(32) DEFAULT NULL,
`zd_ap_name` VARCHAR(64) DEFAULT NULL,
`channel` INT(2) DEFAULT NULL,
`uplinkRSSI` INT(3) DEFAULT '0',
`downlinkRSSI` INT(3) DEFAULT '0',
`txBytes` BIGINT(20) DEFAULT '0',
`rxBytes` BIGINT(20) DEFAULT '0',
`txPkts` BIGINT(20) DEFAULT '0',
`rxPkts` BIGINT(20) DEFAULT '0',
`hops` INT(1) DEFAULT '0',
`numDownlink` INT(3) DEFAULT '0',
`distance` INT(4) DEFAULT '0',
`phyerr` INT(11) DEFAULT '0',
`max_num_clients` INT(3) DEFAULT '0',
`max_mesh_downlinks` INT(1) DEFAULT '0',
`airtime` INT(3) DEFAULT '0',
`uptimePercentage` INT(3) DEFAULT '0',
`total_num_clients` INT(3) DEFAULT '0',
`tx_actual_throughput` BIGINT(20) DEFAULT '0',
`rx_actual_throughput` BIGINT(20) DEFAULT '0',
`tunnelMode` VARCHAR(32) DEFAULT NULL,
`externalIp` VARCHAR(64) DEFAULT NULL,
`externalPort` VARCHAR(32) DEFAULT NULL,
`level` INT(1) DEFAULT '1'
`essid` VARCHAR(64) DEFAULT NULL,
`total_client_join` INT(11) DEFAULT '0',
PRIMARY KEY (`ap_statisticId`),
KEY `check_time` (`check_time`),
KEY `macaddress` (`macaddress`),
KEY `deviceId` (`deviceId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
the follows are the table file info and some outputs of the monitor tools
Server B
-rw-rw---- 1 mysql mysql 18568183808 Oct 11 14:52 ap_statistic.ibd
[root@localhost itms]# filefrag ./ap_statistic.ibd
./ap_statistic.ibd: 164 extents found, perfection would be 159 extents
TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT
------------ ---------- ---------- ------------ -------------- --------- ----------- -------- ------ ------ ---------- -------
ap_statistic 0 PRIMARY 1 ap_statisticId A 41438751 (NULL) (NULL) BTREE
ap_statistic 1 check_time 1 check_time A 10320 (NULL) (NULL) BTREE
ap_statistic 1 macaddress 1 macaddress A 16 (NULL) (NULL) YES BTREE
ap_statistic 1 deviceId 1 deviceId A 16 (NULL) (NULL) BTREE
mysql>show status;
Variable_name Value
Aborted_clients 0
Aborted_connects 0
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 1256
Bytes_sent 8844
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 0
Com_alter_tablespace 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_binlog 0
Com_call_procedure 0
Com_change_db 1
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
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 0
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 0
Com_rollback_to_savepoint 0
Com_savepoint 0
Com_select 1
Com_set_option 3
Com_show_authors 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
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 1
Com_show_create_trigger 0
Com_show_databases 0
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 1
Com_show_function_status 0
Com_show_grants 0
Com_show_keys 1
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 0
Com_show_profile 0
Com_show_profiles 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 21
Com_show_storage_engines 0
Com_show_table_status 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 0
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 0
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 ON
Connections 323
Created_tmp_disk_tables 1
Created_tmp_files 5
Created_tmp_tables 2
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 1
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 39
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 37
Innodb_buffer_pool_pages_data 43392
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 43822
Innodb_buffer_pool_pages_free 637198
Innodb_buffer_pool_pages_misc 562
Innodb_buffer_pool_pages_total 681152
Innodb_buffer_pool_read_ahead_rnd 9
Innodb_buffer_pool_read_ahead_seq 27
Innodb_buffer_pool_read_requests 36489397
Innodb_buffer_pool_reads 27421
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 4165371
Innodb_data_fsyncs 5228
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 1
Innodb_data_pending_writes 0
Innodb_data_read 626216960
Innodb_data_reads 36565
Innodb_data_writes 293947
Innodb_data_written 1792826880
Innodb_dblwr_pages_written 43822
Innodb_dblwr_writes 830
Innodb_log_waits 0
Innodb_log_write_requests 492588
Innodb_log_writes 268248
Innodb_os_log_fsyncs 2130
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 356559872
Innodb_page_size 16384
Innodb_pages_created 5304
Innodb_pages_read 38087
Innodb_pages_written 43822
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 28637
Innodb_rows_inserted 306449
Innodb_rows_read 16579740
Innodb_rows_updated 887251
Key_blocks_not_flushed 0
Key_blocks_unused 212928
Key_blocks_used 1415
Key_read_requests 393323
Key_reads 16
Key_write_requests 102461
Key_writes 102439
Last_query_cost 9142769.199000
Max_used_connections 19
Not_flushed_delayed_rows 0
Open_files 24
Open_streams 0
Open_table_definitions 142
Open_tables 146
Opened_files 592
Opened_table_definitions 0
Opened_tables 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 1578897
Questions 30
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 2
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
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 1549525
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 17
Threads_created 322
Threads_running 2
Uptime 8093
Uptime_since_flush_status 8093
mysql>show variables;
Variable_name Value
auto_increment_increment 1
auto_increment_offset 1
autocommit ON
automatic_sp_privileges ON
back_log 50
big_tables OFF
binlog_cache_size 32768
binlog_direct_non_transactional_updates OFF
binlog_format STATEMENT
bulk_insert_buffer_size 8388608
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci
completion_type 0
concurrent_insert 1
connect_timeout 10
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
div_precision_increment 4
engine_condition_pushdown ON
error_count 0
event_scheduler OFF
expire_logs_days 0
flush OFF
flush_time 0
foreign_key_checks ON
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
general_log OFF
group_concat_max_len 1024
have_community_features YES
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_geometry YES
have_innodb YES
have_ndbcluster NO
have_openssl DISABLED
have_partitioning NO
have_query_cache YES
have_rtree_keys YES
have_ssl DISABLED
have_symlink YES
hostname localhost.localdomain
identity 0
ignore_builtin_innodb OFF
init_connect
init_file
init_slave
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 67108864
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_size 11159994368
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table ON
innodb_flush_log_at_trx_commit 2
innodb_flush_method O_DIRECT
innodb_force_recovery 0
innodb_lock_wait_timeout 120
innodb_locks_unsafe_for_binlog ON
innodb_log_buffer_size 134217728
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_stats_on_metadata ON
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000
innodb_use_legacy_cardinality_algorithm ON
insert_id 0
interactive_timeout 28800
join_buffer_size 268435456
keep_files_on_create OFF
key_buffer_size 268435456
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
large_files_support ON
large_page_size 0
large_pages OFF
last_insert_id 0
lc_time_names en_US
license Commercial
local_infile ON
locked_in_memory OFF
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_bin_trust_routine_creators OFF
log_output FILE
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings 1
long_query_time 10.000000
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 1
max_allowed_packet 134217728
max_binlog_cache_size 18446744073709547520
max_binlog_size 1073741824
max_connect_errors 10
max_connections 300
max_delayed_threads 20
max_error_count 64
max_heap_table_size 268435456
max_insert_delayed_threads 20
max_join_size 18446744073709551615
max_length_for_sort_data 1024
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 18446744073709551615
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 18446744073709551615
min_examined_row_limit 0
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036853727232
myisam_mmap_size 18446744073709551615
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old OFF
old_alter_table OFF
old_passwords OFF
open_files_limit 10240
optimizer_prune_level 1
optimizer_search_depth 62
optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
pseudo_thread_id 18
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
rand_seed1
rand_seed2
range_alloc_block_size 4096
read_buffer_size 67108864
read_only OFF
read_rnd_buffer_size 67108864
relay_log
relay_log_index
relay_log_info_file relay-log.info
relay_log_purge ON
relay_log_space_limit 0
report_host
report_password
report_port 3306
report_user
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
server_id 0
skip_external_locking ON
skip_name_resolve ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_exec_mode STRICT
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
slow_query_log OFF
sort_buffer_size 16777216
sql_auto_is_null ON
sql_big_selects ON
sql_big_tables OFF
sql_buffer_result OFF
sql_log_bin ON
sql_log_off OFF
sql_log_update ON
sql_low_priority_updates OFF
sql_max_join_size 18446744073709551615
sql_mode
sql_notes ON
sql_quote_show_create ON
sql_safe_updates OFF
sql_select_limit 18446744073709551615
sql_slave_skip_counter
sql_warnings OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine MyISAM
sync_binlog 0
sync_frm ON
system_time_zone UTC
table_definition_cache 256
table_lock_wait_timeout 50
table_open_cache 512
table_type MyISAM
thread_cache_size 0
thread_handling one-thread-per-connection
thread_stack 262144
time_format %H:%i:%s
time_zone +08:00
timed_mutexes OFF
timestamp 1349946061
tmp_table_size 1073741824
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
unique_checks ON
updatable_views_with_limit YES
version 5.1.53-enterprise-commercial-pro
version_comment MySQL Enterprise Server - Pro Edition (Commercial)
version_compile_machine x86_64
version_compile_os unknown-linux-gnu
wait_timeout 28800
warning_count 0
mysql> show innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
121011 10:22:13 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3806, signal count 3778
Mutex spin waits 0, rounds 282892, OS waits 2075
RW-shared spins 1969, OS waits 864; RW-excl spins 2336, OS waits 749
------------
TRANSACTIONS
------------
Trx id counter 0 5303968
Purge done for trx's n:o < 0 5303951 undo n:o < 0 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 30336, OS thread id 1189509440
MySQL thread id 520, query id 1861594 localhost root
show innodb status
---TRANSACTION 0 5303967, not started, process no 30336, OS thread id 1188710720
MySQL thread id 526, query id 1861593 127.0.0.1 root
---TRANSACTION 0 5303962, not started, process no 30336, OS thread id 1186314560
MySQL thread id 519, query id 1861555 127.0.0.1 root
---TRANSACTION 0 5303952, not started, process no 30336, OS thread id 1188444480
MySQL thread id 515, query id 1861567 127.0.0.1 root
---TRANSACTION 0 5303948, not started, process no 30336, OS thread id 1187912000
MySQL thread id 516, query id 1861566 127.0.0.1 root
---TRANSACTION 0 5303937, not started, process no 30336, OS thread id 1190308160
MySQL thread id 511, query id 1861568 127.0.0.1 root
---TRANSACTION 0 0, not started, process no 30336, OS thread id 1090791744
MySQL thread id 18, query id 1596073 172.18.112.84 root
---TRANSACTION 0 5303959, ACTIVE 63 sec, process no 30336, OS thread id 1090525504 fetching rows, thread declared inside InnoDB 500
mysql tables in use 1, locked 0
MySQL thread id 17, query id 1861400 localhost root Sending data
select * from ap_statistic
Trx read view will not see trx with id >= 0 5303960, sees < 0 5303960
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
63521 OS file reads, 294656 OS file writes, 5641 OS fsyncs
1 pending preads, 0 pending pwrites
149.38 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 318, seg size 320,
63593 inserts, 63593 merged recs, 9674 merges
Hash table size 22086161, node heap has 607 buffer(s)
0.08 hash searches/s, 0.26 non-hash searches/s
---
LOG
---
Log sequence number 15 2873617336
Log flushed up to 15 2873617336
Last checkpoint at 15 2873617336
0 pending log writes, 0 pending chkp writes
269102 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 12452785320; in additional pool allocated 15261440
Dictionary memory allocated 789024
Buffer pool size 681152
Free buffers 610013
Database pages 70532
Modified db pages 0
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages read 65043, created 5488, written 45924
149.38 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 888 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 30336, id 1185782080, state: waiting for server activity
Number of rows inserted 336555, updated 1112311, deleted 28681, read 29200669
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 8258.58 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
iostat -dx 2
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 2.50 141.50 11.50 4516.00 112.00 2258.00 56.00 30.25 0.95 6.23 5.70 87.25
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 2.50 141.50 11.50 4516.00 112.00 2258.00 56.00 30.25 0.95 6.23 5.70 87.25
dm-0 0.00 0.00 141.50 14.00 4516.00 112.00 2258.00 56.00 29.76 0.97 6.24 5.62 87.35
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 3.00 0.00 154.50 0.00 4932.00 0.00 2466.00 0.00 31.92 0.93 6.04 6.04 93.25
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 3.00 0.00 154.50 0.00 4932.00 0.00 2466.00 0.00 31.92 0.93 6.04 6.04 93.25
dm-0 0.00 0.00 157.50 0.00 4932.00 0.00 2466.00 0.00 31.31 0.95 6.04 5.93 93.40
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 3.00 1.50 150.50 1.50 4804.00 24.00 2402.00 12.00 31.76 0.94 6.15 6.14 93.40
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 3.00 1.50 150.50 1.50 4804.00 24.00 2402.00 12.00 31.76 0.94 6.15 6.14 93.40
dm-0 0.00 0.00 153.50 3.00 4804.00 24.00 2402.00 12.00 30.85 0.95 6.08 5.97 93.50
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
vmstat 2
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 1 16 27358488 18152 115500 0 0 2558 0 1193 8634 14 1 73 12 0
1 1 16 27346840 18168 115500 0 0 2356 12 1174 9129 14 2 73 12 0
2 1 16 27334320 18172 115504 0 0 2522 0 1184 8918 14 1 73 12 0
1 1 16 27321812 18180 115496 0 0 2456 12 1183 7357 13 1 74 12 0
1 1 16 27310132 18180 115504 0 0 2344 12 1174 6990 12 2 74 12 0
1 1 16 27297608 18184 115508 0 0 2506 0 1182 6203 12 2 74 11 0
1 1 16 27285444 18200 115504 0 0 2420 90 1187 9667 13 2 73 12 0
1 1 16 27277640 18200 115508 0 0 2248 0 1165 8103 19 2 69 11 0
2 1 16 27265380 18204 115504 0 0 2498 0 1179 5690 13 1 74 12 0
4 1 16 27252972 18216 115508 0 0 2434 12 1178 6096 14 1 74 12 0
1 1 16 27241032 18232 115496 0 0 2520 0 1181 9252 12 1 75 11 0
2 1 16 27229136 18240 115508 0 0 2468 10 1178 7116 13 1 74 12 0
1 0 16 27630612 18248 115508 0 0 1536 20 1121 4082 13 1 79 7 0
mpstat -P ALL 2
02:48:57 PM CPU %user %nice %system %iowait %irq %soft %idle intr/s
02:48:59 PM all 13.69 0.00 1.31 11.56 0.00 0.62 72.81 1190.95
02:48:59 PM 0 33.67 0.00 0.50 0.00 0.00 0.00 65.83 1006.03
02:48:59 PM 1 6.53 0.00 0.50 92.96 0.50 0.50 0.00 160.80
02:48:59 PM 2 1.01 0.00 0.50 0.00 0.00 0.00 98.49 0.00
02:48:59 PM 3 0.00 0.00 0.00 0.00 0.00 0.00 100.50 3.52
02:48:59 PM 4 35.68 0.00 1.01 0.00 0.00 1.01 62.81 13.57
02:48:59 PM 5 4.52 0.00 0.00 0.00 0.00 0.00 96.48 0.50
02:48:59 PM 6 3.52 0.00 0.00 0.00 0.00 0.00 96.98 0.50
02:48:59 PM 7 25.13 0.00 7.54 0.00 0.00 4.02 63.82 6.03
02:48:59 PM CPU %user %nice %system %iowait %irq %soft %idle intr/s
02:49:01 PM all 12.50 0.00 1.19 11.69 0.00 0.56 74.06 1177.11
02:49:01 PM 0 22.89 0.00 1.49 0.00 0.00 1.49 74.13 995.52
02:49:01 PM 1 5.97 0.00 0.50 92.54 0.00 0.50 0.00 159.70
02:49:01 PM 2 0.50 0.00 0.50 0.50 0.00 0.00 98.01 1.99
02:49:01 PM 3 0.00 0.00 0.00 0.00 0.00 0.00 99.50 2.49
02:49:01 PM 4 45.77 0.00 1.49 0.00 0.00 0.50 51.24 11.94
02:49:01 PM 5 0.00 0.00 0.00 0.00 0.00 0.00 99.50 0.50
02:49:01 PM 6 0.50 0.00 0.00 0.00 0.00 0.00 99.00 0.50
02:49:01 PM 7 23.38 0.00 5.47 0.00 0.00 1.99 68.16 4.48
02:49:01 PM CPU %user %nice %system %iowait %irq %soft %idle intr/s
02:49:03 PM all 13.05 0.00 1.12 11.62 0.00 0.50 73.70 1179.00
02:49:03 PM 0 43.50 0.00 0.50 0.00 0.00 0.00 56.00 1000.50
02:49:03 PM 1 6.50 0.00 1.00 93.00 0.00 0.50 0.00 157.00
02:49:03 PM 2 1.50 0.00 0.50 0.00 0.00 0.00 98.50 0.00
02:49:03 PM 3 0.00 0.00 0.00 0.00 0.00 0.00 100.00 2.50
02:49:03 PM 4 32.50 0.00 1.50 0.00 0.00 1.00 65.50 13.00
02:49:03 PM 5 11.00 0.00 4.00 0.00 0.00 1.50 83.50 0.50
02:49:03 PM 6 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
02:49:03 PM 7 10.50 0.00 2.00 0.00 0.00 1.00 87.00 5.50
Best Answer
Run
OPTIMIZE TABLEon your tables to get them into a fresh state.If your table is fragmented the system IO read speed gets really slow.