Hi, In fact, I think you run out of disk space. You MYD takes 135 GB. You have 375 GB free When MySQL try to add the index on your database, it copies the MYD and frm under a #sql* name. So again 135 GB are eaten again. So it remains 240 GB to build the index file, so it's possible you run out of free space during the MYI generation ? (what does df report ? )
Regards, Jocelyn ----- Original Message ----- From: "Chris Stoughton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 22, 2002 9:49 PM Subject: How to index a large table? > I have tried a few different variables to allow a "create index" command > to finish successfully. > > 1. I set tmpdir to be a file system with ample space > 2. I increased tmp_table_size > 3. I increases myisam_sort_buffer_size to 100M > > Here are the sizes of the database files: > > bash-2.04$ ls -l targetTsObj* > -rw-rw---- 1 mysql mysql 144173128578 Oct 20 05:37 targetTsObj.MYD > -rw-rw---- 1 mysql mysql 5120 Oct 21 11:38 targetTsObj.MYI > -rw-rw---- 1 mysql mysql 32750 Oct 18 21:05 targetTsObj.frm > > > I continue to get this error: > > > Database changed > mysql> create index targetTsObjobjId on targetTsObj (objId); > ERROR 1034: 136 when fixing table > mysql> > > It takes 75 minutes, for this to happen. During that time, it creates a > set of files called #sql*.MYD, .MYI, and .frm, and these grow until they > are identical in size to the targetTsObj.* files. > > Then, after several minutes of mysqld consuming 99% CPU time, it ends > with an error. > > 1. Is there a variable I should set in my.cnf? > 2. Should I be using innodb tables instead of myisam? > > Thanks. > > ============================================================================ ============== > For the record, here is what mysqladmin variables says: > +---------------------------------+----------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- --------+ > | Variable_name | > Value > | > +---------------------------------+----------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- --------+ > | back_log | > 50 > | > | basedir | > / > | > | bdb_cache_size | > 8388600 > | > | bdb_log_buffer_size | > 262144 > | > | bdb_home | > /export/data/dp20.a/data/mysql/ > | > | bdb_max_lock | > 10000 > | > | bdb_logdir > | > | > | bdb_shared_data | > OFF > | > | bdb_tmpdir | > /export/data/dp20.a/tmp/ > | > | bdb_version | Sleepycat Software: Berkeley DB > 3.2.9a: (August 14, > 2002) > | > | binlog_cache_size | > 32768 > | > | character_set | > latin1 > | > | character_sets | latin1 big5 czech euc_kr gb2312 gbk > sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 > danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek > win1250 croat cp1257 latin5 | > | concurrent_insert | > ON > | > | connect_timeout | > 5 > | > | datadir | > /export/data/dp20.a/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 | > DISABLED > | > | have_isam | > YES > | > | have_raid | > NO > | > | have_openssl | > NO > | > | 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 | > 16777216 > | > | 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 > | > | interactive_timeout | > 28800 > | > | join_buffer_size | > 131072 > | > | key_buffer_size | > 536866816 > | > | language | > /usr/share/mysql/english/ > | > | large_files_support | > ON > | > | locked_in_memory | > OFF > | > | log | > OFF > | > | log_update | > OFF > | > | log_bin | > OFF > | > | log_slave_updates | > OFF > | > | log_long_queries | > OFF > | > | long_query_time | > 10 > | > | low_priority_updates | > OFF > | > | lower_case_table_names | > 0 > | > | max_allowed_packet | > 1048576 > | > | 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 | > 256 > | > | myisam_max_sort_file_size | > 2047 > | > | myisam_recover_options | > 0 > | > | myisam_sort_buffer_size | > 104857600 > | > | net_buffer_length | > 16384 > | > | net_read_timeout | > 30 > | > | net_retry_count | > 10 > | > | net_write_timeout | > 60 > | > | open_files_limit | > 0 > | > | pid_file | > /var/run/mysqld/mysqld.pid > | > | port | > 3306 > | > | protocol_version | > 10 > | > | record_buffer | > 131072 > | > | record_rnd_buffer | > 131072 > | > | query_buffer_size | > 0 > | > | safe_show_database | > OFF > | > | server_id | > 0 > | > | 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 | > 0 > | > | thread_stack | > 65536 > | > | transaction_isolation | > READ-COMMITTED > | > | timezone | > CDT > | > | tmp_table_size | > 33554432 > | > | tmpdir | > /export/data/dp20.a/tmp/ > | > | version | > 3.23.52-Max > | > | wait_timeout | > 28800 > | > +---------------------------------+----------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- --------+ > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
