Tuesday, May 27, 2014

MySQL Fabric: Server Properties & Scaling out reads

MySQL Replication is a well-known approach to providing high-availability and scaling out read-only operations (i.e. transactions) as well. In order to make it easy to exploit this scalability axis, we have extended both Fabric and the connectors' interface so that an application can express its willingness to execute a read-only operation and have its request redirected to the server that is most capable of handling it.

In this post, we are going to describe how we can use Fabric and the connector python to scale out read-only operations. We need to introduce some concepts first though.

Fabric organizes the servers in high-availability groups, uses the standard MySQL Replication to synchronize the servers which can be classified according to its status, mode and weight. The blog post MySQL Fabric High-Availability Groups from Mats briefly describes these concepts and the background for them.

Status

The status of the server provide information about what the server is currently doing in the group:
  • Primary can accept both reads and writes. It is the master in the standard MySQL Replication.
  • Secondary can accept only reads. It is a slave in the standard MySQL Replication.
  • Spare does not accept reads nor writes, but can be used to replace servers in the event of a failure. It is also a slave in the standard MySQL Replication.
  • Faulty is used to denote that a server is not working properly for any reason.
The following table depicts the transitions from one status to the other and the operations that can trigger them:
Target Status
Source Status PRIMARY SECONDARY SPARE FAULTY
PRIMARY promote/demote report
SECONDARY pomote/demote set_status report
SPARE promote/demote set_status report
FAULTY set_status

The report commands, which are "threat report_failure" and "threat report_error", can be used by external entities (e.g. connectors) and MySQL Fabric itself to inform that either a server is faulty or that it seems to be faulty, respectively. In other words, Fabric immediately sets a server to faulty after getting a notification through the "threat report_failure" command and only sets a server to faulty after getting notifications from different entities within a time period through the "threat report_error". 

These parameters might be set up in the configuration file under the "failure_tracking" section:
  • notifications: Number of reported errors after which a server is considered unstable.
  • notification_clients: Number of different sources that must report an error to consider a server unstable.
  • notification_interval: Window interval in seconds that is considered to compute the number of reported errors.
The "group promote" command is used to automatically demote the current primary, if there is any, and elect a new one among the healthy slaves. The "group demote" command is used to demote the current primary. Only secondaries are automatically picked by the "group promote" command as possible candidates to replace a primary. On the other hand, spares might be used to replace a primary only if explicitly requested.

Note that if a server is marked as faulty and we want to change its status after it has been repaired, the "server set_status" command must be used. In this case, the only possibility is to set its status from faulty to spare:

mysqlfabric server set_status server-id spare

Secondaries are ready to accept read requests so setting the status to spare avoid serving stale data as the server was down for a period of time and might have missed some updates. If the recently re-enabled spare was once a primary and we want to restore its previous status, the following command would do the task:

mysqlfabric group promote group-name --slave_id=server-id

The group-name and server-id must be replaced by the group which the server belongs to and its real UUID or address, respectively.

Mode

The mode of the server gives information on whether it can be read or written and provides information to the connector on where it should send queries to.
  • Offline server does not accept reads nor writes. Any spare server has its mode set to offline.
  • Read-only server can accept only reads. Any secondary server has its mode set to read-only.
  • Write-only server can accept only writes. It is possible to make a primary accept only writes.
  • Read-write server can accept both reads and writes. By default a primary accepts both reads and writes.
The server's mode is automatically changed when the status is changed. However, we can still perform some additional changes through the "server set_mode":

Possible Mode
Status OFFLINE READ_ONLY WRITE_ONLY READ_WRITE
PRIMARY x x
SECONDARY x x
SPARE x x
FAULTY

By default a spare server will not be picked by connectors to serve neither reads nor writes. However, if we want to expand the pool of scale-out servers, we can change its mode to read-only as follows:

mysqlfabric server set_mode server-id read_only

Weight

The weight represents the relative power of the server. It is a float number greater than zero and less or equal to one. By default, any server has the weight property equal to 1.0. Although any server may have its weighted changed through the "server set_weight" command in the server group command, this information is only used now to implement a weighted round-robin scheduling in the connectors to distribute reads:

