UDFs and trigger operation via MySQL memcached

via MySQL the UDFs and Trigger Operation Memcached

UDFs is the abbreviation for user Defined functions, which represents a mysql-defined function that the application can use to access the data memcached writes or obtains from a MySQL version of 5.0 or more databases. In addition, MySQL supports triggers from version 5.1, allowing you to directly update memcached content in triggers using UDFs, which reduces the complexity of application design and authoring. The following is a brief introduction to the installation and use of UDFs.

UDFs installation

Installing UDFs, you need to install MySQL, libevent, memcached, libmemcached, memcached_functions_sql in turn.

1. Installing mysql-5.6.24.tar.gz

Refer to the MySQL installation section of the previous blog post, "InnoDB memcached plug-in deployment."

2. Installing the libevent-2.0.22-stable.tar.gz and installing the memcached-1.4.22.tar.gz

Refer to the previous post "Memcached 1.4.22 installation and Configuration".

3. Installing libmemcached-0.34.tar.gz

The 1.1 version of the memcached functions for MySQL website is 09 and is only compatible with the libmemcached0.34 version.

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. Installing 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 below

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 Memcache UDF function

MySQL <./sql/install_functions.sql

Note:

The first time you need to use CREATE function to initialize a user-defined function. There are two ways to initialize all of the user-defined functions provided by MySQL:

The first method is to execute the Sql/install_functions.sql in the Memcached_functions_mysql source directory on the MySQL SQL command line. The second way is to run the Memcached_functions_mysql source directory utils/install.pl This perl script, the Memcache function as UDFs join MySQL.

8. Execute the following SQL command to see if the installation was 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)

using Trigger Operation Memcached

1. Create two tables

New two sheets: URLs and results, updating the contents of the URLs table, so that the system automatically updates the contents of the memcached. Results is used to record records that update memcached failed.

The SQL code is as follows:

Use Test;drop table if exists URLs;  CREATE TABLE ' URLs ' (' id ' int () not null, ' url ' varchar (255) is not null DEFAULT ', PRIMARY KEY (' id '));d ROP table if exists Results  CREATE TABLE ' results ' (' id ' int () not NULL, ' result ' varchar (255) is not null default ' error ', ' time ' timestamp null default Current_timestamp,primary KEY (' id '));

2. Establishment of 3 trigger

When data is inserted into the URLs table, a set operation is performed on the memcached. The code for trigger 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 the data in the URLs table is updated, the replace operation is performed on the 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;

When the data in the URLs table is deleted, the delete operation is performed on the memcached. 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 thenin SERT into results (ID) values (old.id); End If; END//delimiter;

3. Setting memcached Related parameters

Set the IP address and port of the UDFs operation Memcaced Server.

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)

The behavior of the memcached parameter can be modified in the MySQL command line, and the command and output results 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, so that when the memcached problem (cannot connect), the data can continue to insert in MySQL, but there is an error message, if you do not set this value, then memcached failure, The data needs to wait until the memcached failure time-out before it can be inserted into the table.

This can be avoided by following the settings below.

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 function of the Memcached_functions_mysql

1) Insert the data into the table URLs and see if Memcached performs a 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 the memcached for updates.

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 from the table URLs and see if memcached has deleted the data as well.

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 relatively simple to use, but due to environmental differences, there are many problems that may be encountered in the process of practice. The following summarizes some of the issues and considerations that may arise during the use of MEMCACHED_FUNCTIONS_MYSQ.

1) MySQL Restart problem

If the MySQL server restarts, you need to reset the connection memcached relationship (SELECT memc_servers_set (' 192.168.11.52:11211 ')).

2) Program Bug problem

The source program for Memcached_functions_mysql may have bugs and will cause MySQL to fail. To solve this problem, readers should try to choose a stable version of the source program.

3) Network factors

The network factor is whether MySQL and memcached are in the same IDC, and the network performance between them is very good. The better the network performance, the faster it becomes. Using native memcached can reduce network overhead appropriately.

4) Amount of data inserted

The size of the inserted data contains two aspects: the size of each record is inserted into MySQL, and the size of the data is updated to memcached. Update MySQL, memcached the larger the data, the slower the update. Therefore, we should do well in the early planning.

5) Delay problem

If MySQL is on a machine that uses a larger resource, it can cause the update memcached to be too slow, that is, a delay problem like M/s.

6) Disaster tolerance problem

If there are outages in MySQL and memcached, you need to consider how to recover, according to the previous section of the test can be considered: to build an error table, if there is a problem in updating MC, automatically put the update error record into this table, by querying this table, You can know what data has been updated at what time.

If the memcached_functions_mysql is applied to a production environment, you need to consider the recovery effort when monitoring and problems occur (write scripts to refine the job).

7) MySQL self factor

These issues need to be considered, such as the efficiency of MySQL statements executed and the connection overhead of the client program (PHP) that connects MySQL.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1641786

UDFs and trigger operation via MySQL memcached