Impact of MySQL multihost client connections(master-slave)

Background:

When there is MySQL replication, with slaves replicating the master asynchronously, the application can be configured to connect to both master and slave. The MySQL Replication Driver can be used to load balance between different slaves, or choose slave over master for specific queries.
The following spring datasource will do the work,

To send queries to slaves, we need to set the JDBC Connection to readOnly. This can be achieved by setting the readyOnly attribute of @Transactional to be True. Actually, it depends on the configured TransactionManager, it works for DataSourceTransactionManager, but may not work for other transaction managers. If it's not working, you can get the current connection in the application code using DataSourceUtils.getConnection(DataSource) and set readOnly using Connection.setReadOnly(True). We used this way type of connections to scale out our reads and to increase the utilisation of rather idle slaves.

Impact:

The effect was now each abstract connection object holds as many hosts in the connection URL. Let's assume that the max connection limit setting is enabled and the limit is reached for one of the slave host. Now, the application won't be able to create any connection at all, since it can't open a connection to that specific slave, which had hit its limit. Even though all the other mysql servers can accept incoming connections,  the application won't be able to create new connection. For it to create a new connection, at the time of creation, master and all the slaves must be able to accept connections or the slave must be down. If the slave is totally down, we are able to created a new connection successfully, since this scenario is handled by allowSlaveDownConnections flag.

 After this issue, we learnt that it's better to separate the master and slave as two different datasources at the application level and use it accordingly.

Comments

Post a Comment

Popular posts from this blog

Spring data mongodb secondary node reads

Reading from secondary mongodb is one of the simplest way to scale number of reads from the application. By default, all the reads and writes goes to primary. Although mongodb docs caution against going ahead with this option, there are certain scenarios where it's useful, Data is mostly static and changes if at all, rarely Reporting systems, where lagging data is fine High number of reads, relatively low writes The risk is that the data read from secondary might not be latest, so we have to be careful in choosing which queries are to be sent to secondary.  It's best to consider setting SecondaryPreferred with maxStaleness for the driver to determine, whether to send to secondary or fallback to primary. We can achieve this behaviour by making use of Read Preference Spring Data MongoDB Spring provides two API to set Read Preference. One is via MongoTemplate and the other is through Query flags(slave ok). Option 1: MongTemplate: This approach is slightly tedio...
Read more

Spring mongodb connection pool limit

Spring Mongodb Connection Pool Limit I had started to observe performance degradation in my app, when the traffic spikes up in very few minutes. The current connections of front facing Apache will spike up during this period of time. Upon investigation found that mongodb primary server experiences high loadavg during this time. My mongo primary is CPU bound, as the entire working set fits comfortably in to the main memory. During the high loadavg, there were about 3000 active client connections, served by 4 cores. It is evident that 4 cores is struggling to deal with 3000 client connections at the same time. Just when I was thinking about increasing the no.of cores, I had came across this excellent article on pool sizing  About-Pool-Sizing . Instead of scaling up the CPU, I decided to limit the pool size in each server. By default, Java Mongo driver can open 100 connections in a pool, but it can be customised with additional options in the connection string. spring.data....
Read more

Spark streaming with kafka and HDFS

There are lot of resources for doing spark streaming to consume messages from Kafka, while running on top of HDFS. It is slightly tricky to get it done if both the Kafka and HDFS is secured using Kerberos and both belongs to different realms. If cross realms trust is setup between kafka and HDFS, we can use single principal/keytab for both of the realms. If not, spark must be configured as follows HDFS auth: In Yarn cluster deploy mode, spark config spark.yarn.principal and  spark.yarn.keytab must be set with the hadoop user's crendentials so that Yarn can periodically get kerberos tickets to access HDFS. This will copy the keytab over to the hdfs for yarn to access. Yarn will get a new ticket once the current ticket reaches 80% of its lifetime. Since this involves copying keytab to the hdfs, we must make sure that the hdfs have proper access security policy and unauthorised access to keytab file is checked. These two config can also be passed to spark-submit with --princ...
Read more