In previous blogs, we described how to select a database support partner, how to prepare your team and what the three phases of effective database support are. But how does it actually work? In this blog, Eric Gipon gives an example of how we worked at one of our customers.
MariaDB Galera Cluster with conflicts
If you are an organization working with data-driven solutions, a well-functioning database environment is crucial, that will be clear. Yet things sometimes go differently, as we found out when an organization engaged us. They work with a MariaDB Galera Cluster, a multi-master cluster running on Linux. They were having a lot of problems with conflicts in their database that were disrupting their business operations. Time to enlist OptimaData's help.
To begin with, we did a QuickScan. During such a QuickScan - also called a database assessment - we take a close look at the entire database environment and database architecture. Both the logical and technical data model, the hardware platform, the generic settings, performance, tuning, availability and disaster-recovery. Based on the results of the QuickScan, we issue a report with our findings and recommendations that we explain and discuss with the client.
Distribute SQL queries smartly
The Quick Scan revealed that the conflicts were caused by writing very quickly at the same time on the same database through the three database servers of the Galera Cluster. We made some configuration changes to optimize set-up to begin with. On top of that, we installed ProxySQL, a tool that provides proxy functionality for MySQL databases so that incoming SQL queries and other activities are distributed in the most optimal way across the database cluster - the Galera Cluster in this case.
Read and write
To avoid future problems, we also modified the setup so that writes are only made to one database server and reads are made to the other database servers. That seems contrary to what a Galera Cluster is intended for, which is that it can write and read at the same time. But the high availability of the Galera Cluster is also a big advantage of this solution: if a server goes down, it can be written to another server in an automated way.
Alerting and monitoring tools
We also installed Percona Monitoring and Management and Zabbix, two alerting and monitoring tools with which we have had good experiences. The first issues alerts based on self-set triggers, the second is a monitoring tool that looks under the hood, so to speak, at what is happening within the database environment, which queries are running and how often. This allows us to even better (proactively) identify where possible bottlenecks could occur.
After these initial actions, this - very satisfied - customer signed a maintenance contract with us. They can engage us for a number of hours per month and in case of production problems we provide 24/7 support. The alerts generated by the aforementioned tools therefore come directly to us. This set-up provides so much stability and performance improvements that we also recommend and implement this, or somewhat similar, set-up for other customers.
Want to know more?
Want to know more about the route to effective database management? We wrote a whitepaper about that. Download the whitepaper here. Are you interested in a QuickScan or would you like to exchange thoughts with us? Feel free to contact us, we would love to get to know you.