Wai, At 11:39 AM 11/2/01 -0500, you wrote: >Hi, >The table has 76 columns in it, there are 35 columns are not populated yet >but they are all in VARCHAR format (NOT FIXED-SIZE columns). Does it still >take space even in VARCHAR format?
no, a NULL in a VARCHAR column only takes 1 - 2 bytes. >Let's say the 35 columns take space, the worst case will be doubling the >space like myISAM table is taking that roughly around 500M in disk space, >why it took 2.3G instead? I do not know, it should not. Please send me the CREATE TABLE statement and a dump of a few typical rows you have. >When altering a table(remove index), mySQL handles myISAM type table by >copying it to temporary table then remove the index saving in MYI file. How >InnoDB handles removing index? Similar way? > >Are you saying I should drop the index from an InnoDB table by doing: > >alter table mytable >drop index idx1, type=myISAM I mean that if you have little disk space left, then converting to to MyISAM will not use so much space. But it does not really help to drop the index, because the index cannot be responsible for so much disk space usage. >Any suggestions are greatly appreciated. > >Wai >Zeborg Regards, Heikki http://www.innodb.com > > > >-----Original Message----- >From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] >Sent: Friday, November 02, 2001 10:59 AM >To: [EMAIL PROTECTED] >Subject: Re: InnoDB disk full problem > > >Wai, > >>Hi, >> >>Help!!! Can anyone kindly tell me why InnoDB will take up 10 times table >>space comparing with myISAM's table file? I only have one index built in >>this table and I don't think it took a lot of table space. The table has >>543009 records and has a file size 256M in myISAM but it takes 2.3G table >>space in InnoDB. > >what is a typical row like? I calculate from above that a typical row takes >500 bytes in MyISAM. Does it have many NULL columns? In InnoDB a NULL stored >in a fixed-size column takes the same size as a non-NULL value (this is to >avoid fragmentation). > >What is the table CREATE statement? > >>When I try to drop the index from InnoDB, it gives me a error message: >> >>ERROR 1114: The table '#sql-258_4' is full > >ALTER TABLE in MySQL is performed by creating a temporary table where data >is copied. You should try ALTER TABLE ... TYPE = MYISAM. > >>The InnoDB is a very new concept to me, I am testing mysqld-max-nt 3.23.43 >>on Windows 2000(512M memory 833MHz CPU) with the following configuration: >> >>set-variable= key_buffer=32M >>set-variable= max_allowed_packet=1M >>set-variable= table_cache=256 >>set-variable= sort_buffer=1M >>set-variable= record_buffer=1M >>set-variable= myisam_sort_buffer_size=16M >>set-variable= thread_cache=8 >> >>innodb_data_home_dir = c:\ibdata >>innodb_data_file_path = ibdata1:1500M;ibdata2:1500M >>set-variable = innodb_mirrored_log_groups=1 >>innodb_log_group_home_dir = c:\iblogs >>set-variable = innodb_log_files_in_group=3 >>set-variable = innodb_log_file_size=50M >>set-variable = innodb_log_buffer_size=8M >>innodb_flush_log_at_trx_commit=0 >>innodb_log_arch_dir = c:\iblogs >>innodb_log_archive=0 >>set-variable = innodb_buffer_pool_size=300M >>set-variable = innodb_additional_mem_pool_size=20M >>set-variable = innodb_file_io_threads=4 >>set-variable = innodb_lock_wait_timeout=50 >>set-variable=tmp_table_size=500M >> >>Wai Lee >>Zeborg > >Regards, > >Heikki >http://www.innodb.com/ibman.html > > > >--------------------------------------------------------------------- >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
