Re: Way too slow Load Data Infile
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| 1 | | 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
Re: Way too slow Load Data Infile
Hi Mike, Try the following: ALTER TABLE tblname DISABLE KEYS; LOAD DATA INFILE ... ALTER TABLE tblname ENABLE KEYS; hth, Cor - Original Message - From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, July 29, 2006 4:50 AM Subject: Re: Way too slow Load Data Infile 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| 1 | | 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
Re: Way too slow Load Data Infile
At 03:56 AM 7/29/2006, C.R.Vegelin wrote: Hi Mike, Try the following: ALTER TABLE tblname DISABLE KEYS; LOAD DATA INFILE ... ALTER TABLE tblname ENABLE KEYS; hth, Cor Hi, I had tried that on another (larger) t year, and it does of course load the data slightly faster because the non-unique keys are disabled (the unique keys primary key are still enabled), but when it tries to rebuild the index with the Alter Table Enable Keys, the same thing happens. It runs for days trying to rebuild the index. It looks like rebuilding the index is disk bound even though there is 400MB still free and available for use. I could go out and buy more RAM but I doubt with my current MySQL settings it's going to do any good. So if there is a way to improve the index building by adjusting my MySQL settings, I'd certainly like to hear from you. If I don't get this solved soon, I may have to abandon MySQL and use another database. :( Mike - Original Message - From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, July 29, 2006 4:50 AM Subject: Re: Way too slow Load Data Infile 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| 1 | | 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
Way too slow Load Data Infile
I executed a Load Data Infile 24 hours ago and its still running. Yikes! I have 6 of these tables to load and at this rate it will take forever. Can someone please run through my Status variables and let me know if there is a way I can optimize it? (These values are current with the Load Data still running.) This is running on a dedicated AMD 3500 machine without no one else accessing it. There is 400+MB of memory remaining on a 1gb machine running XP. TIA Mike +--++ | Variable_name| Value | +--++ | Aborted_clients | 12 | | Aborted_connects | 0 | | Binlog_cache_disk_use| 0 | | Binlog_cache_use | 0 | | Bytes_received | 680821739 | | Bytes_sent | 2089813461 | | Com_admin_commands | 19 | | Com_alter_db | 0 | | Com_alter_table | 2 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 4 | | Com_change_db| 0 | | Com_change_master| 0 | | Com_check| 0 | | Com_checksum | 0 | | Com_commit | 4 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 87 | | Com_dealloc_sql | 0 | | Com_delete | 117| | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 88 | | 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 | 1 | | Com_insert_select| 156| | Com_kill | 0 | | Com_load | 18 | | 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 | 961267 | | Com_set_option | 69 | | 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 | 12 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys| 10 | | Com_show_logs| 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 19 | | Com_show_slave_hosts | 0 | | Com_show_slave_status| 0 | | Com_show_status | 4 | | Com_show_storage_engines | 0 | | Com_show_tables | 14 | | Com_show_variables | 2 | | Com_show_warnings| 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables| 0 | | Com_update | 28461549 | | Com_update_multi | 0 | | Connections | 13 | | Created_tmp_disk_tables | 0 | | Created_tmp_files| 0 | | Created_tmp_tables | 91 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 444013 | | Handler_discover | 0 | | Handler_read_first |
Re: Way too slow Load Data Infile
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| 1 | | 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
