Static, 120GB, InnoDB table is unusuable

[Nicholas Hans]

I've inherited a MySQL 5.5.16 x64 server running OpenSUSE 12.1 on an Intel Xeon E5520 with 74 GB RAM.

The main table uses the InnoDB engine and has 10 fields, all 16- or 32-bit integers. There are 1.67 billion rows (~120GB). It is a static table.

Running simple queries or altering the table (e.g. adding an index) times out eventually and is unable to fulfill the request. Even running COUNT(*) takes 15 - 30 minutes.

As a workaround, I've been writing .NET client applications to slide through the table using the primary ID in groups of 1,000,000 rows. That takes 7 - 8 hours though just to complete once the code is written and tested.

Any advice on what to check for to help improve it would be greatly appreciated.

SHOW VARIABLES:

Variable_name | Value
auto_increment_increment | 1
auto_increment_offset | 1
autocommit | ON
automatic_sp_privileges | ON
back_log | 50
basedir | /usr
big_tables | OFF
binlog_cache_size | 4096
binlog_direct_non_transactional_updates | OFF
binlog_format | STATEMENT
binlog_stmt_cache_size | 32768
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
character_sets_dir | /usr/share/mysql/charsets/
collation_connection | utf8_general_ci
collation_database | utf8_general_ci
collation_server | utf8_general_ci
completion_type | NO_CHAIN
concurrent_insert | AUTO
connect_timeout | 60
datadir | /home/mysqladmin/mysql/
date_format | %Y-%m-%d
datetime_format | %Y-%m-%d %H:%i:%s
default_storage_engine | InnoDB
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
external_user |
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 | ON
general_log_file | /home/mysqladmin/mysql/mysql1.log
group_concat_max_len | 1024
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 | YES
have_profiling | YES
have_query_cache | YES
have_rtree_keys | YES
have_ssl | DISABLED
have_symlink | YES
hostname | mysql1
identity | 0
ignore_builtin_innodb | OFF
init_connect |
init_file |
init_slave |
innodb_adaptive_flushing | ON
innodb_adaptive_hash_index | ON
innodb_additional_mem_pool_size | 8388608
innodb_autoextend_increment | 8
innodb_autoinc_lock_mode | 1
innodb_buffer_pool_instances | 1
innodb_buffer_pool_size | 134217728
innodb_change_buffering | all
innodb_checksums | ON
innodb_commit_concurrency | 0
innodb_concurrency_tickets | 500
innodb_data_file_path | ibdata1:10M:autoextend
innodb_data_home_dir | /home/mysqladmin/mysql/innodb
innodb_doublewrite | ON
innodb_fast_shutdown | 1
innodb_file_format | Antelope
innodb_file_format_check | ON
innodb_file_format_max | Antelope
innodb_file_per_table | OFF
innodb_flush_log_at_trx_commit | 1
innodb_flush_method |
innodb_force_recovery | 0
innodb_io_capacity | 200
innodb_large_prefix | OFF
innodb_lock_wait_timeout | 50
innodb_locks_unsafe_for_binlog | OFF
innodb_log_buffer_size | 8388608
innodb_log_file_size | 5242880
innodb_log_files_in_group | 2
innodb_log_group_home_dir | /home/mysqladmin/mysql/innodb
innodb_max_dirty_pages_pct | 75
innodb_max_purge_lag | 0
innodb_mirrored_log_groups | 1
innodb_old_blocks_pct | 37
innodb_old_blocks_time | 0
innodb_open_files | 300
innodb_purge_batch_size | 20
innodb_purge_threads | 0
innodb_random_read_ahead | OFF
innodb_read_ahead_threshold | 56
innodb_read_io_threads | 4
innodb_replication_delay | 0
innodb_rollback_on_timeout | OFF
innodb_rollback_segments | 128
innodb_spin_wait_delay | 6
innodb_stats_method | nulls_equal
innodb_stats_on_metadata | ON
innodb_stats_sample_pages | 8
innodb_strict_mode | OFF
innodb_support_xa | ON
innodb_sync_spin_loops | 30
innodb_table_locks | ON
innodb_thread_concurrency | 0
innodb_thread_sleep_delay | 10000
innodb_use_native_aio | OFF
innodb_use_sys_malloc | ON
innodb_version | 1.1.8
innodb_write_io_threads | 4
insert_id | 0
interactive_timeout | 28800
join_buffer_size | 131072
keep_files_on_create | OFF
key_buffer_size | 17179869184
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_messages | en_US
lc_messages_dir | /usr/share/mysql/
lc_time_names | en_US
license | GPL
local_infile | ON
lock_wait_timeout | 31536000
locked_in_memory | OFF
log | ON
log_bin | OFF
log_bin_trust_function_creators | OFF
log_error | /home/mysqladmin/mysql/mysqld.log
log_output | FILE
log_queries_not_using_indexes | OFF
log_slave_updates | OFF
log_slow_queries | OFF
log_warnings | 1
long_query_time | 10
low_priority_updates | OFF
lower_case_file_system | OFF
lower_case_table_names | 0
max_allowed_packet | 134217728
max_binlog_cache_size | 18446744073709551615
max_binlog_size | 1073741824
max_binlog_stmt_cache_size | 18446744073709551615
max_connect_errors | 1000
max_connections | 151
max_delayed_threads | 20
max_error_count | 64
max_heap_table_size | 16777216
max_insert_delayed_threads | 20
max_join_size | 18446744073709551615
max_length_for_sort_data | 1024
max_long_data_size | 134217728
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 | 8589934592
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 | 1185
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,engine_condition_pushdown=on
performance_schema | OFF
performance_schema_events_waits_history_long_size | 10000
performance_schema_events_waits_history_size | 10
performance_schema_max_cond_classes | 80
performance_schema_max_cond_instances | 1000
performance_schema_max_file_classes | 50
performance_schema_max_file_handles | 32768
performance_schema_max_file_instances | 10000
performance_schema_max_mutex_classes | 200
performance_schema_max_mutex_instances | 1000000
performance_schema_max_rwlock_classes | 30
performance_schema_max_rwlock_instances | 1000000
performance_schema_max_table_handles | 100000
performance_schema_max_table_instances | 50000
performance_schema_max_thread_classes | 50
performance_schema_max_thread_instances | 1000
pid_file | /var/run/mysql/mysqld.pid
plugin_dir | /usr/lib64/mysql/plugin
port | 3306
preload_buffer_size | 32768
profiling | OFF
profiling_history_size | 15
protocol_version | 10
proxy_user |
pseudo_thread_id | 167
query_alloc_block_size | 8192
query_cache_limit | 1048576
query_cache_min_res_unit | 4096
query_cache_size | 4294967296
query_cache_type | ON
query_cache_wlock_invalidate | OFF
query_prealloc_size | 8192
rand_seed1 | 0
rand_seed2 | 0
range_alloc_block_size | 4096
read_buffer_size | 8388608
read_only | OFF
read_rnd_buffer_size | 25165824
relay_log |
relay_log_index |
relay_log_info_file | relay-log.info
relay_log_purge | ON
relay_log_recovery | OFF
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 | 1
skip_external_locking | ON
skip_name_resolve | OFF
skip_networking | OFF
skip_show_database | OFF
slave_compressed_protocol | OFF
slave_exec_mode | STRICT
slave_load_tmpdir | /home/mysqladmin/mysql/tmp
slave_net_timeout | 3600
slave_skip_errors | OFF
slave_transaction_retries | 10
slave_type_conversions |
slow_launch_time | 2
slow_query_log | OFF
slow_query_log_file | /home/mysqladmin/mysql/mysql1-slow.log
socket | /var/run/mysql/mysql.sock
sort_buffer_size | 1073741824
sql_auto_is_null | OFF
sql_big_selects | ON
sql_big_tables | OFF
sql_buffer_result | OFF
sql_log_bin | ON
sql_log_off | OFF
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 | 0
sql_warnings | OFF
ssl_ca |
ssl_capath |
ssl_cert |
ssl_cipher |
ssl_key |
storage_engine | InnoDB
sync_binlog | 0
sync_frm | ON
sync_master_info | 0
sync_relay_log | 0
sync_relay_log_info | 0
system_time_zone | EDT
table_definition_cache | 400
table_open_cache | 512
thread_cache_size | 20
thread_concurrency | 16
thread_handling | one-thread-per-connection
thread_stack | 262144
time_format | %H:%i:%s
time_zone | SYSTEM
timed_mutexes | OFF
timestamp | 1370103352
tmp_table_size | 55834574848
tmpdir | /home/mysqladmin/mysql/tmp
transaction_alloc_block_size | 8192
transaction_prealloc_size | 4096
tx_isolation | REPEATABLE-READ
unique_checks | ON
updatable_views_with_limit | YES
version | 5.5.16-log
version_comment | Source distribution
version_compile_machine | x86_64
version_compile_os | Linux
wait_timeout | 28800
warning_count | 0



Edited 1 time(s). Last edit at 06/01/2013 10:26AM by Nicholas Hans.