Mysql

重啟後Apache 2處理請求非常慢

  • December 22, 2019

我們有一個使用 NodeJS 的 GPS 設備套接字處理伺服器，它將接收到的 HTTPS 請求發送到 Apache 安裝的伺服器並將數據保存到 MySQL 數據庫中。當我們重新啟動 NodeJS 或 Apache 安裝的伺服器時，Apache 安裝的伺服器的請求處理速度變得非常慢。到目前為止，我們已經檢查了 MySQL 的性能及其正常工作。每秒只能從 NodeJS 伺服器觸發 5 到 15 個請求，在正常情況下它工作正常，但重啟後伺服器變得非常糟糕。幾個小時後，問題自動解決。請指導，如何解決此問題以及共享監視它所需的詳細資訊？

伺服器詳情：

Ubuntu server with 8GB RAM and 4 Cores processor.

MySQL 配置：

key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

query_cache_limit       = 1M
query_cache_size        = 16M
max_binlog_size   = 100M

innodb_log_file_size=512M
innodb_buffer_pool_size=6G
innodb_buffer_pool_instances=6
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_log_files_in_group=5
innodb_open_files=1000
sync_binlog=0

max_connections=512
table_open_cache=1000
table_open_cache_instances=16
back_log=1000

query_cache_limit=2M
query_cache_size=0
query_cache_type=0

sort_buffer_size=32M
read_rnd_buffer_size=32M

頂級命令： 頂級程序列表，MySQL 使用 6.8GB

一段時間後，MySQL 自動重新啟動，伺服器再次變慢。

阿帕奇夥伴

[ -- ] Parent PID: 19547.
[ OK ] Memory usage of parent PID is less than 50MB: 7268 Kilobytes.
[ -- ] Apache has been running 0d 23h 19m 11s.
[ !! ] *** LOW UPTIME ***.
[ @@ ] The following recommendations may be misleading - apache has been restarted within the last 24 hours.
[ -- ] Your server has 7976 MB of PHYSICAL memory.
[ -- ] Your ServerLimit setting is 512.
[ -- ] Your MaxRequestWorkers setting is 512.
[ OK ] Current Apache Process Count is 45, including the parent PID.
[ -- ] Number of vhosts detected: 5.
[ -- ]             |________ of which 3 are HTTP (specifically, port 80).
[ -- ]             |________ of which 2 are HTTPS (specifically, port 443).
[ OK ] Current Apache vHost Count is less than maxrequestworkers.
[ >> ] MaxRequestsPerChild directive not found.
[ -- ] This server is NOT running Plesk.
[ -- ] This server is NOT running cPanel.
[ -- ] This server is NOT running Virtualmin.
[ -- ] Your PHP Memory Limit (Per-Process) is 128M.
[ -- ] MySQL Detected => Using 7038.63 MB of memory.

[ OK ] No large log files were found in /var/log/apache2.
[ OK ] MaxClients has not been hit recently.
[ >> ] Apache only logs maxclients/maxrequestworkers hits once in a lifetime, if no restart has happened this event may have been rotated away.
[ >> ] As a backup check, please compare number of running apache processes (minus 1 for parent) against maxclients/maxrequestworkers.
[ OK ] No PHP Fatal Errors were found.

[ -- ] apache2 is currently using 1308.65 MB of memory.
[ -- ] The smallest apache process is using 9.73 MB of memory
[ -- ] The average apache process is using 9.82 MB of memory
[ -- ] The largest apache process is using 10.19 MB of memory
[ !! ] Going by the average Apache process, Apache can potentially use 5027.85 MB RAM:
       Without considering services: 63.04 % of total installed RAM
       Considering extra services: 536.38 % of remaining RAM
[ !! ] Going by the largest Apache process, Apache can potentially use 5217.28 MB RAM:
       Without considering services: 65.42 % of total installed RAM
       Considering extra services: 556.59 % of remaining RAM


