MySQL Performance Degredation

[NateBrady23]

As we work on getting you guys some preview data for Round 15, we've noticed a significant performance decrease for MySQL. After monitoring the Database server, there have been no real changes between Round 14 and Round 15. We've made a few slight modifications to the conf file which you can see here: https://github.com/TechEmpower/FrameworkBenchmarks/blob/master/toolset/setup/linux/databases/mysql/my.cnf

Another change was the currently supported version of MySQL Server in the apt repository is now 5.5.55 https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-55.html

Most frameworks that have MySQL tests are seeing a whopping 20% to 50% decrease in RPS from Round 14 final.

[raziel057]

Do you tried to revert the modifications of config file to check if it change something?

Which Mysql version was installed for Round 14?

[NateBrady23]

@raziel057 Thanks for checking in here. The config file has been the same for quite some time. The only difference was what mysql-server version was available in apt. It was only a patch version update from 5.5.54 to 5.5.55. Trying to revert back was difficult since that version is no longer in the apt repository, but after some effort it produced the same results.

We've moved to 5.7 which helped a lot but still seeing performance decreases for mysql db and query tests across the board. After some help with mysqltuner and folks from the mysql community, this is the current conf on our database server: https://github.com/nbrady-techempower/FrameworkBenchmarks/blob/d42de20a4a2b3b632f02ce9b282d882a88364dab/toolset/setup/linux/databases/mysql/my.cnf

[raziel057]

If you have the same result (degradation compared to Round 14) by reverting the Mysql version, we can estimate the problem doesn't comes from Mysql no? Maybe related to the server itself? It's a bit difficult to help...

[NateBrady23]

@raziel057 It's a bit difficult to solve too. If it were related to the DB server itself you would likely see the degradation across the board. MongoDB, PostgreSQL are both fine. It's quite the mystery. Nonetheless, we're moving forward with MySQL 5.7 and will work on tweaking the conf. Some spot tests actually show an uptick in the Updates test. We'll be releasing numbers (assuming the current run finishes without issue) around Monday.

[zloster]

@nbrady-techempower @raziel057

Some frameworks with impressive boost in the Updates test, all are using MySQL:

actframework-ebean-my	update	750	907	+21.0%	
activeweb	update	1,096	1,306	+19.1%
activeweb-jackson	update	1,154	1,449	+25.6%
aspnet-mono-mysql-entityframework	update	620	947	+52.6%
aspnet-mono-mysql-raw	update	739	1,757	+137.7%	
beego-orm-mysql	update	660	822	+24.5%
fasthttp	update	1,255	2,209	+76.0%	
fat-free	update	1,116	2,059	+84.5%
fintrospect	update	1,068	2,183	+104.3%
flask-mysql-raw	update	1,315	1,763	+34.0%
gemini-mysql	update	1,004	1,925	+91.7%
go	update	1,044	2,265	+117.0%
kami	update	1,065	1,993	+87.1%
php-php5-raw	update	1,064	1,995	+87.5%
php-raw7	update	1,046	1,956	+86.9%	
servlet-raw	update	1,029	1,935	+88.0%	
symfony2	update	523	698	+33.6%
urweb-mysql	update	187	276	+47.3%	
vertx-web-mysql	update	875	1,910	+118.2%
yaf	update	1,146	2,701	+135.7%
zend1	update	657	874	+33.0%
zend	update	766	979	+27.8%

Some frameworks with quite serious loses in the Updates test, all are using MySQL:

akka-http	update	2,039	1,573	-22.8%
dropwizard	update	1,104	832	-24.7%	
pedestal	update	1,269	575	-54.7%
play2-java-ebean-hikaricp	update	442	263	-40.6%
spring	update	897	356	-60.4%
symfony2-raw	update	1,570	1,275	-18.8%	
wicket	update	1,420	1,232	-13.2%

You can see that all major languages and frameworks get the gain. The heavy updates case is roughly on par with PostgreSQL results - just check some of the frameworks that are using both databases. Seems like a big achievement for the MySQL development team.

Data taken from here: https://www.techempower.com/benchmarks/previews/round15/r14-vs-r15p1.html

IMO I've found a clue about for the lower performance in the other cases. Open http://tfb-logs.techempower.com/round-15/preview-1/dropwizard/out.txt and scroll to the end:

Server dropwizard: Fri Jun 09 06:31:11 CDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Server dropwizard: Fri Jun 09 06:31:11 CDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

It seems something have changed in the interaction between the JDBC driver at least and the MySQL server - the connections are using SSL by default. This will add overhead to the conversations with the database. The connection string should be changed. I don't have time to check it but looks promising.

[NateBrady23]

@zloster I saw the same thing in some of the other frameworks. This seems like the issue. I will look in to updating the connection strings this week or pinging the fw contributors. Thanks for posting such detail!

[martin-g]

@zloster @nbrady-techempower Wicket is using useSSL=false since a long time (

FrameworkBenchmarks/frameworks/Java/wicket/src/main/java/hellowicket/WicketApplication.java

Line 82 in 7f5bbb8

ds.setJdbcUrl("jdbc:mysql://localhost:3306/hello_world?jdbcCompliantTruncation=false&elideSetAutoCommits=true&useLocalSessionState=true&cachePrepStmts=true&cacheCallableStmts=true&alwaysSendSetIsolation=false&prepStmtCacheSize=4096&cacheServerConfiguration=true&prepStmtCacheSqlLimit=2048&zeroDateTimeBehavior=convertToNull&traceProtocol=false&useUnbufferedInput=false&useReadAheadInput=false&maintainTimeStats=false&useServerPrepStmts=true&cacheRSMetadata=true&useSSL=false");

) but still its results dropped in the last preview run :-/

[NateBrady23]

@martin-g yes, I imagine this isn't the only reason. Also has a new config file.

[zloster]

@nbrady-techempower There is a typo in your mention of @martin-g
@martin-g It's my mistake to put Wicket in the list.
The case I'm referring to is the following:

1. The framework JSON and plaintext results are the same or better.
2. The framework MySQL DB read tests have lower results.
3. The framework MySQL DB update test has better results.

All of the above conditions at the same time.

Wicket results doesn't meet the above criteria.

[martin-g]

Thank you, @zloster !

I am watching this project so even without mentioning me I still read all (JVM related) discussions ;-)

[methane]

Maybe, query_cache is disabled by default from MySQL 5.6.
While query_cache had nice effect on read-heavy / no-update workload,
it had very bad effect on concurrent update workload.
Since many core server is very common on nowdays, I think we keep disabling
query_cache to show realistic performance.

[zloster]

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_type It seems @methane have nailed it.
MySQL with versions >= 5.6.8 disable it by default. Version 5.6.8 is a Release Candidate, version 5.6.10 is the first General Availability release - https://dev.mysql.com/doc/relnotes/mysql/5.6/en/

I haven't done extensive research but from a quick look it seems the reasoning is exactly what methane mentioned - very bad effects on concurrent update workload (on big machines). More details here:

• https://www.flamingspork.com/blog/2014/06/05/performance-impact-of-mysql-query-cache-on-modern-hardware/
• http://www.tocker.ca/2013/09/27/how-do-you-use-the-query-cache.html

[joanhey]

MySQL 5.7 is slower too by this other benchmark: https://mariadb.org/maria-10-1-mysql-5-7-commodity-hardware/
We can think that is biased, but the winner was MySQL 5.6.

Also send #3261 to disable performance schema