I’m attempting to load a 3GB file, 850K rows, into an unused InnoDB table using LOAD DATA INFILE (local). When I do this, all other InnoDB tables in the db freeze up, and hundreds of update queries start waiting, and timing out after table_lock_wait_timeout (50). I’m thinking some kind of disk contention, perhaps size of tmp or something.
This may be associated… not sure…
I have set innodb_buffer_pool_size to 512M, and in fact it shows as
innodb_buffer_pool_size 536870912
in the “system variables” pane of mysql administrator.
However! On the “status variables” pane of mysql administrator, which shows the real values I have the following:
innodb_buffer_pool_size 32768
innodb_buffer_free_buffers 0
innodb_buffer_pages_created 170169
innodb_buffer_pages_read 6298
innodb_buffer_pages_written 170169
This tells me that mysql needs more buffer space, but for some reason is not growing the pool size to the size that it is allowed to grow. Why would this be, and is it related to my problem of not being able to import a large file into an innodb table?
Thanks in advance. any help appreciated!
SHOW VARIABLES
auto_increment_increment = 1
auto_increment_offset = 1
automatic_sp_privileges = ON
back_log = 50
basedir = /usr/
bdb_cache_size = 8388600
bdb_home = /var/lib/mysql/
bdb_log_buffer_size = 131072
bdb_logdir =
bdb_max_lock = 10000
bdb_shared_data = OFF
bdb_tmpdir = /tmp/
binlog_cache_size = 32768
bulk_insert_buffer_size = 8388608
character_set_client = utf8
character_set_connection = utf8
character_set_database = latin1
character_set_filesystem = binary
character_set_results = utf8
character_set_server = latin1
character_set_system = utf8
character_sets_dir = /usr/share/mysql/charsets/
collation_connection = utf8_general_ci
collation_database = latin1_swedish_ci
collation_server = latin1_swedish_ci
completion_type = 0
concurrent_insert = 1
connect_timeout = 5
datadir = /var/lib/mysql/
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 = OFF
expire_logs_days = 0
flush = OFF
flush_time = 0
ft_boolean_syntax = + -><()~*:“”&|
ft_max_word_len = 84
ft_min_word_len = 4
ft_query_expansion_limit = 20
ft_stopword_file = (built-in)
group_concat_max_len = 1024
have_archive = NO
have_bdb = YES
have_blackhole_engine = NO
have_compress = YES
have_crypt = YES
have_csv = NO
have_example_engine = NO
have_federated_engine = NO
have_geometry = YES
have_innodb = YES
have_isam = NO
have_ndbcluster = NO
have_openssl = DISABLED
have_query_cache = YES
have_raid = NO
have_rtree_keys = YES
have_symlink = YES
init_connect =
init_file =
init_slave =
innodb_additional_mem_pool_size = 20971520
innodb_autoextend_increment = 1000
innodb_buffer_pool_awe_mem_mb = 0
innodb_buffer_pool_size = 536870912
innodb_checksums = ON
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 500
innodb_data_file_path = ibdata1:3000M:autoextend
innodb_data_home_dir =
innodb_doublewrite = ON
innodb_fast_shutdown = 1
innodb_file_io_threads = 4
innodb_file_per_table = OFF
innodb_flush_log_at_trx_commit = 1
innodb_flush_method =
innodb_force_recovery = 0
innodb_lock_wait_timeout = 50
innodb_locks_unsafe_for_binlog = OFF
innodb_log_arch_dir =
innodb_log_archive = OFF
innodb_log_buffer_size = 8388608
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_support_xa = ON
innodb_sync_spin_loops = 20
innodb_table_locks = ON
innodb_thread_concurrency = 8
innodb_thread_sleep_delay = 10000
interactive_timeout = 28800
join_buffer_size = 10481664
key_buffer_size = 268435456
key_cache_age_threshold = 300
key_cache_block_size = 1024
key_cache_division_limit = 100
language = /usr/share/mysql/english/
large_files_support = ON
large_page_size = 0
large_pages = OFF
license = GPL
local_infile = ON
locked_in_memory = OFF
log = OFF
log_bin = ON
log_bin_trust_function_creators = OFF
log_error =
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 = 16000000
max_binlog_cache_size = 4294967295
max_binlog_size = 1073741824
max_connect_errors = 10
max_connections = 5000
max_delayed_threads = 20
max_error_count = 64
max_heap_table_size = 16777216
max_insert_delayed_threads = 20
max_join_size = 4294967295
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_relay_log_size = 0
max_seeks_for_key = 4294967295
max_sort_length = 1024
max_sp_recursion_depth = 0
max_tmp_tables = 32
max_user_connections = 0
max_write_lock_count = 4294967295
multi_range_count = 256
myisam_data_pointer_size = 6
myisam_max_sort_file_size = 2147483647
myisam_recover_options = OFF
myisam_repair_threads = 1
myisam_sort_buffer_size = 67108864
myisam_stats_method = nulls_unequal
net_buffer_length = 16384
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
new = OFF
old_passwords = OFF
open_files_limit = 25010
optimizer_prune_level = 1
optimizer_search_depth = 62
pid_file = /var/run/mysqld/mysqld.pid
prepared_stmt_count = 0
port = 3306
preload_buffer_size = 32768
protocol_version = 10
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_min_res_unit = 4096
query_cache_size = 16777216
query_cache_type = ON
query_cache_wlock_invalidate = OFF
query_prealloc_size = 8192
range_alloc_block_size = 2048
read_buffer_size = 1998848
read_only = OFF
read_rnd_buffer_size = 4190208
relay_log_purge = ON
relay_log_space_limit = 0
rpl_recovery_rank = 0
secure_auth = OFF
server_id = 1
skip_external_locking = ON
skip_networking = OFF
skip_show_database = OFF
slave_compressed_protocol = OFF
slave_load_tmpdir = /tmp/
slave_net_timeout = 3600
slave_skip_errors = OFF
slave_transaction_retries = 10
slow_launch_time = 2
socket = /var/lib/mysql/mysql.sock
sort_buffer_size = 256000000
sql_mode =
sql_notes = ON
sql_warnings = ON
storage_engine = MyISAM
sync_binlog = 0
sync_frm = ON
system_time_zone = CDT
table_cache = 256
table_lock_wait_timeout = 50
table_type = MyISAM
thread_cache_size = 8
thread_stack = 196608
time_format = %H:%i:%s
time_zone = SYSTEM
timed_mutexes = OFF
tmp_table_size = 33554432
tmpdir =
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
tx_isolation = REPEATABLE-READ
updatable_views_with_limit = YES
version = 5.0.22-log
version_bdb = Sleepycat Software: Berkeley DB 4.1.24: (May 25 = 2006)
version_comment = Source distribution
version_compile_machine = i686
version_compile_os = redhat-linux-gnu
wait_timeout = 28800
SHOW STATUS
Aborted_clients = 7051
Aborted_connects = 962
Binlog_cache_disk_use = 18
Binlog_cache_use = 428667
Bytes_received = 187
Bytes_sent = 259
Com_admin_commands = 0
Com_alter_db = 0
Com_alter_table = 0
Com_analyze = 0
Com_backup_table = 0
Com_begin = 0
Com_change_db = 1
Com_change_master = 0
Com_check = 0
Com_checksum = 0
Com_commit = 0
Com_create_db = 0
Com_create_function = 0
Com_create_index = 0
Com_create_table = 0
Com_dealloc_sql = 0
Com_delete = 0
Com_delete_multi = 0
Com_do = 0
Com_drop_db = 0
Com_drop_function = 0
Com_drop_index = 0
Com_drop_table = 0
Com_drop_user = 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_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_rename_table = 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_savepoint = 0
Com_select = 2
Com_set_option = 2
Com_show_binlog_events = 0
Com_show_binlogs = 0
Com_show_charsets = 0
Com_show_collations = 0
Com_show_column_types = 0
Com_show_create_db = 0
Com_show_create_table = 0
Com_show_databases = 0
Com_show_errors = 0
Com_show_fields = 0
Com_show_grants = 0
Com_show_innodb_status = 0
Com_show_keys = 0
Com_show_logs = 0
Com_show_master_status = 0
Com_show_ndb_status = 0
Com_show_new_master = 0
Com_show_open_tables = 0
Com_show_privileges = 0
Com_show_processlist = 0
Com_show_slave_hosts = 0
Com_show_slave_status = 0
Com_show_status = 1
Com_show_storage_engines = 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_reset = 0
Com_stmt_send_long_data = 0
Com_truncate = 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 = OFF
Connections = 5525
Created_tmp_disk_tables = 0
Created_tmp_files = 8
Created_tmp_tables = 1
Delayed_errors = 0
Delayed_insert_threads = 0
Delayed_writes = 0
Flush_commands = 2
Handler_commit = 0
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 = 0
Handler_rollback = 0
Handler_savepoint = 0
Handler_savepoint_rollback = 0
Handler_update = 0
Handler_write = 130
Innodb_buffer_pool_pages_data = 32734
Innodb_buffer_pool_pages_dirty = 42
Innodb_buffer_pool_pages_flushed = 344512
Innodb_buffer_pool_pages_free = 1
Innodb_buffer_pool_pages_latched = 0
Innodb_buffer_pool_pages_misc = 33
Innodb_buffer_pool_pages_total = 32768
Innodb_buffer_pool_read_ahead_rnd = 87
Innodb_buffer_pool_read_ahead_seq = 26
Innodb_buffer_pool_read_requests = 67692652
Innodb_buffer_pool_reads = 2644
Innodb_buffer_pool_wait_free = 0
Innodb_buffer_pool_write_requests = 11645744
Innodb_data_fsyncs = 793874
Innodb_data_pending_fsyncs = 1
Innodb_data_pending_reads = 0
Innodb_data_pending_writes = 0
Innodb_data_read = 105140224
Innodb_data_reads = 3525
Innodb_data_writes = 894304
Innodb_data_written = 1643344384
Innodb_dblwr_pages_written = 344512
Innodb_dblwr_writes = 9346
Innodb_log_waits = 0
Innodb_log_write_requests = 6950991
Innodb_log_writes = 770955
Innodb_os_log_fsyncs = 778708
Innodb_os_log_pending_fsyncs = 1
Innodb_os_log_pending_writes = 0
Innodb_os_log_written = 3236460544
Innodb_page_size = 16384
Innodb_pages_created = 160179
Innodb_pages_read = 6284
Innodb_pages_written = 344512
Innodb_row_lock_current_waits = 1
Innodb_row_lock_time = 60876042
Innodb_row_lock_time_avg = 335
Innodb_row_lock_time_max = 5194
Innodb_row_lock_waits = 181202
Innodb_rows_deleted = 467013
Innodb_rows_inserted = 1386569
Innodb_rows_read = 55444397
Innodb_rows_updated = 488241
Key_blocks_not_flushed = 0
Key_blocks_unused = 206174
Key_blocks_used = 25786
Key_read_requests = 5003135
Key_reads = 82701
Key_write_requests = 494454
Key_writes = 120223
Last_query_cost = 0.000000
Max_used_connections = 896
Not_flushed_delayed_rows = 0
Open_files = 88
Open_streams = 0
Open_tables = 209
Opened_tables = 0
Qcache_free_blocks = 90
Qcache_free_memory = 16089184
Qcache_hits = 27542
Qcache_inserts = 73232
Qcache_lowmem_prunes = 0
Qcache_not_cached = 79181
Qcache_queries_in_cache = 267
Qcache_total_blocks = 645
Questions = 1349114
Rpl_status = NULL
Select_full_join = 0
Select_full_range_join = 0
Select_range = 0
Select_range_check = 0
Select_scan = 1
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 = 601047
Table_locks_waited = 21465
Tc_log_max_pages_used = 0
Tc_log_page_size = 0
Tc_log_page_waits = 1
Threads_cached = 6
Threads_connected = 356
Threads_created = 3414
Threads_running = 6
Uptime = 56966