Adam Daniels

Adam Daniels

1558501902

The performance benchmarks of Redis and MySQL

To begin with, we will start with the introduction and installation of Redis on an Ubuntu machine. Then we will move towards the benchmarking between these two.

Introduction to Redis

According to the official website, Redis is an open source (BSD licensed), in-memory data structure store that’s used as a database, cache, and message broker. Actually, Redis is an advanced key-value store. It is super fast with amazingly high throughput as it can perform approximately 110000 SETs per second, about 81000 GETs per second. It also supports a very rich set of data types to store. As a matter of fact, Redis keeps the data in-memory every time but is also a persistent on-disk database. So, it comes with a trade-off: Amazing speed with the size limit on datasets (as per memory). In this article, to have some benchmarks in comparison to MySQL, we would be using Redis as a caching engine only.

Prerequisites

Installation of Redis on Ubuntu

In this section, we will be discussing the steps required to install/configure Redis on an Ubuntu machine. Here is the approach for this:

Installation and Configuration of Redis on Ubuntu

To start with, run the following commands:

 sudo apt update sudo apt install redis-server 

These commands will update the proper package and install Redis on your Ubuntu machine.

Now, as a matter of fact, to enable Redis to be used as a Service, you need to configure it by updating the supervised directive present inside the configuration file of Redis. You can easily find the configuration file here:

 sudo vi /etc/redis/redis.conf 

By default, the supervised directive is set to “no.” You need to set it to: systemd . After updating, this section of the configuration file will look something like this:

 ################################# GENERAL ##################################### # By default Redis does not run as a daemon. Use 'yes' if you need it. # Note that Redis will write a pid file in /var/run/redis.pid when daemonized. daemonize yes # If you run Redis from upstart or systemd, Redis can interact with your # supervision tree. Options: # supervised no - no supervision interaction # supervised upstart - signal upstart by putting Redis into SIGSTOP mode # supervised systemd - signal systemd by writing READY=1 to $NOTIFY_SOCKET # supervised auto - detect upstart or systemd method based on # UPSTART_JOB or NOTIFY_SOCKET environment variables # Note: these supervision methods only signal "process is ready." # They do not enable continuous liveness pings back to your supervisor. supervised systemd # If a pid file is specified, Redis writes it where specified at startup # and removes it at exit. # # When the server runs non daemonized, no pid file is created if none is # specified in the configuration. When the server is daemonized, the pid file # is used even if not specified, defaulting to "/var/run/redis.pid". # # Creating a pid file is best effort: if Redis is not able to create it # nothing bad happens, the server will start and run normally. pidfile /var/run/redis/redis-server.pid # Specify the server verbosity level. # This can be one of: # debug (a lot of information, useful for development/testing) # verbose (many rarely useful info, but not a mess like the debug level) # notice (moderately verbose, what you want in production probably) 

Save this file and, to have the changes reflected on Redis, restart the Redis Service by using the following command:

 sudo systemctl restart redis.service 

Enabling Password Authentication

Configuring Redis with password authentication is not mandatory, but it is super important (and easy, too) as it enables better security for your Redis instance. Configuring our Redis server with the password is super easy and can be done through the same configuration file as mentioned above. So, open the configuration file and look for the requirepassdirective. You would that line is commented by default, just uncomment it and enter your password there. The configuration file should look similar to this:

 # Require clients to issue AUTH <PASSWORD> before processing any other # commands. This might be useful in environments in which you do not trust # others with access to the host running redis-server. # # This should stay commented out for backward compatibility and because most # people do not need auth (e.g. they run their own servers). # # Warning: since Redis is pretty fast an outside user can try up to # 150k passwords per second against a good box. This means that you should # use a very strong password otherwise it will be very easy to break. requirepass yourpasswordhere # Command renaming. # # It is possible to change the name of dangerous commands in a shared # environment. For instance the CONFIG command may be renamed into something # hard to guess so that it will still be available for internal-use tools # but not available for general clients. # # Example: # # rename-command CONFIG b840fc02d524045429941cc15f59e41cb7be6c52 

