API Server Cluster Optimized for JSON Data Submission
I have a distributed application sending info to API servers that are slaves of a MySQL 8 master. Applications do full initial sync (~100,000 records in batches of 500) followed by incremental syncs every 5 minutes.
I have 3 Dell R620 servers with 512GB RAM, 5 SSD in RAID 6 that are acting as web servers. I have dedicated one to being a master MySQL using the following config:
[mysqld] server-id=1 # GENERAL # pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql/ bind-address=* # GENERAL # user = mysql default-storage-engine = InnoDB # MyISAM # key-buffer-size = 32M myisam-recover-options = FORCE,BACKUP # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 skip-name-resolve #skip-grant-tables # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin binlog_expire_logs_seconds = 2000 sync-binlog = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M max-connections = 500 thread-cache-size = 50 open-files-limit = 10000 table-definition-cache = 4096 table-open-cache = 4096 # ## INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 360G # ## LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log # ## REPLICATION ## slave-parallel-workers=10 slave-parallel-type = LOGICAL_CLOCK innodb-flush-log-at-timeout=1800 [mysql] # CLIENT # port = 3306
On the other servers that host the API, the goal is for them to do select queries on the local slave server, and write changes back to the master which will allow us to have additional resources dedicated to receiving incoming API calls. Because they are primarily for Apache / PHP, I have reduced
innodb-buffer-pool-size = 64G.
What optimizations should I use for Apache and PHP for high RAM servers?
I set this up, but not sure if I am under utilizing available resources:
<IfModule mpm_prefork_module> StartServers 200 MinSpareServers 20 MaxSpareServers 50 MaxRequestWorkers 100 MaxConnectionsPerChild 0 ServerLimit 512 MaxClients 512 MaxRequestsPerChild 10000 </IfModule>
A more complete overview of my settings including variables, status, mysqltuner.pl report can be found here: http://plnkr.co/edit/eeGHzFX95j5auJ5lTYum?p=catalogue
We are receiving about 5600 requests per hour right now, about 70% may have up to 500 records per request that needs an update or insert query. That adds up to around 550 queries per second. Server load is commonly between 2.5-4.
The website was written in Laravel 5.4 and we tested throughput to the normal API routes using Laravel, Eloquent, and so on and when using Apache Benchmark using the following:
ab -c 100 -n 2000 -p sample.json -T application/json -H "Content-Type: application/json" -H "Authorization: Bearer eyJ0eXAiO" https://www.myserver.com/api/accounting
Here are the results:
Benchmarking www.myserver.com (be patient) Completed 200 requests Completed 400 requests Completed 600 requests Completed 800 requests Completed 1000 requests Completed 1200 requests Completed 1400 requests Completed 1600 requests Completed 1800 requests Completed 2000 requests Finished 2000 requests Server Software: Apache/2.4.29 Server Hostname: www.myserver.com Server Port: 443 SSL/TLS Protocol: TLSv1.2,ECDHE-RSA-CHACHA20-POLY1305,2048,256 TLS Server Name: www.myserver.com Document Path: /api/accounting Document Length: 65 bytes Concurrency Level: 100 Time taken for tests: 375.487 seconds Complete requests: 2000 Failed requests: 1134 (Connect: 0, Receive: 0, Length: 1134, Exceptions: 0) Total transferred: 735018 bytes Total body sent: 162864000 HTML transferred: 131018 bytes Requests per second: 5.33 [#/sec] (mean) Time per request: 18774.370 [ms] (mean) Time per request: 187.744 [ms] (mean, across all concurrent requests) Transfer rate: 1.91 [Kbytes/sec] received 423.57 kb/s sent 425.49 kb/s total Connection Times (ms) min mean[+/-sd] median max Connect: 3 315 1554.1 5 11497 Processing: 8420 18299 2501.9 18658 24051 Waiting: 8419 18298 2501.9 18658 24050 Total: 8424 18614 2791.2 18792 30388 Percentage of the requests served within a certain time (ms) 50% 18792 66% 19699 75% 20247 80% 20619 90% 21560 95% 22343 98% 23933 99% 27099 100% 30388 (longest request)
sample.json contained 500 records and we had the server load hit a load of 103. You will also notice we had over half our posts fail.
It seems apache is our bottleneck, and as I dug into it using
get_included_files() I found that Laravel uses 275 includes just to get to the routes.php file, by the time it starts posting to our API, it uses 462, and by the end of posting to the API it uses 575 included files.
We rebuilt the same function outside Laravel using a single PHP page that defined the PDO connection, looped over the data queries in the same way generating queries for inserts and updates and it completed the same task with these stats:
Concurrency Level: 100 Time taken for tests: 16.367 seconds Complete requests: 2000 Failed requests: 228 (Connect: 0, Receive: 0, Length: 228, Exceptions: 0) Total transferred: 502228 bytes Total body sent: 162804000 HTML transferred: 126228 bytes Requests per second: 122.19 [#/sec] (mean) Time per request: 818.366 [ms] (mean) Time per request: 8.184 [ms] (mean, across all concurrent requests) Transfer rate: 29.97 [Kbytes/sec] received 9713.76 kb/s sent 9743.73 kb/s total Connection Times (ms) min mean[+/-sd] median max Connect: 3 9 14.7 6 98 Processing: 242 800 281.3 764 2187 Waiting: 241 799 281.3 764 2187 Total: 246 809 283.8 774 2195 Percentage of the requests served within a certain time (ms) 50% 774 66% 905 75% 986 80% 1040 90% 1201 95% 1328 98% 1493 99% 1618 100% 2195 (longest request)
Server load only hit 12 while posting these with 0 failed posts. Because of the significant improvement, we are looking at pulling the API code out of Laraverl and optimizing one server for Mysql, and then having multiple slaves. Each slave would have read only access to localhost for the API to query to determine whether each record should be an update or insert statement, then would execute queries on the MySQL master.
While I have looked around for answers, so many resources were written when 4GB-32GB RAM was normal, and when you do find one with 512GB, it usually refers to an SSD.