2

We have a GPS device socket handling server using NodeJS which sends the received HTTPS request to Apache installed server and saving the data into MySQL Database. When we restart NodeJS or Apache installed server, Request Handling rate of the Apache installed server gets really slow. As far now, we have checked MySQL performance and its working properly. Per second only 5 to 15 requests may be triggered from NodeJS server, In normal situation its working fine but after restart the server goes very worse. After several hours, automatically the problem solves. Please do guide, how to resolve this issue and required details to share for monitoring it?

Server details:

Ubuntu server with 8GB RAM and 4 Cores processor.

MySQL Config:

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

Top Command: Top Process list, MySQL using 6.8GB

After sometime, MySQL restarts automatically and server getting slow again.

Apache Buddy

[ -- ] 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)

Location Table:

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

Trips Table

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

Schema critique

Lat/lng: We are planning to implement Geo-fence, so thought we require Points for Geo spacial search so kept this field. We have not implemented it, if thats not required we will remove that.

BIGINT: Devices will be growing. But at present only 400 devices. Hope we can use small int at present.

raw Will remove both fields

updated_at we never used

created_at to generate trip for the current date we use that while in select

status I dont know about composite index I will try to implement it.

Top Command Top command screenshot

jAddict
  • 23
  • 8
  • 1
    Have you over-committed RAM? – Rick James Dec 19 '19 at 17:37
  • 1
    Hi @RickJames, I have updated the question with the necessary details – jAddict Dec 20 '19 at 06:55
  • Is Apache running on the same machine? How many children is it configured for? – Rick James Dec 20 '19 at 06:58
  • 1
    Yes apache is running on the same machine. Also attached the apache2buddy report in the question @RickJames – jAddict Dec 20 '19 at 07:20
  • 512 * 128M = 64TB potentially usable by Apache + PHP. Lower the 512 drastically! – Rick James Dec 20 '19 at 07:31
  • Is it good to reduce the PHP memory limit to 20MB as largest request being 10 MB in apache? And reduce the Apache ServerLimit to 100? – jAddict Dec 20 '19 at 07:40
  • Have changed Apache server limit to 100 and Buffer pool 5GB. Our database size is 6.5GB currently. Server reads and writes are high on one table. If 1000 records exists and those records are read, then it will never be used again. Records adding new from NodeJS server will be used further. So indexing and having the old records may be waste. Will that have any impact? Every day the RAM gets filled, we truncate that table. – jAddict Dec 20 '19 at 11:33
  • Whether those 1000 clutter the buffer_pool or get pushed out depends on many factors. Please provide `SHOW CREATE TABLE` and clues on how to recognize the 1000. – Rick James Dec 20 '19 at 18:00
  • Included the SHOW CREATE TABLE Output in the question – jAddict Dec 20 '19 at 18:11
  • @RickJames Am unable to find your answer in this question. Actually your suggestions of reducing the Apache server limit and buffer pool suggestion is very helpful that server collapse on restart normalised. I need to accept it. – jAddict Dec 22 '19 at 03:35
  • @WardReinstateMonica - Why was my answer deleted? – Rick James Dec 22 '19 at 05:08
  • What version of MySQL is it? – Rick James Dec 22 '19 at 16:47
  • Please post TEXT results of SHOW CREATE TABLE devices; in the original question for us. Thanks – Wilson Hauck Dec 28 '19 at 16:52
  • Devices table is a big table with around 100 fields. Can I post with minimal required items? @WilsonHauck – jAddict Dec 29 '19 at 02:48
  • No, SHOW CREATE TABLE devices; with 100 fields/columns will be less than 400 lines of text. Thanks – Wilson Hauck Dec 29 '19 at 14:00
  • As device sends data to Node JS every 5 seconds, all the HTTPS request generated to Apache server are sending in the Interval of 5 seconds. Not continuously triggered to Apache server. – jAddict Dec 30 '19 at 16:52
  • @WilsonHauck I have sent you the devices table create statement, please take a look into. Currently after updating the changes in config MySQL working fine. I have turned off MySQL code and tried restarting NodeJS pm2 server. Actually the node js server not sending the HTTPS post request to apache server as it receives. The problem relies in Node JS execution, Not continuously triggered to Apache server as we hope so. – jAddict Dec 30 '19 at 18:23
  • 1
    @jAddict Could we Skype connect to review this specific comment, soon. Thanks – Wilson Hauck Jan 05 '20 at 20:49

3 Answers3

1

Memory issues

Lower to innodb_buffer_pool_size=5G.

I suspect that the system is running out of memory and either crashing or using the OOM killer to make a mess.

88% for MySQL -- but how much for Apache? The OS? Other things? Do not let it get to 100%. Swapping is terrible for performance of MySQL.

Lower Apache's MaxRequestWorkers.

Schema critique

Shrinking the disk footprint will help since you have a large amount of traffic in this table.

Lat/lng: Do you need both POINT (25 bytes) and a pair of numbers (6 bytes each)? Do you need that much precision in the numbers? See http://mysql.rjweb.org/doc.php/latlng#representation_choices

