Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-826

LP #1380985: Execution of stored procedures with subqueries leaks memory

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Vojtech Kurka last update 14-05-2015 08:34:17

      We have this problem:

      with 50GB innodb buffer pool the server eats about 56GB of memory (RES) with the production workload thats OK. But then the memory consumption is increasing and after 3 days of runtime, it uses about 76GB of memory (RES) and the machine starts swapping out. I have to restart mysqld every 3 days.

      We have the exact version of PerconaServer on the slave (without production workload, just the slave thread) and it doesnt have this problem (54 GB mem RES after 7 days).

      This problem is the same for a few recent versions of Percona server, not just the last one.

      Server gets just normal OLTP traffic + mysqldump and xtrabackup nightly.
      We DO use a few InnoDB tables with compression and we use some MyISAM tables with range partitioning.
      TokuDB is not installed.

      – ----------------------------------------------

      mysql> select version();
      -----------------

      version()

      -----------------

      5.6.21-69.0-log

      -----------------

      CentOS release 6.4 (Final)
      Linux xxxxx 2.6.32-358.14.1.el6.x86_64 #1 SMP Tue Jul 16 23:51:20 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

      my.cnf:

      [mysqld]

      user=mysql
      datadir=/home/users/our_project/mysql/data
      tmpdir=/home/users/our_project/mysql/data
      innodb_data_home_dir=/home/users/our_project/mysql/innodb
      innodb_log_group_home_dir=/home/users/our_project/mysql/innodb

      log-error=/var/log/mysqld.log

      character-set-server=UTF8
      collation_server=utf8_general_ci
      init_connect='SET NAMES utf8'

      sql-mode="STRICT_ALL_TABLES"

      query_cache_type=0

      local_infile=0
      lock_wait_timeout=5
      skip-name-resolve
      explicit_defaults_for_timestamp=0

      max_binlog_size=1000M
      expire_logs_days=3
      binlog-format=ROW
      binlog_row_image=minimal
      log-slave-updates
      binlog_cache_size=512000

      binlog-ignore-db=temp
      replicate-wild-ignore-table=temp.%
      replicate-wild-ignore-table=mysql.%

      slave_type_conversions=ALL_NON_LOSSY

      sync_binlog=1
      sync_relay_log=100

      relay-log-recovery=1
      relay_log_info_repository=TABLE

      max_connections=1000
      max_connect_errors=10000

      back_log=3000
      thread_cache_size=100
      max_allowed_packet=134217728

      myisam-recover-options=FORCE
      delay-key-write=OFF

      table_open_cache=10000
      table_definition_cache=2000

      loose-innodb=FORCE
      innodb_file_per_table
      innodb_file_format=Barracuda
      innodb_flush_method=O_DIRECT
      innodb_open_files=10000
      innodb_stats_on_metadata=0

      innodb_log_file_size=256M
      innodb_max_dirty_pages_pct=30
      innodb_io_capacity=2000
      innodb_io_capacity_max=10000

      innodb_strict_mode=1
      innodb_buffer_pool_size=50G
      innodb_buffer_pool_dump_at_shutdown=1
      innodb_buffer_pool_load_at_startup=1
      innodb_flush_log_at_trx_commit=1
      innodb_thread_concurrency=64

      innodb_purge_threads=1

      innodb_stats_persistent=1
      innodb_stats_persistent_sample_pages=500

      innodb_checksum_algorithm=crc32

      innodb_flush_neighbors=0

      tmp_table_size=100M
      max_heap_table_size=100M

      event_scheduler=1

      server-id=1
      log-bin=/home/users/klarka/mysql/binlog/klarka1-bin
      master-info-file=/home/users/klarka/mysql/info/klarka1-master.info

      relay-log=/home/users/klarka/mysql/relay/klarka1-relay-bin
      relay-log-info-file=/home/users/klarka/mysql/info/klarka1-relay.info

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist