Hi folks,
I've got a problem creating an index on a MYISAM table with 302,000,000 lines,
roughly 58 GB on disk. Attached you will find the table definition, output
of "mysqladmin variables", and "mysql -e 'show status'".
After creating the above-mentioned table, I ran:
ALTER TABLE test_table ENABLE KEYS;
loaded the data and then ran (and is currently still runing):
ALTER TABLE test_table ENABLE KEYS;
show processlist reveals;
+--------+----------------------+-------------------------------------------+
| Time | State | Info |
+--------+----------------------+-------------------------------------------+
| 948878 | Repair with keycache | alter table fd_aetna_trad_clm enable keys |
and it's still running! That's roughly 11 days and it's not complete yet!
Here's the current file sizes under the mysql root directory...
-rw-rw---- 1 mysql mysql 61361175364 Mar 18 00:51 test_table.MYD
-rw-rw---- 1 mysql mysql 7320667136 Mar 29 10:07 test_table.MYI
-rw-rw---- 1 mysql mysql 10190 Mar 17 21:16 test_table.frm
The box has 1GB of memory and a P4 1.6GHz processor with EIDE disks (dma
enabled) and no raid. The system is Linux 2.4.21-rc7-openmosix with smp
and the filesystem is ext3, running MySQL version 4.0.13-log.
So, if any of you out there have time to look at the attached file, I'd greatly
appreciate it. I'd like to know when the index creation will potentially end,
and also if I can get the index creation to complete in a shorter amount of
time, given the memory and cpu specs of the box.
Thanks,
Jeff
=====
J. Horner Software
www.jhorner.com
615-347-6899
Hi folks,
I've got a problem creating an index on a MYISAM table with 302,000,000 lines,
roughly 58 GB on disk. Attached you will find the table definition, output
of "mysqladmin variables", and "mysql -e 'show status'".
After creating the above-mentioned table, I ran:
ALTER TABLE test_table ENABLE KEYS;
loaded the data and then ran (and is currently still runing):
ALTER TABLE test_table ENABLE KEYS;
show processlist reveals;
+--------+----------------------+-------------------------------------------+
| Time | State | Info |
+--------+----------------------+-------------------------------------------+
| 948878 | Repair with keycache | alter table fd_aetna_trad_clm enable keys |
and it's still running! That's roughly 11 days and it's not complete yet!
Here's the current file sizes under the mysql root directory...
-rw-rw---- 1 mysql mysql 61361175364 Mar 18 00:51 test_table.MYD
-rw-rw---- 1 mysql mysql 7320667136 Mar 29 10:07 test_table.MYI
-rw-rw---- 1 mysql mysql 10190 Mar 17 21:16 test_table.frm
The box has 1GB of memory and a P4 1.6GHz processor with EIDE disks (dma
enabled) and no raid. The system is Linux 2.4.21-rc7-openmosix with smp
and the filesystem is ext3.
So, if any of you out there have time to look at the attached file, I'd greatly
appreciate it. I'd like to know when the index creation will potentially end,
and also if I can get the index creation to complete in a shorter amount of
time, given the memory and cpu specs of the box.
Thanks,
Jeff
# table definition
create table test_table (
field1 numeric(20),
field2 varchar(15),
field3 varchar(19),
field4 varchar(2),
field5 varchar(2),
field6 CHAR(1),
field7 varchar(8),
field8 DATE,
field9 varchar(6),
field10 varchar(6),
field11 varchar(6),
field12 varchar(6),
field13 varchar(2),
field14 DATE,
field15 DATE,
field16 varchar(2),
field17 varchar(2),
field18 CHAR(1),
field19 varchar(5),
field20 varchar(3),
field21 numeric(12,2),
field22 numeric(12,2),
field23 numeric(12,2),
field24 numeric(12,2),
field25 numeric(12,2),
field26 numeric(12,2),
field27 numeric(12,2),
field28 CHAR(1),
field29 varchar(9),
field30 varchar(2),
field31 varchar(1),
field32 varchar(15),
field33 varchar(8),
field34 varchar(5),
field35 varchar(22),
field36 varchar(2),
KEY fd_aetna_trad_clm_idx1 (field3,field1,field2)
)
MAX_ROWS = 500000000;
# mysqladmin variables
+---------------------------------+------------+
| Variable_name | Value
+---------------------------------+------------+
| back_log | 50
| basedir | /usr/
| bdb_cache_size | 8388600
| bdb_log_buffer_size | 32768
| bdb_home | /data02/mysql/
| bdb_max_lock | 10000
| bdb_shared_data | OFF
| bdb_tmpdir | /tmp/
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (May 14,
2003)
| binlog_cache_size | 32768
| bulk_insert_buffer_size | 8388608
| character_set | latin1
| concurrent_insert | ON
| connect_timeout | 5
| datadir | /data02/mysql/
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| ft_boolean_syntax | + -><()~*:""&|
| ft_min_word_len | 4
| ft_max_word_len | 254
| ft_max_word_len_for_sort | 20
| ft_stopword_file | (built-in)
| have_bdb | YES
| have_crypt | YES
| have_innodb | DISABLED
| have_isam | YES
| have_raid | YES
| have_symlink | YES
| have_openssl | YES
| have_query_cache | YES
| init_file |
| innodb_additional_mem_pool_size | 1048576
| innodb_buffer_pool_size | 8388608
| innodb_data_file_path |
| innodb_data_home_dir |
| innodb_file_io_threads | 4
| innodb_force_recovery | 0
| innodb_thread_concurrency | 8
| innodb_flush_log_at_trx_commit | 1
| innodb_fast_shutdown | ON
| innodb_flush_method |
| innodb_lock_wait_timeout | 50
| 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_mirrored_log_groups | 1
| innodb_max_dirty_pages_pct | 90
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 16777216
| language | /usr/share/mysql/english/
| large_files_support | ON
| local_infile | ON
| locked_in_memory | OFF
| log | ON
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| log_slow_queries | OFF
| log_warnings | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | OFF
| max_allowed_packet | 1047552
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 100
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 268435456
| myisam_max_sort_file_size | 2147483647
| myisam_repair_threads | 1
| myisam_recover_options | OFF
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| new | OFF
| open_files_limit | 0
| pid_file | /var/run/mysqld/mysqld.pid
| log_error |
| port | 3306
| protocol_version | 10
| read_buffer_size | 131072
| read_rnd_buffer_size | 262144
| rpl_recovery_rank | 0
| query_cache_limit | 1048576
| query_cache_size | 0
| query_cache_type | ON
| server_id | 0
| slave_net_timeout | 3600
| skip_external_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /var/run/mysqld/mysqld.sock
| sort_buffer_size | 2097144
| sql_mode | 0
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 131072
| tx_isolation | REPEATABLE-READ
| timezone | UTC
| tmp_table_size | 33554432
| tmpdir | /tmp/
| version | 4.0.13-log
| wait_timeout | 28800
+---------------------------------+-----
# mysql -e 'show status'
Variable_name Value
Aborted_clients 14
Aborted_connects 5
Bytes_received 150482
Bytes_sent 18937248
Com_admin_commands 15
Com_alter_table 23
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 10
Com_change_master 0
Com_check 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 20
Com_delete 2
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 3
Com_drop_table 17
Com_flush 13
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 0
Com_insert_select 14
Com_kill 9
Com_load 98
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_purge 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_rollback 0
Com_select 80
Com_set_option 8
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 8
Com_show_databases 30
Com_show_fields 181
Com_show_grants 0
Com_show_keys 6
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 42
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 4
Com_show_innodb_status 0
Com_show_tables 46
Com_show_variables 4
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 0
Connections 259
Created_tmp_disk_tables 3
Created_tmp_tables 19
Created_tmp_files 9
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_read_first 21
Handler_read_key 6858914
Handler_read_next 51691864
Handler_read_prev 0
Handler_read_rnd 4542750
Handler_read_rnd_next 339143211
Handler_rollback 0
Handler_update 4349988
Handler_write 697690786
Key_blocks_used 15586
Key_read_requests 774274910
Key_reads 138684651
Key_write_requests 122246901
Key_writes 68411504
Max_used_connections 5
Not_flushed_key_blocks 5879
Not_flushed_delayed_rows 0
Open_tables 13
Open_files 21
Open_streams 0
Opened_tables 104
Questions 869
Qcache_queries_in_cache 0
Qcache_inserts 0
Qcache_hits 0
Qcache_lowmem_prunes 0
Qcache_not_cached 0
Qcache_free_memory 0
Qcache_free_blocks 0
Qcache_total_blocks 0
Rpl_status NULL
Select_full_join 18
Select_full_range_join 0
Select_range 2
Select_range_check 0
Select_scan 79
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 131
Sort_merge_passes 2
Sort_range 0
Sort_rows 1077462
Sort_scan 18
Ssl_accepts 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_accept_renegotiates 0
Ssl_connect_renegotiates 0
Ssl_callback_cache_hits 0
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_timeouts 0
Ssl_used_session_cache_entries 0
Ssl_client_connects 0
Ssl_session_cache_overflows 0
Ssl_session_cache_size 0
Ssl_session_cache_mode NONE
Ssl_sessions_reused 0
Ssl_ctx_verify_mode 0
Ssl_ctx_verify_depth 0
Ssl_verify_mode 0
Ssl_verify_depth 0
Ssl_version
Ssl_cipher
Ssl_cipher_list
Ssl_default_timeout 0
Table_locks_immediate 312
Table_locks_waited 1
Threads_cached 0
Threads_created 258
Threads_connected 5
Threads_running 5
Uptime 1109224
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
