I think your my.cnf settings are far to low for good performance.
However, not all performance issues are related to mariadb. Sometimes it is the fault of the cloud service provider. You can get a feel for the performance of the physical server where your instance is hosted by running the following command:
wget -qO- bench.sh | bash
You should get a response that looks similar to this:
I circled in red the numbers that you need to be most concerned about. This is an indication of how your interaction with the physical server is performing. To give you some idea of what that means, consider the following:
The above server with an average I/O speed of 262.7MB/s will respond to a erpnext login request in about 3 to 4 seconds,
The exact same database and configuration on another set of similar hardware can still look very different. For example, this is another one of my servers that I only use as a training tool:
Notice the numbers here are almost half of the previous server. The time to login to ERPNext on this server is about 12 to 15 seconds. The lower numbers means the physical server is being shared by other virtual servers or droplets that you cannot see and each of them are taking up some of the available I/O throughput.
Here is another example of the exact same server configuration and the exact same database on yet another set of similar hardware:
Notice the huge difference in the performance numbers?
The ERPNext login time on this server is almost instantaneous!
All three of the examples above are from a single client. I keep 3 servers running all the time for each client using my Poor Mans Backup System just in case I have to move them to hardware in a different city due to some unforeseen internet outage.
I have other examples of 3 servers in the same building giving a similar wide range of performance results and it is all due to what other virtual hosts are being shared on the same hardware.
Oh… and the database size on these three servers is 2.4GB, so size can be big or small and still perform well if the database is tuned properly and the server has a high I/O throughput.
As another indicator of speed…
I restore the primary database (2.4GB) to all three of these servers every Sunday night:
- the fastest one restores in 3.5 minutes
- the middle one restores in 9 minutes
- and the slowest one restores in 26 minutes
Just try running the command that will give you the same display I presented above and you can get a feel for how your server is performing. If you made the MariaDB adjustments and didn’t get a satisfactory result, then look at the server performance and if needed contact your service provider to ask them to move your server to a different node that performs better.
BKM