MySQL changing large table to InnoDB -


i have mysql server running on centos houses large (>12gb) db. have been advised move innodb performance reasons experiencing lockups application relies on db becomes unresponsive when server busy.

i have been reading around , can see alter command changes table innodb take long time , hammer server in process. far can see, change required use following command:

alter table t engine=innodb 

i have run on test server , seems complete fine, taking 26 minutes on largest of tables needs converted.

having never run on production system interested know following:

  1. what changes recommended made mysql config take advantage of additional performance of innodb tables? server has 3gb assigned innodb cache - thinking of increasing 15gb once additional ram installed.
  2. is there else should server change?

i recommend using either percona mysql or mariadb. both have tools out of innodb, tools diagnose , optimize database further (for example, percona's online schema change tool used alter tables without downtime).

as far optimization of innodb, think agree innodb_buffer_pool_size 1 of important parameters tune (and typically people set around 70-80% of total available memory, that's not magic number). it's not important config variable, though, , there's no magic run_really_fast setting. should pay attention innodb_buffer_pool_instances (and there's discussion topic on https://dba.stackexchange.com/questions/194/how-do-you-tune-mysql-for-a-heavy-innodb-workload)

also, should check out tips offered in mysql documentation (http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb.html). it's idea pay attention innodb hit ratio (rolado on @ dba stackexchange has great answer on topic, eg, https://dba.stackexchange.com/questions/65341/innodb-buffer-pool-hit-rate) , analyze slow query logs carefully. towards later end, recommend taking @ percona again. slow query analyzer top notch , can give leg when comes optimizing sql performance.


Comments

Popular posts from this blog

java - Plugin org.apache.maven.plugins:maven-install-plugin:2.4 or one of its dependencies could not be resolved -

Round ImageView Android -

How can I utilize Yahoo Weather API in android -