Save this file and, to have the changes reflected on Redis, restart the Redis Service by using the following command:

 sudo systemctl restart redis.service 

Installation of PHPRedis on Ubuntu

Now, to enable your PHP code to use Redis as a service:

  • Run the following command to install PHPRedis extension:
 sudo apt-get install php-redis 

  • Add the following line to your php.ini file:
 extension=redis.so 

Workflow

  • With MySQL only:
  • Randomly generate a key between [1, 10000] and search for that key in a MySQL database.
  • Take a note of the amount of time it takes to do so.
  • Take samples to see how long it takes to process n number of such requests while increasing n gradually as 1, 10, 100, 1000, 10000, 100000, 1000000, 1000000.
  • With MySQL and Redis:
  • Randomly generate a key between [1, 10000].
  • Check if that key is already present/stored on our Redis instance.
  • If it is present on Redis, we will not hit a MySQL DB.
  • If it is not present on Redis, we will search for that key on our MySQL database and store that key to Redis.
  • Take a note of the amount time it has taken to do so.
  • Take samples to see how long it takes to process n number of such requests while increasing n gradually as 1, 10, 100, 1000, 10000, 100000, 1000000, 1000000.

Source Code

MySQL Only Source Code

(When we try to fetch a key from MySQL only)

 <?php $con = mysqli_connect("localhost","root","admin","blog_db"); for($i = 1; $i <= 10000000; $i = $i *10) { $startTime = microtime(true); for($j = 1; $j <= $i; $j++) { $rand = rand(1, 100000); $sql = "SELECT VALUE from data WHERE `key` = $rand"; if (!mysqli_query($con, $sql)) { echo "Error: " . $sql . "" . mysqli_error($con); } } $endTime = microtime(true); file_put_contents('/home/ayush/Desktop/temp/blog/mysqlonly.log', $i . ',' . ($endTime - $startTime) . "\n" , FILE_APPEND); } 

MySQL and Redis Source Code

(When we try to fetch a key from Redis first and then from MySQL if we are unable to find that key on Redis)

 <?php $con = mysqli_connect("localhost","root","admin","blog_db"); $client = new Redis(); $client->connect('localhost'); for($i = 1; $i <= 10000000; $i = $i *10) { $startTime = microtime(true); for($j = 1; $j <= $i; $j++) { $rand = rand(1, 100000); if(!$client->exists($rand)) { $client->set($rand, $rand); $sql = "SELECT VALUE from data WHERE `key` = $rand"; if (!mysqli_query($con, $sql)) { echo "Error: " . $sql . "" . mysqli_error($con); } } } $endTime = microtime(true); file_put_contents('/home/ayush/Desktop/temp/blog/redis.log', $i . ',' . ($endTime - $startTime) . "\n" , FILE_APPEND); $client->flushAll(); } 

BenchMarks

Tabular Data

Graphical Representation

Conclusion

As it can be easily seen from graphical representation given above, as the number of requests increases significantly, Redis starts outperforming by a significant margin. Hence, using a caching engine like Redis along with your database is a good idea if the number of requests processed is huge.

References

#database #redis #mysql

What is GEEK

Buddha Community

The performance benchmarks of Redis and MySQL
Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

Loma  Baumbach

Loma Baumbach

1596679140

Redis Transactions & Long-Running Lua Scripts

Redis offers two mechanisms for handling transactions – MULTI/EXEC based transactions and Lua scripts evaluation. Redis Lua scripting is the recommended approach and is fairly popular in usage.

Our Redis™ customers who have Lua scripts deployed often report this error – “BUSY Redis is busy running a script. You can only call SCRIPT KILL or SHUTDOWN NOSAVE”. In this post, we will explain the Redis transactional property of scripts, what this error is about, and why we must be extra careful about it on Sentinel-managed systems that can failover.

Redis Lua Scripts Diagram - ScaleGrid Blog

Transactional Nature of Redis Lua Scripts

Redis “transactions” aren’t really transactions as understood conventionally – in case of errors, there is no rollback of writes made by the script.

