Announcement

Announcement Module
Collapse
No announcement yet.

LOAD DATA locks out ALL innoDB tables.

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

  • LOAD DATA locks out ALL innoDB tables.

    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
Working...
X