Indeed, setting MAX_ROWS lets me create the index. Thanks for the tip. How would I find out what the MAX_ROWS setting on a table is?
----- Original Message ----- From: Ken Menzel <[EMAIL PROTECTED]> Date: Tuesday, October 22, 2002 4:05 pm Subject: Re: How to index a large table? > 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
