Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
What storage are the servers using? Is one local disk and the other NAS/SAN? –  Phil Oct 11 '12 at 11:43
    
Could you use pastebin (or the like) for linking huge logs instead of pasting them here? –  dezso Oct 11 '12 at 11:46
    
What does Show Full Processlist shows while executing this query on Server, mention query state? –  Mahesh Patil Oct 11 '12 at 12:23
1  
You say the servers have roughly the same hardware and software configuration. How close are they? Is the RAM the same speed, are both CPU's the same speed? Are they from the same hardware vendor? Do they have the same FSB? Is server B running anything other than MySQL? Have you tried running mysqltuner.pl? –  Mark D Nov 8 '12 at 2:13
1  
Why in the world would you need to query (and transport on the network) 40 mill rows (and 12 gb of data) at once? This yells bad design (and that is not about that SELECT *, but raises the alarm too), not only IO problems. –  Marian Feb 20 '13 at 8:51

1 Answer 1

Run OPTIMIZE TABLE on your tables to get them into a fresh state.

If your table is fragmented the system IO read speed gets really slow.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.