IMEI -- Isn't that 15 digits? Certainly don't need utf8mb4. 15 digits in varchar(20) would take 17 bytes. In DECIMAL(15), it would take 7.

GSM: mcc, mnc, lac, cid could be normalized into another table and have a 3-byte MEDIUMINT UNSIGNED for JOINing.

BIGINT -- 8 bytes each. How many devices and drivers do you expect to have?

raw* -- You probably don't use these? They could be move to a less convenient file (for example), to save a lot of space in the table. (I am assuming you would 'never' look at the file(s).)

raw* -- If you choose to keep them in the table, compressing them and using VARBINARY will shrink the space by a factor of about 3. (Do the compress/uncompress in the client.)

created_at, updated_at -- Sounds like boilerplate that is never used. (5 bytes each).

status -- Indexing low-cardinality column by itself is rarely useful. Do you have a query that might need it? If so, perhaps a composite (multi-column) index would be useful.

I suspect those changes would shrink this table by half.

Buffer_pool caching

Each row inserted needs to insert into each index:

  • PK -- onto the end of the data (1 hot spot)
  • device_id -- 1 hot spot per device. How many devices?
  • status -- 2? hot spots
  • created at -- virtually every row goes onto the 'end' of this index. (1)

Conclusion: For inserting, very little of the buffer pool is actually used. 16KB (size of a block) times (D+4) where D is the number of devices. Total: a few megabytes?

Do you have any big SELECTs? These could sweep through the table forcing blocks to come and go. Any table scan would probably lead to a bunch of I/O. So, try to avoid table scans.

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • Will allocating 5GB for InnoDB be sufficient? I am not sure about the exact metrics to assign for the usage. – jAddict Dec 20 '19 at 07:21
  • OK, pick 3G.... – Rick James Dec 20 '19 at 07:25
  • No my question is 5 or 3 GB be OK or else need to allocate high RAM than 6GB by increasing the Server size for innodb_buffer_pool_size. In mysql log, yesterday I have seen like buffer pool size has been completed. – jAddict Dec 20 '19 at 07:27
  • @jAddict - 6G of the 6.8G is the buffer pool currently. I now see that apache is using 1.3G; 1.3+6.8 exceeds your RAM size. Something needs to shrink. The buffer_pool is a quick fix. Try it; see if the problem stops. – Rick James Dec 20 '19 at 07:29
  • Ok I will try by setting 5GB now. When we restart MySQL manually too the same problem of server slow appears. – jAddict Dec 20 '19 at 07:32
  • @jAddict - what happens to the GPS messages that come in during the restart? To they pile up? Perhaps filling up more and more of the 512 Apache children? See apache2buddy and my comments above. – Rick James Dec 20 '19 at 07:34
  • Why our application innodb pool size eats larger and larger RAM over time and not giving back it. Is it normal behaviour of the MySQL or our architectural issue? – jAddict Dec 20 '19 at 07:36
  • Yes when we restart the mysql server, we stop the http posts from nodejs server. Then after restart, we open the http posts from nodejs, then the server getting collapsed. **We are unable to recognise whether nodejs request pooling / apache request handling makes the issue / Server blocking continous requests (we use cloudflare)**. Eventually MySQL iowrites getting high because in one second 90-100 requests appearing from different times the NodeJS triggered the request. Slowly the request handling takes more time even more than 15 minutes where GPS coordinates received in the NodeJS server. – jAddict Dec 20 '19 at 07:48
  • @jAddict - The buffer_pool grows until it reaches the indicated size (6GB), then stops. (This is by design.) Decrease both innodb_buffer_pool_size and MaxRequestWorkers. – Rick James Dec 20 '19 at 17:57
  • Yes changed buffer pool to 5GB and MaxRequestWorkers to 100 – jAddict Dec 20 '19 at 18:05
  • @jAddict - I added a lot. – Rick James Dec 20 '19 at 18:46
  • Thanks a lot @Rick. What we are doing is we save all the received co-ordinates in to locations table. And at every fifteen minutes we make the recently received location items as a json and append in another table. I have attached that table in the question. – jAddict Dec 20 '19 at 19:34
  • I am unable to get exactly about Buffer_pool caching suggestions you mentioned, Please do help in that section @Rick – jAddict Dec 20 '19 at 20:13
  • Hope Buffer pool caching you mentioned explains the strategy how it processes. There are more select than inserts. But to avoid big selects we append chunk of data into trips LONGTEXT field as JSON at certain interval of time. Once moved those records is not used. Whether that idea is good to follow? There are more selects than inserts but what to do you mean by table scans @Rick? Thanks. – jAddict Dec 21 '19 at 03:17
  • After 9 hours of mysql running, innodb status -> Number of rows inserted 504485, updated 517144, deleted 0, read 99046153 13.83 inserts/s, 13.83 updates/s, 0.00 deletes/s, 303.53 reads/s – jAddict Dec 21 '19 at 04:55
  • @jAddict - Moving data around is usually not wise, but I can't tell for your case. A "table scan" is a `SELECT` (or update or delete) that looks at every row in a table, often because of lack of an adequate index. – Rick James Dec 21 '19 at 05:33
  • Within 24 hours, 3-4 million datas getting accumulated in the locations table. If we need to get trip of a week before date. We need to save more than 15 million records. It goes even worse on other aspects so planned the table like this. Is there any better option for doing this @Rick? – jAddict Dec 21 '19 at 06:16
  • @jAddict - What are the `UPDATEs`? And why are the virtually in lock step with `INSERTs`? What will you do with the 15M records? Download them to your smartphone? – Rick James Dec 21 '19 at 06:41
  • We have device, location and trip table. Current state and basic info of devices will be saved in device table. Live device data will be in locations. Due to its size getting larger and record fetching takes more time we are using cron to move the saved locations to trips at regular interval. So we truncate locations daily. – jAddict Dec 21 '19 at 07:00
  • Yes using that information we show live tracking and history of the device in the web and also in android. – jAddict Dec 21 '19 at 07:01
  • @jAddict - more tips: http://mysql.rjweb.org/doc.php/staging_table – Rick James Dec 21 '19 at 07:05
  • Sure will take a look into it. Thank @Rick. – jAddict Dec 21 '19 at 07:51
  • @jaddict Please post 'device table' above your 'location table' so we have all 3 table definitions available in the Question area. – Wilson Hauck Dec 21 '19 at 12:14
