Changed MyISAM to InnoDB - getting really slow UPDATES

[Tama]

Hi there

I've recently got all inspired and purchased "High Performance SQL" - an O'Reilly's book. Using a slow query log I'd already identified two tables that were causing problems. Both had an equal number of SELECT and UPDATE/DELETE statements being run against them.

This was leading to table-locking problems and after reading about the advantages (and disadvantages) of InnoDB tables versus MyISAM tables I thought that it would be good to convert both tables to InnoDB.

The conversion was carried out using:

ALTER TABLE `table_name` TYPE = INNODB

I then cleared my slow query log and waited to see what happened...

..and it's not good. The UPDATE commands are taking even longer than before and I'm swing between coverting the tables back to MyISAM and feverishly searching for a reason.

My two thoughts are:
1.) I somehow need to refresh the indexes on the InnoDB tables -but I'm not sure how to. It seems the only way I can is to delete the current indexes and to create new ones.

2.) I've messed up the InnoDB settings. As I'm using the default ones this could be a distinct possibility. I tried putting the InnoDB settings from large.cnf into my.cnf and MySQL wouldn't restart.

I'll list my InnoDB variables at the bottom of this post. Any suggestions, or comments are greatly appreciated.

Thank you in advance
Tama

innodb additional mem pool size  1048576  1048576  
innodb buffer pool size  8388608  8388608  
innodb data file path  ibdata1:10M:autoextend  ibdata1:10M:autoextend  
innodb data home dir    
innodb file io threads  4  4  
innodb force recovery  0  0  
innodb thread concurrency  8  8  
innodb flush log at trx commit  1  1  
innodb fast shutdown  ON  ON  
innodb flush method    
innodb lock wait timeout  50  50  
innodb log arch dir  ./  ./  
innodb log archive  OFF  OFF  
innodb log buffer size  1048576  1048576  
innodb log file size  5242880  5242880  
innodb log files in group  2  2  
innodb log group home dir  ./  ./  
innodb mirrored log groups  1  1  
innodb max dirty pages pct  90  90

I do my sums on fingers and thumbs.

 

[Bastien]

Hey Tama,

have you converted the sql statements from the application to use the begin, sql statement, commit format?

Bastien

Cat, the other other white meat

 

[Tama]

Hi Bastien

Sorry for the delay in replying, network problems drove me to take a break from work, which was all good.

I haven't made the format conversion you mention. Does InnoDB require BEGIN/COMMIT to function properly? I had assumed that BEGIN/COMMIT was only needed with multiple queries in one package.

Cheers
Tama

I do my sums on fingers and thumbs.

 

[Bastien]

nope, needs its for one or more...try it and let me know

Bastien

Cat, the other other white meat

 

[Tama]

Cool - I know just the table to test it on - will give it a shot over the next few days and get back to you.

Cheers
Tama

I do my sums on fingers and thumbs.