--------------------------------------------------------------------------------
### GENERAL FINDINGS & RECOMMENDATIONS ###
--------------------------------------------------------------------------------
Apache2buddy.pl report for server:
Settings considered for this report:
[ !! ] *** LOW UPTIME ***.
[ @@ ] The following recommendations may be misleading - apache has been restarted within the last 24 hours.

   Your server's physical RAM:                                   7976 MB
   Remaining Memory after other services considered:             937 MB
   Apache's MaxRequestWorkers directive:                         512      <--------- Current Setting    
   Apache MPM Model:                                             prefork
   Largest Apache process (by memory):                           10 MB
[ !! ]  Your MaxRequestWorkers setting is too high.
   Your recommended MaxRequestWorkers setting (based on available memory) is between 81 and 91. <------- Acceptable Range (10% of MAX)
   Max potential memory usage:                                   5217 MB
   Percentage of TOTAL RAM allocated to Apache:                  65.42  %
   Percentage of REMAINING RAM allocated to Apache:              556.59  %
--------------------------------------------------------------------------------
A log file entry has been made in: /var/log/apache2buddy.log for future reference.

Last 5 entries:

2019/12/19 07:37:28 Uptime: "0d 02h 26m 52s" Model: "Prefork" Memory: "7976 MB" MaxRequestWorkers: "512" Recommended: "352" Smallest: "9.81 MB" Avg: "10.28 MB" Largest: "12.35 MB" Highest Pct Remaining RAM: "145.16%" (79.28% TOTAL RAM)
2019/12/20 07:06:41 Uptime: "0d 23h 19m 11s" Model: "Prefork" Memory: "7976 MB" MaxRequestWorkers: "512" Recommended: "91" Smallest: "9.73 MB" Avg: "9.82 MB" Largest: "10.19 MB" Highest Pct Remaining RAM: "556.59%" (65.42% TOTAL RAM)

位置表：

CREATE TABLE `locations` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `device_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Device Associated with Location',
 `driver_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Driver associated with the Device',
 `packet_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1=Login\\n2=Heartbeat\\n3=Ping\\n4=Alarm',
 `latlng` point DEFAULT NULL COMMENT 'Longitude, Latitude',
 `lng` decimal(10,7) NOT NULL DEFAULT '0.0000000' COMMENT 'Longitude',
 `lat` decimal(10,7) NOT NULL DEFAULT '0.0000000' COMMENT 'Latitude',
 `device_time` datetime DEFAULT NULL,
 `server_time` datetime DEFAULT NULL,
 `imei` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `satellite` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `speed` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `acc` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `heading` smallint(5) unsigned NOT NULL DEFAULT '0',
 `gsm_mcc` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Mobile Country Code',
 `gsm_mnc` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Mobile Network Code',
 `gsm_lac` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Location Area Code',
 `gsm_cid` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Cell Tower ID',
 `gsm_signal` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'GSM Signal Percentage',
 `battery_level` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Battery Level Percentage',
 `alarm_code` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Alarm Code sent by GPS Device',
 `raw_data` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `raw_json` varchar(3000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '0=Not Active\\n1=Active\\n2=Deleted',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 KEY `locations_device_id_index` (`device_id`),
 KEY `locations_status_index` (`status`),
 KEY `locations_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

行程表

CREATE TABLE `trips` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `device_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Device Associated with Trip',
 `last_location_id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Last Location associated with the Trip Date',
 `trip_date` date NOT NULL,
 `items` longtext COLLATE utf8mb4_unicode_ci COMMENT 'Trips with points',
 `points` longtext COLLATE utf8mb4_unicode_ci COMMENT 'Received points',
 `is_place` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'When enabled, Need to get places',
 `is_fetch` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'When enabled, Need to fetch points from locations table',
 `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '0=Not Active\\n1=Active\\n2=Deleted',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `trips_device_id_index` (`device_id`),
 KEY `trips_trip_date_index` (`trip_date`),
 KEY `trips_is_place_index` (`is_place`),
 KEY `trips_is_fetch_index` (`is_fetch`),
 KEY `trips_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

