Operate Memcached through MySQL UDFs and Trigger

Using MySQL UDFs and Trigger to operate MemcachedUDFs is short for UserDefinedFunctions, indicating MySQL user-defined functions, applications can use these functions to access data written or obtained by Memcached from databases of MySQL or later versions. In addition, MySQL 5.1 and later versions support triggers.

Using MySQL UDFs and Trigger to operate Memcached UDFs is the abbreviation of User-Defined Functions, indicating MySQL User-Defined Functions, applications can use these functions to access data written or obtained by Memcached from databases of MySQL 5.0 or later versions. In addition, MySQL 5.1 and later versions support triggers.

Use MySQLUDFsAnd TriggerOperate Memcached

UDFs, short for User-Defined Functions, indicates MySQL User-Defined Functions. Applications can use these Functions to access data written or obtained by Memcached from databases of MySQL 5.0 or later versions. In addition, MySQL supports triggers starting from version 5.1, so that you can use UDFs in triggers to directly update Memcached content, which reduces the complexity of Application Design and writing. The following describes how to install and use UDFs.

UDFsInstall

To install UDFs, install MySQL, libevent, memcached, libmemcached, and memcached_functions_ SQL in sequence.

1. Install mysql-5.6.24.tar.gz

Refer to the MySQL installation section in the previous blog InnoDB memcached plug-in deployment.

2. Install libevent-2.0.22-stable.tar.gzand install memcached-1.4.22.tar.gz.

Refer to the previous blog post "install and configure Memcached 1.4.22".

3. Install libmemcached-0.34.tar.gz

The official website of memcached functions for mysql released Version 1.1, which is only compatible with libmemcached0.34.

mkdir -p /usr/local/libmemcachedwget https://launchpad.net/libmemcached/1.0/0.34/+download/libmemcached-0.34.tar.gztar zxvf libmemcached-0.34.tar.gzcd libmemcached-0.34./configure --prefix=/usr/local/libmemcached --with-memcached=/usr/local/bin/memcachedmakemake install

4. Install memcached_functions_mysql

wget https://launchpad.net/memcached-udfs/trunk/1.1/+download/memcached_functions_mysql-1.1.tar.gztar xvf memcached_functions_mysql-1.1.tar.gzcd memcached_functions_mysql-1.1./configure --prefix=/usr/local/memcache_mysql --with-mysql=/usr/local/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcachedmakemake install

5. Copy the lib file to the mysql plugin.

cp /usr/local/memcache_mysql/lib/libmemcached_functions* /usr/local/mysql/lib/plugin

6. Configure the dynamic link library

echo "/usr/local/lib" >> /etc/ld.so.confldconfig

7. Add the memcache UDF Function

mysql < ./sql/install_functions.sql

Note:

For the first time, you must use create function to initialize user-defined functions. There are two ways to initialize all user-defined functions provided by MySQL:

The first method is to execute SQL/install_functions. SQL under the memcached_functions_mysql source code directory in the MySQL SQL command line. The second method is to run the utils/install. pl Perl script under the memcached_functions_mysql source code directory and add memcache function as UDFs to MySQL.

8. Run the following SQL command to check whether the installation is successful.

mysql> select name,dl from mysql.func;+------------------------------+---------------------------------+| name | dl |+------------------------------+---------------------------------+| memc_add | libmemcached_functions_mysql.so || memc_add_by_key | libmemcached_functions_mysql.so || memc_servers_set | libmemcached_functions_mysql.so || memc_server_count | libmemcached_functions_mysql.so || memc_set | libmemcached_functions_mysql.so || memc_set_by_key | libmemcached_functions_mysql.so || memc_cas | libmemcached_functions_mysql.so || memc_cas_by_key | libmemcached_functions_mysql.so || memc_get | libmemcached_functions_mysql.so || memc_get_by_key | libmemcached_functions_mysql.so || memc_get_cas | libmemcached_functions_mysql.so || memc_get_cas_by_key | libmemcached_functions_mysql.so || memc_delete | libmemcached_functions_mysql.so || memc_delete_by_key | libmemcached_functions_mysql.so || memc_append | libmemcached_functions_mysql.so || memc_append_by_key | libmemcached_functions_mysql.so || memc_prepend | libmemcached_functions_mysql.so || memc_prepend_by_key | libmemcached_functions_mysql.so || memc_increment | libmemcached_functions_mysql.so || memc_decrement | libmemcached_functions_mysql.so || memc_replace | libmemcached_functions_mysql.so || memc_replace_by_key | libmemcached_functions_mysql.so || memc_servers_behavior_set | libmemcached_functions_mysql.so || memc_servers_behavior_get | libmemcached_functions_mysql.so || memc_behavior_set | libmemcached_functions_mysql.so || memc_behavior_get | libmemcached_functions_mysql.so || memc_list_behaviors | libmemcached_functions_mysql.so || memc_list_hash_types | libmemcached_functions_mysql.so || memc_list_distribution_types | libmemcached_functions_mysql.so || memc_udf_version | libmemcached_functions_mysql.so || memc_libmemcached_version | libmemcached_functions_mysql.so || memc_stats | libmemcached_functions_mysql.so || memc_stat_get_keys | libmemcached_functions_mysql.so || memc_stat_get_value | libmemcached_functions_mysql.so |+------------------------------+---------------------------------+34 rows in set (0.00 sec)

