Use Case:
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
Updates
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.