mysqlfabric server set_weight server-id weight

The server-id and weight must be replaced by a real UUID or address and float number greater than zero and less or equal to one.

Scaling out Reads

Using python, an application can set that it wants to execute a read-only transaction as follows:

self.__cnx = mysql.connector.connect(**fabric_params)
self.__cnx.set_property(group=group, mode=MODE_READONLY)

Note the "mode" parameter in the "set_property" method. Read-only transactions are sent to any server that has the "mode" set to read_only or read_write meaning that it might be sent to either a secondary, a spare or a primary. The primary is only used though as the last resort to serve a query. Connectors try to offload the primary which might become overloaded if both read-only and read-write transactions are sent to it. If Using secondaries and spares to process read operations has a drawback though. Read operations may return stale information because the replication protocol is asynchronous which means that changes to the primary are not immediately propagated to slaves. If we want to read updated information, we need to guarantee that requests are sent to the primary (i.e. master) as follows:

self.__cnx = mysql.connector.connect(**fabric_params)
self.__cnx.set_property(group=group, mode=MODE_READWRITE)

Note the "mode" parameter in the "set_property" method. It specifies that the application wants to execute a read-write transaction. However, only the primary accepts writes. So a connection to the primary will be established thus allowing the application to retrieve updated information. We are considering ways to improve this by providing the possibility of returning updated information even when a secondary server is used. See some insights on this in the blog post MySQL Fabric: Musings on Release 1.4.3 from Mats.

To take advantage of all possible secondary servers in the system, the load should be distributed among them. To make this easier, servers in Fabric have a weight property which represents the relative power of the server. When balancing the load between servers, the connector will figure out which servers are eligible for accepting a transaction and then pick one of the servers in such a way that the distribution over time will be proportional to the weight of the server.

Summary and a sketch of an application

After installing and configuring MySQL Fabric (click here for further details on that), there aren't many things we need to do to exploit its ability to scale out reads:
  • If there are spares, decide whether they will accept reads or not. If a spare should accept reads, execute the following command:
      mysqlfabric server set_mode server-id read_only
  • If there are secondaries that should not accept reads, execute the following command:
      mysqlfabric server set_mode server-id offline
  • Configure the servers' weights according to their capacity to handle requests:
      mysqlfabric server set_weight server-id weight
The next step consisting in calling the appropriate functions and methods within the application:

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "username": "admin",
            "password" : "adminpass"
           },
    user="webuser", password="webpass"
)
conn.set_property(group="my_group", mode=fabric.MODE_READONLY)
cur = conn.cursor()
cur.execute(
    "SELECT first_name, last_name FROM employees "
    "WHERE emp_no = %s", (emp_no, )
)
conn.commit()

19 comments:

neha kumari said...

Great article, Thanks a lot for providing such a wonderful and informative to us. A2hosting Review

sindhu said...


Great articles, first of all Thanks for writing such lovely Post! Earlier I thought that posts are the only most important thing on any blog. But here at Shoutmeloud I found how important other elements are for your blog.Keep update more posts..

App store optimization services

Anonymous said...

Thanks for such a great article here. I was searching for something like this for quite a long time and at last, I’ve found it on your blog. It was definitely interesting for me to read about their market situation nowadays.iot certification chennai | iot training courses in chennai | iot training in chennai | iot training in chennai quora

CMCINS said...

Thanks for sharing informative Post. Its very useful to me.

List Of Schools In Coimbatore With Address

Cmc Marine said...

Happy for your innovative ideas.
marine engineering courses

gaurav kumar said...

this post are enlightening in Classified Submission Site List India . An obligation of thankfulness is all together for sharing this outline, Actually I found on various domains and after that continued with this site so I discovered this is hugely improved and related.
https://myseokhazana.com/

Repairtech Solutions said...

This page truly has all of the info I wanted about this subject and didn’t know who to ask. onsite mobile repair bangalore There's definately a lot to find out about this topic. I love all of the points you made. asus display repair bangalore You have made some good points there. I looked on the web to find out more about the issue and found most people will go along with your views on this web site. huawei display repair bangalore

