MySQL 5.6.13 released

Just some thoughts I wanted to share. I am preparing to upgrade my servers from MySQL 5.5 to MySQL 5.6. I read this article: http://www.mysqlperformanceblog.com/2013/02/18/is-mysql-5-6-slower-than-mysql-5-5/ here two quotes from the article:

Another thing to remember about MySQL 5.6 is – it comes with Performance Schema enabled by default. Even though Performance Schema overhead was reduced in MySQL 5.6 it is still not free and it will cause an overhead, [..]
Click to expand...

For single thread MySQL 5.5 is 11% faster than MySQL 5.6. If you disable Performance Schema in MySQL 5.6 comes to just about 3% showing Performance Schema itself is responsible for 7.5% overhead in this case. This is quite reasonable result and I do not think 3% difference will be noticed in most production cases.
Click to expand...

Because of this, I have decided to turn off Performance Schema in /etc/my.cnf when I upgrade to MySQL 5.6, this is how to do it: Add the following somwhere after [mysqld]:
Code: 
performance_schema=off

More information about that in http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html and http://dev.mysql.com/doc/refman/5.6...stem-variables.html#sysvar_performance_schema - I welcome any feedback on this and anything related to MySQL 5.6.
 
I have now upgraded all my servers from MySQL 5.5.32 to 5.6.13. I did set performance_schema=off in my.cnf, and everything seems to run quick without problems.

However on one of my servers with very many databases and tables, during the upgrade, running mysql check, it was only able to run about 30% of the database tables before it stopped, it gave errors like this before it stopped:

Code: 
/usr/bin/mysqlcheck: Error: Couldn't get table list for database USER_NAME: Can't read dir of './USER_NAME/' (errno: 24 - Too many open files)

I hope it is not important that mysqlcheck finish run on all databases, because as said, it was not able to finish. Everything seems to work without problems.
 
as soon as i upgraded 5.6 my mysql started to consume alot more cpu, my cnf was the same but i tried mysql tuner to optimize my.cnf (including performance_schema=off)but it didnt work. theserver load was 1.7 as average. monitoring using htop i noticed thst mysqld ate 2 of my cpu cores at 80% most of the time so when i downgraded mysql 5.5 my server load went back to normal. now consuming at highest 10% at 2 cpu cores max but thge server load is back to 0.20
so i recommend ppl not to upgrade 5.6 as it seems to be a resource hog

mrtg graph when i downgraded to 5.5


VgYEtwF.png
 
Last edited:
I have upgraded all my four servers to MySQL 5.6.13, and serverload is the same as before the upgrade, so I don't have any problems there. But I do not use "mysql tuner" at all, but only have very few lines in my.cnf, wich is manually added.

Only thing I wish, is a new option in options.conf to disable performance_schema at compile time, because I have read it is not equal good result by doing it with "performance_schema=off" in my.cnf, but it is better for performance to disable it at compile time. So a option in options.conf to disable performance_schema in MySQL 5.6.x at compile time, would be great to have.

Remember that performance_schema was never enabled as default in MySQL 5.5.x, so this is a new default in 5.6.x, and I don't like it.
 
Frej, ditto,

Can you tell me which database engine that you mostly use on the server (InnoDB or MyISAM)? I found that the same my.cnf setting that works great in 5.5 doesn't work as it should in 5.6 especially with InnoDB database engine. (performance_schema=off) My Disk IOs jump from ~20 -> ~85. Disk latency from ~5 -> ~280. Lots of the graphs from Munin shows the performance problem in IO related . The CPU usage also increased.

After tested it for about a month, I decided to switch back to 5.5 yesterday.
 
Last edited:
Our servers is used for shared hosting. Most of our customers is running WordPress wich uses MyISAM. However we also have some users running Drupal wich uses InnoDB. But most of the databases is MyISAM. However we have plenty disk IO, because the servers are undersold, and we use only 15K SAS disk in hardware raid 10. Here is the content of my.cnf on my servers:

Code: 
[mysqld]
innodb 
local-infile=0
open_files_limit=30000
max_allowed_packet=20M
default-storage-engine=MyISAM
innodb_file_format=Barracuda
innodb_file_per_table=1
performance_schema=off

Please note that the line "open_files_limit=30000" is something I raised after to this high level after upgrading to MySQL 5.6.x It was suddenly not enough with 1024 after the upgrade, it would no longer be able to make nighly backup of all databases with previous 1024 value. So one thing is very clear, and that is MySQL 5.6.x does use/need MORE open files limit. Maybe that would be better if performance_schema was disabled at compile time instead of in my.cnf? I don't know.

But I do hope Directadmin can add a option to options.conf to disable performance_schema at compile time in MySQL 5.6.x
 
Richard G said:
What does this setting do?
In examples I only see "innodb_xxx_xxx =" settings. What is the standalone "innodb" good for? Isn't that innodb by default?
Click to expand...

Actually I don't know. I have it there because it's been there as default since my first DirectAdmin server in 2009 with MySQL 5.0.x

