Buy Percona ServicesBuy Now!

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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: mysql_error.txt
    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:

    Code:
    #
    #  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,

  • #2
    Hello, sorry to hear that. Could you just provide a few more details please:
    • 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.
    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!
    Last edited by lorraine.pocklington; 09-10-2018, 09:21 AM.

    Comment


    • #3
      Hello,
      Thank you for the fast answer. Here the details you asked:
      • 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)

      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

      Comment


      • #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

        https://dev.mysql.com/doc/refman/5.7...us-series.html

        Blog post: https://www.percona.com/blog/2014/09...est-practices/

        This post by Morgan Tocker is also recommended: https://www.digitalocean.com/communi...ql-5-7-upgrade

        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 https://www.percona.com/blog/2016/09...n-5-6-and-5-7/

        Also, as per this blog post, https://www.percona.com/blog/2016/05...-memory-usage/ 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...

        Comment


        • #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

          Comment


          • #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!

            Comment

            Working...
            X