MySQL 性能與記憶體

我們在Amazon Lightsail Linux實例（2GHz 單核 CPU、2GB RAM、40GB SSD）上執行MySQL Ver 5.7.18 。

除了 MySQL，我們還在這台機器上執行了一些訪問數據庫的Java服務。

MySQL 在預設配置上執行。

重啟機器或重啟 MySQL 服務後，我們觀察到一些語句的性能大幅下降SELECT，主要是在一個有大約 1500 萬行的表上執行（累積超過 1 年）。這些SELECT語句是使用者驅動的（Web 前端），並且主要獲取最新條目。幾個小時後，這些語句的性能開始提高並且足夠快（主要是 <1000 毫秒）。但是，如果我們開始執行影響舊值的查詢，性能又開始急劇下降。

如果我執行，mysqltuner.pl我會得到以下輸出：

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 9.2G (Tables: 33)
[OK] Total fragmented tables: 0

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 20h 4m 38s (4M q [59.289 qps], 20K conn, TX: 8G, RX: 1G)
[--] Reads / Writes: 82% / 18%
[--] Binary logging is disabled
[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 465.9M
[--] Other process memory: 1.1G
[--] Total buffers: 296.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 333.1M (16.64% of installed RAM)
[OK] Maximum possible memory usage: 465.9M (23.28% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (16/4M)
[OK] Highest usage of available connections: 21% (33/151)
[OK] Aborted connections: 0.11%  (23/20438)
[!!] 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: 1% (67 temp sorts / 3K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (623 on disk / 213K total)
[OK] Thread cache hit rate: 99% (45 created / 20K connections)
[OK] Table cache hit rate: 75% (1K open / 1K opened)
[OK] Open file limit used: 1% (66/5K)
[OK] Table locks acquired immediately: 100% (24K immediate / 24K locks)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 256.0M/9.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5 %): 48.0M * 2/256.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 2 for 1 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: 98.28% (338433973 hits/ 344356426 total)
[OK] InnoDB Write log efficiency: 91.11% (3684095 hits/ 4043723 total)
[OK] InnoDB log waits: 0.00% (0 waits / 359628 writes)

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
   Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
   Restrict Host for user@% to user@SpecificDNSorIp
   MySQL was started within the last 24 hours - recommendations may be inaccurate
   Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
   Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: [link]
Variables to adjust:
   innodb_buffer_pool_size (>= 9.2G) if possible.
   innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

輸出建議將其調整innodb_buffer_pool_size為至少數據庫的大小，不幸的是，這比我們擁有的 RAM 記憶體要多得多。

上述行為是由 MySQL 的緩衝/記憶體策略引起的，還是由配置錯誤引起的？

是否要求 MySQL 伺服器實例的 RAM 記憶體必須至少為數據庫的大小才能有效訪問數據？

我們預計未來數據庫將以每月 2000 萬行的速度增長。如果我算一下（每 20M 行大約 10GB 記憶體），一年後我們將需要超過 200GB 的 RAM 記憶體。

何時分配數據庫而不是安裝更多 RAM 記憶體是否有經驗法則？

• 關閉查詢記憶體——您沒有足夠的 RAM 將其浪費在 QC 上。
• 只有 2GB 的 RAM，256M 可能是安全的innodb_buffer_pool_size。提高它會導致交換，這對性能來說更糟。
• 最好的解決方案是加快查詢速度。我們需要找到最差的查詢。它們可能涉及表掃描，並且某些索引（可能是複合索引）會大大加快它們的速度。或重新制定查詢。
• 如果失敗了，那麼“正確”的解決方案是獲得更大的 VM。（但我認為我們可以改進索引/查詢。）
• “分發數據庫”聽起來像是“分片”或一些本土解決方案。這是一項艱鉅的任務；我會把這第三個放在選項列表中。
• 請注意，Java 佔用了寶貴的 RAM 空間。

引用自：https://dba.stackexchange.com/questions/220487