title: Can't reduce mysql 5.7 VIRT Ram Use Too High Mysql
body: <p>I am having trouble understanding how to reduce the max memory used by mysql. You will see here it is reserving approximately 9.8G even though it says 6.7 in the RES column. But the 9.8G seems to be really what is set aside: </p>
  
  <p><a href="https://i.stack.imgur.com/2Ytdu.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2Ytdu.jpg" alt="enter image description here"></a></p>
  
  <p>I want the max VIRT to be around 8G max for the 16G ram server so that the server doesn't run out of ram. According to mysqltuner and percona tools the max used should be what I'm targeting, how do I get it to not use up more than it says? Here is the full output from mysql tuner </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>[--] Skipped version check for MySQLTuner script
  [OK] Currently running supported MySQL version 5.7.23-cll-lve
  [OK] Operating on 64-bit architecture
  -------- Log file Recommendations ------------------------------------------------------------------
  [--] Log file: /var/log/mysqld.log(1M)
  [OK] Log file /var/log/mysqld.log exists
  [OK] Log file /var/log/mysqld.log is readable.
  [OK] Log file /var/log/mysqld.log is not empty
  [OK] Log file /var/log/mysqld.log is smaller than 32 Mb
  [!!] /var/log/mysqld.log contains 165 warning(s).
  [!!] /var/log/mysqld.log contains 936 error(s).
  [--] 47 start(s) detected in /var/log/mysqld.log
  [--] 1) 2018-11-19T03:15:32.547059Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 2) 2018-11-16T03:34:17.625819Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 3) 2018-11-14T10:22:40.815455Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 4) 2018-11-13T05:30:39.431447Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 5) 2018-11-13T02:24:39.126222Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 6) 2018-11-12T17:33:31.280532Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 7) 2018-11-12T16:26:08.192382Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 8) 2018-11-12T16:17:48.393508Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 9) 2018-11-12T14:59:38.463289Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 10) 2018-11-12T14:58:02.659350Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 46 shutdown(s) detected in /var/log/mysqld.log
  [--] 1) 2018-11-19T03:15:30.131899Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 2) 2018-11-16T03:34:15.133264Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 3) 2018-11-14T10:22:38.285742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 4) 2018-11-13T05:30:35.410744Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 5) 2018-11-13T02:21:23.386572Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 6) 2018-11-12T17:33:27.568367Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 7) 2018-11-12T16:26:04.182657Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 8) 2018-11-12T16:17:37.240357Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 9) 2018-11-12T14:59:33.687687Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 10) 2018-11-12T14:49:06.301982Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  -------- Storage Engine Statistics -----------------------------------------------------------------
  [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
  [--] Data in MyISAM tables: 555.6M (Tables: 2477)
  [--] Data in InnoDB tables: 9.0G (Tables: 20153)
  [--] Data in MEMORY tables: 7.4M (Tables: 816)
  [OK] Total fragmented tables: 0
  -------- Analysis Performance Metrics --------------------------------------------------------------
  [--] innodb_stats_on_metadata: OFF
  [OK] No stat updates during querying INFORMATION_SCHEMA.
  -------- Security Recommendations ------------------------------------------------------------------
  [OK] There are no anonymous accounts for any database users
  -------- CVE Security Recommendations --------------------------------------------------------------
  [--] Skipped due to --cvefile option undefined
  -------- Performance Metrics -----------------------------------------------------------------------
  [--] Up for: 3d 2h 51m 1s (28M q [106.213 qps], 481K conn, TX: 56G, RX: 9G)
  [--] Reads / Writes: 95% / 5%
  [--] Binary logging is disabled
  [--] Physical Memory     : 15.5G
  [--] Max MySQL memory    : 7.1G
  [--] Other process memory: 4.1G
  [--] Total buffers: 4.6G global + 16.9M per thread (151 max threads)
  [--] P_S Max memory usage: 72B
  [--] Galera GCache Max memory usage: 0B
  [OK] Maximum reached memory usage: 4.9G (31.90% of installed RAM)
  [OK] Maximum possible memory usage: 7.1G (45.60% of installed RAM)
  [OK] Overall possible memory usage with other process is compatible with memory available
  [OK] Slow queries: 0% (0/28M)
  [OK] Highest usage of available connections: 14% (22/151)
  [OK] Aborted connections: 0.01%  (41/481831)
  [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
  [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
  [OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
  [!!] Joins performed without indexes: 80506
  [!!] Temporary tables created on disk: 65% (2M on disk / 3M total)
  [OK] Thread cache hit rate: 99% (26 created / 481K connections)
  [!!] Table cache hit rate: 0% (6K open / 1M opened)
  [OK] Open file limit used: 2% (1K/50K)
  [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
  -------- Performance schema ------------------------------------------------------------------------
  [--] Memory used by P_S: 72B
  [--] Sys schema is installed.
  -------- ThreadPool Metrics ------------------------------------------------------------------------
  [--] ThreadPool stat is disabled.
  -------- MyISAM Metrics ----------------------------------------------------------------------------
  [!!] Key buffer used: 33.5% (2M used / 8M cache)
  [OK] Key buffer size / total MyISAM indexes: 8.0M/55.9M
  [OK] Read Key buffer hit rate: 99.9% (39M cached / 55K reads)
  [!!] Write Key buffer hit rate: 41.9% (344K cached / 144K writes)
  -------- InnoDB Metrics ----------------------------------------------------------------------------
  [--] InnoDB is enabled.
  [--] InnoDB Thread Concurrency: 0
  [OK] InnoDB File per table is activated
  [!!] InnoDB buffer pool / data size: 4.5G/9.0G
  [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (44.4444444444444 %): 1.0G * 2/4.5G should be equal 25%
  [!!] InnoDB buffer pool instances: 6
  [--] Number of InnoDB Buffer Pool Chunk : 36 for 6 Buffer Pool Instance(s)
  [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size &amp; Innodb_buffer_pool_instances
  [OK] InnoDB Read buffer efficiency: 100.00% (3703840401 hits/ 3703944355 total)
  [!!] InnoDB Write Log efficiency: 70.96% (2254480 hits/ 3176899 total)
  [OK] InnoDB log waits: 0.00% (0 waits / 922419 writes)
  -------- AriaDB Metrics ----------------------------------------------------------------------------
  [--] AriaDB is disabled.
  -------- TokuDB Metrics ----------------------------------------------------------------------------
  [--] TokuDB is disabled.
  -------- XtraDB Metrics ----------------------------------------------------------------------------
  [--] XtraDB is disabled.
  -------- Galera Metrics ----------------------------------------------------------------------------
  [--] Galera is disabled.
  -------- Replication Metrics -----------------------------------------------------------------------
  [--] Galera Synchronous replication: NO
  [--] No replication slave(s) for this server.
  [--] Binlog format: ROW
  [--] XA support enabled: ON
  [--] Semi synchronous replication Master: Not Activated
  [--] Semi synchronous replication Slave: Not Activated
  [--] This is a standalone server
  -------- Recommendations ---------------------------------------------------------------------------
  General recommendations:
      Control warning line(s) into /var/log/mysqld.log file
      Control error line(s) into /var/log/mysqld.log file
      Restrict Host for user@% to user@SpecificDNSorIp
      Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
      Adjust your join queries to always utilize indexes
      When making adjustments, make tmp_table_size/max_heap_table_size equal
      Reduce your SELECT DISTINCT queries which have no LIMIT clause
      Increase table_open_cache gradually to avoid file descriptor limits
      Read this before increasing table_open_cache over 64: 
      Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
      This is MyISAM only table_cache scalability problem, InnoDB not affected.
      See more details here: https://bugs.mysql.com/bug.php?id=49177
      This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
      Beware that open_files_limit (50000) variable
      should be greater than table_open_cache (6000)
      Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 
  Variables to adjust:
      join_buffer_size (&gt; 16.0M, or always use indexes with JOINs)
      tmp_table_size (&gt; 64M)
      max_heap_table_size (&gt; 64M)
      table_open_cache (&gt; 6000)
      innodb_buffer_pool_size (&gt;= 9.0G) if possible.
      innodb_log_file_size should be (=576M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
      innodb_buffer_pool_instances(=4)</code></pre>
  </div>
  </div>
  </p>
  
  <p>and pt-mysql-summary </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>pt-mysql-summary [OPTIONS]
  # Percona Toolkit MySQL Summary Report #######################
                System time | 2018-11-22 06:01:56 UTC (local TZ: UTC +0000)
  # Instances ##################################################
    Port  Data Directory             Nice OOM Socket
    ===== ========================== ==== === ======
                                     0    0   
  # MySQL Executable ###########################################
         Path to executable | /usr/sbin/mysqld
                Has symbols | No
  # Slave Hosts ################################################
  No slaves found
  # Report On Port 3306 ########################################
                       User | root@localhost
                       Time | 2018-11-22 06:01:56 (UTC)
                    Version | 5.7.23-cll-lve MySQL Community Server - (GPL)
                   Built On | Linux x86_64
                    Started | 2018-11-19 03:15 (up 3+02:46:26)
                  Databases | 100
                    Datadir | /var/lib/mysql/
                  Processes | 2 connected, 1 running
                Replication | Is not a slave, has 0 slaves connected
                    Pidfile | /var/run/mysqld/mysqld.pid (exists)
  # Processlist ################################################
  
    Command                        COUNT(*) Working SUM(Time) MAX(Time)
    ------------------------------ -------- ------- --------- ---------
    Query                                 1       1         0         0
    Sleep                                 1       0        50        50
  
    User                           COUNT(*) Working SUM(Time) MAX(Time)
    ------------------------------ -------- ------- --------- ---------
    root                                  2       1         0         0
  
    Host                           COUNT(*) Working SUM(Time) MAX(Time)
    ------------------------------ -------- ------- --------- ---------
    localhost                             2       1         0         0
  
    db                             COUNT(*) Working SUM(Time) MAX(Time)
    ------------------------------ -------- ------- --------- ---------
    information_schema                    1       0         0         0
    NULL                                  1       1         0         0
  
    State                          COUNT(*) Working SUM(Time) MAX(Time)
    ------------------------------ -------- ------- --------- ---------
                                          1       0         0         0
    starting                              1       1         0         0
  
  # Status Counters (Wait 10 Seconds) ##########################
  Variable                                Per day  Per second     10 secs
  Aborted_clients                             100                        
  Aborted_connects                             15                        
  Bytes_received                       3500000000       40000       15000
  Bytes_sent                          20000000000      225000       50000
  Com_admin_commands                         1500                        
  Com_alter_table                            4000                        
  Com_begin                                150000           1            
  Com_change_db                             12500                        
  Com_check                                    40                        
  Com_commit                               150000           1            
  Com_create_table                           3500                        
  Com_delete                                40000                        
  Com_delete_multi                           2500                        
  Com_drop_table                             4000                        
  Com_enable_governor_lve                    1500                        
  Com_flush                                     2                        
  Com_grant                                     3                        
  Com_insert                               125000           1            
  Com_insert_select                          1500                        
  Com_lock_tables                               7                        
  Com_rename_table                              2                        
  Com_replace                                 100                        
  Com_rollback                                 20                        
  Com_select                              8000000          90          40
  Com_set_option                           300000           3           2
  Com_show_create_db                            6                        
  Com_show_create_table                      1750                        
  Com_show_create_trigger                      15                        
  Com_show_databases                          200                        
  Com_show_fields                           40000                        
  Com_show_grants                             150                        
  Com_show_keys                                70                        
  Com_show_master_status                        1                        
  Com_show_processlist                      20000                        
  Com_show_slave_status                         1                        
  Com_show_status                            1750                        
  Com_show_table_status                     40000                        
  Com_show_tables                            1500                        
  Com_show_triggers                          1750                        
  Com_show_variables                         1000                        
  Com_show_create_user                        150                        
  Com_stmt_execute                          60000                        
  Com_stmt_close                            60000                        
  Com_stmt_prepare                          60000                        
  Com_truncate                                  4                        
  Com_unlock_tables                             7                        
  Com_update                               175000           1            
  Com_update_multi                          20000                        
  Connections                              150000           1           3
  Created_tmp_disk_tables                  800000           8           3
  Created_tmp_files                           500                        
  Created_tmp_tables                      1250000          15          10
  Handler_commit                          7000000          90          40
  Handler_delete                           175000           2            
  Handler_external_lock                  35000000         400         150
  Handler_read_first                      1500000          15           4
  Handler_read_key                      125000000        1500         400
  Handler_read_last                           900                        
  Handler_read_next                     225000000        2500         250
  Handler_read_prev                        300000           3            
  Handler_read_rnd                       30000000         350         125
  Handler_read_rnd_next                 800000000        9000        5000
  Handler_rollback                             20                        
  Handler_update                          2250000          25            
  Handler_write                           8000000          90          60
  Innodb_buffer_pool_bytes_data        1250000000       15000            
  Innodb_buffer_pool_pages_flushed         600000           6           4
  Innodb_buffer_pool_read_ahead             15000                        
  Innodb_buffer_pool_read_requests     1250000000       15000        2250
  Innodb_buffer_pool_reads                  35000                        
  Innodb_buffer_pool_write_requests      35000000         400         100
  Innodb_data_fsyncs                       600000           6           5
  Innodb_data_read                      800000000        9000            
  Innodb_data_reads                         60000                        
  Innodb_data_writes                      1000000          10           7
  Innodb_data_written                 17500000000      200000      125000
  Innodb_dblwr_pages_written               500000           5           3
  Innodb_dblwr_writes                       80000                       1
  Innodb_log_write_requests               1000000          10           4
  Innodb_log_writes                        300000           3           1
  Innodb_os_log_fsyncs                     300000           3           2
  Innodb_os_log_written                 800000000        9000        4000
  Innodb_pages_created                      25000                        
  Innodb_pages_read                         50000                        
  Innodb_pages_written                     600000           6           4
  Innodb_row_lock_time                       6000                        
  Innodb_row_lock_waits                       600                        
  Innodb_rows_deleted                      175000           1            
  Innodb_rows_inserted                   30000000         350         125
  Innodb_rows_read                     1000000000       10000        5000
  Innodb_rows_updated                      225000           2            
  Innodb_num_open_files                      2000                        
  Innodb_available_undo_logs                   40                        
  Key_read_requests                      12500000         150            
  Key_reads                                 17500                        
  Key_write_requests                       100000           1            
  Key_writes                                45000                        
  Open_table_definitions                      700                        
  Opened_files                             300000           3           3
  Opened_table_definitions                 175000           2            
  Opened_tables                            600000           6           9
  Performance_schema_digest_lost           150000           1           3
  Performance_schema_file_instances_lost        2500                        
  Queries                                 9000000         100          50
  Questions                               9000000         100          50
  Select_full_join                          25000                        
  Select_full_range_join                   125000           1            
  Select_range                             700000           8           3
  Select_range_check                            6                        
  Select_scan                             1250000          15           4
  Sort_merge_passes                           800                        
  Sort_range                               500000           6            
  Sort_rows                              30000000         350         125
  Sort_scan                               1000000          15           3
  Table_locks_immediate                   1250000          15            
  Table_locks_waited                          100                        
  Table_open_cache_hits                  17500000         200          60
  Table_open_cache_misses                  600000           6           9
  Table_open_cache_overflows               600000           6           9
  Threads_created                               8                        
  Uptime                                    90000           1           1
  # Table cache ################################################
                       Size | 6000
                      Usage | 100%
  # Key Percona Server features ################################
        Table &amp; Index Stats | Not Supported
       Multiple I/O Threads | Enabled
       Corruption Resilient | Not Supported
        Durable Replication | Not Supported
       Import InnoDB Tables | Not Supported
       Fast Server Restarts | Not Supported
           Enhanced Logging | Not Supported
       Replica Perf Logging | Disabled
        Response Time Hist. | Not Supported
            Smooth Flushing | Not Supported
        HandlerSocket NoSQL | Not Supported
             Fast Hash UDFs | Unknown
  # Percona XtraDB Cluster #####################################
  # Plugins ####################################################
         InnoDB compression | ACTIVE
  # Query cache ################################################
           query_cache_type | OFF
                       Size | 0.0
                      Usage | 0%
           HitToInsertRatio | 0%
  # Schema #####################################################
  Specify --databases or --all-databases to dump and summarize schemas
  # Noteworthy Technologies ####################################
                        SSL | No
       Explicit LOCK TABLES | Yes
             Delayed Insert | No
            XA Transactions | No
                NDB Cluster | No
        Prepared Statements | Yes
  Prepared statement count | 0
  # InnoDB #####################################################
                    Version | 5.7.23
           Buffer Pool Size | 4.5G
           Buffer Pool Fill | 80%
          Buffer Pool Dirty | 0%
             File Per Table | ON
                  Page Size | 16k
              Log File Size | 2 * 1.0G = 2.0G
            Log Buffer Size | 16M
               Flush Method | O_DIRECT
        Flush Log At Commit | 1
                 XA Support | ON
                  Checksums | ON
                Doublewrite | ON
            R/W I/O Threads | 4 4
               I/O Capacity | 200
         Thread Concurrency | 0
        Concurrency Tickets | 5000
         Commit Concurrency | 0
        Txn Isolation Level | REPEATABLE-READ
          Adaptive Flushing | ON
        Adaptive Checkpoint |
             Checkpoint Age | 9
               InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
         Oldest Transaction | 0 Seconds
           History List Len | 9
                 Read Views | 0
           Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
          Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
         Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
        Pending I/O Flushes | 0 buf pool, 0 log
         Transaction States | 1xnot started
  # MyISAM #####################################################
                  Key Cache | 8.0M
                   Pct Used | 35%
                  Unflushed | 0%
  # Security ###################################################
                      Users | 2936 users, 0 anon, 0 w/o pw, 0 old pw
              Old Passwords | 0
  # Encryption #################################################
  No keyring plugins found
  # Binary Logging #############################################
  # Noteworthy Variables #######################################
       Auto-Inc Incr/Offset | 1/1
     default_storage_engine | InnoDB
                 flush_time | 0
               init_connect |
                  init_file |
                   sql_mode | NO_ENGINE_SUBSTITUTION
           join_buffer_size | 16M
           sort_buffer_size | 256k
           read_buffer_size | 128k
       read_rnd_buffer_size | 256k
         bulk_insert_buffer | 0.00
        max_heap_table_size | 64M
             tmp_table_size | 64M
         max_allowed_packet | 4M
               thread_stack | 256k
                        log |
                  log_error | /var/log/mysqld.log
               log_warnings | 2
           log_slow_queries |
  log_queries_not_using_indexes | OFF
          log_slave_updates | OFF
  # Configuration File #########################################
                Config File | /etc/my.cnf
  
  [mysqld]
  performance-schema                  = On
  datadir                             = /var/lib/mysql
  socket                              = /var/lib/mysql/mysql.sock
  symbolic-links                      = 0
  log-error                           = /var/log/mysqld.log
  pid-file                            = /var/run/mysqld/mysqld.pid
  innodb_file_per_table               = 1
  sql_mode                            = NO_ENGINE_SUBSTITUTION
  innodb_buffer_pool_size             = 4G # (adjust value here, 50%-70% of total RAM)
  innodb_log_file_size                = 1G
  innodb_flush_log_at_trx_commit      = 1 # may change to 2 or 0
  innodb_flush_method                 = O_DIRECT
  open_files_limit                    = 40000
  table_open_cache                    = 6000
  max_heap_table_size                 = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
  tmp_table_size                      = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
  query_cache_limit                   = 16M
  join_buffer_size                    = 16M
  innodb_buffer_pool_instances        = 6
  query_cache_size                    = 0
  query_cache_type                    = 0
  thread_cache_size                   = 16
  wait_timeout                        = 300
  # Memory management library ##################################
  jemalloc is not enabled in mysql config for process with id 819535
  # The End ####################################################</code></pre>
  </div>
  </div>
  </p>