Chapter 4. Using Memcached with MySQL 8

In the previous chapter, we saw how indexing works in MySQL 8 and how it can be useful for better performance. You also learned how to create indexes and saw the different types of indexes available in MySQL 8 such as clustered index, covering index, descending index, and invisible index. We got a good understanding of fulltext indexing and how natural language fulltext indexes and fulltext indexes on Boolean mode works.

In this chapter, we will discuss using Memcached with MySQL 8. The following are the topics that will be covered:

• Setting up Memcached
• Using Memcached
• Analyzing data stored in Memcached
• Memcached replication configuration
• Memcached APIs for different technologies

Let's first start with an overview of Memcached with MySQL 8 followed by other details of Memcached.

Overview of Memcached

While talking about handling Big Data with MySQL, we would have to obviously think about the performance--how do we handle the performance when storing and retrieving data frequently? One of the prominent answers is Memcached, which boosts the performance of frequent data retrieval and storage because it skips the query parser, SQL optimizer, and other parts of the engine that are unnecessary and allows us to store or retrieve data directly with InnoDB. Using Memcached, data management is much faster and convenient for handling Big Data.

MySQL 8 provides you with the InnoDB Memcached plugin named daemon_memcached, which can help us in managing data easily. It will automatically store and retrieve data from InnoDB tables and provide get, set, and incr operations that remove performance overhead by skipping SQL parsing, which speeds up data operations.

The following diagram will help you understand better how queries are parsed when using Memcache:

As shown in the previous...

Setting up Memcached

MySQL has its own Memcached plugin, daemon_memcached, which is tightly integrated with the MySQL server. This improves the performance and avoids network traffic over the traditional Memcached approach.

In the traditional way, Memcached was running on a separate port and as a different process; hence, it would have an overhead of network communication between MySQL and Memcached. However, this plugin made it easy to use and lightweight as it is integrated with the MySQL server itself.

Supported environments for this plugin are as follows:

• Linux
• Solaris
• OS X

Let's see to how to set up the daemon_memcached plugin in the MySQL server. We would have to install this plugin on each of the MySQL server instances, in case we are using the cluster environment of MySQL.

Installation

To enable the Memcached feature with MySQL, we first need to install the dependency for the Memcached plugin. libevent is the dependency that must be installed on the server to enable the Memcached plugin...

Using of Memcached

Memcached can be used at various stages in real-world applications. Its integration with MySQL makes it more comfortable for you to handle Big Data. Let's see the importance of using Memcached by understanding the different advantages.

Performance tuner

We always care about the performance while handing Big Data. We may have many questions before we start, such as how MySQL can be optimized from a performance perspective. We assume that managing Big Data with MySQL can impact performance as this is a pure structured database. But no! MySQL has a pretty simple way to handle Big Data by integrating Memcached and using it as a NoSQL database. This way, it will improve the performance latency and provide efficiency while managing Big Data with MySQL.

Memcached is one of the performance tuners for MySQL with Big Data. Memcached removes the SQL layer and directly accesses the InnoDB database tables. Hence, overhead operations like SQL parsing will no longer be executed, which really...

Analyzing data stored in Memcached

It's time to get our hands dirty by playing with the Memcached interface. An InnoDB table entry should be there in the containers table of the Memcached database to use this table through the Memcached interface. By default, while installing Memcached, a demo_test table is automatically created under the test database and the same table entry is inserted into the containers table. Let's define the new mapping and perform a few operations. I am getting eager to get to the next section!

I have created a student_result table to store students' results data with the following query:

mysql> CREATE TABLE student_result (
 student_id INT PRIMARY KEY,
 student_name VARCHAR(20),
 maths INT,
 english INT,
 history INT,
 flags INT,
 cas BIGINT UNSIGNED,
 expiry INT);

Mapping of the student_result table can be done by inserting an entry into the containers table as follows:

mysql> INSERT INTO innodb_memcache.containers
 (name, db_schema, db_table, key_columns, value_columns...

Memcached replication configuration

In a simple word, replication is the copying of data from one server to one or more servers. Replication enables the spreading of the load to multiple servers, which improves the performance and decreases the chances of data corruption, as the data is distributed among various servers. MySQL 8 supports different replication methods but the traditional method is to replicate through binary logs of the master server. A binary log is a file that collects all the statements that attempt to change the table data. These statements will be executed on the slave servers to replicate the master data.

The daemon_memcached plugin has the capability to manage the binary log on the master server, and the same can be used for replication on slave servers. All Memcached commands are supported by binary logging.

Follow these steps to enable the binary log replication in MySQL 8:

1. During the startup of the server, add the --innodb_api_enable_binlog=1 and --log-bin system variables...

Memcached APIs for different technologies

An application can store and retrieve data through the Memcached interface using various APIs that are available. Memcached APIs are available in different programming languages such as Perl, Python, Java, PHP, C, and Ruby. With the help of a Memcached API, an application can interact with the Memcached interface to store and retrieve information.

Memcached stores information in a cache that can be referred to by a single string as a key. This key is used as a reference for the data retrieval and storage. Memcached always serves data requests based on the key as a reference. The cache therefore operates like a large associative array or hash table.

The Memcached interface has a common method across all the programming languages to store and retrieve information, although the language-specific mechanism might be different. Let's look at a few common methods and their usage:

• get(key): This method is used to fetch information from the cache with respect...

Summary

In this chapter, we discussed using Memcached with MySQL 8 for Big Data usage and the architecture of the overall system in which Memcached takes a prominent place. You learned how Memcached can be installed on a Linux-based system and we also discussed the usage of Memcached in real-world applications. We also played around with data through the Memcached interface using different query techniques. We covered the Memcached configuration that needs to be done for replication purposes. Finally, we saw the different APIs available for various programming languages including Java, PHP, Ruby, and Python.

In the next chapter, we will see how large data can be distributed using various partitioning techniques to help Big Data, which can be easily managed in MySQL 8.