Hi!
All this was already mentioned in the discussion thread, but I summarize it.
To make the InnoDB inserts to run as fast as possible:
1) Tune the buffer pool size and ib_logfile size as recommended at:
http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html
2) Set:
innodb_flush_log_at_trx_commit=2
for the time of the import. Alternatively, you can wrap the inserts of, say,
1000 rows inside BEGIN ... COMMIT, so that the log does not get flushed to
disk after each individual insert.
3) SET FOREIGN_KEY_CHECKS = 0;
<do the import> ;
SET FOREIGN_KEY_CHECKS = 1;
But make sure your data does not break foreign key constraints!
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
----- Original Message -----
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, January 31, 2006 8:01 PM
Subject: Re: Insert performance
--=_alternative 0062A2DC85257107_=
Content-Type: text/plain; charset="US-ASCII"
Imran Chaudhry <[EMAIL PROTECTED]> wrote on 01/31/2006 12:44:17 PM:
> I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7
millin records. How do I make >my insert run faster.The table has
three foreign key references and the referencing columns are
>indexed . Is that impacting the insert statement performance.
Just thought I'd add a little to the good advice already given to you.
I find an easy way to derive the extended insert syntax is to perform
a mysqldump of a small table.
From 4.1 onwards this wrapped the table dump in the extended insert
syntax.
Regards,
Imran
--
http://www.ImranChaudhry.info
MySQL Database Management & Design Services
The only drawback to doing that for really large tables is you have to
provide mysqldump with the --max_allowed_packet value for the server you
want to send the data to. If you don't, mysqldump will make a single
HUMONGOUS extended insert statement that will be refused by the
destination server. I found this out the hard way a long time ago.
For example: If on server A your max_allowed_packet value is 4193280...
serverA>show variables like 'max%';
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| max_allowed_packet | 4193280 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
+----------------------------+------------+
17 rows in set (0.00 sec)
...and the data you want to load comes from server B then you have to tell
mysqldump to use the value 4193280 or it will make just one large extended
INSERT statement per table, regardless of table size
\mysql\bin> mysqldump -u login -p -h serverB --max_allowed_packet=4193280
sourcedatabasename
Of course there are other options you need to worry about too (like
quoting and compressing) but you get the picture. You can read about them
in the fine manual or check the --help option of mysqldump.
mysqldump --help
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0062A2DC85257107_=--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