Use TriggerOperate Memcached

1. Create two tables

Create two tables: urls and results. Update the content in the urls table so that the system automatically updates the Memcached content. Results is used to record the Memcached failure update records.

The SQL code is as follows:

use test;drop table if exists urls;CREATE TABLE `urls` (`id` int(10) NOT NULL,`url` varchar(255) NOT NULL DEFAULT '',PRIMARY KEY (`id`));drop table if exists results;CREATE TABLE `results` (`id` int(10) NOT NULL,`result` varchar(255) NOT NULL DEFAULT 'error',`time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`));

2. Create Three triggers

When inserting data into the urls table, perform the set operation on Memcached. The trigger code is as follows:

DELIMITER //DROP TRIGGER IF EXISTS url_mem_insert;CREATE TRIGGER url_mem_insertBEFORE INSERT ON urlsFOR EACH ROW BEGINset @mm = memc_set(NEW.id, NEW.url);if @mm <> 0 theninsert into results(id) values(NEW.id);end if;END //DELIMITER ;

When updating the data in the urls table, perform the replace operation on Memcached. The trigger code is as follows:

DELIMITER //DROP TRIGGER IF EXISTS url_mem_update;CREATE TRIGGER url_mem_updateBEFORE UPDATE ON urlsFOR EACH ROW BEGINset @mm = memc_replace(OLD.id,NEW.url);if @mm <> 0 theninsert into results(id) values(OLD.id);end if;END //DELIMITER ;

Delete Memcached When deleting data in the urls table. The trigger code is as follows:

DELIMITER //DROP TRIGGER IF EXISTS url_mem_delete;CREATE TRIGGER url_mem_deleteBEFORE DELETE ON urlsFOR EACH ROW BEGINset @mm = memc_delete(OLD.ID);if @mm <> 0 theninsert into results(id) values(OLD.id);end if;END //DELIMITER ;

3. Set Memcached Parameters

Set the IP address and port of the Memcaced server for UDFs operations.

mysql> SELECT memc_servers_set('192.168.11.52:11211');+-----------------------------------------+| memc_servers_set('192.168.11.52:11211') |+-----------------------------------------+| 0 |+-----------------------------------------+1 row in set (0.07 sec)mysql>select memc_server_count();+---------------------+| memc_server_count() |+---------------------+| 1 |+---------------------+1 row in set (0.00 sec)

In the MySQL command line, list the behaviors that can modify the Memcached parameter. The command and output result are as follows:

mysql>select memc_list_behaviors()\G*************************** 1. row ***************************memc_list_behaviors():MEMCACHED SERVER BEHAVIORSMEMCACHED_BEHAVIOR_SUPPORT_CASMEMCACHED_BEHAVIOR_NO_BLOCKMEMCACHED_BEHAVIOR_TCP_NODELAYMEMCACHED_BEHAVIOR_HASHMEMCACHED_BEHAVIOR_CACHE_LOOKUPSMEMCACHED_BEHAVIOR_SOCKET_SEND_SIZEMEMCACHED_BEHAVIOR_SOCKET_RECV_SIZEMEMCACHED_BEHAVIOR_BUFFER_REQUESTSMEMCACHED_BEHAVIOR_KETAMAMEMCACHED_BEHAVIOR_POLL_TIMEOUTMEMCACHED_BEHAVIOR_RETRY_TIMEOUTMEMCACHED_BEHAVIOR_DISTRIBUTIONMEMCACHED_BEHAVIOR_BUFFER_REQUESTSMEMCACHED_BEHAVIOR_USER_DATAMEMCACHED_BEHAVIOR_SORT_HOSTSMEMCACHED_BEHAVIOR_VERIFY_KEYMEMCACHED_BEHAVIOR_CONNECT_TIMEOUTMEMCACHED_BEHAVIOR_KETAMA_WEIGHTEDMEMCACHED_BEHAVIOR_KETAMA_HASHMEMCACHED_BEHAVIOR_BINARY_PROTOCOLMEMCACHED_BEHAVIOR_SND_TIMEOUTMEMCACHED_BEHAVIOR_RCV_TIMEOUTMEMCACHED_BEHAVIOR_SERVER_FAILURE_LIMITMEMCACHED_BEHAVIOR_IO_MSG_WATERMARKMEMCACHED_BEHAVIOR_IO_BYTES_WATERMARK1 row in set (0.00 sec)

