MySQL 5.6 / MySQL 7.2 = XenForo at warp speed

p4guru said:
Can't wait for MariaDB 10.x - yes 10.x it's going to be MariaDB 5.5 based with merged features from MySQL 5.6 - hoping InnoDB transportable table space feature makes it in MariaDB 10.x :)
Click to expand...
Thanks for the info. :)
My only concern with MariaDB 5.5 is the fact that it has the 5.3 branch included. When I installed 5.3.6 on a client site, it was a performance disaster so I reverted right away to 5.2.12. Small sites with 1-3,000 online users will not see the impact, the dance starts when you hit 15,000+ online users. Those clients usually install Percona or just stick with pure MySQL. That means, some of the current MariaDB "bugs" are present into 5.5 branch... Not really fond of it, let's hope they fixed whatever was wrong into 5.3.
 
AlexT said:
And that is another difference between MariaDB and Percona. While you can go bleeding-edge with MariaDB, Percona tends to follow a more conservative approach in terms of branch and optimization.
Click to expand...
See my edited post, above. Percona Server is just a regular MySQL package with "few" extra InnoDB optimizations, in order to generate more revenue to their client niche (InnoDB tune-ups). IMO, there is nothing fancy about it, they don't innovate at all. MariaDB 5.2 makes Percona Server 5.5 look like a little toy stuck into sand, myself and George (eva2000) were laughing when we benched MariaDB against MySQL and Percona Server.

I will stick with MariaDB and also be free from any licensing schemes other companies impose, just to kill the open-source concept. :)
 
You'll soon learn that Floren's benchmarks are the equivalent to God coming down and giving Moses the Ten Commandments.
 
Floren said:
Thanks for the info. :)
My only concern with MariaDB 5.5 is the fact that it has the 5.3 branch included. When I installed 5.3.6 on a client site, it was a performance disaster so I reverted right away to 5.2.12. Small sites with 1-3,000 online users will not see the impact, the dance starts when you hit 15,000+ online users. Those clients usually install Percona or just stick with pure MySQL. That means, some of the current MariaDB "bugs" are present into 5.5 branch... Not really fond of it, let's hope they fixed whatever was wrong into 5.3.
Click to expand...
Yeah MariaDB folks played with the optimizer too much, but you can tweak it to pre 5.3.x by disabling some of the optimizer stuff i think. Haven't done it myself as sticking with 5.2 right now.

AlexT said:
And that is another difference between MariaDB and Percona. While you can go bleeding-edge with MariaDB, Percona tends to follow a more conservative approach in terms of branch and optimization.
Click to expand...

True, it's double edge sword - Percona has it's place as well and if you need consulting support or more immediate patch/fixes that mysql.com's release schedule, then Percona's paid services is appropriate.

Floren said:
See my edited post, above. Percona Server is just a regular MySQL package with "few" extra InnoDB optimizations, in order to generate more revenue to their client niche (InnoDB tune-ups). IMO, there is nothing fancy about it, they don't innovate at all. MariaDB 5.2 makes Percona Server 5.5 look like a little toy stuck into sand, myself and George (eva2000) were laughing when we benched MariaDB against MySQL and Percona Server.

I will stick with MariaDB and also be free from any licensing schemes other companies impose, just to kill the open-source concept. :)
Click to expand...
I'd have to disagree there, Percona's value is more than that, they do DO alot more and for one thing their testing, benchmarks, analysis and even RPM builds seem more refined process wise than MariaDB. MariaDB still doesn't have proper working MariaDB 5.5 RPMs for wide availability release. I had to build my own MariaDB 5.5 RPMs for RHEL6/CentOS6.

Also George (eva2000)'s benchmarks are pretty old now, MySQL 5.5.2.x community releases have caught up alot in terms of InnoDB performance with both MariaDB 5.2 and Percona 5.5. Of course with MariaDB you can also build it against MySQL 5.5 series InnoDB plugin instead of XtraDB for similar performance.

As MariaDB 5.2 vs 5.3 already highlighted, you can only really compare branch/forks at specific version numbers. Different version numbers may have improved or regressed in terms of performance.

Let's see how next releases fair

  • MariaDB 5.3.8 due August 27th
  • MariaDB 5.5.25 due August 31st
  • MariaDB 10.0.0 due September 21st
 