Atomicity” of Redis scripts is guaranteed in the following manner:

  • Once a script begins executing, all other commands/scripts are blocked until the script completes. So, other clients either see the changes made by the script or they don’t. This is because they can only execute either before the script or after the script.
  • However, Redis doesn’t do rollbacks, so on an error within a script, any changes already made by the script will be retained and future commands/scripts will see those partial changes.
  • Since all other clients are blocked while the script executes, it is critical that the script is well-behaved and finishes in time.

The ‘lua-time-limit’ Value

It is highly recommended that the script complete within a time limit. Redis enforces this in a weak manner with the ‘lua-time-limit’ value. This is the maximum allowed time (in ms) that the script is allowed to run. The default value is 5 seconds. This is a really long time for CPU-bound activity (scripts have limited access and can’t run commands that access the disk).

However, the script is not killed when it executes beyond this time. Redis starts accepting client commands again, but responds to them with a BUSY error.

If you must kill the script at this point, there are two options available:

  • SCRIPT KILL command can be used to stop a script that hasn’t yet done any writes.
  • If the script has already performed writes to the server and must still be killed, use the SHUTDOWN NOSAVE to shutdown the server completely.

It is usually better to just wait for the script to complete its operation. The complete information on methods to kill the script execution and related behavior are available in the documentation.

#cloud #database #developer #high availability #howto #redis #scalegrid #lua-time-limit #redis diagram #redis master #redis scripts #redis sentinel #redis servers #redis transactions #sentinel-managed #server failures

Ruthie  Bugala

Ruthie Bugala

1620409080

Redis on Azure Performance Benchmark – ScaleGrid for Redis™ Vs. Azure Cache

Redis is an advanced key-value store. In fact, it is the number one key value store and eighth most popular database in the world. It has high throughput and runs from memory, but also has the ability to persist data on disk. Redis is a great caching solution for highly demanding applications, and there are many solutions available to help you deploy and manage Redis in the cloud. In this post, we are going to compare ScaleGrid for Redis™ vs. Azure Cache for Redis performance and management features to help you pick the best managed solution for your Redis deployment.

ScaleGrid is a DBaaS provider that provides fully managed hosting not only for Redis™, but also for MongoDB® databaseMySQL, and PostgreSQL. The Bring Your Own Cloud (BYOC) plan hosts the database server in your own AWS, Azure or GCP account.

Azure provides a hosted service for Redis called Azure Cache for Redis.

#performance #redis #azure #benchmark #latency #redis

Loma  Baumbach

Loma Baumbach

1595781840

Exploring MySQL Binlog Server - Ripple

MySQL does not limit the number of slaves that you can connect to the master server in a replication topology. However, as the number of slaves increases, they will have a toll on the master resources because the binary logs will need to be served to different slaves working at different speeds. If the data churn on the master is high, the serving of binary logs alone could saturate the network interface of the master.

A classic solution for this problem is to deploy a binlog server – an intermediate proxy server that sits between the master and its slaves. The binlog server is set up as a slave to the master, and in turn, acts as a master to the original set of slaves. It receives binary log events from the master, does not apply these events, but serves them to all the other slaves. This way, the load on the master is tremendously reduced, and at the same time, the binlog server serves the binlogs more efficiently to slaves since it does not have to do any other database server processing.

MySQL Binlog Server Deployment Diagram - ScaleGrid Blog

Ripple is an open source binlog server developed by Pavel Ivanov. A blog post from Percona, titled MySQL Ripple: The First Impression of a MySQL Binlog Server, gives a very good introduction to deploying and using Ripple. I had an opportunity to explore Ripple in some more detail and wanted to share my observations through this post.

1. Support for GTID based replication

Ripple supports only GTID mode, and not file and position-based replication. If your master is running in non-GTID mode, you will get this error from Ripple:

Failed to read packet: Got error reading packet from server: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON.

You can specify Server_id and UUID for the ripple server using the cmd line options: -ripple_server_id and -ripple_server_uuid

Both are optional parameters, and if not specified, Ripple will use the default server_id=112211 and uuid will be auto generated.

2. Connecting to the master using replication user and password

While connecting to the master, you can specify the replication user and password using the command line options:

-ripple_master_user and -ripple_master_password

