Many developers face the challenge of achieving performance without compromising on real-time application functionality in database management. This brings us to today’s spotlight technology: ReadySet.
What is ReadySet?
According to its GitHub repository, ReadySet is designed to enhance the performance of real-time applications by providing a powerful SQL caching engine that requires no changes to existing code or the need to switch databases. It’s a solution that promises efficiency and ease of integration, making it a compelling option for developers looking to speed up their applications.
ReadySet is based on Jon Gjengset’s MIT Ph.D. thesis, Partial State in Dataflow-Based Materialized Views, and CEO Alana Marzoev leads the company’s innovative journey. The person who presented the tool to me is Marcelo Altmann, my colleague and a developer celebrated for his contributions to Percona XtraBackup. He is now a member of the ReadySet development team.
Setting Up the Stage for Testing
I set up two EC2 instances for this demo, each armed with 4 CPUs and 16GB of RAM, running Ubuntu 22.04 (Jammy Jellyfish). For clarity, these instances are designed to be one dedicated to ReadySet and one for MySQL.
The goal? To install and configure ReadySet from scratch and evaluate its performance enhancements on MySQL databases.
MySQL Installation and Configuration
Adding the Percona Repository
Access the official Percona documentation with instructions on how to add the repository to your Ubuntu system. This step ensures you can install the latest version of MySQL directly from Percona’s repository.
System Update and Upgrade
We will upgrade the system packages to ensure our system is up-to-date and minimize potential compatibility issues.
|
1 2 |
# apt update; # apt upgrade -y; |
Installing Percona Server for MySQL
With the Percona repository added and the system updated, the next step is to install the Percona Server for MySQL. This can be done by following the specific instructions in the Percona documentation, which guides you through installing the server package. As of this writing, the installed version is 8.0.36-28.
|
1 2 |
# mysqld --version /usr/sbin/mysqld Ver 8.0.36-28 for Linux on x86_64 (Percona Server (GPL), Release '28', Revision '47601f19'$) |
Optimizing InnoDB Settings
To minimize disk I/O and enhance performance for the purpose of this test, adjustments are made to the InnoDB settings in the MySQL configuration file (/etc/mysql/my.cnf). The parameters adjusted include:
|
1 2 3 4 5 |
[mysqld] innodb_flush_log_at_trx_commit = 0 sync_binlog = 0 innodb_redo_log_capacity = 6G innodb_buffer_pool_size = 10G |
Creating a High-Privilege User
For ease of testing and to avoid permission-related issues during the ReadySet evaluation, a MySQL user with broad privileges is created (note: in a production environment, stronger password practices are recommended):
|
1 2 3 4 5 |
mysql> CREATE USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY 'app'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* to app@'%'; Query OK, 0 rows affected (0.00 sec) |
Note: The general guide for MySQL is in the ReadySet documentation. It provides detailed information on the necessary parameters and the minimum privileges required for operation.
ReadySet Installation
ReadySet offers different installation options, including binaries and a Docker version. For this test, I opted for the binary installation to closely monitor and control the environment, aligning with our “hands-on” approach.
Downloading the ReadySet Binary
Navigate to the ReadySet releases page to download the latest stable version of the ReadySet binary. At the time of this writing, the version selected was readyset_1.2.0-1_amd64.deb.
|
1 |
# wget https://github.com/readysettech/readyset/releases/download/stable-240328/readyset_1.2.0-1_amd64.deb |
Installation Process
Once the binary is downloaded, installation is straightforward. Utilize the Debian package manager with the command sudo dpkg -i readyset_1.2.0-1_amd64.deb. This command unpacks and installs ReadySet, registering it as a system service and simplifying the process of managing its operation through standard systemd commands.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# dpkg -i readyset_1.2.0-1_amd64.deb Selecting previously unselected package readyset. (Reading database ... 96135 files and directories currently installed.) Preparing to unpack readyset_1.2.0-1_amd64.deb ... Unpacking readyset (1.2.0-1) ... Setting up readyset (1.2.0-1) ... Created symlink /etc/systemd/system/multi-user.target.wants/readyset.service → /lib/systemd/system/readyset.service. Notice: The UPSTREAM_DB_URL and LISTEN_ADDRESS values must be set in /etc/readyset/readyset.conf before starting the readyset service. Press <Enter> to acknowledge this message. |
Configuring ReadySet for Performance Testing
With ReadySet installed, the configuration phase begins.
- Configuring the Database Connection: The configuration file,
readyset.conf, located in/etc/readyset/, holds the key to linking ReadySet with the MySQL database. Within this file, theUPSTREAM_DB_URLparameter requires adjustment to reflect the MySQL instance’s credentials and address. An example configuration would resemble:-
1UPSTREAM_DB_URL="mysql://app:app@172.31.16.153:3306/vinnie_test"
-
- Listening Address Configuration: Additionally, the
LISTEN_ADDRESSparameter in thereadyset.conffile is set to0.0.0.0:3306, enabling ReadySet to listen on all network interfaces of the host machine at the specified port.-
1LISTEN_ADDRESS=0.0.0.0:3306
-
- Finalizing the Setup: With the configuration accurately pointing to the MySQL database and the listening address set, the final step involves restarting the ReadySet service to apply the changes. This is achieved through the command:
1$ sudo systemctl restart readyset
Validating ReadySet’s Operational Status
To confirm ReadySet’s activity and to confirm a successful connection to MySQL, we check the service logs:
|
1 2 3 4 |
# tail -400f <strong>/var/lib/readyset/readyset.log.2024-04-11</strong> [...] 2024-04-11T00:14:23.657997Z INFO replicators::noria_adapter: MySQL connected 2024-04-11T00:14:23.658017Z INFO replicators::noria_adapter: binlog_position=binlog.000003:861 |
Directly interacting with ReadySet provides a definitive confirmation of its operational status:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ mysql -uapp -papp -h 54.160.157.28 -P 3306 mysql> SHOW READYSET STATUS; +----------------------------+-------------------------+ | Variable_name | Value | +----------------------------+-------------------------+ | Database Connection | Connected | | Connection Count | 4 | | Snapshot Status | Completed | | Maximum Replication Offset | binlog.000004:4 | | Minimum Replication Offset | binlog.000004:4 | | Last started Controller | 2024-04-11 00:21:02 UTC | | Last completed snapshot | 2024-04-11 00:21:02 UTC | | Last started replication | 2024-04-11 00:21:02 UTC | +----------------------------+-------------------------+ 8 rows in set (0.17 sec) |
Evaluating ReadySet’s Performance Enhancement
This evaluation focuses on the impact of ReadySet’s caching on query execution times using a specifically designed test scenario.
Creating the Test Database and Table
The first step involves creating a database and table within the MySQL instance to serve as the basis for our performance tests. A table named joinit was created, containing approximately 16 million rows, populated with a mix of integer, varchar, and time fields to simulate a real-world dataset. The script to create the table and populate it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE vinnie_test; DROP TABLE IF EXISTS `joinit`; CREATE TABLE `joinit` ( `i` int(11) NOT NULL AUTO_INCREMENT, `s` varchar(64) DEFAULT NULL, `t` time NOT NULL, `g` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO joinit VALUES (NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 ))); INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; [...] INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; |
Baseline Performance Measurement
To establish a baseline, queries were executed directly against the MySQL database, measuring the completion time. I executed the query multiple times to avoid the data being read from the disk, achieving a consistent execution time of approximately 7.5 seconds after stabilization:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.54 sec) [...] mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.61 sec) |
Introducing ReadySet into the Workflow
Switching the query execution to route through ReadySet initially mirrored the baseline performance, as the caching mechanism had yet to be activated:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
$ mysql -uapp -papp -h 54.160.157.28 -P 3306 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26-readyset Percona Server (GPL), Release '28', Revision '47601f19'$ mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.74 sec) mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.72 sec) |
This step was important in understanding the initial overhead and behavior of ReadySet without caching.
Activating and Assessing Caching Benefits
Upon executing the SHOW PROXIED QUERIES command, I observed a list of queries ReadySet had processed, including one that particularly interested me due to its potential for caching:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SHOW PROXIED QUERIES; +--------------------+------------------------------------------------+--------------------+-------+ | query id | proxied query | readyset supported | count | +--------------------+------------------------------------------------+--------------------+-------+ <strong>| q_ac193aeddd293eac | SELECT count(1) FROM `joinit` WHERE (`g` = $1) | yes | 0 | </strong>| q_95844e527a191a7b | show databases | unsupported | 0 | | q_21d7e9ff50fcbf0e | SELECT DATABASE() | unsupported | 0 | | q_e4add1bc5b3d07c | show global variables like '%timeout%' | unsupported | 0 | +--------------------+------------------------------------------------+--------------------+-------+ 4 rows in set (0.16 sec) |
Noticing that ReadySet had successfully identified and assigned an ID to the query of interest, I proceeded to cache this specific query using the command:
|
1 2 |
mysql> CREATE CACHE FROM q_ac193aeddd293eac; Query OK, 0 rows affected (0.16 sec) |
With the cache now set, I reran the query:
|
1 2 3 4 5 6 7 |
mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.71 sec) |
This timing was anticipated, given the known behavior of caching mechanisms, which require a query to be run once before the cache is effectively utilized. To evaluate the true impact of caching, I executed the same query once more:
|
1 2 3 4 5 6 7 |
mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (3.71 sec) |
The result showed a 50% reduction in execution time. Curious about the consistency of this improvement, I ran the query a third time:
|
1 2 3 4 5 6 7 |
mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (0.16 sec) |
The execution time dramatically dropped to 0.16 seconds, indicating a nearly 98% increase in speed compared to the original run. To confirm the caching effect was not isolated to a single parameter, I tested another value, g=6, inserted a new row, and observed a similar pattern of performance enhancement:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> select count(1) from joinit where g=6 ; +----------+ | count(1) | +----------+ | 559382 | +----------+ 1 row in set (0.15 sec) mysql> INSERT INTO joinit VALUES (NULL, uuid(), time(now()), 6); Query OK, 1 row affected (0.15 sec) mysql> select count(1) from joinit where g=6 ; +----------+ | count(1) | +----------+ | 559383 | +----------+ 1 row in set (0.15 sec) |
When inserting a new row, we see that ReadySet does not flush and reload the entire data set.
Concluding Thoughts
The initial tests revealed that ReadySet’s effectiveness becomes notably apparent after the first query execution when caching mechanisms can fully engage.
The observed reduction in query execution times—from several seconds to a fraction of a second in subsequent runs—illustrates the potential benefits of implementing ReadySet in environments where database query performance is critical.
Furthermore, while ReadySet shows promise for enhancing query efficiency, its adoption should be weighed against other factors, such as system complexity, maintenance overhead, and compatibility with existing technologies and workflows. As with any technology solution, the ultimate value of ReadySet will depend on its alignment with organizational needs, technical requirements, and strategic goals.
In summary, ReadySet emerges from this evaluation as a tool with the potential to significantly improve database query performance.
See you next time and happy databasing!
Hi Vinicius Grippa,
Thanks for this blog. Can you please let me know Ready set is supported with Percona Mysql or Mysql community edition also ?
Hi Rahul,
ReadySet supports both MySQL community and Percona version. The only product not supported is MariaDB.