通過MySQL的UDFs和Trigger操作Memcached

標籤：trigger   memcached   udfs   memcached_functions_mysql   

通過MySQL的UDFs和Trigger操作Memcached

UDFs是User Defined Functions的縮寫，表示MySQL的使用者定義函數，應用程式可以利用這些函數從MySQL 5.0以上版本的資料庫中訪問Memcached寫入或者擷取的資料。此外，MySQL從5.1版本開始支援觸發器，從而可以在觸發器中使用UDFs直接更新Memcached的內容，這種方式降低了應用程式設計和編寫的複雜性。下面簡單介紹UDFs的安裝和使用。

 

UDFs安裝

安裝UDFs，需要依次安裝MySQL、libevent、memcached、libmemcached、memcached_functions_sql。

 

1. 安裝mysql-5.6.24.tar.gz

參照之前的博文《InnoDB memcached外掛程式部署》中MySQL安裝部分。

 

2. 安裝libevent-2.0.22-stable.tar.gz和安裝memcached-1.4.22.tar.gz

參照之前的博文《Memcached 1.4.22安裝和配置》。

 

3. 安裝libmemcached-0.34.tar.gz

memcached functions for mysql官網發布的1.1版本是09年的，只相容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. 安裝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. 拷貝lib檔案到mysql的plugin下面

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

 

6. 配置動態連結程式庫

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

 

7. 添加memcache UDF 函數

mysql < ./sql/install_functions.sql

 

備忘：

首次需要使用CREATE FUNCTION來初始化使用者定義函數。有兩種方法可以初始化所有MySQL提供的使用者定義函數：

第一種方法是在MySQL的SQL命令列中執行memcached_functions_mysql源碼目錄下的sql/install_functions.sql。第二種方法是運行memcached_functions_mysql源碼目錄下的utils/install.pl這個Perl指令碼，把memcache function作為UDFs加入MySQL。

 

8. 執行下面SQL命令，查看安裝是否成功。

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)

 

使用Trigger操作Memcached

1. 建立兩張表

建立兩張表：urls和results，更新urls表中的內容，使系統自動更新Memcached的內容。results用來記錄更新Memcached失敗的記錄。

SQL代碼如下：

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. 建立3個trigger

當向urls表中插入資料時，對Memcached執行set操作。trigger的代碼如下：

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 ;

當對urls表中的資料進行更新時，對Memcached執行replace操作。trigger代碼如下：

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 ;

當對urls表中的資料進行刪除操作時，對Memcached執行delete操作。trigger代碼如下：

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. 設定Memcached相關參數

設定UDFs操作Memcaced伺服器的IP地址和連接埠。

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)

在MySQL命令列中列出可以修改Memcached參數的行為，執行的命令和輸出結果如下：

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)

設定MEMCACHED_BEHAVIOR_NO_BLOCK為開啟狀態，這樣在Memcached出現問題（不能串連）時，資料可以繼續插入MySQL中，但有報錯提示；如果不設定此值，那麼Memcached失敗時，資料需要等到Memcached失敗逾時後才可以插入到表中。

通過下面的設定，可以避免這種情況的發生。

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. 對memcached_functions_mysql的簡易功能進行測試

1）向表urls中插入資料，然後查看Memcached是否對資料執行set操作。

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）更新表urls裡面的資料，然後查詢Memcached中是否也進行了更新。

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）刪除表urls中的資料，然後查看Memcached是否也將該資料刪除了。

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. 使用memcached_functions_mysql的經驗與技巧

memcached_functions_mysql使用起來比較簡單，但是由於環境的差別，在實踐過程中可能會遇到諸多的問題。下面總結了一些在使用memcached_functions_mysq過程中可能出現的問題和注意事項。

1) MySQL重啟問題

如果MySQL伺服器出現重啟，需要重新設定串連Memcached關係（SELECT memc_servers_set(‘192.168.11.52:11211‘))。

2) 程式BUG問題

memcached_functions_mysql的來源程式有可能存在bug，並且會導致MySQL的失敗。針對這個問題，讀者要盡量選擇來源程式的穩定版本。

3) 網路因素

網路因素是指MySQL和Memcached是否處在同一個IDC、它們之間的網路效能是否很好。網路效能越好，則速度越快。使用原生Memcached可以適當減少網路開銷。

4) 插入的資料量

插入資料量的大小包含兩個方面：向MySQL插入每條記錄的大小，向Memcached中更新資料的大小。更新MySQL、Memcached的資料越大，更新的速度越慢。因此，要做好前期規劃。

5) 延時問題

如果MySQL所在的機器使用的資源比較大，會導致更新Memcached過於緩慢，即出現類似m/s的延時問題。

6) 容災問題

如果MySQL和Memcached中有宕機情況出現時，需要考慮怎麼恢複，根據前一小節的測試可以這樣考慮：建一張錯誤表，如果在更新mc時出現問題，自動把更新錯誤的記錄插到這張表中，通過查詢這張表，可以知道哪些資料在什麼時間出現過更新錯誤。

如果memcached_functions_mysql應用於生產環境，需要考慮監控和出現問題時的恢複工作（寫好指令碼以完善這個工作）。

7) MySQL自身因素

如執行的MySQL語句的效率以及串連MySQL的client程式（php）的串連開銷等，這些問題都需要考慮。

本文出自 “SQL Server Deep Dives” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1641786

通過MySQL的UDFs和Trigger操作Memcached