我们有一个使用 NodeJS 的 GPS 设备套接字处理服务器，它将收到的 HTTPS 请求发送到 Apache 安装的服务器并将数据保存到 MySQL 数据库中。当我们重新启动 NodeJS 或 Apache 安装的服务器时，Apache 安装的服务器的请求处理率会变得非常慢。到目前为止，我们已经检查了 MySQL 性能并且它运行正常。每秒可能只有 5 到 15 个请求可能从 NodeJS 服务器触发，在正常情况下它工作正常，但重新启动后服务器会变得非常糟糕。几个小时后，问题会自动解决。请提供指南，如何解决此问题以及监控它所需的详细信息？
服务器详细信息：
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
架构批判
纬度/经度：我们计划实施地理围栏，因此认为我们需要点来进行地理空间搜索，所以保留了此字段。我们尚未实施它，如果不需要，我们将删除它。
大智能：设备数量会越来越多。但目前只有 400 个设备。希望我们目前可以使用小 int。
生的将删除两个字段
更新于我们从未使用过
创建于为了生成当前日期的行程，我们在选择时使用它
地位我不知道复合索引，但我会尝试实现它。
最高指挥部
答案1
内存问题
降低至innodb_buffer_pool_size=5G。
我怀疑系统内存不足，要么崩溃，要么使用 OOM 杀手来制造混乱。
MySQL 占用 88% —— 但 Apache 占用多少？操作系统？其他东西？不要让它达到 100%。交换是糟糕的以提高 MySQL 的性能。
降低 Apache 的 MaxRequestWorkers。
架构批判
由于此表中有大量流量，因此缩小磁盘占用空间将会有所帮助。
纬度/经度：您是否需要两者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 个字节）。
status -- 单独索引低基数列很少有用。您是否有可能需要它的查询？如果有，也许合成的（多列）索引会很有用。
我怀疑这些变化会使这个表缩小一半。
Buffer_pool 缓存
插入的每一行都需要插入到每个索引中：
- PK——到达数据末端（1个热点）
- device_id -- 每个设备 1 个热点。有多少个设备？
- 状态 -- 2? 热点
- 创建于——实际上每一行都进入该索引的“末尾”。（1）
结论：对于插入，实际上使用的缓冲池非常少。16KB（块大小）乘以（D+4），其中 D 是设备数量。总计：几兆字节？
您有任何大型 SELECT 吗？这些可能会扫过整个表，迫使块进出。任何表扫描都可能导致大量 I/O。因此，请尽量避免表扫描。
答案2
每秒速率 = RPS
对 my.cnf [mysqld] 部分的建议
read_rnd_buffer_size=256K # from 32M to conserve RAM and reduce handler_read_rnd_next RPS
sort_buffer_size=4M # from 32M to conserve RAM footprint
thread_cache_size=100 # from 8 to reduce threads_created
您会发现这些配置更改会显著减少 CPU。查看我的个人资料、网络个人资料，获取可下载的免费实用程序脚本以提高性能。
答案3
innodb_buffer_pool_size= 8GB 服务器上的 6G 很紧张。将其降低到 5G 或更少。
Apache 的MaxRequestWorkers512 占用大量 RAM，并可能因过多的并发连接而使 MySQL 不堪重负。将其降低，MySQL 的 512max_connections就会下降到 100。