I was able to speed it up somewhat by increasing the Key_buffer_size to
512M and its down to 4 hours to load 30 million rows. Still I find that's
pretty slow. Is there anything else I can do to speed it up? TIA
Mike
At 10:50 PM 7/28/2006, mos wrote:
I ran a file monitor and it appears MySQL has been updating the table's
index for the past several hours as part of the Load Data Infile process.
Is there any way to speed this up? I'm using MySIAM tables in v4.1.10.
Here are the settings I'm using. Is there anything in there that will
speed up the re-indexing?
TIA
Mike
+---------------------------------+-------------------------------------------------------------+
| Variable_name |
Value |
+---------------------------------+-------------------------------------------------------------+
| back_log |
50 |
| basedir |
u:\mysql\ |
| bdb_cache_size |
8388600 |
| bdb_home | |
| bdb_log_buffer_size |
0 |
| bdb_logdir | |
| bdb_max_lock |
10000 |
| bdb_shared_data |
OFF |
| bdb_tmpdir | |
| binlog_cache_size |
32768 |
| bulk_insert_buffer_size |
33554432 |
| 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 |
u:\mysql\share\charsets/ |
| collation_connection |
latin1_swedish_ci |
| collation_database |
latin1_swedish_ci |
| collation_server |
latin1_swedish_ci |
| concurrent_insert |
ON |
| connect_timeout |
5 |
| datadir |
u:\mysql_data\ |
| date_format |
%Y-%m-%d |
| datetime_format | %Y-%m-%d
%H:%i:%s |
| default_week_format |
0 |
| delay_key_write |
OFF |
| delayed_insert_limit |
100 |
| delayed_insert_timeout |
300 |
| delayed_queue_size |
1000 |
| 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 |
NO |
| have_bdb |
DISABLED |
| have_compress |
YES |
| have_crypt |
NO |
| have_csv |
NO |
| have_example_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 |
YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size |
1048576 |
| innodb_autoextend_increment |
8 |
| innodb_buffer_pool_awe_mem_mb |
0 |
| innodb_buffer_pool_size |
8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_fast_shutdown |
ON |
| 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 |
1048576 |
| 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_table_locks |
ON |
| innodb_thread_concurrency |
8 |
| interactive_timeout |
28800 |
| join_buffer_size |
33550336 |
| key_buffer_size |
67108864 |
| key_cache_age_threshold |
300 |
| key_cache_block_size |
1024 |
| key_cache_division_limit |
100 |
| language |
u:\mysql\share\english\ |
| large_files_support |
ON |
| license |
GPL |
| local_infile |
ON |
| log |
OFF |
| log_bin |
OFF |
| log_error |
.\errors500.err |
| log_slave_updates |
OFF |
| log_slow_queries |
OFF |
| log_update |
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 |
1073740800 |
| max_binlog_cache_size |
4294967295 |
| max_binlog_size |
1073741824 |
| max_connect_errors |
10 |
| max_connections |
10 |
| 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 |
| myisam_data_pointer_size |
4 |
| myisam_max_extra_sort_file_size |
107374182400 |
| myisam_max_sort_file_size |
107374182400 |
| myisam_recover_options |
OFF |
| myisam_repair_threads |
1 |
| myisam_sort_buffer_size |
83886080 |
| 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 |
1044 |
| pid_file |
u:\mysql_data\test3500.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 |
16777216 |
| 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 |
8384512 |
| relay_log_purge |
ON |
| 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_net_timeout |
3600 |
| slow_launch_time |
2 |
| sort_buffer_size |
9437176 |
| sql_mode | |
| storage_engine |
MyISAM |
| sync_binlog |
0 |
| sync_replication |
0 |
| sync_replication_slave_id |
0 |
| sync_replication_timeout |
0 |
| sync_frm |
ON |
| system_time_zone | Central Daylight
Time |
| table_cache |
512 |
| table_type |
MyISAM |
| thread_cache_size |
8 |
| thread_stack |
196608 |
| time_format |
%H:%i:%s |
| time_zone |
SYSTEM |
| tmp_table_size |
67108864 |
| tmpdir |
u:/mysql_temp;e:/mysql_temp;f:/mysql_temp;d:/mysql_temp |
| transaction_alloc_block_size |
8192 |
| transaction_prealloc_size |
4096 |
| tx_isolation |
REPEATABLE-READ |
| version |
4.1.10-nt-max |
| version_bdb | Sleepycat Software: Berkeley DB
4.1.24: (February 12, 2005) |
| version_comment | MySQL Community Edition
(GPL) |
| version_compile_machine |
i32 |
| version_compile_os |
NT |
| wait_timeout |
28800 |
+---------------------------------+-------------------------------------------------------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
