MySQL Forums
Forum List  »  InnoDB

Database slower from 5.5 to 5.7 and page_cleaner error
Posted by: Anders Hvidgaard Poder
Date: August 23, 2017 04:51AM

I have recently moved our system to a new server. From a Windows Server 2008 R2 with a MySQl 5.5 to Windows Server 2012 R2 where I installed MySQL 5.7. The new server has more RAM (8G), but other than that they are the same (2.3GHz single CPU)

I used mysqldump to extract data from the old server and import it into the database at the new server.

I updated

key_buffer_size=64M
innodb_buffer_pool_size=4G
query_cache_size=2M
innodb_buffer_pool_instances=16
max_allowed_packet=64M

otherwise kept it default.

The old server (MySql 5.5) had:

key_buffer_size=8M
innodb_buffer_pool_size=654M
query_cache_size=0
max_allowed_packet=64M

The new server ran for a few days but the night after I imported our event log table and archive it became unstable with 100% CPU. I restarted the server and the problem disappeared.

In the error log maty be found many lines like this:

2017-08-19T02:35:26.868276Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 14052ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-08-19T02:38:10.602938Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7985ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-08-19T02:39:54.978138Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4578ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-08-19T02:40:12.650048Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6359ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-08-19T02:40:23.543423Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6643ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
...

A little research pointed to innodb_lru_scan_depth. I changed this value to

innodb_lru_scan_depth=256 (default is 1024)

but it had no affect as far as I can tell.

I still on occasion get

2017-08-22T04:00:13.980515Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5820ms. The settings might not be optimal. (flushed=158 and evicted=0, during the time.)
2017-08-22T21:13:25.889016Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7348ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-08-23T04:00:06.342990Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4484ms. The settings might not be optimal. (flushed=29 and evicted=0, during the time.)
2017-08-23T04:00:15.329197Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5986ms. The settings might not be optimal. (flushed=99 and evicted=0, during the time.)

but no crashes.

I can also see that sometimes my updates are postponed for several seconds, causing my Thread pool to grow, so something is delaying the operation.

Does anyone know why the 5.7 seems to have these delays and why the page_cleaner have the above failures and what to do about it? Can the increased RAM buffer cause a longer locking?

Thank you in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Database slower from 5.5 to 5.7 and page_cleaner error
295
August 23, 2017 04:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.