關鍵方案

**Lat/lng：**我們正計劃實施地理圍欄，所以認為我們需要點進行地理空間搜尋，所以保留了這個欄位。我們還沒有實現它，如果不需要，我們將刪除它。

**BIGINT：**設備將不斷增長。但目前只有400台設備。希望我們目前可以使用small int。

raw將刪除兩個欄位

updated_at我們從未使用過

created_at為我們在選擇時使用的目前日期生成行程

status我不知道複合索引我會嘗試實現它。

最高指揮 頂部命令截圖

記憶體問題

降低到innodb_buffer_pool_size=5G。

我懷疑系統記憶體不足，要麼崩潰，要麼使用 OOM 殺手搞得一團糟。

MySQL 為 88%——但 Apache 需要多少？作業系統？其他事情？不要讓它達到 100%。交換對於 MySQL 的性能來說是很糟糕的。

降低 Apache 的 MaxRequestWorkers。

關鍵方案

縮小磁碟佔用空間會有所幫助，因為此表中有大量流量。

Lat/lng：你需要POINT（25 個字節）和一對數字（每個 6 個字節）嗎？你需要這麼精確的數字嗎？見http://mysql.rjweb.org/doc.php/latlng#representation_choices

IMEI——不是 15 位數字嗎？當然不需要utf8mb4。varchar(20) 中的 15 位數字將佔用 17 個字節。在DECIMAL(15)中，需要 7。

GSM: mcc, mnc, lac, cid 可以被規範化到另一個表中並且有一個 3 字節MEDIUMINT UNSIGNED的JOINing.

BIGINT – 每個 8 個字節。您希望擁有多少設備和驅動程序？

raw* – 你可能不使用這些？它們可以移動到不太方便的文件（例如），以節省表中的大量空間。（我假設您“永遠不會”查看文件。）

raw* – 如果您選擇將它們保留在表中，壓縮它們並使用VARBINARY會將空間縮小大約 3 倍。（在客戶端執行壓縮/解壓縮。）

created_at, updated_at – 聽起來像是從未使用過的樣板文件。（每個 5 個字節）。

狀態——單獨索引低基數列很少有用。您有可能需要的查詢嗎？如果是這樣，也許複合（多列）索引會很有用。

我懷疑這些更改會將這張表縮小一半。

Buffer_pool 記憶體

插入的每一行都需要插入到每個索引中：

  • PK——到數據的末尾（1個熱點）
  • device_id – 每個設備 1 個熱點。有多少設備？
  • 狀態——2？熱點
  • 創建於 – 幾乎每一行都進入該索引的“末尾”。(1)

結論：對於插入，實際使用的緩衝池很少。16KB（塊大小）乘以 (D+4) 其中 D 是設備數。總計：幾兆？

你有什麼大的選擇嗎？這些可能會掃過桌子，迫使方塊來來去去。任何表掃描都可能導致一堆 I/O。所以，盡量避免表掃描。

引用自：https://serverfault.com/questions/996129

相關問答

Mysql

Ubuntu 18.04 伺服器上的 mysql.service 位置？

  • September 6, 2021
檢視問答
Mysql

使用 mysql 生成 apache 兼容密碼

  • August 18, 2020
檢視問答
Virtualhost

單個液滴/虛擬主機上的多個 Wordpress 站點/一個站點正在執行，第二個站點出現白屏當機。為什麼？- 數字海洋

  • July 27, 2020
檢視問答
Nginx

ubuntu 18.04 上缺少 install_fastcgi.sh

  • May 2, 2020
檢視問答
Linux

Apache沒有載入首頁

  • April 24, 2020
檢視問答
Ubuntu

在哪裡正確給出 index.php？

  • April 23, 2020
檢視問答