Proposed functionality

For those who want it, allows the functions with read only queries to be sent to a read only MySQL instance.

A few example functions are:

• yourls_get_db_stats
• yourls_get_link_stats
• yourls_get_stats

Use case

Why are we doing this?

We are doing this to lower the traffic load on the write/master MySQL installation. By sending the read only queries to another MySQL installation it lowers the overall load on the write/master allowing it to focus on doing only the writes.

What use cases does it support?

A use case for this would be when the DB has millions of URLs added to it, all of the unique checks and other SELECT statements run when creating a new URL can over load the system if there are lots of requests for new links coming in. By shunting the read queries to another MySQL instance we can alleviate the stress put on the write/master.

Another use case would be to fully utilize AWS Aurora MySQL installations, which have a master and read only instance in their cluster by default.

A final use case would be to continue serving traffic for existing links when the master/write instance is overloaded with writes, or unavailable for any other reason. This would only be applicable of clicks tracking is disabled, or being handled differently.

What is the expected outcome?

The expected outcome is that YOURLS can handle more reads and writes on big and/or busy installations.

Potential solution

What I propose is to add some new sections to the config.php file for setting up a read only connection. Then in the class-mysql.php add another variable/property called $rdb (or whatever else you want to call it) that establishes a connection with the read only settings. Finally, update all of the functions in functions.php that have SELECT only queries to use $rdb instead of $ydb.

An alternative I was considering was to make my own plugin for this. However, I came to realize that many of the read only functions do not have the shunt option to allow plugins to override them (see the Proposed functionality section for a few examples). I debated on adding the shunt option to the ones that are missing, then realized if I was going to do that I should just change the $ydb to $rdb. However, since it is heavily advised that changing the Core files should not be done, I figured I would put in a feature request as others might be interested in something like this.

I started looking into this when our RDS installation was overloaded with new URL requests. Investigating that led me to find a bunch of Select queries that we could have run on a Read Replica. In our specific case we are allowing for duplicate URLs and have disabled click tracking so we would like to be able to serve links from a Read Replica as well.