innodb and HIGH server load

MySQL & MariaDB Other MySQL® Questions
1

hi,
i’ve some two forum tables with abot 700Mb each one, and they was type myisam. I was getting some lock problems and i decided to switch them to innodb, but server load growed from 3 to 20. I followed your steps but i got not any server load improvements.

should i back to myisam? or is there any way to solve it?

thanks

2

spud,

Tuning server settings (I guess this is what you’re speaking about by following my steps) only helps if they are the main thing limiting your performance. In your case it might be something else. Going to Innodb tables should not be taken lightly - it may increase database size dramatically especially if you have a lot of indexes as Innodb does not pack indexes.

Also explain plans may change.

In your case I would take a look at queries and see which of them started to take a lot of time and then learn why it happened.

3

Peter,

Is it true InnoDB doesn’t support packed keys even when specified?

E.g.
ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=4294967295 PACK_KEYS=1;

Will this result in packed keys or will it be ignored because of the storage engine?

4

Yes. Innodb does not support packet keys.

If you specify pack_keys=1 it will be simply ignored. Many options are MyISAM specific but accepted for other storage engines,
ie MAX_ROWS, DELAY_KEY_WRITES etc.