Repairtech Solutions said...

Greetings! Very helpful advice in this particular article! It is the little changes that produce the greatest changes. Thanks a lot for sharing! online laptop repair center bangalore
Howdy! This blog post couldn’t be written any better! Looking at this article reminds me of my previous roommate! He constantly kept talking about this. I'll forward this information to him. Fairly certain he'll have a very good read. Thanks for sharing! dell repair center bangalore

Repairtech Solutions said...

I was able to find good advice from your blog posts. macbook repair center bangalore Very good post! We are linking to this great article on our website. Keep up the great writing. acer repair center bangalore

Dietary Cafe said...

Peau Jeune


Site officiel (Magasinez maintenant) :- https://www.dietarycafe.com/peau-jeune-avis-fr/



Visitez maintenant plus d'informations:-

https://www.completefoods.co/diy/recipes/peau-jeune-2
https://sites.google.com/site/dietarycafe/peau-jeune
https://medium.com/@dietarycafe/peau-jeune-avis-d89c0665a2ee
https://dietarycafe.blogspot.com/2019/11/peau-jeune.html
http://thedietarycafe.over-blog.com/peau-jeune

Ca ne marchera pas. L'information que ces entités primordiale. Dans quelle mesure les affiliés frappent-ils sur négocier des trucs de soins de la peau? Je peux en discuter avec un peu de satisfaction. Cela peut être évité en inversant l'ordre. C'est la meilleure façon d'orchestrer vos soins de la peau.

Unknown said...

Hi! No matter how intelligent you are, there would be a time when you might have to visit a writing service. If you need help with writing a poem, this website cah help you!

Unknown said...

Nice blog on the web. Today, Emblix solutions as one of the best and top most service-oriented Digital Marketing Agency in Hyderabad and India, Which provides high-quality result-oriented Digital Services ranging from SEO to Web Design, Social Media Marketing and more, to a broad spectrum of clients from diverse industry segments

KL Ads India Pvt Ltd said...

We support all forms of media promotion Outdoor Advertising, Indoor Advertising, Instore Branding, Print&Media planning, Digital Marketing, Web Development. And our partner base is a unique combination of creativity and presentation. Improvisation and change management is an eternal part of branding with supportive innovations and we have a professional in-house team to enhance and deliver quality professional service.


Web Development

Candyce Herr said...


You will need to search for Keto Advanced Fat Burner and find a number of that you never use. You may find a Keto Advanced Fat Burner there, although probably not. I have been scouring the Internet to see what I can discover about other Keto Advanced Fat Burner because you will realize that the feeling just doesn't go away. Goody goody gumdrops!



Keto Advanced Fat Burner

Candyce Herr said...


I sometimes forget that Keto Advanced Fat Burner is a great viewpoint. I want you to take the Keto Advanced Fat Burner challenge. I might want to foritfy my assumptions with complete facts and figures. It is an immovable truth. It looked like a very good Keto Advanced Fat Burner.

Keto Advanced
Keto Advanced Weight Loss

Business Services said...

Your Content is amazing and I am glad to read them. If you are familiar with SEO, you already know how important Links Building. If a link relation is given as dofollow, then you will get a good boost over SERP and Boost your Ranking Buy Services From it 24/7
unique 35 article submission high 40 da backlinks
top rated classified website Ads
Wordpress Shopify Wix On Page SEO services

MaksimMB said...

Hi all. If you need a content manager for your business. Check out this website, there are outsourced marketing companies there and that you can book outsourced marketing support. They can help you improve your business, reduce costs, increase revenues and improve business efficiency and quality of work. Use it and enjoy it!

Repute Agency said...

Thanks for sharing this post.
Best Digital Marketing Company in Coimbatore
Best Website Development Company in Coimbatore
Best Digital Marketing Services Provider in Coimbatore

best digital marketing said...

Never seen a blog with so much helpful information in a great format. if you need any
contact them to gain knowledge for digital marketing with 100percent placement