I have tried to find out what it means/does, but have not been able to. I was going to ask in a forum post about it later. Hopefully someone knows more about this?
 
I do recommend everybody with shared hosting to enable "innodb-file-per-table" option (prior starting the server).

offtopic:

ditto - please note that Wordpress runs without any problems on InnoDB. You can safely convert Wordpress databases to InnoDB with no problems.
 
Last edited:
wattie said:
ditto - please note that Wordpress runs without any problems on InnoDB. You can safely convert Wordpress databases to InnoDB with no problems.
Click to expand...

Yes, I know WordPress runs fine with InnoDB. However, this is shared hosting servers, so if my WordPress customers want InnoDB, they will have to convert themself. I have both InnoDB and MyISAM enabled on my servers, but I have set MyISAM to be default in my.cnf, so if the CMS under installation does not choose the one or the other format, it will be MyISAM.

And when customers install Drupal 7, Drupal will make the database in InnoDB (if available in server), but WordPress will just install the default set in my.cnf, wich is MyISAM. So WordPress does not choose the one or the other, but let the default on server decide if it is going to be MyISAM or InnoDB.
 
Last edited:
I do recommend everybody with shared hosting to enable "innodb-file-per-table" option (prior starting the server).
Click to expand...
Any recommendations about the value of that setting? I see ditto using "innodb-file-per-table=1" and I'm using the same setting for over a year. Or do you think another value is better on shared hosting, and if yes, why?
 
Thanks ditto,

My tested server is mainly for one large single website and the load is usually around 2. After upgraded, the load seems to be a little bit higher but the performance I found is really unacceptable when query DB especially when compare to MySQL 5.5. Will find sometime to test open_files_limit=30000 though. For now, 5.5 seems to work best for me. Also, 5.6, I think innodb_file_per_table=1 is the default.

Richard G, innodb_file_per_table=1 should be used in most case as you may read below :

You can reclaim disk space when truncating or dropping a table. For tables created when file-per-table mode is turned off, truncating or dropping them creates free space internally in the ibdata files. That free space can only be used for new InnoDB data.
Click to expand...

You may read more about pros and cons here -> http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html
 
i use combination of innodb and myisam, but i also noticed the io increased significantly and started to swap during peak hours which is really wierd
nmb said:
Frej, ditto,

Can you tell me which database engine that you mostly use on the server (InnoDB or MyISAM)? I found that the same my.cnf setting that works great in 5.5 doesn't work as it should in 5.6 especially with InnoDB database engine. (performance_schema=off) My Disk IOs jump from ~20 -> ~85. Disk latency from ~5 -> ~280. Lots of the graphs from Munin shows the performance problem in IO related . The CPU usage also increased.

After tested it for about a month, I decided to switch back to 5.5 yesterday.
Click to expand...
 
Hi everyone, got a question...
the server I am using has been in service maybe one month and was setup with innoDB as default, can we make the change to use MyISAM without breaking something?
and what are the reasons to use innoDB? Im not positive about this but I am thinking that using innoDB has put some limitations that are not wanted or needed.
are we better off just staying with innoDB?
are we giving up some functionality not using MyISAM?
are todays scripts dependent on MyISAM?. like WOrdPress?

update: according to Wordpress, it dont matter BUT some plugins may be dependent on MyISAM
so in our case, with some older scripts, perhaps we should revert back to MyISAM as default and innoDB as an alternative, let the script (if wriiten as such) make the choice?
 
Last edited:
I have the same question as AndyII.

I found that new servers are being set up with innoDB, and we've been finding some databases are using what I'm calling mixed engines within one database.

I'm moving a lot of my infrastructure to new servers this month and I'm anxious to know if it'll be better for me to just switch back to MyISAM as default to avoid this, or doesn't it matter?

Anyone?

Thanks.

Jeff
 
What I do on my shared hosting servers, is that I have MyISAM set as default, but I do not disable InnoDB.

I have noticed that a new WordPress install will always use what you have set to be default, so if you have MyISAM as default, WordPress will create the tables as MyISAM, and the same if you have InnoDB as default, then WordPress will create the tables as InnoDB.

However Drupal 7 prefer InnoDB. If you have MyISAM set to be default, but have not disabled InnoDB, then a new Drupal 7 install will create the tables as InnoDB.

I think this is fine, I am most comfortable with have MyISAM as default, and then let users and CMS decide if they want to use InnoDB instead.

I see several users having mixed tables with both MyISAM and InnoDB in the same database on my servers, and it have not caused any trouble. Most of the time this happens if the user install plugins and/or modules to Drupal, WordPress and other CMS, and for example if it is Drupal, and a user install new modules, the new modules might be installed as MyISAM while Drupal itself is in InnoDB.
 
Thanks, ditto. I'll likely go the same route. I'm going to wait for AndyII to reply to the thread to see if he has any additional points or insight, but since we're building out at least one new server this weekend (maybe two) I think this is the best way to go.

Jeff
 
You must log in or register to reply here.
Back
Top