I did a Webinar about MySQL Upgrade – Best Practices Yesterday and there were some questions we could not answer during Webinar, following Jay’s Lead I decided to post them as a Blog Post.
Q: Can you go directly MySQL 5.0 to 5.5 for MyISAM tables?
MyISAM have not been getting any significant development since MySQL 4.1, so in-place upgrade of MySQL 5.0 to MySQL 5.5 should be rather safe from data standpoint. There are still possibilities for sorting order and data type related changes, so you still need to run mysql_upgrade to check if any tables need to be rebuilt for new versions. Note this only applies to the data part of upgrade, you still need to keep into account possible query changes, replication etc.
Q: Is it possible to move MyISAM to Innodb through plain ALTER TABLE statements?
Yes. This is exactly how it is done in many cases. There is really no gain in dumping table via mysqldump and loading it back. If performance is of importance I would recommend using Percona Server with enabled expand_fast_index_creation feature, as I just wrote this option can help creating tables a lot faster. You can also use pt-online-schema-change tool for online migration.
Q: Should we change ROW replication to MIXED during Upgrade?
I prefer simple solutions when possible, which is either using STATEMENT or ROW replication. MIXED mode means you will have to deal with STATEMENT and ROW replication issues at the same time. I would only recommend MIXED replication mode if you have something substantial to gain.
Q: How would Upgrade work if Old Server has Antelope format and New one is Barracuda?
It should not be the problem. Innodb internal format is rather low level and it rarely impacts application functionality wise. As long a you go over normal upgrade checks you should be fine.
Q: What are the key concerns upgrading MySQL 4.1 to MySQL 5.5?
This is jumping over 2 Major versions and many years of development so it is the road less traveled and something not really tested well. One of specific issues might be making MySQL Replication to work. You might need to inject MySQL 5.0 in between so it will convert binary log events, as described here yet it does not guaranty replication will work for any application. There is just too many events. In the worse case you might need to do staged upgrades for your replicated setup or plan some downtime for upgrade.
Q: Upgrading to MySQL 5.5 do we really need all tables changed to Innodb and character set set to utf8?
No. MySQL 5.5 continues to support MyISAM tables, so you can continue use them as needed. I would evaluate changing though as MyISAM tables are on track to be depreciated in future MySQL versions also MyISAM tables are essentially at the state they were in MySQL 4.1 while Innodb storage engine got dramatic performance improvements and is getting better in each new release. Also note the default storage engine changed to Innodb in MySQL 5.5, meaning you might need to set storage_engine=MYISAM if you’re relaying on tables to be created as MyISAM by default. Regarding setting character set to utf8 – this is not needed as MySQL 5.5 continues to support various character sets same as previous MySQL versions. Moreover utf8 character set continues to cause significant performance overhead so you should only use it in case you’re storing multi language data.
Q: What to do if Query Execution Plan changes for the worst during Upgrade?
This is one of the big challenges with upgrade which can’t be resolved in all cases – sometimes queries just need to be changed or supplemented with hints to get to old execution plans. In many cases though you can get to former execution plan either by updating statistics (ANALYZE TABLE) or disabling some of the optimization/statistics related features in new version. Innodb statistics can especially be the problem as they can be very volatile and sometimes it is hard to get them to be same as in old version.
Q: What is about running 2 instances of MySQL with different versions to test upgrade?
This may or may not be good idea. In small environments when we often care more about functionality than performance this can be great way to validate the application is working without significant extra expenses. It also can be used for testing if hardware if there is little extra hardware available. If you’re doing performance testing though you need to ensure only one
instance is running at the same time so other has full resources (including memory) available. Another thing to mention having multiple instances of different MySQL versions on the same box typically means you will install them from binary distribution instead of packages (most typically used in production) which adds the difference compared to your production use. If resources allow I prefer to have upgrade testing done in the environment which is maximally close to production.
Q: How safe is it to upgrade from MySQL 5.5 to Percona Server 5.5
Compatibility is our highest priority with Percona Server, so it should be good as drop in replacement with no data conversion needed. You can also rollback easily unless you have enabled any special Percona Server features which are marked as making database binary incompatible with MySQL. Our focus on compatibility is indeed so high there are number of great features which are disabled by default to make sure you do not start relying on them by accident, so make sure to check out our Documentation to get most our of your Percona Server install.
Thank you for your questions and for attending. See you at our other MySQL Webinars!
“Moreover utf8 character set continues to cause significant performance overhead”.
I have seen this undocumented postulate quite some times on various Percona postings now. Can we have some numbers/benchmarks that justify the term *significant* here?
IMHO you can forget about using non-unicode in the 21th century. After 20 years or so from now US wil be the 3rd or 4th ranking economy of the World and the idea that computer use is linked to English natural language is ridiculous (still IMHO). Without localizations, nu future for business.
NUMBERS PLEASE – or stop replicating this!
Peter Laursen
Webyog
Hi, Peter:
when I did in place upgrade from 5.0->5.1, I got the following errors in the mysql_upgrade output:
error : Table upgrade required. Please do “REPAIR TABLE
account” or dump/reload to fix it!this error happens to all my innodb tables, I understand that “repair table” statement does not work with innodb, thus, I had to do a “alter table table_name engine=innodb”, which is very time consuming since 99% of my 2 TB database is innodb tables.
Do you know why I am getting these errors? is there a faster way to fix this?
Thanks
Peter,
This is the case when benchmarks have little value as it is very workload dependent. How much faster index access compared to full table scan because of lack of index ? I can easily demonstrate you cases with no difference as well as huge difference. The problem with utf8 is mainly when it comes to in memory temporary tables and sort files because it takes 3x more space and can make them go to disk earlier.
Now I am not suggesting NOT to use utf8 when it is needed. Of course when multiple languages is desired or possible the unicode is a way to go. At the same time simply converting database to utf8 without application support on upgrade is a waste and furthermore can cause nasty data issues. Believe me if application is storing latin1 and database interprets it as utf8 bad things can happen.
The question is whenever moving to UTF8 is REQUIRED when you upgrade to MySQL 5.5 and the answer is – not it is not.
honeybee,
Run mysql_upgrade again and see what it prints out. In some versions it would exactly print out this message and when actually do and rebuild table already. If table needs to be rebuilt though it needs to be rebuilt… which can take plenty of time. Using Percona Server with expand_fast_index_creation can significantly reduce the rebuild time for large and well indexed databases (can be 10x or more).
hi, Peter:
Thanks for the advice.
I run mysql_upgrade multiple times, but got the same error message everytime. you mentioned that it might have rebuilt table already, how do I know if the innodb tables already been rebuild or not?
If you run mysql_upgrade again and it prints you some more warnings this means tables were not altered and you need to go ahead and alter them manually one way or another… when you can run mysql_upgrade again to validate no more changes are suggested.
I have problem with mysql query.Can you solve my problem please.
Actually..I have two fields numbers and telecallers.Everyday the list of name of telecaller and the numbers of the persons they have to call is added.My problem is based on following table.On date 1st June.List of the telecaller is as follows.
numbers name
123 ABC
231 XYZ
IN Above table one telecaller named ABC will call on the number 123.
on the next she has to call the same no.but the list of telecaller is as follows on 2nd june
numbers name
123 XYZ
231 XYZ
i JUST WANT TO GIVE THE SAME NUMBER TO THE SAME TELECALLER WHO CALLS HIM ON FIRST FROM THE MYSQL.THAT MEANS I WANT MY ABOVE TABLE IS AS FOLLOWS::
numbers name
123 ABC
231 XYZ
I HAVE TRIED FOR IT VERY MUCH BUT I AM NOT GETTING LOGIC FOR IT WILL U HELP ME??.