MySQL Forums
Forum List  »  MyISAM

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'

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow INSERTs on BIG tables.
7310
November 17, 2005 11:16AM
3647
November 18, 2005 05:41AM
3210
November 18, 2005 07:27AM
2754
November 21, 2005 02:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.