3. Connection endpoint for the Ripple server

You can use the command line options -ripple_server_ports and -ripple_server_address to specify the connection end points for the Ripple server. Ensure to specify the network accessible hostname or IP address of your Ripple server as the -rippple_server_address. Otherwise, by default, Ripple will bind to localhost and hence you will not be able to connect to it remotely.

4. Setting up slaves to the Ripple server

You can use the CHANGE MASTER TO command to connect your slaves to replicate from the Ripple server.

To ensure that Ripple can authenticate the password that you use to connect to it, you need to start Ripple by specifying the option -ripple_server_password_hash

For example, if you start the ripple server with the command:

rippled -ripple_datadir=./binlog_server -ripple_master_address= <master ip> -ripple_master_port=3306 -ripple_master_user=repl -ripple_master_password='password' -ripple_server_ports=15000 -ripple_server_address='172.31.23.201' -ripple_server_password_hash='EF8C75CB6E99A0732D2DE207DAEF65D555BDFB8E'

you can use the following CHANGE MASTER TO command to connect from the slave:

CHANGE MASTER TO master_host='172.31.23.201', master_port=15000, master_password=’XpKWeZRNH5#satCI’, master_user=’rep’

Note that the password hash specified for the Ripple server corresponds to the text password used in the CHANGE MASTER TO command. Currently, Ripple does not authenticate based on the usernames and accepts any non-empty username as long as the password matches.

Exploring MySQL Binlog Server - Ripple

CLICK TO TWEET

5. Ripple server management

It’s possible to monitor and manage the Ripple server using the MySQL protocol from any standard MySQL client. There are a limited set of commands that are supported which you can see directly in the source code on the mysql-ripple GitHub page.

Some of the useful commands are:

  • SELECT @@global.gtid_executed; – To see the GTID SET of the Ripple server based on its downloaded binary logs.
  • STOP SLAVE; – To disconnect the Ripple server from the master.
  • START SLAVE; – To connect the Ripple server to the master.

#cloud #database #developer #high availability #mysql #performance #binary logs #gtid replication #mysql binlog #mysql protocol #mysql ripple #mysql server #parallel threads #proxy server #replication topology #ripple server

Loma  Baumbach

Loma Baumbach

1595774031

ScaleGrid DigitalOcean Support for MySQL, PostgreSQL and Redis™

PALO ALTO, Calif., June 9, 2020 – ScaleGrid, a leading Database-as-a-Service (DBaaS) provider, has just announced support for their MySQLPostgreSQL and Redis™ solutions on DigitalOcean. This launch is in addition to their current DigitalOcean offering for MongoDB® database, the only DBaaS to support this database on DigitalOcean.

MySQL and PostgreSQL are the top two open source relational databases in the world, and Redis is the top key-value database. These databases are a natural fit for the developer market that has gravitated towards DigitalOcean since its launch just nine years ago in 2011. The open source model is not only popular with the developer market, but also enterprise companies looking to modernize their infrastructure and reduce spend.  DigitalOcean instance costs are also over 28% less expensive than AWS, and over 26% less than Azure, providing significant savings for companies who are struggling in this global climate.

ScaleGrid’s MySQL, PostgreSQL and Redis™ solutions on DigitalOcean are competitively priced starting at just $15/GB, the same as DigitalOcean’s Managed Database solution, but offer on average 30% more storage for the same price. Additionally, ScaleGrid offers several competitive advantages such as full superuser access, custom master-slave configurations, and advanced slow query analysis and monitoring capabilities through their sophisticated platform. To compare more features, check out their ScaleGrid vs. DigitalOcean MySQLScaleGrid vs. DigitalOcean PostgreSQL and ScaleGrid vs. DigitalOcean Redis™ pages.

#cloud #database #developer #digital ocean #mysql #postgresql #redis #scalegrid #advanced performance #database infrastructure #dbaas on digitalocean #digitalocean customers #digitalocean instance costs #digitalocean managed databases #high performance ssd #mysql digitalocean #postgresql digitalocean #redis digitalocean #scalegrid digitalocean #scalegrid vs. digitalocean