What is the expected production requirements for memory / cpu for maxscale
We have a server with 4 GB of memory configured, to run maxscale as a query r / w router, with a replication manager running on the same server.
I have found on doing a large number of inserts in a single transaction, in the millions of rows. 5G File with 10 Million rows Using LOAD data infile. Running this same load of data infile against the backend server directly works with out any issues.
This is the max_allowed_packet on the backend server.
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'\G
*************************** 1. row ***************************
Variable_name: max_allowed_packet
Value: 16777216
The backend server has 32 GB of ram, with no services currently useing it as we are still testing it out and tuneing configuration.
The max scale server runs out of memory. This large set of inserts causes maxscale to crash.
I see the following errors on my client side.
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server at 'reading initial communication packet', system error: 111
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server at 'reading initial communication packet', system error: 111
And on the server side I see the following in the max scale logs.
2017-10-16 19:06:32 notice : Started MaxScale log flusher.
2017-10-16 19:06:32 notice : MaxScale started with 7 server threads.
2017-10-16 19:15:14 notice : Waiting for housekeeper to shut down.
2017-10-16 19:15:15 notice : Finished MaxScale log flusher.
2017-10-16 19:15:15 notice : Housekeeper shutting down.
2017-10-16 19:15:15 notice : Housekeeper has shut down.
2017-10-16 19:15:15 notice : MaxScale received signal SIGTERM. Exiting.
2017-10-16 19:15:15 notice : MaxScale is shutting down.
2017-10-16 19:15:15 notice : MaxScale shutdown completed.
2017-10-16 19:15:15 MariaDB MaxScale is shut down.
----------------------------------------------------
MariaDB MaxScale /var/log/maxscale/maxscale.log Mon Oct 16 19:15:17 2017
----------------------------------------------------------------------------
2017-10-16 19:15:17 notice : Working directory: /var/log/maxscale
2017-10-16 19:15:17 notice : MariaDB MaxScale 2.1.5 started
2017-10-16 19:15:17 notice : MaxScale is running in process 21067
2017-10-16 19:15:17 notice : Configuration file: /etc/maxscale.cnf
2017-10-16 19:15:17 notice : Log directory: /var/log/maxscale
2017-10-16 19:15:17 notice : Data directory: /var/cache/maxscale
2017-10-16 19:15:17 notice : Module directory: /usr/lib64/maxscale
2017-10-16 19:15:17 notice : Service cache: /var/cache/maxscale
2017-10-16 19:15:17 notice : Loading /etc/maxscale.cnf.
2017-10-16 19:15:17 notice : /etc/maxscale.cnf.d does not exist, not reading.
2017-10-16 19:15:17 notice : Loaded module ccrfilter: V1.1.0 from /usr/lib64/maxscale/libccrfilter.so
2017-10-16 19:15:17 notice : [cli] Initialise CLI router module
2017-10-16 19:15:17 notice : Loaded module cli: V1.0.0 from /usr/lib64/maxscale/libcli.so
2017-10-16 19:15:17 notice : [readwritesplit] Initializing statement-based read/write split router module.
2017-10-16 19:15:17 notice : Loaded module readwritesplit: V1.1.0 from /usr/lib64/maxscale/libreadwritesplit.so
2017-10-16 19:15:17 notice : [mysqlmon] Initialise the MySQL Monitor module.
2017-10-16 19:15:17 notice : Loaded module mysqlmon: V1.5.0 from /usr/lib64/maxscale/libmysqlmon.so
2017-10-16 19:15:17 notice : Loaded module MySQLBackend: V2.0.0 from /usr/lib64/maxscale/libMySQLBackend.so
2017-10-16 19:15:17 notice : Loaded module MySQLBackendAuth: V1.0.0 from /usr/lib64/maxscale/libMySQLBackendAuth.so
2017-10-16 19:15:17 notice : Loaded module maxscaled: V2.0.0 from /usr/lib64/maxscale/libmaxscaled.so
2017-10-16 19:15:17 notice : Loaded module MaxAdminAuth: V2.1.0 from /usr/lib64/maxscale/libMaxAdminAuth.so
2017-10-16 19:15:17 notice : Loaded module MySQLClient: V1.1.0 from /usr/lib64/maxscale/libMySQLClient.so
2017-10-16 19:15:17 notice : Loaded module MySQLAuth: V1.1.0 from /usr/lib64/maxscale/libMySQLAuth.so
2017-10-16 19:15:17 notice : No query classifier specified, using default 'qc_sqlite'.
2017-10-16 19:15:17 notice : Loaded module qc_sqlite: V1.0.0 from /usr/lib64/maxscale/libqc_sqlite.so
2017-10-16 19:15:17 notice : Encrypted password file /var/cache/maxscale/.secrets can't be accessed (No such file or directory). Password encryption is not used.
2017-10-16 19:15:17 notice : [MySQLAuth] [Read-Write_Service] Loaded 227 MySQL users for listener Read-Write_Listener.
2017-10-16 19:15:17 notice : Listening for connections at [10.56.229.60]:3306 with protocol MySQL
2017-10-16 19:15:17 notice : Listening for connections at [::]:6603 with protocol MaxScale Admin
2017-10-16 19:15:17 notice : Started MaxScale log flusher.
2017-10-16 19:15:17 notice : MaxScale started with 7 server threads.
2017-10-16 19:15:17 notice : Server changed state: tmsdb-isa-01[10.56.228.64:3306]: new_master. [Running] -> [Master, Running]
2017-10-16 19:15:17 notice : Server changed state: tmsdb-isa-02[10.56.228.65:3306]: new_slave. [Running] -> [Slave, Running]
2017-10-16 19:15:17 notice : Server changed state: tmsdb-rp-01[10.21.228.65:3306]: new_slave. [Running] -> [Slave, Running]
2017-10-16 19:15:17 notice : [mysqlmon] A Master Server is now available: 10.56.228.64:3306
I ran VMSTAT and noticed that the server runs out of memory
[root@maxscale-isa-02 ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 1485828 0 356652 0 0 1 0 0 0 0 0 100 0 0
[root@maxscale-isa-02 ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 368000 0 356496 0 0 1 0 0 0 0 0 100 0 0
[root@maxscale-isa-02 ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
2 0 0 173388 0 356512 0 0 1 0 0 0 0 0 100 0 0
[root@maxscale-isa-02 ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 1 0 103660 0 308088 0 0 1 0 0 0 0 0 100 0 0
[root@maxscale-isa-02 ~]# vmstat
-bash: fork: Cannot allocate memory
[root@maxscale-isa-02 ~]# vmstat
-bash: fork: Cannot allocate memory
[root@maxscale-isa-02 ~]# vmstat
-bash: fork: Cannot allocate memory
[root@maxscale-isa-02 ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 2478676 0 323508 0 0 1 0 0 0 0 0 100 0 0
Edit
We added an additional 4G RAM to the max scale server for 4GB to 8GB total.
We can now see that it will read up to the 5G of ram for the import process with out crashing.
This seams to indicate that the server running maxscale will need as much memory as all of the services running queries through the server will require at peak memory usage. When using the ReadWriteSplit router.
We are going to test the ReadConnRoute router to see if we can lower the memory usage requirements.