不能降低MySQL5.7VirtRAM使用太高的mysql

xxslljrj  于 2021-06-19  发布在  Mysql
关注(0)|答案(3)|浏览(501)

我很难理解如何减少mysql使用的最大内存。你会看到这里它是保留约9.8克,即使它说6.7在res列。但9.8克似乎真的被搁置了:

我想最大的virt大约是8g最大的16g内存服务器,这样服务器就不会用完内存。根据mysqltuner和percona工具的最大使用量应该是我的目标,我如何让它不使用超过它说的?以下是mysql tuner的完整输出

[--] 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 & 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 (> 16.0M, or always use indexes with JOINs)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_open_cache (> 6000)
    innodb_buffer_pool_size (>= 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)

和pt mysql摘要

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 & 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 ####################################################
hrysbysz

hrysbysz1#

为您的my.cnf[mysqld]部分考虑的建议-(基于您问题中截至2018年11月22日的可用信息)-每秒速率=rps
从linux命令提示符,

ulimit -n 65536     to support more Open Files

要使linux更改在关闭/重新启动期间保持不变,请查看此url
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ 由于版本不同,您的详细信息可能略有不同。
对于mysql示例my.cnf[mysqld]

thread_cache_size=64  # from 16 to reduce threads_created (expensive operation)
query_cache_limit=0  # from 16M to conserve RAM when QC not used
query_cache_min_res_unit=512  # from 4096 to conserve QC space if ever used
innodb_lru_scan_depth=100  # from 1024 to conserve CPU cycles every SECOND
table_open_cache=10000  # from 6000 and you may need more
key_cache_age_threshold=7200  # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50  # from 100 for Hot/Warm cache
key_cache_block_size=16K  # from 1K to reduce CPU cycles to manage key_cache
query_prealloc_size=32K  # from 8K to reduce malloc frequency
query_alloc_block_size=32K  # from 8K to reduce CPU cycles to manage blocks
transaction_prealloc_size=32K  # from 4K to reduce malloc frequency
transaction_alloc_block_size=32K  # from 8K to reduce CPU cycles to manage blocks

有些问题不能用一句话来回答,就这样。
如需其他建议,请查看我的个人资料,联系信息的网络个人资料。

798qvoo8

798qvoo82#

如果你想让virt在8g左右,那么你就必须让你的操作系统知道如何将swap增加到1g以上,这是问题中第一个屏幕截图显示的限制。

bz4sfanl

bz4sfanl3#

快速的答案是降低 innodb_buffer_pool_size 足够让virt降到想要的8克。然而,。。。
未经进一步讨论,不要将其设置在1g以下;
如果降低它不改变病毒;让我们进一步讨论;
你有0.5克“免费”--真的有问题吗?

相关问题