I suspect your slow UPDATES occur due to your high innodb_max_dirty_pages_pct
. This is a very good article on how InnoDB handles checkpoints and dirty page flushing, but the gist of my recommendation is to lower innodb_max_dirty_pages_pct
to 60 or 70 and see if that helps.
Unfortunately, I suspect you are running native InnoDB in 5.1 and not the InnoDB plugin. This will limit your ability to tune your checkpoints.
ANSWERS TO YOUR QUESTIONS
1) Since I have a Master/Slave structure, do I need to manage this and change the Slave also? Or any structural changes will directly affect the Slave?
I would recommend you change the Master to all InnoDB and leave the Slave all MyISAM
You will definitely need to make configuration changes since InnoDB has more a lot more moving parts in memory and disk.
2) Related to n.1, do I have to stop the slave before the conversion?
You can disable binary logging in your session and convert all tables on the Master without replicating any of it to the Slave. Therefore, you do not need to run STOP SLAVE;
3) I'm using MySQL 5.5.32 (on ubuntu 12.04). Should I update MySQL (to 5.6.*) before the conversion?
You need to upgrade either way. Why? I wrote a post describing how Oracle had a security path installed in MySQL 5.5.40 and 5.6.21 (See my post Find MySQL Patch Level)
- If you want to remain with MySQL 5.5, upgrade to MySQL 5.5.40 or greater
- If you want to upgrade to MySQL 5.6, upgrade to MySQL 5.6.21 or greater
4) I use only stored procedure to run queries. Is there something I need to change for the new engine?
No changes for stored procedures are necessary
I have discussed this before. Here a two of my posts where I describe having a Master and Slave with different engines:
CONVERSION SCRIPT
Create a script that has these instructions. It will convert everything to InnoDB without replicating:
CONVERT_SCRIPT=/tmp/MassConvertRowFormat.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Query to Create SQL Script
# that Converts All Your MyISAM tables to InnoDB
#
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY data_length"
#
# 1. Disable the Binary Logging of Your Session
# 2. Append storage engine conversion statements
# 3. Execute the Script
#
echo "SET SQL_LOG_BIN = 0;" > ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" >> ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} < ${CONVERT_SCRIPT}
NOTE: Please do your homework and make changes to the InnoDB settings and log files before doing this conversion. Without do so, some have experienced slow performance because of the default settings of InnoDB
MY RECOMMENDED OLDER POSTS
Here are my other posts on how and why to tune InnoDB after (or before) converting from MyISAM
Best Answer
My comments on converting from MyISAM to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb
My comments on Galera (PXC): http://mysql.rjweb.org/doc.php/galera
ALTER
, on Galera/PXC, comes in two flavors;Further discussion (in addition to the manual, and my doc): http://www.codership.com/wiki/doku.php?id=rolling_schema_upgrade