Slow INSERTs on BIG tables.
Posted by:
Ran Geva
Date: November 17, 2005 11:16AM
Hi,
I tried to find an answer on many news groups and forums and found some ideas but nothing really works well, so I hope someone here can provide a solution:
I have a table that is growing fast and should grow to Tens of Giga bytes and more… This table is constantly updating with new rows and clients also read from it. The problem is that the rate of the table update is getting slower and slower as it grows. Im doing the following to optimize the inserts:
1) LOAD DATA CONCURRENT LOCAL INFILE '/TempDBFile.db' IGNORE INTO TABLE TableName FIELDS TERMINATED BY '\r';
Notice here that Im doing a concurrent insert and using the IGNORE keyword to make it faster.
2)I have set "pack_keys=1 and delay_key_write=1" (and set it so MySQL will flush the table every 30 minutes to save memory and update the keys)
3) I tried to "ALTER TABLE ... DISABLE KEYS" and "ALTER TABLE ... ENABLE KEYS" but it didn’t improve the speed and took forever to "ALTER TABLE ... ENABLE KEYS" to finish.
Three keys are numberic (MEDIUMINT(8), BIGINT(20), FLOAT) and one is binary(1).
The table have 4 index (3 for each field + Primary made out of two keys (MEDIUMINT(8), BIGINT(20))).
MySQL server runs on Dell (Intel Xeon 2.8Ghz + 2GB DDR2 SDRAM). Windows 2003 server with 4 300GB HD with RAID 5.
I just out of ideas about how to insert rows fast enough to a HUGE tables.
Any help or ideas will be highly appriciated.
Ran Geva
Here is the DB configuration:
'auto_increment_increment', '1'
'auto_increment_offset', '1'
'automatic_sp_privileges', 'ON'
'back_log', '50'
'basedir', 'C:\Program Files\MySQL\MySQL Server 5.0\'
'binlog_cache_size', '32768'
'bulk_insert_buffer_size', '104857600'
'character_set_client', 'latin1'
'character_set_connection', 'latin1'
'character_set_database', 'latin1'
'character_set_results', 'latin1'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
'character_sets_dir', 'C:\Program Files\MySQL\MySQL Server 5.0\share\charsets/'
'collation_connection', 'latin1_swedish_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'
'completion_type', '0'
'concurrent_insert', '1'
'connect_timeout', '5'
'datadir', 'D:\DB\'
'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', '1800'
'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', 'YES'
'have_bdb', 'NO'
'have_blackhole_engine', 'NO'
'have_compress', 'YES'
'have_crypt', 'NO'
'have_csv', 'NO'
'have_example_engine', 'NO'
'have_federated_engine', 'NO'
'have_geometry', 'YES'
'have_innodb', 'DISABLED'
'have_isam', 'NO'
'have_ndbcluster', 'NO'
'have_openssl', 'NO'
'have_query_cache', 'YES'
'have_raid', 'NO'
'have_rtree_keys', 'YES'
'have_symlink', 'NO'
'init_connect', ''
'init_file', ''
'init_slave', ''
'innodb_additional_mem_pool_size', '6291456'
'innodb_autoextend_increment', '8'
'innodb_buffer_pool_awe_mem_mb', '0'
'innodb_buffer_pool_size', '262144000'
'innodb_checksums', 'ON'
'innodb_commit_concurrency', '0'
'innodb_concurrency_tickets', '500'
'innodb_data_file_path', ''
'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', '3145728'
'innodb_log_file_size', '52428800'
'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', '131072'
'key_buffer_size', '1073741824'
'key_cache_age_threshold', '300'
'key_cache_block_size', '1024'
'key_cache_division_limit', '100'
'language', 'C:\Program Files\MySQL\MySQL Server 5.0\share\english\'
'large_files_support', 'ON'
'large_page_size', '0'
'large_pages', 'OFF'
'license', 'GPL'
'local_infile', 'ON'
'log', 'OFF'
'log_bin', 'OFF'
'log_bin_trust_routine_creators', 'OFF'
'log_error', '.\db.err'
'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', '1'
'max_allowed_packet', '16776192'
'max_binlog_cache_size', '4294967295'
'max_binlog_size', '1073741824'
'max_connect_errors', '10'
'max_connections', '800'
'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_relay_log_size', '0'
'max_seeks_for_key', '4294967295'
'max_sort_length', '1024'
'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', '67108864'
'myisam_recover_options', 'OFF'
'myisam_repair_threads', '1'
'myisam_sort_buffer_size', '67108864'
'myisam_stats_method', 'nulls_unequal'
'named_pipe', 'OFF'
'net_buffer_length', '16384'
'net_read_timeout', '30'
'net_retry_count', '10'
'net_write_timeout', '60'
'new', 'OFF'
'old_passwords', 'OFF'
'open_files_limit', '2048'
'optimizer_prune_level', '1'
'optimizer_search_depth', '62'
'pid_file', 'D:\DB\db.pid'
'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', '536870912'
'query_cache_type', 'ON'
'query_cache_wlock_invalidate', 'OFF'
'query_prealloc_size', '8192'
'range_alloc_block_size', '2048'
'read_buffer_size', '33550336'
'read_only', 'OFF'
'read_rnd_buffer_size', '33550336'
'relay_log_purge', 'ON'
'relay_log_space_limit', '0'
'rpl_recovery_rank', '0'
'secure_auth', 'OFF'
'shared_memory', 'OFF'
'shared_memory_base_name', 'MYSQL'
'server_id', '0'
'skip_external_locking', 'ON'
'skip_networking', 'OFF'
'skip_show_database', 'OFF'
'slave_compressed_protocol', 'OFF'
'slave_load_tmpdir', 'C:\WINDOWS\TEMP\'
'slave_net_timeout', '3600'
'slave_skip_errors', 'OFF'
'slave_transaction_retries', '10'
'slow_launch_time', '2'
'sort_buffer_size', '33554424'
'sql_mode', 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
'sql_notes', 'ON'
'sql_warnings', 'ON'
'storage_engine', 'MyISAM'
'sync_binlog', '0'
'sync_frm', 'ON'
'sync_replication', '0'
'sync_replication_slave_id', '0'
'sync_replication_timeout', '10'
'system_time_zone', 'Central Standard Time'
'table_cache', '619'
'table_lock_wait_timeout', '50'
'table_type', 'MyISAM'
'thread_cache_size', '38'
'thread_stack', '196608'
'time_format', '%H:%i:%s'
'time_zone', 'SYSTEM'
'timed_mutexes', 'OFF'
'tmp_table_size', '31457280'
'tmpdir', ''
'transaction_alloc_block_size', '8192'
'transaction_prealloc_size', '4096'
'tx_isolation', 'REPEATABLE-READ'
'updatable_views_with_limit', 'YES'
'version', '5.0.15-nt'
'version_comment', 'Official MySQL binary'
'version_compile_machine', 'ia32'
'version_compile_os', 'Win32'
'wait_timeout', '28800'