Hello! I hope someone can help us with a problem we have:
We had a table that was 35G big and the index file was about 16G (ca 120 million records). We ran into a problem where the database produced an "no more room in index file" error. To fix this we regenerated the table and set max_rows to 10.000.000.000. (MySQL changed max_rows to 4.294.967.295, which I guess is an upper limit?). We stripped the table for all indecies except one unique index and inserted the data. We used the opportunity to remove old, unneeded data leaving us with a table of 20G and index file of 4G. Now, our next step was to add the other indecies. We added all indecies with one alter table statement. Mysql started with "copy to tmp table" and after a few days it changed to "repair by keycache", which is odd because we have set the mysql variables pretty high (se below), so we had anticipated "repair by sorting" When finished, the index file should be about 10G, but when the tmp MYI reach 4.5 G it slows down and when it reach 5.2G it stops growing. There is no error message. The CPU usage is minimal and the IO is also minimal compared to what we have seen the disk system perform earlier. (It's a 1+0 raid with 10 disks, IIRC.There is also plenty of disk space) Could it be that setting the max_rows to this high number and then adding the indecies would make the index tree balancing go astray? Any pointer to a solution would be very welcome! All table are MyISAM. (I know that it might not bee the smartest type to use) Mysql version : 3.23.41 OS: RedHat 7.1(Linux version 2.4.9-31Enterprise) (Red Hat Linux 7.1 2.96-98) DISK The diskset is a 1+0 raid with about 10 15k rpm disks (plenty of space) -----------------------+---------------------------------------------------- ---------------------------------------------------------------------------- -------------------------------------------------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- --------+ | back_log | 50 | basedir | /usr/ | bdb_cache_size | 8388600 | bdb_log_buffer_size | 262144 | bdb_home | /data/mysql/ | bdb_max_lock | 10000 | bdb_logdir | | bdb_shared_data | OFF | bdb_tmpdir | /data/mysql/ | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (August 11, 2001) | binlog_cache_size | 32768 | character_set | latin1 | character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 | | concurrent_insert | ON | connect_timeout | 5 | datadir | /data/mysql/ | delay_key_write | ON | delayed_insert_limit | 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | flush | OFF | flush_time | 0 | have_bdb | YES | have_gemini | NO | have_innodb | NO | have_isam | YES | have_raid | NO | have_ssl | NO | init_file | | interactive_timeout | 28800 | join_buffer_size | 131072 | key_buffer_size | 402649088 | language | /usr/share/mysql/english/ | large_files_support | ON | locked_in_memory | OFF | log | OFF | log_update | OFF | log_bin | ON | log_slave_updates | OFF | log_long_queries | OFF | long_query_time | 10 | low_priority_updates | OFF | lower_case_table_names | 0 | 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_recover_options | 0 | myisam_max_extra_sort_file_size | 2000 | myisam_max_sort_file_size | 2097152000 | myisam_sort_buffer_size | 1572864000 | net_buffer_length | 16384 | net_read_timeout | 30 | net_retry_count | 10 | net_write_timeout | 60 | open_files_limit | 0 | pid_file | /data/mysql/dlp-news02.pid | port | 3306 | protocol_version | 10 | record_buffer | 2093056 | record_rnd_buffer | 2093056 | query_buffer_size | 0 | safe_show_database | OFF | server_id | 2 | slave_net_timeout | 3600 | skip_locking | ON | skip_networking | OFF | skip_show_database | OFF | slow_launch_time | 2 | socket | /var/lib/mysql/mysql.sock | sort_buffer | 2097144 | sql_mode | 0 | table_cache | 512 | table_type | MYISAM | thread_cache_size | 8 | thread_stack | 65536 | transaction_isolation | READ-COMMITTED | timezone | CEST | tmp_table_size | 33554432 | tmpdir | /data/mysql/ | version | 3.23.41-log | wait_timeout | 28800 Mvh / Best Regards Gunnar Lunde sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
