There are several reasons why that could be happening, and it is very difficult to point one specific cause -it could be because a change on the query optimizer, bad query statistics, parameters whose defaults changed from version to version, the changes on replication format, the data types you are handling, a change on the row format, etc.
The critical point here is to identify the differences on query execution: it would be nice if that would have been identified before the upgrade, but if you say that the master is not showing that behaviour, that may be enough. You need to discover which queries are creating those temporary tables. As you are using Percona Server 5.6, there are 2 main tools:
- use the extended logging available on Percona and using pt-query-digest to identify and summary the offending queries, or
- Use the performance_schema available since 5.6
Both tools can be used for identifying those bad queries. After that, you will be able to profile each query with the standard tools: EXPLAIN
, the profiling metrics, the handler statistics, etc. In some cases, you may need to change the configuration, in other the queries, indexes or the structure itself of your database (for example, blobs usually cause problems because temporary tables using them cannot be on memory).
One thing that may actually be useful, independently of all of above is the replication format. If you are suffering from temporary table creation on the slave you are using STATEMENT
or MIXED
replication format (or your tables do not have primary keys). Row based replication, in some cases (when queries do not modify many records at the same time) may help with both query latency and avoiding slave drifting, so you should at least consider that.
You could load the mysql schema into the 5.6 instance and run mysql_upgrade
. I have a much cleaner method in mind: You could dump the grants as pure SQL from the MySQL 5.5. instance and then run it in the MySQL 5.6 instance.
You can do one of the following:
mysql -u... -p... --AN -e"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';') FROM mysql.user WHERE user<>''" | mysql -u... -p... --skip-column-names -AN | sed 's/$/;/g' > MySQLUserGrants.sql
Once you create the script from the 5.5. instance, run the script in the 5.6 instance.
GIVE IT A TRY !!!
Note : I have a post where I helped someone manually fix mysql.user
(MySQL service stops after trying to grant privileges to a user). Try this only when everything else fails ...
Best Answer
MyISAM didn't change between 5.5 and 5.6. It's not being developed, and it's virtually stagnant.
Use
FLUSH TABLES WITH READ LOCK
and then it's safe to copy the MyISAM tables. This is mentioned in https://dev.mysql.com/doc/refman/5.6/en/backup-methods.htmlOther solutions:
mysqldump --tab outputs tab-delimited data files instead of SQL, so it's much faster to import with mysqlimport.
mysqlhotcopy does a physical copy of MyISAM tables (it doesn't work for InnoDB tables). But be careful because mysqlhotcopy is on the chopping block for deprecation.
mydumper, which dumps to SQL, but it supports compression and multi-threaded execution for both dump and restore. Unfortunately it does not support an option like
--tab
.