Just curious Chris, did you set MAX_ROWS option during table create to a really large value? If not alter the the table with this and try again, it affects the size of pointers used to create the index. (See CREATE TABLE in the manual)
Hope it helps, Ken ----- Original Message ----- From: "Chris Stoughton" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, October 22, 2002 4:11 PM Subject: Re: How to index a large table? > I have 760 GB free, not 375. > > bash-2.04$ df -h . > Filesystem Size Used Avail Use% Mounted on > /export/data/dp20.a 1.0T 302G 760G 29% /data/dp20.a > bash-2.04$ > > The .MYD file is 134 GBytes > bash-2.04$ ls -lh targetTsObj* > -rw-rw---- 1 mysql mysql 134G Oct 20 05:37 targetTsObj.MYD > -rw-rw---- 1 mysql mysql 5.0k Oct 21 11:38 targetTsObj.MYI > -rw-rw---- 1 mysql mysql 32k Oct 18 21:05 targetTsObj.frm > > so while the "create index" command is running, after the #sql* files > are the same size as the targetTsObj.* files, we still have over 600 GB > of disk space left. > > > Jocelyn Fournier wrote: > > >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 > > --------------------------------------------------------------------- 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
