Planet MySQL

MySQL High Availability: Stale Reads and How to Fix Them

Continuing on the series of blog posts about MySQL High Availability, today we will talk about stale reads and how to overcome this issue.

The Problem

Stale reads is a read operation that fetches an incorrect value from a source that has not synchronized an update operation to the value (source Wiktionary).

A practical scenario is when your application applies INSERT or UPDATE data to your master/writer node, and has to read it immediately after. If this particular read is served from another server in the replication/cluster topology, the data is either not there yet (in case of an INSERT) or it still provides the old value (in case of an UPDATE).

If your application or part of your application is sensitive to stale reads, then this is something to consider when implementing HA/load balancing.

How NOT to fix stale reads

While working with customers, we have seen a few incorrect attempts to fix the issue:


The most common incorrect approach that we see in Percona support is when customers add a sleep between the write and the read. This may work in some cases, but it’s not 100% reliable for all scenarios, and it can add latency when there is no need.

Let’s review an example where by the time you query your slave, the data is already applied and you have configured your transaction to start with a SELECT SLEEP(1). In this case, you just added 1000ms latency when there was no need for it.

Another example could be when the slave is lagging behind for more than whatever you configured as the parameter on the sleep command. In this case, you will have to create a login to keep trying the sleep until the slave has received the data: potentially it could take several seconds.

Reference: SELECT SLEEP.

Semisync replication

By default, MySQL replication is asynchronous, and this is exactly what causes the stale read. However, MySQL distributes a plugin that can make the replication semi-synchronous. We have seen customers enabling it hoping the stale reads problem will go away. In fact, that is not the case. The semi-synchronous plugin only ensures that at least one slave has received it (IO Thread has streamed the binlog event to relay log), but the action of applying the event is done asynchronously. In other words, stale reads are still a problem with semi-sync replication.

Reference: Semisync replication.

How to PROPERLY fix stale reads

There are several ways to fix/overcome this situation, and each one has its pros and cons:


Consists of executing a SHOW MASTER STATUS right after your write, getting the binlog file and position, connecting on a slave, and executing the SELECT MASTER_POS_WAIT function, passing the binlog file and position as parameters. The execution will block until the slave has applied the position via the function. You can optionally pass a timeout to exit the function in case of exceeding this timeout.


  • Works on all MySQL versions
  • No prerequisites


  • Requires an application code rewrite.
  • It’s a blocking operation, and can add significant latency to queries in cases where a slave/node is too far behind.



Requires GTID: this is similar to the previous approach, but in this case, we need to track the executed GTID from the master (also available on SHOW MASTER STATUS).


  • Works on all MySQL versions.


  • Requires an application code rewrite.
  • It’s a blocking operation, can add significant latency to queries in cases where a slave/node is too far behind.
  • As it requires GTID, it only works on versions from 5.6 onwards.


3) Querying slave_relay_log_info

Consists of enabling relay_log_info_repository=TABLE and sync_relay_log_info=1 on the slave, and using a similar approach to option 1. After the write, execute  SHOW MASTER STATUS, connect to the slave, and query mysql.slave_relay_log_info , passing the binlog name and position to verify if the slave is already applying a position after the one you got from SHOW MASTER STATUS.


  • This is not a blocking operation.
  • In cases where the slave is missing the position you require, you can try to connect to another slave and repeat the process. There is even an option to fail over back to the master if none of the slaves have the said position.


  • Requires an application code rewrite.
  • In cases of checking multiple slaves, this can add significant latency.

Reference: slave_relay_log_info.

4) wsrep-sync-wait

Requires Galera/Percona XtraDB Cluster: Consists of setting a global/session variable to enforce consistency. This will block execution of subsequent queries until the node has applied all write-sets from it’s applier queue. It can be configured to trigger on multiple commands, such as SELECT, INSERT, and so on.


  • Easy to implement. Built-in as a SESSION variable.


  • Requires an application code rewrite in the event that you want to implement the solution on per session basis.
  • It’s a blocking operation, and can add significant latency to queries if a slave/node is too far behind.

Reference: wsrep-sync-wait

5) ProxySQL 2.0 GTID consistent reads

Requires MySQL 5.7 and GTID: MySQL 5.7 returns the GTID generated by a commit as part of the OK package. ProxySQL with the help of binlog readers installed on MySQL servers can keep track of which GTID the slave has already applied. With this information + the GTID received from the OK package at the moment of the write, ProxySQL will decide if it will route a subsequent read to one of the slaves/read nodes or if the master/write node will serve the read.


  • Transparent to the application – no code changes are required.
  • Adds minimal latency.


  • This still a new feature of ProxySQL 2.0, which is not yet GA.

