Re: MySQL 5.7 Innodb performans issue
- Date: Wed, 20 May 2015 14:52:00 -0400
- From: Morgan Tocker <morgan.tocker@xxxxxxxxxx>
- Subject: Re: MySQL 5.7 Innodb performans issue
>> - The data collector system processing jobs, is it multi threaded?
> Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases system using several
> script running sequently in an infinite loop. Each script taking care of part of the job of
> processing the data.
Newer MySQL releases are often more scalable (= works better for multi-threaded applications).
On a single threaded basis though, as functionality is added it is difficult to always keep the same performance numbers. When you mentioned that there was a regression in 5.6 as well, it might be interesting to compare to the most recent 5.6. There were some single-threaded regression fixes after the GA release.
>> - Do you have a sample schema + set of queries we could look at?
>> (We pay close attention to regressions.)
> This will be BIG, since it it so many different queries and tables.
> Not sure what you mean by "We pay close attention to regressions”.
Bugs that introduce a loss of performance have a higher priority factor applied to them. We are very interested to hear clear cases where an operation takes more time in MySQL 5.7 versus earlier releases.
>> In terms of your configuration:
>> I would usually recommend assuming the default values for some of the
>> settings you’ve specified (table_open_cache, sort_buffer_size,
>> thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..).
>> A 25G buffer pool on a 32G server with some of your other buffers being
>> quite large is something you may need to look into too.
> I know, but sofare no swapping is taking place. The test server is used for testing this system
> only. And in 99% of the time, only one client is using the SQL server.
This makes some sense based on your workload being single threaded as well.
It looks from show engine innodb status that your server is just starting up, and caches are empty, so versus a 5.5 server that has been running for a while it will likely be slower.
What you may be able to do to track a specific set of statements that take longer in 5.7, is convert a mysqlbinlog to sql (mysqlbinlog mybinlogfile > output.sql) and replay it on a 5.5 and 5.7 server. If you can pair it down a little from there, then the output.sql file can be uploaded to bugs.mysql.com and evaluated.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql