Hi!
At 05:04 PM 5/16/01 +0200, you wrote:
>>>4) Ok, datafiles created overnight. Now trying alter table ...
>>type=innodb
>>on a
>>>file with 1.2mil records. Still waiting after about 12 hours. Dumping
>>files
>>this
>>>size is going to be problematic. Machine pegs with load of around 12! I
>>suggest
>>>LOTS more testing on BIG tables. Small stuff normally works quite well,
>>but
>>HUGE
>>>tables is where it counts.Thanks for the help.
>>
>>It sounds like that the operation is disk bound. What is the CPU and
>>disk load?
>
>Did not check that - sorry. Machine was very sluggish - takes more than a
minute to log in. I don't think the process yeilds?
>
>>How big is the table in MBs? What kind of indexes you have?
>
>MyISAM table was around 60megs, index file around 35megs, table structure:
>
>CREATE TABLE dailystats (
> email varchar(80) default NULL,
> numsent smallint(5) unsigned default NULL,
> totsent int(10) unsigned default NULL,
> numreceived smallint(5) unsigned default NULL,
> totreceived int(10) unsigned default NULL,
> uniqueno varchar(7) default NULL,
> cc varchar(6) default NULL,
> statsdate date default NULL,
> KEY statsdate (statsdate),
> KEY cc (cc),
> KEY email (email),
> KEY uniqueno (uniqueno)
>) TYPE=MyISAM PACK_KEYS=1;
>
>
>>How much buffer pool you have configured in my.cnf?
>
>Settings taken from manual:
>
>[mysqld]
>
>innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
>innodb_data_home_dir = /data/ibdata
>set-variable = innodb_mirrored_log_groups=1
>innodb_log_group_home_dir = /data/iblogs
>set-variable = innodb_log_files_in_group=3
>set-variable = innodb_log_file_size=30M
>set-variable = innodb_log_buffer_size=8M
>innodb_flush_log_at_trx_commit=1
>innodb_log_arch_dir = /data/iblogs
>innodb_log_archive=0
>set-variable = innodb_buffer_pool_size=80M
>set-variable = innodb_additional_mem_pool_size=10M
>set-variable = innodb_file_io_threads=4
>set-variable = innodb_lock_wait_timeout=50
Ok, it should not be too much disk bound because the buffer pool is that big.
I have never tested altering a table where MyISAM keys were packed.
A bug might be lurking there. Another possibility is that the fsync
slows down disk writes extremely much.
You could try altering the table first to a non-packed MyISAM table
and then to an InnoDB table. Also configuring a very big buffer pool
should be tested. Then look if the operation is disk bound or CPU bound.
Test also with smaller tables. Altering a table which has 1 million rows
should normally last 1 min - 3 hours, depending on whether it is
disk bound or CPU bound.
Regards,
Heikki
>
>--
>Richard Ellerbrock
>[EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
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