Referece: GTID consistent reads.


Undesirable issues can arise from adding HA and distributing the load across multiple servers. Stale reads can cause an impact on applications sensitive to them. We have demonstrated various approaches you can use to overcome them.

Photo by Tim Evans on Unsplash

MySQL InnoDB Cluster with 2 Data Centers for Disaster Recovery: howto – part 2

In the first part of this howto, I illustrated how to setup two MySQL InnoDB Cluster linked by an asynchronous replication.

In that solution, I didn’t use any replication filters to ignore the replication of the InnoDB Cluster’s metadata (mysql_innodb_cluster_metadata), but I used the same metadata tables with two different clusters in it.

The benefit is that this allows to backup everything from any node in any of the data center, it works also in MySQL 5.7, and there is not risk to mess up with the replication filters.

In this blog I will show how to use replication filters to link two different clusters. This doesn’t work on MySQL 5.7 because you cannot have filters for a specific channel and if you globally filter the metadata, changes like topology changes, won’t be replicated inside the group and you will have issue with MySQL Shell and MySQL Router. So this solution works only for MySQL 8.0 (since 8.0.1).

Let’s consider we have again the exact same setup: 2 DCs and 6 nodes (see previous post).

On DC1 we have a cluster like this one:

JS> cluster.status(); { "clusterName": "clusterDC1", "defaultReplicaSet": { "name": "default", "primary": "mysql1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "mysql1:3306": { "address": "mysql1:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql2:3306": { "address": "mysql2:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql3:3306": { "address": "mysql3:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://clusteradmin@mysql1:3306" }

Now we will take a backup of this running cluster (in the first post, we were using the backup of a single instance, that I called the “production server”).

We choose one of the 3 members and we run the following command (this is using MySQL Enterprise Backup):

/opt/meb/bin/mysqlbackup \ --host \ --backup-dir /vagrant/backup/ \ backup-and-apply-log

And we restore it on the 3 machines that will be part of the new InnoDB Cluster in DC2:

# systemctl stop mysqld # rm -rf /var/lib/mysql/* # /opt/meb/bin/mysqlbackup --backup-dir=/vagrant/backup/ copy-back # chown -R mysql. /var/lib/mysql # systemctl start mysqld

We can also already configure the instances using MySQL Shell:

JS> dba.configureInstance('clusteradmin@mysql4') JS> dba.configureInstance('clusteradmin@mysql5') JS> dba.configureInstance('clusteradmin@mysql6')

When done, as the backup was performed on a machine that was already part of a Group and as I use different name for relay logs on each nodes (default behavior), I need to perform on all 3 new servers the following SQL statement:

SQL> RESET SLAVE ALL FOR CHANNEL "group_replication_applier";

It’s time to create the second cluster:

JS> \c clusteradmin@mysql4 JS> cluster2=dba.createCluster('clusterDC2') JS> cluster2.addInstance('clusteradmin@mysql5') JS> cluster2.addInstance('clusteradmin@mysql6')

And we have now our second cluster:

JS> cluster2.status() { "clusterName": "clusterDC2", "defaultReplicaSet": { "name": "default", "primary": "mysql4:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "mysql4:3306": { "address": "mysql4:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql5:3306": { "address": "mysql5:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql6:3306": { "address": "mysql6:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://clusteradmin@mysql4:3306" }

As recommended, I will create a user to use with replication. On the clusterDC1’s Primary-Master, I enter the following statements in SQL:

SQL> create user 'repl'@'%' identified by 'replication' REQUIRE SSL SQL> grant replication slave on *.* to 'repl'@'%';

And finally setup asynchronous replication between the Primary-Master of DC2 and another member of DC1:


Of course we can see that it works as expected:

SQL> show slave status FOR CHANNEL 'asyncDC1'\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 2092043 Relay_Log_File: mysql5-relay-bin-asyncdc1.000002 Relay_Log_Pos: 57195 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql_innodb_cluster_metadata Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2092043 Relay_Log_Space: 57405 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 14177781 Master_UUID: 21b458bd-f298-11e8-9bf0-08002718d305 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 463c9331-f29e-11e8-9f64-08002718d305:44-77 Executed_Gtid_Set: 21b458bd-f298-11e8-9bf0-08002718d305:1-12, 4358f6f3-f2a0-11e8-8243-08002718d305:1-14, 463c9331-f29e-11e8-9f64-08002718d305:1-77, 4c63779c-f29f-11e8-918e-08002718d305:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: asyncdc1 Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0

We can also verify all this in performance_schema:


And now we can do the same on the Primary-Master of DC1 to point on the Primary-Master of DC2 (or use MySQL Router as explained in part 1). Don’t forget that this is a Disaster Recovery Solution and that you should not write on both DCs at the same time !

The Symfony Demo Application and MySQL 8

The Symfony Frame work is very popular with PHP developers and it has a very nice Demo Application to help novices learn the intricacies involved. But the demo does not use MySQL. So this blog entry is about re configuring the demo so it works with MySQL 8. And I am using Ubuntu 18.04 LTS to you may have to adjust the following commands to work with your operating system.

This is not difficult but there are some steps that are not exactly self evident that this blog will show you how to get the demo working.  

The first thing to do is to make sure you have PHP 7.2 or better installed including the php7.2-intl (sudo apt-get install php7.2-intl) package as well as the PDO connector. I will admit I have been using PHP since it appeared and this is the first time I have had to install this package.

And you will want Composer to do the behind the scenes lifting for you and Doctrine to map the relations in the PHP code to the database.  Please see my previous blog on getting Doctrine to work with MySQL 8 (Big hint for the TL;DR crowd, set your .env to DATABASE_URL=mysql://account:password@localhost:3306/databasename ).

You will want to create an account on the MySQL server for use with this demo and then make sure it will have the proper rights to use the new schema.

CREATE USER 'demouser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'S3cr3t#'; 


GRANT ALL on 'databasename'.* to 'demouser'@'localhost'; 

The Demo Now we can start work on the demo itself. 

The first thing to do in a terminal window is type composer create-project symfony/symfony-demo.  Composer will get the demo code ready for you.  Now cd cymfony-demo.   

Change the .env file (you may have to copy the .env-dist to .env and edit it) as noted above DATABASE_URL=mysql://demouser:S3c3t#@localhost:3306/databasename 

Now it is time to use Doctrine to build create the database, the schemas, and load the data.

$ php bin/console doctrine:database:create
$ php bin/console doctrine:schema:create
$ php bin/console doctrine:fixtures:load

Finally enter php bin/console server:run to start the demo. You will get a notice about the URL to use to get to the demo via a web browser. Use that URL in your web browser to get to the actual demo and below you can see that URL is 

Running the Symfony Demo Application. Note that the 
application is listening on http: The Symfony Demo to help you explore this popular PHP Freamwork

10 Node Frameworks to Use in 2019


More developers have switched to using JavaScript to build more applications, especially for the web. This has brought about an exponential growth in the usage of frameworks built specifically for the JavaScript community to facilitate quick prototyping and building of awesome projects.

When Node.js was introduced to the tech community in 2009 as a tool for building scalable server-side web applications, it came with a lot of benefits which includes but not limited to the usage of event-driven non-blocking input/output model, single-threaded asynchronous programming amongst others.

The fact that, as a developer, you can easily use the same language both for the client-side and server-side scripting easily increased the quick adoption and rapid the usage of Node.

Over the years, a lot of experienced JavaScript developers have built quite a number of impressive frameworks to easily get started with Node.js when developing web applications.

As we look into 2019, I will list some of the most popular Node.js frameworks that you should consider using for building web applications irrespective of the size.

What is a Node framework?

A Node.js framework is just some abstract design, built out of Node.js, that embodies the control flow of the given framework’s design. So it is almost like the skeleton of a program whereby the customised codes you write kind of makes up as the meat that completes the program.

So for every Node.js function, there would be some generic implementation unique to the framework which would then require the user to follow the lead of the framework by adding more codes to define its use case.

Benefits of Node frameworks

Node.js frameworks are mostly used because of their productivity, scalability and speed, making them one of the first choice for building enterprise applications for companies.

Node.js allows you to write the same language for both your front-end and backend, saving you the stress of learning a new language for some simple implementation, and also helping you maintain the same coding pattern all through.

By using a framework, you can work with a set of tools, guidelines, and recommended practices that help you save time. It also can help solidify the code standards across a team of developers.

Selecting a Node Framework

Selecting a framework can be a bit tricky and subjective to its use case. This is because we choose based on a particular feature we like. Ranging from the weight of the framework on the application, speed, simplicity, learning curve, flexibility and configuration, use case or maybe even popularity in some cases, GitHub stars.

Next, lets take a deep dive into the objective of this post and go through the list of Node.js frameworks that will help boost your productivity when building JavaScript applications, especially on the server-side.

Stars aren't everything so we'll be organizing by what we've seen to be popular on

1. AdonisJs [GitHub Stars: 5,053]

AdonisJsis a Node.js framework. From the official documentation, "AdonisJs is a Node.js MVC framework that runs on all major operating systems. It offers a stable ecosystem to write a server-side web application so that you can focus on business needs over finalising which package to choose or not."

Adonis is billed as the Laravel of Node. Simplicity and a focus on getting things done.

"We're big fans of Laravel's approach (Scotch is built on Laravel) so when we saw those same principles come to the Node side, we were very excited." - Chris Sevilleja

Why AdonisJS?

AdonisJs has a support for an ORM is made with SQL-databases in mind (PostgreSQL). It creates efficient SQL-queries and is based on active record idea. Its query builder is easy to learn and allows us to build simple queries quickly.

AdonisJs has good support for No-SQL database like MongoDB too. It's MVC structure is quite similar to Laravel, so if you've been using Laravel for web development, AdonisJs will be a walk in the park.

To get started easily check out this comprehensive article by Chimezie here on

2. Express.js [GitHub Stars: 41,036]

Express.js is a fast, non-opinionated, minimalist web framework for Node.js. It is simply a technology built on Node.js which behaves like a middleware to help manage our servers and routes. Looking at the asynchronous nature of Node.js and the fact that Express.js was built on node, the ability to build a light-weight application that can process more than a single request seamlessly actually depends on the serving capability of technologies like express.

It’s robust API allows users to configure routes to send/receive requests between the front-end and the database (acting as a HTTP server framework). A good advantage with express is how it supports a lot of other packages and other template engines such as Pug, Mustache, EJS and a lot more.

Some of the numerous advantages of Express.js includes:

  • Almost the standard for Node.js web middleware

  • Fully customisable

  • Low learning curve

  • Majorly focused on browsers, making templating and rendering an almost out of the box feature.

Express.js has shown, over time, that it’s popularity is worth the hype with its easy to use methods and functions. It is probably the most popular Node.js framework available for the JavaScript community on GitHub with over 41,000 stars [Github stars: 41,036].

Looking at this framework and all it’s exciting abilities, I do not see it going away anytime soon.

3. Meteor.js [GitHub Stars: 40,490]

The Meteor docs defines meteor as a full-stack JavaScript platform for developing modern web and mobile applications. It’s major advantage is it’s realtime update. As changes are made to the web app, it automatically updates the template with the latest changes.

The Node.js framework makes development quite very simplified by providing a platform for the entire tier of the application to be in the same language; JavaScript. Making it function just as efficient in both the server and client side.

Meteor stands the capability of serving large projects like reaction commerce( known to be one of the largest and most popular e-commerce open source projects).

The most fascinating aspect of the Meteor framework is the very rich and organised documentation/large community it has, helping users learn fast by reaching out and getting their hands dirty with projects, very fast.

With the fact that meteor is leveraging on the Facebook GraphQL datastack to come up with meteor Apollo, as far back as 2016, only indicates that they have good plans and a visionary perception of what the future holds for data, how it is managed and how it flows. If there is any list of Node.js frameworks to watch out for, I would probably be arrested if I did not add Meteor to that list.

4. Nest.js [GitHub Stars: 10,128]

NestJs is a framework built with Node.js, It is used for building efficient, scalable Node.js server-side applications. Nest uses progressive JavaScript and is written with TypeScript. Being built with TypeScript means that Nest comes with strong typing and combines elements of OOP(Object Oriented Programming), FP(Functional Programming) and FRP(Functional Reactive Programming).

Nest also makes use of Express, It provides an out of the box application architecture which allows for the effortless creation of highly testable, scalable, loosely coupled, and easily maintainable applications.

Nest CLI can be used to generate nest.js applications with a lot of features out of the box. According to the information on the website, one can contact the nest community of developers behind the nest framework to find out more about expertise consulting, on-site enterprise support, trainings, and private sessions. Isn’t that cool? Well I guess it is, and I also think this also should make it into the list of Node.js frameworks to look out for in 2019.

5. Sails.js [GitHub Stars: 19,887]

According to the official site, Sails is another Node.js framework used to build custom enterprise-grade Node.js apps. It boasts of being the most popular MVC Node.js framework with the support for modern apps requirements. The APIs are data-driven, with a scalable service oriented architecture.

Let us take a closer look at what they mean here. Sails bundles an ORM, waterlines, that makes compatibility possible with almost all databases, going as far as providing a huge number of community projects. Some of its officially supported adapters include MYSQL, Mongo, PostgreSQL, Redis, and even Local Disk.

Looking at the backend, Just by running an installation command, `sails generate api bookstore` for instance, sails blows your mind by providing you some basic blueprints, without you writing any codes at all.

This command provides you endpoints to CRUD bookstore. You think that is all right, check this out: Sails is also compatible with almost all frontend technologies ranging from React, Angular, Backbone, iOS/objective C, Android/java, windows phone and probably even some technologies yet to be created. For this one, 2019 it is! summarised features include:

  • Many automated generators.

  • Requires no additional routing

  • Great frontend compatibility with other frontend technologies.

  • Transparent support for Websockets.

  • Enables faster build of REST API.

  • Compatible with almost all database, thanks to its waterline ORM.

6. Koa.js [GitHub Stars: 23,902]

Referred to as the next generation web framework for Node.js(according to the website), Koa was created by the same team that created Express.js, making it seem like it would pick up from where express left off. Koa is unique in the fact that it uses some really cool ECMAScript(ES6) methods that have not even landed in some browsers yet, it allows you to work without callbacks, while also providing you with an immense increase in error handling. it requires a Node.js version of at least 0.11 or higher.

According to the website, Koa does not bundle any middleware within core, meaning the middlewares are more cascaded/streamlined, and every line of code is quite elegant and granular, thereby allowing you to structure the parts however you want(component-based middlewares). This makes the framework to have more control over configurations and handling.

Koa became futureproof owing to the fact that it could actually ditch the holy grail of asynchronous functionality: callbacks.

Some key features include:

  • Ditched callbacks hell

  • Component-based building blocks

  • Cascading middlewares

  • Quite modular

  • Efficient error handling

This is definitely a framework for the future and I am almost beginning to see that if an article for frameworks to lookout for in the year 2020 comes out, it would still probably make the list.

7. LoopBack.js [GitHub Stars: 11,985]

LoopBack is another Node.js framework with an easy-to-use CLI and a dynamic API explorer. It allows you to create your models based on your schema or dynamic models in the absence of a schema. It is compatible with a good number of REST services and a wide variety of databases including MySQL, Oracle, MongoDB, Postgres and so on.

It has the ability to allow a user build a server API that maps to another server, almost like creating an API that is a proxy for another API. It’s support for native mobile and browser SDKs for clients like Android/Java, iOS, Browser javaScript(Angular).

Key features:

Most of these details were collected from their Website/documentation which I found very exciting to go through and even try to get a basic setup up, Indicating that they have a well structured documentation and a community distributed across different media( StrongLoop blog, LoopBack Google Group, LoopBack Gitter channel ). For instance, the Loopback blog provides lots of tutorials and use cases on how to leverage the use of the technology in different ways.

Amongst some of its powerful users are Go Daddy, Flight Office, Bank of America(Meryll Linch), Symantec, Intellum, ShoppinPal and so on.

8. Hapi.js [GitHub Stars: 10,371]

Just like ExpressJs, the common hapi.js(supported by Walmart Labs) is a Node.js framework that helps serve data by intermediating between the server side and client. It is quite a good substitute for Express(they both have their unique features).

Hapi is a configuration-driven pattern, traditionally modeled to control web server operations. A unique feature it has is the ability to create a server on a specific IP, with features like the ‘onPreHandler’, we can do something with a request before it is completed by intercepting it and doing some pre-processing on the request.

Considering it’s ‘handler’ function where we can call a route and still pass some configurations while making the requests, just to get the function to do something specified in the configuration. This handler, from what we see, acts like a pseudo-middleware.

Let us look at some key features that make hapiJs promising:

  • There is a deeper control over request handling.

  • Detailed API reference and a good support for document generation

  • Has more functions for building web servers

  • Configuration-based approach to some sub-middlewares(pseudo-middlewares)

  • Provides the availability of caching, Authentication, and input validation.

  • Has a plugin-based architecture for scaling.

  • Provides you with really good enterprise plugins like the joi, yar, catbox, boom, tv, travelogue, and so on.

HapiJs might not be as popular [github stars: 10,371] as Express but it has some good backing up and it seems to be gaining some grounds too. It does not seem like it is slowing down its mark and relevance anytime soon.

9. Derby.js [4,350]

According to the Derby.js site, it is a full stack Node.js framework for writing modern web applications. Derby has been around a little while, quite long enough to have proven itself to hop into 2019 and rock some chords. Let’s see what we have here.

DerbyJs provides you with seamless data synchronisation between your server and client with an automatic conflict resolution powered by ShareDB's operational transformation of JSON and text. It permits you the opportunity to add customised codes to build highly efficient web applications.

10. Total.js [Github stars: 3,853]

Total.js boast of being a very fast development Node.js framework, that requires little maintenance, with a good performance and a seamless scaling transition. It shows some promise by giving some insight on their website, where they ask for visitors willing to contribute to the growth of the framework. So far the Total.js team has spent some time trying to get more premium sponsors to join them. This is another indication that they have plans to expand and should be checked out for more growth in the nearest future to come.

Total.js has some really beautiful versions like the Total.js Eshop, which contains a user interface optimized for mobile devices, and downloadable by all premium members. The Eshop is one of the best Node.js e-commerce system. This is because of its many versions of unique content management system(CMS).


If there is anything we can pick from this article, I can bet you must have noticed before now that picking a framework is based on what you actually want to achieve with it.

The Node.js frameworks above have purely shown us that whatever framework we are going for, there is an open and welcoming community out there solving issues and ready to aid you with learning the basics of that particular framework, which a is vital factor to look out for amongst lots more other factors like GitHub contributions, stars, issues and so on. Just for the fun of it, you can find a lot more of Node.js frameworks here.

Please note that all the Node.js framework highlighted in this post were selected based on popularity in the JavaScript community, usage and personal opinion.

Do you know of any other awesome Node.js framework that you feel its worthy of being added to the list? please feel free to mention it in the comment section below.

I do hope you find this post very helpful. Happy coding.

MySQL Partition Manager (Yahoo!) in a nutshell

Partitioning is a way of splitting the actual data down into separate .ibd files (data compartments) in the file system based on the defined ranges using the partitioning key. It can help us with maintaining the enormous amount of data in different partitions without much hassle.

In this blog post, we are going to see how to manage table partitioning using yahoo partition manager.

Needs for partitioning:

  • Enhanced data retrieval ( reduced IO ) with smaller B+Tree.
  • Easy Archival or Purge by dropping or truncate  of partition
  • Lesser fragmentation, hence avoiding frequent table optimization.

Partitions management activity like adding/Dropping partition is made easy and automated by using yahoo partition manager.


Implementation of this partition manager tool is quite simple. We can start using by importing this sql file , We will walk through the Internal working of partition manager in this blog.

The partition_manager_settings table

CREATE TABLE `partition_manager_settings` ( `table` varchar(64) NOT NULL COMMENT 'table name', `column` varchar(64) NOT NULL COMMENT 'numeric column with time info', `granularity` int(10) unsigned NOT NULL COMMENT 'granularity of column, i.e. 1=seconds, 60=minutes...', `increment` int(10) unsigned NOT NULL COMMENT 'seconds per individual partition', `retain` int(10) unsigned NULL COMMENT 'seconds of data to retain, null for infinite', `buffer` int(10) unsigned NULL COMMENT 'seconds of empty future partitions to create', PRIMARY KEY (`table`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=Dynamic;

Table breakdown:

Column Name Definition Table Table name which we need to make partitions column name to make partitions granularity factoring granularity in seconds (1 denotes seconds, 60 denotes minutes, 3600 denotes hours) increment Number of seconds per individual partition (86400 denotes 1 day) retain Seconds worth of data to retain or null for infinite buffer Seconds worth of empty feature partitions to maintain

This table (partition_manager_settings) will be created by executing the partition manager script.

Along with partition_manager_settings table by default, it will create an event for automatic execution of this procedure in the specified interval to have a check for dropping and adding partitions based on the conditions.

mysql> show events\G *************************** 1. row *************************** Db: mydbops Name: run_partition_manager Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 86400 Interval field: SECOND Starts: 2000-01-01 00:00:00 Ends: NULL Status: ENABLED Originator: 2345 character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci

Event structure:

mysql> show create event run_partition_manager\G *************************** 1. row *************************** Event: run_partition_manager sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION time_zone: SYSTEM Create Event: CREATE DEFINER=`root`@`localhost` EVENT `run_partition_manager` ON SCHEDULE EVERY 86400 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN IF @@global.read_only=0 THEN CALL partition_manager(); END IF; END character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci

By default, this event will run for every day (86400 seconds).

Now let’s see our demo implementation.

A sample test table structure below,

mysql> show create table data\G *************************** 1. row *************************** Table: data Create Table: CREATE TABLE `data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`created`), KEY `index_created` (`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

A mandatory thing for creating partitions for a table is to have the column(partition key) based on which are adding has to be a part of the primary key/Unique key

Here we are making `created` as our partition key, which is part of composite primary key “PRIMARY KEY (`id`,`created`)

You need to insert the table and column which you need to do partitions.

mysql> select * from partition_manager_settings\G *************************** 1. row *************************** table: data column: unix_timestamp(created) granularity: 1 increment: 3600 retain: 7200 buffer: 36000

Here I have added the table to create an hourly based partition and drop the partitions which are older than 2 hours. Along with that, it will create 10 empty partitions as a buffer, each time when the partition manager event is being called.

Below is table structure after execution of the partition manager procedure.

mysql> show create table data\G *************************** 1. row *************************** Table: data Create Table: CREATE TABLE `data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`created`), KEY `index_created` (`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (unix_timestamp(created)) (PARTITION p_START VALUES LESS THAN (0) ENGINE = InnoDB, PARTITION p_1534402800 VALUES LESS THAN (1534402800) ENGINE = InnoDB, PARTITION p_1534406400 VALUES LESS THAN (1534406400) ENGINE = InnoDB, PARTITION p_1534410000 VALUES LESS THAN (1534410000) ENGINE = InnoDB, PARTITION p_1534413600 VALUES LESS THAN (1534413600) ENGINE = InnoDB, PARTITION p_1534417200 VALUES LESS THAN (1534417200) ENGINE = InnoDB, PARTITION p_1534420800 VALUES LESS THAN (1534420800) ENGINE = InnoDB, PARTITION p_1534424400 VALUES LESS THAN (1534424400) ENGINE = InnoDB, PARTITION p_1534428000 VALUES LESS THAN (1534428000) ENGINE = InnoDB, PARTITION p_1534431600 VALUES LESS THAN (1534431600) ENGINE = InnoDB, PARTITION p_1534435200 VALUES LESS THAN (1534435200) ENGINE = InnoDB, PARTITION p_1534438800 VALUES LESS THAN (1534438800) ENGINE = InnoDB, PARTITION p_1534442400 VALUES LESS THAN (1534442400) ENGINE = InnoDB, PARTITION p_1534446000 VALUES LESS THAN (1534446000) ENGINE = InnoDB, PARTITION p_END VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

In the file system level, each partition has its own tablespace.

mysql> \! ls -lrth /usr/local/var/mysql/mydbops/ total 3608 -rw-r----- 1 dhanasekar admin 61B Jul 18 19:24 db.opt -rw-r----- 1 dhanasekar admin 8.7K Aug 16 13:52 partition_manager_settings.frm -rw-r----- 1 mydbops admin 96K Aug 16 13:54 partition_manager_settings.ibd -rw-r----- 1 mydbops admin 8.5K Aug 16 13:54 data.frm -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_START.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534402800.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534406400.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534417200.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534420800.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534424400.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534410000.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534413600.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534428000.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534431600.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534435200.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534442400.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534446000.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_END.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534438800.ibd

So Now its easy to maintain the partitions for n number of tables using the partition manager.

The column retains will have the value of how much data which needs to be active in the table. In this case, the retain has the value as 7200 seconds which is 2 hours So it will drop the partitions after the certain period of time.

The next iteration of the procedure call is based on the value of a minimum of increment from the partition_manager_settings table. There is one another partition to correct this setting at the end of the partition_manager procedure.

Last few lines of partition manager procedure:

.... .... close cur_table_list; # confirm schedule for next run call schedule_partition_manager(); /* 5.6.29+/5.7.11+ only - mysql bug 77288 */ END;; DELIMITER ;

The procedure Schedule_partition_manager controls the execution of the “run_partition_manager“, Based on the min increment value of each table which is being managed.

mysql> show create procedure schedule_partition_manager\G *************************** 1. row *************************** Procedure: schedule_partition_manager sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `schedule_partition_manager`() begin declare min_increment int unsigned; set min_increment=null; select min(s.increment) from partition_manager_settings s into min_increment; if min_increment is not null then ALTER DEFINER='root'@'localhost' EVENT run_partition_manager ON SCHEDULE EVERY min_increment SECOND STARTS '2000-01-01 00:00:00' ENABLE; end if; end character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci


The partition_manager_settings table is where we need to add/remove tables to make partitions. This will be executed in an order of insertion in this table.

Key Takeaways/Limitations:

  • This partition manager will only work for range partitions.
  • This directly won’t work for the partitions for timezone dependent.
  • We have to make use of functions to get the required partitions.
  • Ensure that events are always on

This procedure will help you in managing the partition in an efficient way.  I hope this blog post gives you a better idea about yahoo partition manager.

What Happens If You Set innodb_open_files Higher Than open_files_limit?

The settings of MySQL configuration variables have a fundamental impact on the performance of your database system. Sometimes it can be a little tricky to predict how changing one variable can affect others, and especially when dealing with cases like the one I’ll describe in this post, where the outcome is not very intuitive. So here, we’ll look at what happens when you set innodb_open_files higher than the open_files_limit.

We can set the maximum number of open files in our MySQL configuration file using:


If this isn’t set, then the default – which is 5,000 in MySQL 5.7 – should be used.

See Sveta’s excellent blog post for an explanation of how to change the open file limit; if this value is set it will take the SystemD LIMIT_NOFILES unless it’s set to infinity (and on CentOS 7 it will then use 65536,  though much higher values are possible if specified manually):

[root@centos7-pxc57-3 ~]# grep open_files_limit /etc/my.cnf open_files_limit=10000 [root@centos7-pxc57-3 ~]# grep LimitNOFILE /lib/systemd/system/mysqld.service.d/limit_nofile.conf LimitNOFILE=infinity [root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit” +--------------------+ | @@open_files_limit | +--------------------+ | 65536 | +--------------------+ [root@centos7-pxc57-3 ~]# perl -pi -e ’s/LimitNOFILE=infinity/LimitNOFILE=20000/‘ /lib/systemd/system/mysqld.service.d/limit_nofile.conf && systemctl daemon-reload && systemctl restart mysqld [root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit” +--------------------+ | @@open_files_limit | +--------------------+ | 20000 | +--------------------+ [root@centos7-pxc57-3 ~]# perl -pi -e ’s/LimitNOFILE=20000/LimitNOFILE=5000/‘ /lib/systemd/system/mysqld.service.d/limit_nofile.conf && systemctl daemon-reload && systemctl restart mysqld [root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit” +--------------------+ | @@open_files_limit | +--------------------+ | 5000 | +--------------------+

As you can see above, MySQL cannot set the value of open_files_limit higher than the system is configured to allow, and open_files_limit will default back to the maximum if it’s set too high.

That seems pretty straightforward, but what isn’t quite as obvious is how that affects innodb_open_files. The innodb_open_files value configures how many .ibd files MySQL can keep open at any one time.

As this obviously requires files to be open, it should be no higher than the open_files_limit (and should be lower). If we try to set it higher as per this example, MySQL will print a warning in the log file:

[root@centos7-pxc57-3 ~]# grep innodb_open_files /var/log/mysqld.log 2018-09-21T08:31:06.002120Z 0 [Warning] InnoDB: innodb_open_files should not be greater than the open_files_limit.

What the warning doesn’t state is that the value is being lowered. Not to the maximum value allowed though:

[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@innodb_open_files” +---------------------+ | @@innodb_open_files | +---------------------+ | 2000 | +---------------------+

2000? Why 2000?

It’s because if we set innodb_open_files too high, it reverts back to the default value, which per the documentation is:

300 if innodb_file_per_table is not enabled, and the higher of 300 and table_open_cache otherwise. Before 5.6.6, the default value is 300.

And table_open_cache? Well that defaults to 400 for versions of MySQL up to 5.6.7, and 2000 for 5.6.8 onwards.

Note that table_open_cache is another setting completely. innodb_open_files controls the number of InnoDB files (.ibd) the server can keep open at once; whilst table_open_cache controls the number of table definition (.frm) files the server can have open at once.


Photo by Logan Kirschner from Pexels