Gary's got another point about the transactions.
I'd still look to using mysqldump first if possible, if they have the
ability it will be remarkably faster.
Otherwise, turning transactions off before the insert, and locking the
table as well, (if you haven't already done that) could prove to save
you a lot of time.
Scott.
On Tue, 1 Mar 2005 15:32:54 -0800, Gary Richardson
<gary.richardson@stripped> wrote:
> What have you actually done to 'tune' the server? How are you doing
> the inserts?
>
> InnoDB uses transactions. If you are doing each row as a single
> transaction (the default), it would probably take a lot longer.
>
> I assume you're doing your copying as a INSERT INTO $new_table SELECT
> * FROM $old_table. Try wrapping that in a
> BEGIN;
> INSERT INTO $new_table SELECT * FROM $old_table;
> COMMIT;
>
> How do you have your table space configured?
>
> Just some random thoughts..
>
> On Tue, 1 Mar 2005 17:24:32 -0600, Alfredo Cole <alfredo@stripped>
> wrote:
> > Hi:
> >
> > I have switched from MyISAM tables to InnoDB, using MySQL 4.1.10 under SuSE
> > 8.2.
> >
> > My application, an ERP system developed in-house, uses 70 tables, the largest
> > one holding a little over one million rows. To assist when changing table
> > structures, we developed a software that creates a new table for each of the
> > 70 tables, one at a time, using the new structure, copies all of the records
> > from the old table to the new one, drops the old one and renames the new one.
> >
> > Using MyISAM tables, this process takes 10 minutes using a two Xeon 2.4 Ghz
> > server, with 4 Gb RAM and SCSI RAID 5 disks. The same system takes 2 1/2
> > hours using InnoDB tables with the same configuration. We have followed the
> > guidelines for tuning the server, and still, we find this to be excessive.
> > Can somebody point to some docs, guidelines or web sites we can consult to
> > improve InnoDB's performance? It seems inserting many rows decreases
> > performance significantly.
> >
> > Thank you and regards.
> >
> > --
> > Alfredo J. Cole
> > Grupo ACyC
> > www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
