Announcement

Announcement Module
Collapse
No announcement yet.

simple performance question

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • simple performance question

    I have a table with ~1.4 billion records. I want to check how many records are exactly there.
    I have a Percona 5.5 server running on Ubuntu 12.04. The server has 80 GB of memory.
    The table itself is ~100 GB.
    I issue a simple command

    Select count(*) from table

    I then, monitor the INNODB engine. I get a reading of 35-45,000 reads/second
    If my calculations are correct, it will need 9-18 hours just to do this simple command.
    I thought that using databases would improve the speed of handling vast amounts of data. Is this realistic? Can I make my server faster?
    I used the Percona my.cnf wizard to set all my variables.

    Any help would appreciated.

    Csaba

  • #2
    I am trying to execute this statement


    select `Distance`, count(`Distance`) from `hamming_q22_sd6_11` group by `Distance`


    My table has approximately 300 million rows.
    When I look at the process list it says

    copying to tmp table

    and it stays like that for hours. Something is not right. These things should take seconds not hours. I am really desperate, what am I doing wrong?

    Csaba

    Comment


    • #3
      Can you post the output of:

      SHOW ENGINE INNODB STATUS\G
      SHOW GLOBAL VARIABLES;
      SHOW GLOBAL STATUS;

      please describe the hardware you are using.

      Can you also post the EXPLAIN output for the affected queries as well as the schema for involved tables.

      Comment


      • #4
        I am sorry for the long post.
        Here are my variables

        INNODB Engine

        =====================================120827 9:11:30 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 15 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 46 1_second, 46 sleeps, 4 10_second, 6 background, 6 flushsrv_master_thread log flush and writes: 282----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 7379, signal count 7385Mutex spin waits 222, rounds 1677, OS waits 37RW-shared spins 7414, rounds 219974, OS waits 7261RW-excl spins 79, rounds 2365, OS waits 78Spin rounds per wait: 7.55 mutex, 29.67 RW-shared, 29.94 RW-excl--------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: 0Pending flushes (fsync) log: 0; buffer pool: 0440273 OS file reads, 47 OS file writes, 20 OS fsyncs1 pending preads, 0 pending pwrites144.92 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 0, seg size 2, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 144498467, node heap has 1 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 1442779337294Log flushed up to 1442779337294Last checkpoint at 1442779337294Max checkpoint age 869019772Checkpoint age target 841862905Modified age 0Checkpoint age 00 pending log writes, 0 pending chkp writes21 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 75010932736; in additional pool allocated 0Internal hash tables (constant factor + variable factor) Adaptive hash index 1156008336 (1155987736 + 20600) Page hash 72250072 (buffer pool 0 only) Dictionary cache 289515676 (288998768 + 516908) File system 102472 (82672 + 19800) Lock system 180624648 (180623896 + 752) Recovery system 0 (0 + 0)Dictionary memory allocated 516908Buffer pool size 4456447Buffer pool size, bytes 73014427648Free buffers 4016207Database pages 440239Old database pages 162530Modified db pages 0Pending reads 1Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 440237, created 1, written 29144.92 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 971 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 440239, unzip_LRU len: 0I/O sum[0]:cur[11], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue2 read views open inside InnoDB---OLDEST VIEW---Normal read viewRead view low limit trx n 1CD6A3ARead view up limit trx id 1CD6A3ARead view low limit trx id 1CD6A3ARead view individually stored trx ids:-----------------Main thread process no. 4871, id 140149481813760, state: flushing logNumber of rows inserted 0, updated 8, deleted 0, read 2379223000.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 37559.63 reads/s------------TRANSACTIONS------------Trx id counter 1CD6A3BPurge done for trx's n < 1CD6A37 undo n < 0History list length 1201LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 25, OS thread handle 0x7f88f45bf700, query id 773 localhost 127.0.0.1 rootSHOW ENGINE INNODB STATUS---TRANSACTION 1CD6A3A, ACTIVE 41 sec fetching rowsmysql tables in use 1, locked 0MySQL thread id 37, OS thread handle 0x7f88f4683700, query id 747 localhost root Copying to tmp tableSELECT `Distance`, count(*) FROM `hamming_q22_sd6_12` group by `Distance` LIMIT 0, 30Trx read view will not see trx with id >= 1CD6A3B, sees < 1CD6A3B----------------------------END OF INNODB MONITOR OUTPUT============================


        Global Variables:


        auto_increment_increment 1auto_increment_offset 1autocommit ONautomatic_sp_privileges ONback_log 50basedir /usrbig_tables OFFbinlog_cache_size 32768binlog_direct_non_transactional_updates OFFbinlog_format STATEMENTbinlog_stmt_cache_size 32768bulk_insert_buffer_size 8388608character_set_client latin1character_set_connection latin1character_set_database latin1character_set_filesystem binarycharacter_set_results latin1character_set_server latin1character_set_system utf8character_sets_dir /usr/share/mysql/charsets/collation_connection latin1_swedish_cicollation_database latin1_swedish_cicollation_server latin1_swedish_cicompletion_type NO_CHAINconcurrent_insert AUTOconnect_timeout 10datadir /var/lib/mysql/date_format %Y-%m-%ddatetime_format %Y-%m-%d %H:%i:%sdefault_storage_engine InnoDBdefault_week_format 0delay_key_write ONdelayed_insert_limit 100delayed_insert_timeout 300delayed_queue_size 1000div_precision_increment 4enforce_storage_engine engine_condition_pushdown ONevent_scheduler OFFexpand_fast_index_creation OFFexpire_logs_days 10fast_index_creation ONflush OFFflush_time 0foreign_key_checks ONft_boolean_syntax + -><()~*:""&|ft_max_word_len 84ft_min_word_len 4ft_query_expansion_limit 20ft_stopword_file (built-in)general_log OFFgeneral_log_file /var/lib/mysql/toto.loggroup_concat_max_len 1024have_compress YEShave_crypt YEShave_csv YEShave_dynamic_loading YEShave_geometry YEShave_innodb YEShave_ndbcluster NOhave_openssl DISABLEDhave_partitioning YEShave_profiling YEShave_query_cache YEShave_response_time_distribution YEShave_rtree_keys YEShave_ssl DISABLEDhave_symlink YEShostname totoignore_builtin_innodb OFFinit_connect init_file init_slave innodb_adaptive_flushing ONinnodb_adaptive_flushing_method estimateinnodb_adaptive_hash_index ONinnodb_adaptive_hash_index_partitions 1innodb_additional_mem_pool_size 8388608innodb_autoextend_increment 8innodb_autoinc_lock_mode 1innodb_blocking_buffer_pool_restore OFFinnodb_buffer_pool_instances 1innodb_buffer_pool_restore_at_startup 0innodb_buffer_pool_shm_checksum ONinnodb_buffer_pool_shm_key 0innodb_buffer_pool_size 73014444032innodb_change_buffering allinnodb_checkpoint_age_target 0innodb_checksums ONinnodb_commit_concurrency 0innodb_concurrency_tickets 500innodb_corrupt_table_action assertinnodb_data_file_path ibdata1:10M:autoextendinnodb_data_home_dir innodb_dict_size_limit 0innodb_doublewrite ONinnodb_doublewrite_file innodb_fake_changes OFFinnodb_fast_checksum OFFinnodb_fast_shutdown 1innodb_file_format Antelopeinnodb_file_format_check ONinnodb_file_format_max Antelopeinnodb_file_per_table ONinnodb_flush_log_at_trx_commit 1innodb_flush_method O_DIRECTinnodb_flush_neighbor_pages areainnodb_force_load_corrupted OFFinnodb_force_recovery 0innodb_ibuf_accel_rate 100innodb_ibuf_active_contract 1innodb_ibuf_max_size 36507205632innodb_import_table_from_xtrabackup 0innodb_io_capacity 200innodb_kill_idle_transaction 0innodb_large_prefix OFFinnodb_lazy_drop_table 0innodb_lock_wait_timeout 50innodb_locks_unsafe_for_binlog OFFinnodb_log_block_size 512innodb_log_buffer_size 8388608innodb_log_file_size 536870912innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct 75innodb_max_purge_lag 0innodb_merge_sort_block_size 1048576innodb_mirrored_log_groups 1innodb_old_blocks_pct 37innodb_old_blocks_time 0innodb_open_files 300innodb_page_size 16384innodb_purge_batch_size 20innodb_purge_threads 1innodb_random_read_ahead OFFinnodb_read_ahead linearinnodb_read_ahead_threshold 56innodb_read_io_threads 4innodb_recovery_stats OFFinnodb_recovery_update_relay_log OFFinnodb_replication_delay 0innodb_rollback_on_timeout OFFinnodb_rollback_segments 128innodb_show_locks_held 10innodb_show_verbose_locks 0innodb_spin_wait_delay 6innodb_stats_auto_update 1innodb_stats_method nulls_equalinnodb_stats_on_metadata ONinnodb_stats_sample_pages 8innodb_stats_update_need_lock 1innodb_strict_mode OFFinnodb_support_xa ONinnodb_sync_spin_loops 30innodb_table_locks ONinnodb_thread_concurrency 0innodb_thread_concurrency_timer_based OFFinnodb_thread_sleep_delay 10000innodb_use_global_flush_log_at_trx_commit ONinnodb_use_native_aio ONinnodb_use_sys_malloc ONinnodb_use_sys_stats_table OFFinnodb_version 1.1.8-rel28.0innodb_write_io_threads 4interactive_timeout 28800join_buffer_size 131072keep_files_on_create OFFkey_buffer_size 33554432key_cache_age_threshold 300key_cache_block_size 1024key_cache_division_limit 100large_files_support ONlarge_page_size 0large_pages OFFlc_messages en_USlc_messages_dir /usr/share/mysql/lc_time_names en_USlicense GPLlocal_infile ONlock_wait_timeout 31536000locked_in_memory OFFlog OFFlog_bin OFFlog_bin_trust_function_creators OFFlog_error /var/log/mysql/error.loglog_output FILElog_queries_not_using_indexes ONlog_slave_updates OFFlog_slow_admin_statements OFFlog_slow_filter log_slow_queries OFFlog_slow_rate_limit 1log_slow_rate_type sessionlog_slow_slave_statements OFFlog_slow_sp_statements ONlog_slow_verbosity log_warnings 1log_warnings_suppress long_query_time 10.000000low_priority_updates OFFlower_case_file_system OFFlower_case_table_names 0max_allowed_packet 16777216max_binlog_cache_size 18446744073709547520max_binlog_size 1048576000max_binlog_stmt_cache_size 18446744073709547520max_connect_errors 1000000max_connections 100max_delayed_threads 20max_error_count 64max_heap_table_size 33554432max_insert_delayed_threads 20max_join_size 18446744073709551615max_length_for_sort_data 1024max_long_data_size 16777216max_prepared_stmt_count 16382max_relay_log_size 0max_seeks_for_key 18446744073709551615max_sort_length 1024max_sp_recursion_depth 0max_tmp_tables 32max_user_connections 0max_write_lock_count 18446744073709551615metadata_locks_cache_size 1024min_examined_row_limit 0multi_range_count 256myisam_data_pointer_size 6myisam_max_sort_file_size 9223372036853727232myisam_mmap_size 18446744073709551615myisam_recover_options BACKUP,FORCEmyisam_repair_threads 1myisam_sort_buffer_size 8388608myisam_stats_method nulls_unequalmyisam_use_mmap OFFnet_buffer_length 16384net_read_timeout 30net_retry_count 10net_write_timeout 60new OFFold OFFold_alter_table OFFold_passwords OFFopen_files_limit 8302optimizer_fix ONoptimizer_prune_level 1optimizer_search_depth 62optimizer_switch index_merge=on,index_merge_union=on,index_merge_so rt_union=on,index_merge_intersection=on,engine_con dition_pushdown=onperformance_schema OFFperformance_schema_events_waits_history_long_si ze 10000performance_schema_events_waits_history_size 10performance_schema_max_cond_classes 80performance_schema_max_cond_instances 1000performance_schema_max_file_classes 50performance_schema_max_file_handles 32768performance_schema_max_file_instances 10000performance_schema_max_mutex_classes 200performance_schema_max_mutex_instances 1000000performance_schema_max_rwlock_classes 30performance_schema_max_rwlock_instances 1000000performance_schema_max_table_handles 100000performance_schema_max_table_instances 50000performance_schema_max_thread_classes 50performance_schema_max_thread_instances 1000pid_file /var/lib/mysql/toto.pidplugin_dir /usr/lib/mysql/plugin/port 3306preload_buffer_size 32768profiling OFFprofiling_history_size 15protocol_version 10query_alloc_block_size 8192query_cache_limit 1048576query_cache_min_res_unit 4096query_cache_size 0query_cache_strip_comments OFFquery_cache_type OFFquery_cache_wlock_invalidate OFFquery_prealloc_size 8192query_response_time_range_base 10query_response_time_stats OFFrange_alloc_block_size 4096read_buffer_size 131072read_only OFFread_rnd_buffer_size 16777216relay_log relay_log_index relay_log_info_file relay-log.inforelay_log_purge ONrelay_log_recovery OFFrelay_log_space_limit 0report_host report_password report_port 3306report_user rpl_recovery_rank 0secure_auth OFFsecure_file_priv server_id 0skip_external_locking ONskip_name_resolve OFFskip_networking OFFskip_show_database OFFslave_compressed_protocol OFFslave_exec_mode STRICTslave_load_tmpdir /tmpslave_max_allowed_packet 1073741824slave_net_timeout 3600slave_skip_errors OFFslave_transaction_retries 10slave_type_conversions slow_launch_time 2slow_query_log OFFslow_query_log_file /var/lib/mysql/toto-slow.logslow_query_log_timestamp_always OFFslow_query_log_timestamp_precision secondslow_query_log_use_global_control socket /var/run/mysqld/mysqld.socksort_buffer_size 16777216sql_auto_is_null OFFsql_big_selects ONsql_big_tables OFFsql_buffer_result OFFsql_log_bin ONsql_log_off OFFsql_low_priority_updates OFFsql_max_join_size 18446744073709551615sql_mode sql_notes ONsql_quote_show_create ONsql_safe_updates OFFsql_select_limit 18446744073709551615sql_slave_skip_counter 0sql_warnings OFFssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine InnoDBstored_program_cache 256sync_binlog 0sync_frm ONsync_master_info 0sync_relay_log 0sync_relay_log_info 0system_time_zone MDTtable_definition_cache 4096table_open_cache 4096thread_cache_size 50thread_concurrency 10thread_handling one-thread-per-connectionthread_stack 196608thread_statistics OFFtime_format %H:%i:%stime_zone SYSTEMtimed_mutexes OFFtmp_table_size 33554432tmpdir /tmptransaction_alloc_block_size 8192transaction_prealloc_size 4096tx_isolation REPEATABLE-READunique_checks ONupdatable_views_with_limit YESuserstat OFFversion 5.5.27-28.0version_comment Percona Server (GPL), Release 28.0version_compile_machine x86_64version_compile_os Linuxwait_timeout 28800


        Global Status:


        Aborted_clients 0Aborted_connects 0Binlog_cache_disk_use 0Binlog_cache_use 0Binlog_stmt_cache_disk_use 0Binlog_stmt_cache_use 0Bytes_received 59802Bytes_sent 1621369Com_admin_commands 0Com_assign_to_keycache 0Com_alter_db 0Com_alter_db_upgrade 0Com_alter_event 0Com_alter_function 0Com_alter_procedure 0Com_alter_server 0Com_alter_table 0Com_alter_tablespace 0Com_analyze 0Com_begin 0Com_binlog 0Com_call_procedure 0Com_change_db 19Com_change_master 0Com_check 0Com_checksum 0Com_commit 0Com_create_db 0Com_create_event 0Com_create_function 0Com_create_index 0Com_create_procedure 0Com_create_server 0Com_create_table 0Com_create_trigger 0Com_create_udf 0Com_create_user 0Com_create_view 0Com_dealloc_sql 0Com_delete 0Com_delete_multi 0Com_do 0Com_drop_db 0Com_drop_event 0Com_drop_function 0Com_drop_index 0Com_drop_procedure 0Com_drop_server 0Com_drop_table 0Com_drop_trigger 0Com_drop_user 0Com_drop_view 0Com_empty_query 0Com_execute_sql 0Com_flush 0Com_grant 0Com_ha_close 0Com_ha_open 0Com_ha_read 0Com_help 0Com_insert 1Com_insert_select 0Com_install_plugin 0Com_kill 0Com_load 0Com_lock_tables 0Com_optimize 0Com_preload_keys 0Com_prepare_sql 0Com_purge 0Com_purge_before_date 0Com_release_savepoint 0Com_rename_table 0Com_rename_user 0Com_repair 0Com_replace 5Com_replace_select 0Com_reset 0Com_resignal 0Com_revoke 0Com_revoke_all 0Com_rollback 0Com_rollback_to_savepoint 0Com_savepoint 0Com_select 212Com_set_option 75Com_signal 0Com_show_authors 0Com_show_binlog_events 0Com_show_binlogs 2Com_show_charsets 0Com_show_client_statistics 0Com_show_collations 0Com_show_contributors 0Com_show_create_db 0Com_show_create_event 0Com_show_create_func 2Com_show_create_proc 0Com_show_create_table 2Com_show_create_trigger 0Com_show_databases 4Com_show_engine_logs 0Com_show_engine_mutex 0Com_show_engine_status 4Com_show_events 0Com_show_errors 0Com_show_fields 3Com_show_function_status 1Com_show_grants 0Com_show_index_statistics 0Com_show_keys 2Com_show_master_status 1Com_show_open_tables 0Com_show_plugins 14Com_show_privileges 0Com_show_procedure_status 1Com_show_processlist 386Com_show_profile 0Com_show_profiles 0Com_show_relaylog_events 0Com_show_slave_hosts 0Com_show_slave_status 1Com_show_slave_status_nolock 0Com_show_status 20Com_show_storage_engines 0Com_show_table_statistics 0Com_show_table_status 5Com_show_tables 5Com_show_temporary_tables 0Com_show_thread_statistics 0Com_show_triggers 0Com_show_user_statistics 0Com_show_variables 8Com_show_warnings 0Com_slave_start 0Com_slave_stop 0Com_stmt_close 0Com_stmt_execute 0Com_stmt_fetch 0Com_stmt_prepare 0Com_stmt_reprepare 0Com_stmt_reset 0Com_stmt_send_long_data 0Com_truncate 0Com_uninstall_plugin 0Com_unlock_tables 0Com_update 3Com_update_multi 0Com_xa_commit 0Com_xa_end 0Com_xa_prepare 0Com_xa_recover 0Com_xa_rollback 0Com_xa_start 0Compression OFFConnections 38Created_tmp_disk_tables 22Created_tmp_files 6Created_tmp_tables 92Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flashcache_enabled OFFFlush_commands 1Handler_commit 51Handler_delete 0Handler_discover 0Handler_prepare 0Handler_read_first 109Handler_read_key 241933609Handler_read_last 1Handler_read_next 70Handler_read_prev 1Handler_read_rnd 138Handler_read_rnd_next 241943359Handler_rollback 0Handler_savepoint 0Handler_savepoint_rollback 0Handler_update 241933119Handler_write 10727Innodb_adaptive_hash_cells 144498467Innodb_adaptive_hash_heap_buffers 0Innodb_adaptive_hash_hash_searches 0Innodb_adaptive_hash_non_hash_searches 2711Innodb_background_log_sync 282Innodb_buffer_pool_pages_data 455679Innodb_buffer_pool_pages_dirty 0Innodb_buffer_pool_pages_flushed 29Innodb_buffer_pool_pages_LRU_flushed 0Innodb_buffer_pool_pages_free 4000767Innodb_buffer_pool_pages_made_not_young 0Innodb_buffer_pool_pages_made_young 0Innodb_buffer_pool_pages_misc 1Innodb_buffer_pool_pages_old 168229Innodb_buffer_pool_pages_total 4456447Innodb_buffer_pool_read_ahead_rnd 0Innodb_buffer_pool_read_ahead 431312Innodb_buffer_pool_read_ahead_evicted 0Innodb_buffer_pool_read_requests 31603694Innodb_buffer_pool_reads 24366Innodb_buffer_pool_wait_free 0Innodb_buffer_pool_write_requests 76Innodb_checkpoint_age 0Innodb_checkpoint_max_age 869019772Innodb_checkpoint_target_age 841862905Innodb_data_fsyncs 20Innodb_data_pending_fsyncs 0Innodb_data_pending_reads 1Innodb_data_pending_writes 0Innodb_data_read 7468027904Innodb_data_reads 455713Innodb_data_writes 47Innodb_data_written 968704Innodb_dblwr_pages_written 29Innodb_dblwr_writes 2Innodb_deadlocks 0Innodb_dict_tables 92Innodb_have_atomic_builtins ONInnodb_history_list_length 1201Innodb_ibuf_discarded_delete_marks 0Innodb_ibuf_discarded_deletes 0Innodb_ibuf_discarded_inserts 0Innodb_ibuf_free_list 0Innodb_ibuf_merged_delete_marks 0Innodb_ibuf_merged_deletes 0Innodb_ibuf_merged_inserts 0Innodb_ibuf_merges 0Innodb_ibuf_segment_size 2Innodb_ibuf_size 1Innodb_log_waits 0Innodb_log_write_requests 26Innodb_log_writes 12Innodb_lsn_current 1442779337294Innodb_lsn_flushed 1442779337294Innodb_lsn_last_checkpoint 1442779337294Innodb_master_thread_1_second_loops 46Innodb_master_thread_10_second_loops 4Innodb_master_thread_background_loops 6Innodb_master_thread_main_flush_loops 6Innodb_master_thread_sleeps 46Innodb_max_trx_id 30239291Innodb_mem_adaptive_hash 1156008336Innodb_mem_dictionary 289515676Innodb_mem_total 75010932736Innodb_mutex_os_waits 37Innodb_mutex_spin_rounds 1677Innodb_mutex_spin_waits 222Innodb_oldest_view_low_limit_trx_id 30239290Innodb_os_log_fsyncs 16Innodb_os_log_pending_fsyncs 0Innodb_os_log_pending_writes 0Innodb_os_log_written 16384Innodb_page_size 16384Innodb_pages_created 1Innodb_pages_read 455677Innodb_pages_written 29Innodb_purge_trx_id 30239287Innodb_purge_undo_no 0Innodb_row_lock_current_waits 0Innodb_current_row_locks 0Innodb_row_lock_time 0Innodb_row_lock_time_avg 0Innodb_row_lock_time_max 0Innodb_row_lock_waits 0Innodb_rows_deleted 0Innodb_rows_inserted 0Innodb_rows_read 241932540Innodb_rows_updated 8Innodb_s_lock_os_waits 7261Innodb_s_lock_spin_rounds 219974Innodb_s_lock_spin_waits 7414Innodb_truncated_status_writes 0Innodb_x_lock_os_waits 78Innodb_x_lock_spin_rounds 2365Innodb_x_lock_spin_waits 79Key_blocks_not_flushed 0Key_blocks_unused 26774Key_blocks_used 18Key_read_requests 554Key_reads 18Key_write_requests 0Key_writes 0Last_query_cost 0.000000Max_used_connections 7Not_flushed_delayed_rows 0Open_files 66Open_streams 0Open_table_definitions 127Open_tables 158Opened_files 456Opened_table_definitions 127Opened_tables 165Performance_schema_cond_classes_lost 0Performance_schema_cond_instances_lost 0Performance_schema_file_classes_lost 0Performance_schema_file_handles_lost 0Performance_schema_file_instances_lost 0Performance_schema_locker_lost 0Performance_schema_mutex_classes_lost 0Performance_schema_mutex_instances_lost 0Performance_schema_rwlock_classes_lost 0Performance_schema_rwlock_instances_lost 0Performance_schema_table_handles_lost 0Performance_schema_table_instances_lost 0Performance_schema_thread_classes_lost 0Performance_schema_thread_instances_lost 0Prepared_stmt_count 0Qcache_free_blocks 0Qcache_free_memory 0Qcache_hits 0Qcache_inserts 0Qcache_lowmem_prunes 0Qcache_not_cached 0Qcache_queries_in_cache 0Qcache_total_blocks 0Queries 808Questions 808Rpl_status AUTH_MASTERSelect_full_join 1Select_full_range_join 0Select_range 9Select_range_check 0Select_scan 102Slave_heartbeat_period 0.000Slave_open_temp_tables 0Slave_received_heartbeats 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 0Slow_queries 40Sort_merge_passes 0Sort_range 0Sort_rows 159Sort_scan 19Ssl_accept_renegotiates 0Ssl_accepts 0Ssl_callback_cache_hits 0Ssl_cipher Ssl_cipher_list Ssl_client_connects 0Ssl_connect_renegotiates 0Ssl_ctx_verify_depth 0Ssl_ctx_verify_mode 0Ssl_default_timeout 0Ssl_finished_accepts 0Ssl_finished_connects 0Ssl_session_cache_hits 0Ssl_session_cache_misses 0Ssl_session_cache_mode NONESsl_session_cache_overflows 0Ssl_session_cache_size 0Ssl_session_cache_timeouts 0Ssl_sessions_reused 0Ssl_used_session_cache_entries 0Ssl_verify_depth 0Ssl_verify_mode 0Ssl_version Table_locks_immediate 260Table_locks_waited 0Tc_log_max_pages_used 0Tc_log_page_size 0Tc_log_page_waits 0Threads_cached 1Threads_connected 6Threads_created 7Threads_running 2Uptime 2255Uptime_since_flush_status 2255binlog_commits 0binlog_group_commits 0

        Comment


        • #5
          Server:
          4 Quad-core AMD CPU
          80 GB RAM
          RAID 6 hard disks
          Linux UBUNTU 12.04

          Comment


          • #6
            I apologize,I modified my post after sending it, could you also post the output of

            SHOW CREATE TABLE table\G
            (where table is the table from SELECT COUNT(*) FROM table)

            EXPLAIN select `Distance`, count(`Distance`) from `hamming_q22_sd6_11` group by `Distance`
            SHOW CREATE TABLE hamming_q22_sd6_11\G

            Comment


            • #7
              Thanks for your help!

              SD6 table

              CREATE TABLE `hamming_q22_sd6_12` ( `id1` int(10) unsigned NOT NULL, `id2` int(10) unsigned NOT NULL, `seq1` varchar(50) NOT NULL, `seq2` varchar(50) NOT NULL, `Distance` varchar(25) DEFAULT NULL, `length` smallint(6) NOT NULL, PRIMARY KEY (`id1`,`id2`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT


              Explain statement


              id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE hamming_q22_sd6_11 ALL NULL NULL NULL NULL 118203335 Using temporary; Using filesort

              Comment


              • #8
                You should probably add an index on `Distance`

                ALTER TABLE hamming_q22_sd6_12 ADD INDEX(Distance);

                May also want to watch the following webinars:
                http://www.percona.com/webinars/2012-08-15-mysql-indexing-be st-practices
                http://www.percona.com/webinars/2012-02-22-explain-demystifi ed/

                Also the following manual entries are relevant:
                http://dev.mysql.com/doc/refman/5.5/...g-explain.html
                https://dev.mysql.com/doc/refman/5.5...l-indexes.html

                Comment


                • #9
                  I would expect better performance out of SELECT COUNT(*) as well.... Innodb can be bad for SELECT COUNT(*) with no WHERE clause, however 9 hours seems suspect to me. even with a billion and a half rows.

                  Can you also put the EXPLAIN for the SELECT COUNT(*) on here?

                  Comment


                  • #10
                    I am tryin to add the index to the table. I am sure it will take a while. Here is the explain you requested:

                    id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE hamming_q22_sd6_12 ALL NULL NULL NULL NULL 245387653 Using temporary; Using filesort


                    Is it possible that it's the underlying filesystem that makes these queries this slow?

                    Comment


                    • #11
                      doubtful its the filesystem.

                      I'm surprised its not at least reading the PK as they are NOT NULL.
                      There's probably some caveat in the optimizer i'm forgetting/unaware of.

                      I assume you have a dev/test system to benchmark these on. I would toy with adding an index on id1.

                      Also, debate what you really need out of SELECT COUNT(*).
                      Does it need to accurate 100% of the time? Or can we fudge it?

                      What response time would you _like_ to see out of it ?

                      Comment


                      • #12
                        I am trying to add the index to the table. It has been running now for over 90 minutes. I don't know when it will finish.
                        I would like to have a reasonable response time.

                        I have just written a small python script that does the same things as the SQL statement. I dumped my table into an csv file and loaded into python. It finished in 5 seconds with 40 million records.
                        I would expect that databases would be more efficient than Python.

                        I am really baffled by this.

                        Comment


                        • #13
                          Index adds require a rebuild of the table (for the most part: See Innodb fast index in the manual).

                          Im not sure your doing an apples to apples comparison when comparing python.

                          Also if you just dumped that file into a CSV you just filled the linux page cache with it.

                          Comment


                          • #14
                            For the record, the reason Innodb can be slow for SELECT COUNT(*) is due to how B Trees work and Innodb's implementation of ACID.

                            http://en.wikipedia.org/wiki/Multiversion_concurrency_contro l

                            Comment


                            • #15
                              If you could, let me know how those index(es) work out.

                              Comment

                              Working...
                              X