Set MEMCACHED_BEHAVIOR_NO_BLOCK to open. In this way, when Memcached encounters a problem (cannot be connected), data can be inserted into MySQL, but an error message is returned. If Memcached fails to be set, data can be inserted into the table only after the Memcached failure times out.

The following settings can be used to avoid this situation.

mysql>select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');+--------------------------------------------------------------+| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1') |+--------------------------------------------------------------+| 0 |+--------------------------------------------------------------+1 row in set (0.00 sec)mysql>select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');+-----------------------------------------------------------------+| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1') |+-----------------------------------------------------------------+| 0 |+-----------------------------------------------------------------+1 row in set (0.00 sec)

4. Test the simple functions of memcached_functions_mysql.

1) insert data to the table urls and check whether Memcached performs the set operation on the data.

mysql>insert into urls (id,url) values (1, 'http://www.test.com.cn');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select memc_get('1');+------------------------+| memc_get('1') |+------------------------+| http://www.test.com |+------------------------+1 row in set (0.00 sec)

1>telnet 192.168.11.52 11211Trying 192.168.11.52...Connected to 192.168.11.52 (192.168.11.52).Escape character is '^]'.get 1VALUE 1 0 22http://www.test.com.cnEND

2) update the data in the table urls, and then query whether Memcached has been updated.

mysql>update test.urls set url='http://blog.test.com.cn' where id=1;Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select memc_replace('1','http://blog.test.com.cn');+---------------------------------------------+| memc_replace('1','http://blog.test.com.cn') |+---------------------------------------------+| 0 |+---------------------------------------------+1 row in set (0.00 sec)mysql> select memc_get('1');+-------------------------+| memc_get('1') |+-------------------------+| http://blog.test.com.cn |+-------------------------+1 row in set (0.00 sec)

1>telnet 192.168.11.52 11211Trying 192.168.11.52...Connected to 192.168.11.52(192.168.11.52).Escape character is '^]'.get 1VALUE 1 0 23http://blog.test.com.cnEND

3) Delete the data in the table urls and check whether Memcached has deleted the data.

mysql>delete from test.urls where id=1;Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select memc_get('1');+---------------+| memc_get('1') |+---------------+| NULL |+---------------+1 row in set (0.00 sec)

1>telnet 192.168.11.52 11211Trying 192.168.11.52...Connected to 192.168.11.52 (192.168.11.52).Escape character is '^]'.get 1END

5. experience and skills in using memcached_functions_mysql

Memcached_functions_mysql is easy to use. However, due to environment differences, you may encounter many problems in practice. The following summarizes some problems and precautions that may occur when using memcached_functions_mysq.

1) MySQL restart Problem

If the MySQL server restarts, You need to reset the Memcached connection relationship (SELECT memc_servers_set ('192. 168.11.52: 100 ')).

2) program BUG

The source program of memcached_functions_mysql may have bugs and may cause MySQL to fail. To solve this problem, the reader should try to select a stable version of the source program.

3) network Factors

Network factors refer to whether MySQL and Memcached are in the same IDC, and whether the network performance between them is good. The better the network performance, the faster the speed. The locally deployed Memcached can reduce network overhead.

4) inserted data volume

The size of the inserted data volume includes two aspects: the size of each record inserted into MySQL, and the size of the Data updated to Memcached. The more data MySQL and Memcached are updated, the slower the update speed. Therefore, we need to make a good preliminary plan.

5) latency issues

If the machine where MySQL is located uses a large amount of resources, it will lead to too slow updates to Memcached, that is, the latency issue similar to m/s.

6) Disaster Tolerance Problems

If MySQL and Memcached are down, you need to consider how to recover it. Based on the test in the previous section, you can consider this: Create an error table. If there is a problem when updating mc, update error records are automatically inserted into this table. by querying this table, you can know which data has encountered an update error at what time.

If memcached_functions_mysql is applied to the production environment, you need to consider monitoring and recovery in case of problems (write a script to complete this job ).

7) MySQL Factors

For example, the efficiency of the MySQL statement executed and the connection overhead of the client Program (php) connected to MySQL must be considered.