p4guru said:
I'd have to disagree there, Percona's value is more than that, they do DO alot more and for one thing their testing, benchmarks, analysis and even RPM builds seem more refined process wise than MariaDB. MariaDB still doesn't have proper working MariaDB 5.5 RPMs for wide availability release. I had to build my own MariaDB 5.5 RPMs for RHEL6/CentOS6.
Click to expand...

I agree, what I was referring to is the fact that Percona offers ONLY paid support to their products and services. :)
Me too I had to build my own RPM's, I realized that basically any release out there has flaws and missing patches. If I have an issues, I re-write the RPM with the needed patches. :)
 
Floren, do you maybe have some benchmarks with a large Xf db? Very interesting for large XF communities!
 
Floren said:
I agree, what I was referring to is the fact that Percona offers ONLY paid support to their products and services. :)
Click to expand...
yes indeed, it's only way to get support for their products. Only thing i dislike is they market Percona XtraDB backup tool as InnoDB/XtraDB backup tool but have officially gone on record as saying MariaDB isn't a supported MySQL version for their tool, despite MariaDB using XtraDB! And the fix seems so easy for MariaDB 5.2/5.5 detection at least. So I prefer using mydumper for backups, testing on MariaDB 5.2, 5.3 and 5.5.
 
duderuud said:
Floren, do you maybe have some benchmarks with a large Xf db? Very interesting for large XF communities!
Click to expand...
I did a live test with a vBulletin 3.8.3 forum, it was a disaster. With 16,000 online users, MariaDB 5.3.6 used 600% CPU and eventually made the daemon crash (in about 15min). With MariaDB 5.2.12 everything is purring nice, around 100% CPU which is great for vBulletin. I noticed the same behavior with a tiny XenForo forum (1 million posts), the CPU goes to the sky. Stay away from 5.3 branch. :)
 
p4guru said:
So I prefer using mydumper for backups, testing on MariaDB 5.2, 5.3 and 5.5.
Click to expand...
# yum --enablerepo=axivo install mydumper :)
I've been using mydumper for a while. In fact, I made a nice backup script for one of my clients that shuts the XenForo forums down, backs the database at light speed and opens the forums. For some reason, on XenForo you need to close the forums on a busy site, or else mydumper does not complete... don't ask me why. It should, as it locks automatically the tables, no?
 
AlexT said:
XF is InnoDB by default, isn't it? As such, mydumper shouldn't require the locking of tables. Did you try using --single-transaction as a parameter?
Click to expand...
I thought XenForo uses a mix (i.e. search table is MyISAM), so mydumper only locks the MyISAM tables. I do get a message that tells me a non-InnoDB backup is performed and an "unlocking tables in progress", did you run yours with a verbose 3 to see the output? Also, I don't see the "--single-transaction" option into mydumper manual. From my experience, MySQL is producing inconsistent backups with that option so I presume mydumper uses the MySQL function? I would not use that, just to be safe.
 
AlexT said:
XF is InnoDB by default, isn't it? As such, mydumper shouldn't require the locking of tables. Did you try using --single-transaction as a parameter?
Click to expand...
XF only InnoDB default if InnoDB engine is enabled in MySQL. If InnoDB engine disabled, all XF install/created tables end up MyISAM format.

single-transaction is only for mysqldump

mydumper doesn't have such an option as in mixed innodb/myisam mydumper backups, table read lock is released as soon as myisam data is backed up regardless as to whether innodb tables are backed up yet (non-blocking aspect).

Floren said:
# For some reason, on XenForo you need to close the forums on a busy site, or else mydumper does not complete... don't ask me why. It should, as it locks automatically the tables, no?
Click to expand...
What do you mean doesn't complete ? How many threads you running mydumper with and how many cpu core/threads ? Try watching mysql processlist while mydumper is running with forums open and see what's going on as well as doing an ascending time order list output for mydumper destination backup directory to see which database tables are last to complete. Could correspond to largest or busiest table of your XF database.

Probably keyword here is busy forum and if innodb dataset size is proportionally larger of the innodb/myisam mix.
 
Nice AlexT, thanks for sharing, didn't know about MySQL 5.6's mysqlbinlog improvements - i rarely use mysqlbinlog heh. Hopefully, MariaDB 10.x series merges MySQL 5.6's mysqlbinlog too.
 
indeed will be interesting

just noticed AlexT deleted his last post's contents.. don't recall what was said but find it curious as i hit liked on it heh

edit: okay memory coming back to me, i think it's about mysql 5.6's new raw mode for mysqlbinglog for remotely backing up binary logs ?
 
You must log in or register to reply here.
Top Bottom