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]>
---------------------------------------------------------------------
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
