Thanks for the hint. I'll try it with more tablespace.
The indexes have been optimized pretty much. I have to check whether I
can send you the table layout.
Just for your information. 3.23.38 has the same problem.
Heikki Tuuri wrote:
>Frank,
>
>there is obviously a bug in the insert buffer code of InnoDB. I will
>look into it. The assertion failure indicates that there was not enough
>free space on an index record page for a record cached in the insert buffer.
>
>Your log files are ok. The size of log files does not constrain the
>size of an InnoDB transaction. The constraint is the size of your
>tablespace.
>
>When I look at your table size I suspect that InnoDB has run out
>of file space during the table load. You have 27 indexes on the table
>and InnoDB does not do any index compression. The size of an index record
>in InnoDB is the size of the primary key + the size of the index columns
>+ 6 bytes additional overhead. In addition, B-tree pages are typically
>only 70 % full. During a large import also the rollback segment takes
>space in the tablespace.
>
>7 million * 30 bytes * 1.5 = 300 MB.
>
>27 * 300 MB = 8 GB. It is probable that you ran out of tablespace.
>
>You should make your tablspace bigger and monitor with
>SHOW TABLE STATUS how much space InnoDB uses. (See the InnoDB manual
>at www.innobase.fi).
>
>Some suggestions: do not create indexes whose selectivity is small:
>for example if a column can have only 2 different values, it usually
>does not pay to create an index.
>
>If you have a primary key of length < 6 bytes, create a primary key
>to the table. If the length of the primary key is > 10 bytes, try
>creating the table without a primary key.
>
>Try loading the table a part at a time. You may create an InnoDB
>table with same definitions as the MyISAM table. Then do several
>
>INSERT INTO innodbtable SELECT myisamtable WHERE yourkey < something
> AND yourkey > something-else;
>
>If you could email me the CREATE TABLE you use for the InnoDB table,
>it would be easier for me to track the insert buffer bug.
>
>Regards,
>
>Heikki Tuuri
>Innobase Oy
>
>>--=-jUldtxkxbNmI4+gJVNJEContent-Type: text/plain; charset=ISO-8859-1
>>Content-Transfer-Encoding: quoted-printable
>>
>>Hi,
>>I'm trying to load a large table with 7 million records, 50 columns and
>>27 indexes into an innodb table. Works fine for MyISAM. Compressed size
>>of the data file is about 280MB (830MB uncompressed) and 1.5 GB for the
>>index file. Platform is RedHat Linux 6.2 with kernel 2.2.16-3 and MySQL
>>is 3.23.37 compiled with egcs-2.91.66 for i686 from the standard MySQL
>>source RPM. The machine is a dual CPU Pentium machine with 512 MB RAM
>>and 2 IDE drives (30GB and 40GB).
>>For the InnoDB tables I have 6 data files with 1GB on 2 disks and 3 log
>>files a 50MB. I've also tried it with 5 log files a 1.5GB.From /etc/my.cnf:
>>
>#
>
>> # Innobase options # innodb_data_file_path =3D
>> vol1/ibdata/ibdata00:1024M;vol2/ibdata/ibdata01:1024M;vol1/ibdata/ibdat=
>>a02:1024M;vol2/ibdata/ibdata03:1024M;vol1/ibdata/ibdata04:1024M;vol2/ibdata=
>>/ibdata05:1024M innodb_data_home_dir =3D /
>> set-variable =3D innodb_mirrored_log_groups=3D1
>> innodb_log_group_home_dir =3D /vol2/iblogs
>> set-variable =3D innodb_log_files_in_group=3D3
>> set-variable =3D innodb_log_file_size=3D50M
>> set-variable =3D innodb_log_buffer_size=3D8M
>> innodb_flush_log_at_trx_commit=3D1 innodb_log_arch_dir =3D /vol2/iblogs
>> innodb_log_archive=3D0 set-variable =3D innodb_buffer_pool_size=3D400M
>> set-variable =3D innodb_additional_mem_pool_size=3D20M
>> set-variable =3D innodb_file_io_threads=3D4
>> set-variable =3D innodb_lock_wait_timeout=3D50
>>Tried to load it with LOAD DATA INFILE from a FIFO and to convert it
>>
>>from a MyISAM table with ALTER TABLE foo TYPE=3Dinnodb;. Both produced the
>
>>same result.After some time MySQL dies and then the rollback begins.
>>This is what I see in the .err file:
>> Innobase: Assertion failure in thread 3076 in file ibuf0ibuf.c line 2264
>> Innobase: we intentionally generate a memory trap.
>> Innobase: Send a bug report to [EMAIL PROTECTED]
>> mysqld got signal 11;
>> The manual section 'Debugging a MySQL server' tells you how to use a
>> stack trace and/or the core file to produce a readable backtrace
>>
>that may
>
>> help in finding out why mysqld died.
>> Attempting backtrace. You can use the following information to find out
>> where mysqld died. If you see no messages after this, something went
>> terribly wrong...
>> Cannot determine thread, ebp=3D0xbf3ff070, backtrace may not be correct.
>> Stack range sanity check OK, backtrace follows: 0x823244a 0x81700fb
>> 0x8170a1e 0x81a5989 0x81c13a8 0x8160006 0x8230856 0x825736a
>> New value of ebp failed sanity check, terminating backtrace!
>> Ibuf insert fails; page free 26, dtuple size 28 Bitmap bits 3
>>
>>Is there a way of turning the transactional logging off during the load
>>of a table? I know that I'm out on my own then but this table is loaded
>>only every once in a while.Am I running out of space on the log/or data files?
>>Is there a way of turning the innobase logging up?
>>Any help is highly appreciated.--=20Frank Schr=F6der <[EMAIL PROTECTED]>
>>
--
Frank Schröder <[EMAIL PROTECTED]>
AirFlash Engineering Europe
www.airflash.com
AirFlash Inc.