0

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

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

You will find these configuration changes REDUCE CPU significantly. View my profile, Network profile for downloadable FREE Utility Scripts to improve performance.

Wilson Hauck
  • 472
  • 5
  • 11
  • Why our application innodb pool size eats larger and larger RAM over time and not giving back it. Is it normal behaviour of the MySQL or our architectural issue? – jAddict Dec 20 '19 at 16:18
  • 1
    With your configuration of yesterday, EVERY connection could use 32M for read_rnd_buffer_size PLUS 32M for sort_buffer_size. So your RAM needed is much greater than your available 8G of RAM. Apply these 3 suggestions and let us know how you are doing after 24 hours, please. – Wilson Hauck Dec 20 '19 at 16:24
  • have updated it. Will update you the progress. Thank you. – jAddict Dec 20 '19 at 16:41
  • Also can I change this values? ```-------- MyISAM Metrics ------- [!!] Key buffer used: 18.3% (3M used / 16M cache) [OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K [!!] Read Key buffer hit rate: 92.2% (90 cached / 7 reads)``` – jAddict Dec 20 '19 at 16:42
  • No, this area is also used to manage tmp table creation, 24 x 7. – Wilson Hauck Dec 20 '19 at 16:52
  • **Key_buffer used:** is always 18.3%. If I set 4M, 767 bytes used. If I set 256K, 52K is used. I will keep this as 16M. And watch for 24 hours and update you. Thank you. – jAddict Dec 20 '19 at 18:23
  • @jaddict Looking forward to your update later today. Thank you. – Wilson Hauck Dec 21 '19 at 12:30
  • After changing apache limits and mysql suggestions, mysql innodb buffer completed within 13 hours of mysql started and now using 6.1GB RAM. But RAM availability is not went exhausted till now. Server restarted before 22 hours. – jAddict Dec 21 '19 at 15:02
  • At 10 hours after restart gather the 'Data We Need', please. There will be clues on why you are running out of ram with our Slow Query Log Analysis. – Wilson Hauck Dec 21 '19 at 16:27
  • @jAddict - Let's see the top of "top", where it indicates swap space, etc. – Rick James Dec 22 '19 at 16:43
  • No I dont configured swap space. Using only 8GB RAM. – jAddict Dec 22 '19 at 17:10
  • Please POST the 1st page of 'top' report. Valuable additional needed information will be available. – Wilson Hauck Dec 22 '19 at 18:43
  • Without swap space, it crashes instead of slowing down first. – Rick James Dec 22 '19 at 21:16
  • @jAddict Make your swap space 20% of RAM is best practice according to Red Hat, rather run slow for a while than CROAK. – Wilson Hauck Dec 22 '19 at 21:21
  • Have added TOP screenshot. I will add swap space now. Thanks for the valuable suggestion. – jAddict Dec 22 '19 at 22:23
  • @jAddict TOP indicates there are 25 apache2 PID's hanging around for extended TIME of 10 or more MINUTES. and they are SLEEPING. How can they be useful after so long doing nothing? – Wilson Hauck Dec 28 '19 at 17:34
0

innodb_buffer_pool_size = 6G on an 8GB server is tight. Lower it to 5G or less.

Apache's MaxRequestWorkers at 512 takes a bunch of RAM and threatens to overwhelm MySQL with too many concurrent connections. Lower it and MySQL's max_connections down to, say, 100.

Rick James
  • 2,463
  • 1
  • 6
  • 13