Need help to troubleshoot RAM usage with Percona MySQL 5.7 after upgrade

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hello,
After an upgrade from MySQL 5.5 to Percona MySQL 5.7, I noticed that the usage of the RAM never goes down and MySQL always finished by using all the RAM of the server which crashed. Here some logs before the crash:
The example is based on a small server but I have big servers facing the same issue. What I found is that Percona MySQL 5.7 seems to nerver release RAM until it crashes (as shown on the graph).
I have read many information about the innodb_buffer_pool_size or on how MySQL is using the RAM but none of this helped me to get a stable system.
Any advise or information is more than welcome.

Here my my.cnf if that helps:

#
# INNODB
#

# innodb_buffer_pool_size_mb needs to be 25%, 50% of total available RAM
innodb_buffer_pool_size = 986M

innodb_log_file_size = 50331648
innodb_log_buffer_size = 16777216
innodb_flush_log_at_trx_commit = 1
innodb_stats_on_metadata = 0
innodb_purge_batch_size = 5000
innodb_max_purge_lag = 1000000
innodb_purge_threads = 2
innodb_file_per_table = 1
innodb_undo_tablespaces = 6
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 1073741824

#
# TIMEOUT AND LIMITS
#
wait_timeout = 1200
interactive_timeout = 7200

thread_stack = 192K
thread_cache_size = 8

max_connections = 184
max_connect_errors = 51615
max_allowed_packet = 16M
group_concat_max_len = 10000000
open_files_limit = 25000
table_open_cache = 20000
table_definition_cache = 20000

#
# Query Cache Configuration
#
query_cache_limit = 1M
query_cache_type = 0
query_cache_size = 0

#
# Log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

#
# BINLOG
#
expire_logs_days = 10
max_binlog_size = 100M

#
# MYISAM
#
key_buffer_size = 16M

[isamchk]
key_buffer_size = 16M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

Thanks,

photoid=52363
photoid=523631212×797 34.3 KB

mysql_error.txt (15.6 KB)

2

Hello, sorry to hear that. Could you just provide a few more details please:
[LIST]
[]What are the OS and environment details please?
[]What specific version of Percona Server for MySQL 5.7 are you running? And if possible what specific version of 5.5?
[*]Did you follow a particular process i.e. is there a link somewhere to the upgrade method you followed? We’d expect you to step through the versions in your updates.
[/LIST] A couple of the docs I found online suggested disk problems, but from what you are saying this is happening to you with more than one server. Could you confirm that please? Thanks!

3

Hello,
Thank you for the fast answer. Here the details you asked:
[LIST]
[]OS is Ubuntu 16.04 VM 4 CPUs, 4GB RAM, MySQL takes 18GB on a 40GB SSD. The VM is dedicated to MySQL
[]Percona is 5.7.19-17 but I’ve seen the same issue on 5.7.22-22. Before, it was a MySQL 5.5.60 on Ubuntu 14.04
[*]The processs is just to bring the MySQL dump from 5.5 to 5.7. We changed the server during the process (Percona was a fresh installation)
[/LIST]
Yes, I’m facing the same issue in several servers with different sizes as well. I tested the disk and in average I have 1GB/s.
Therefore, I think it might be due to a deadly cocktail of not well setup variables as I’m seeing a lot of logs like:
[Note] InnoDB: page_cleaner: 1000ms intended loop took 4057ms. The settings might not be optimal. (flushed=4, during the time.)

But even playing with variables like lru_scan_depth nothing changed.

Thanks

4

Hello again

OK, first I’d advise caution since it seems like you might not be following the prescribed upgrade path so please be sure to take all the steps necessary to secure your data. There is a blog post that covers upgrades, you can substitute the version 57 into the links (it’s an older blog post) to get to the right MySQL manual pages for example

[url]https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html[/url]

Blog post: [url]https://www.percona.com/blog/2014/09/19/mysql-upgrade-best-practices/[/url]

This post by Morgan Tocker is also recommended: [url]https://www.digitalocean.com/community/tutorials/how-to-prepare-for-your-mysql-5-7-upgrade[/url]

I know that’s not where you are, but I need to state that to help future posters too! Plus reviewing these might help identify potential incompatibilities that you need to address.

Meanwhile, the team took a look at your post, too, and suggested table_open_cache_instances defaults to 16 in 5.7 - thus splitting your table cache sizes. Here is a blog post that looks at soe of the parameter changes between 5.6 and 5.7 [url]https://www.percona.com/blog/2016/09/14/mysql-default-configuration-changes-between-5-6-and-5-7/[/url]

Also, as per this blog post, [url]https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/[/url] if you have performance schema enabled that could be hogging memory.

Take a look at these suggestions and see if any of them help? If you are still struggling it is possible that the support needed is very specific to your case and falls outside of what we can provide via the open source forum, BUT let’s see how you get on first…

5

Hi Lorraine,

Thank you for your answer.
I went through the documentation and regarding the upgrade in our case we transfer only our data, we do not touch the system tables and we recreat our tables from scratch.
Furthermore, our new servers (preinstalled with Percona 5.7.22-22) are also impacted with this RAM usage so I guess the issue might only be link to 5.7 and not to the upgrade.
As you can see on the screenshot, we have this new server with a really small DB (20GB) which is using more than 6GB of RAM when InnoDB is set at 3GB.
What also intrigues me is why MySQL does not release a bigger amount of RAM when it is not heavily used.

Disabling the performance schema did help but still our 5.7 servers are hogging a lot of RAM. I have seen on some servers that the table innodb_index_stats contains wrong information which led to error logs like:
[Note] InnoDB: Ignoring strange row from mysql.innodb_index_stats WHERE database_name = ‘db’ AND table_name = ‘tab’ AND index_name = ‘PRIMARY’ AND stat_name = ‘n_diff_pfx02’; because stat_name is out of range, the index has 1 unique columns"

Could this issue be linked to a memory leak ?

Thanks

6

OK thanks for that update, I will see if I can get someone from the team to take a look at this with you. Bear with me! :slight_smile:

7

Hi Lorraine,

Have you been able to gather information regarding my current issue ?
If you need any specific information that could help to troubleshoot I can gather it.

Thank you for your help

photoid=52391
photoid=523911182×780 38.6 KB

8

Hi ebuton to date I’ve not had anything back from our tech team.
This is usually because the scope of the question goes beyond what they’re able to answer in the context of a ‘free’ open source forum. The feedback that I gave on 9/11 comes into play here.
In other words, there’s not a simple answer if those responses did not help you. To identify and resolve the issues would require a more direct engagement and access to your systems.
I don’t know your situation, but I can put you in touch with someone who can talk to you about our professional services options if you would like me to.