memcached functions for mysql_文体教育 - 管理与工程技术学习平台 - 技术标准网

memcached functions for mysql

memcached Functions For MySQL Seemless caching for MySQLPatrick Galbraith, NorthScale.About the speakerPatrick GalbraithSoftware developer,Northscale17 Years dabbling in Open SourceAuthor of Developing Web Applications with Apache, MySQL, memcached and PerlFederated Storage Engine, Memcached Functions for MySQL/UDFs, DBDmysql...What are the memcached Functions for MySQLA suite of functions available to use with MySQL that allow you to store, retrieve and delete data, as well as most of the functions/operations that are available with libmemcached such as server connectivity to the client, server status, client behaviors and more. The power of the SQL engine which can be used to initiate caching or data retri using the result sets from query. You can combine the fetching of data from one or more tables with the fetching of data from memcached and be able to apply any SQL operations on that result set such as LIMIT, sorting and other conditional operations.What are the memcached Functions for MySQLWritten in C using libmemcached and the MySQL UDF APIProvide numerous memcached verbs/operations get, CAS, set, delete, replace, add, , stats, behavior setting and retri. Can be used in stored procedures and triggers Allow one to interact with memcached and MySQL independent of the language the application using them is written in, or for languages that dont have clients for memcached Open Source What is memcacheda high-perance, distributed memory object caching system Simply a memory server that provides caching layer for applications to store data to alleviate database load, as well as providing a dictionary, or hash lookup tableThe data stored in memcached is not durablemeaning its gone when the memcached server is shut down or restarted, as well as having no failover or authenticationIs an LRU Least Recently Used cache, which means that stored data that is the oldest and least accessed will be replaced with newer items when memcacheds memory capacity is reached.provides expiration timeouts for stored dataWhat is libmemcachedfaster memcached client library written in C. faster, more efficient, thread-safe, full-featured C library that has a significant perance gain over existing client libraries. more control to affect how the client functions by being able to set the client behavior memcached_behavior_set with numerous behavior settings such as hashing algorithm or whether the client is blocking or non-blocking, CAS Check and Set support, server host sorting, etc.How do these UDFs workUsing both MySQL UDF API and libmemcachedInstallationObtain source Configure, compile, and install code Install functions SQL install script Perl installation utility Post installation check Obtain source https// http// 1/download/memcached_functions_mysql-1.1.tar.gzConfigure, compile and install the codeRun configure, make, make install./configure --with- mysql/usr/local/mysql/bin/mysql_config -- libdir/usr/local/mysql/lib/mysql/plugin/makemake installInstall the functionsUsing SQL filemysql -uroot -ps3kr1t select name, dl from mysql.func where name like memc;Using the UDFsConnection Verifying the connection Setting values Getting/fetching values Increment/Decrement Behavioral functions Statistical functions Utility/Version functionsConnection mysql select memc_servers_set127.0.0.1 11211; ------------------------------------- | memc_servers_set127.0.0.111211 | ------------------------------------- | 0 | -------------------------------------Connection mysql select memc_servers_set127.0.0.1 11211, 127.0.0.1112112, 127.0.0.1112113, 127.0.0.1112114, 10.20.0.24611211;Verifying the connectionmysql select memc_server_count; --------------------- | memc_server_count | --------------------- | 5 | ---------------------Setting values mysql select memc_setmy_key, somue; --------------------------------- | memc_setmy_key, somue | --------------------------------- | 0 | ---------------------------------mysql select memc_set_by_keymaster1, my_key, somue; --------------------------------------------------- | memc_set_by_keymaster1, my_key, somue | --------------------------------------------------- | 0 | ---------------------------------------------------CAS - check and switchCAS is a check and set operation which means “store this data but only if no one else has updated since I last fetched it.“mysql select memc_sett1, initial value; --------------------------------- | memc_sett1, initial value | --------------------------------- | 1 | --------------------------------- mysql select memc_gett1 as t1_value, memc_get_cast1 as t1_cas; ----------------------- | t1_value | t1_cas | ----------------------- | initial value | 1 | -----------------------CAS - check and setCAS continuedmysql select memc_cast1, new value, 1; -------------------------------- | memc_cast1, new value, 1 | -------------------------------- | 1 | -------------------------------- mysql select memc_gett1 as t1_value, memc_get_cast1 as t1_cas; ------------------- | t1_value | t1_cas | ------------------- | new value | 2 | -------------------CAS - check and set contin another MySQL client windowmysql select memc_sett1, someone else changed it; ------------------------------------------- | memc_sett1, someone else changed it | ------------------------------------------- | 1 | -------------------------------------------CAS - check and set contmysql select memc_cast1, new value, 1; -------------------------------- | memc_cast1, new value, 1 | -------------------------------- | 0 | -------------------------------- mysql select memc_gett1 as t1_value, memc_get_cast1 as t1_cas; --------------------------------- | t1_value | t1_cas | --------------------------------- | someone else changed it | 3 | ---------------------------------Prepend, appendselect memc_setfoo1, this value ;select memc_prependfoo1, before;select memc_appendfoo1, after;Getting/fetching values mysql select memc_getfoo1; ------------------------- | memc_getfoo1 | ------------------------- | before this value after | -------------------------Incrementing valuesselect memc_setcounter, 0; select memc_incrementcounter, 1; select memc_incrementcounter, 1; select memc_incrementcounter, 20;select memc_decrementcounter; select memc_decrementcounter, 5; ...Behavioral functionsmysql select memc_list_behaviorsG *************************** 1. row *************************** memc_list_behaviors MEMCACHED SERVER BEHAVIORS MEMCACHED_BEHAVIOR_SUPPORT_CAS MEMCACHED_BEHAVIOR_NO_BLOCK MEMCACHED_BEHAVIOR_TCP_NODELAY MEMCACHED_BEHAVIOR_HASH MEMCACHED_BEHAVIOR_CACHE_LOOKUPS MEMCACHED_BEHAVIOR_SOCKET_SEND_SIZE MEMCACHED_BEHAVIOR_SOCKET_RECV_SIZE MEMCACHED_BEHAVIOR_BUFFER_REQUESTS MEMCACHED_BEHAVIOR_KETAMA MEMCACHED_BEHAVIOR_POLL_TIMEOUT MEMCACHED_BEHAVIOR_RETRY_TIMEOUT MEMCACHED_BEHAVIOR_DISTRIBUTION MEMCACHED_BEHAVIOR_BUFFER_REQUESTS ...Setting a behaviormysql select memc_servers_behavior_set MEMCACHED_BEHAVIOR_TCP_NODELAY, 1; ---------------------------------------------------------------- | memc_servers_behavior_setMEMCACHED_BEHAVIOR_TCP_NODELAY, 1 | ---------------------------------------------------------------- | 0 | ----------------------------------------------------------------Distribution typesThis function allows you see the different means of distributing values to servers for use with memc_behavior_setmysql select memc_list_distribution_typesG *************************** 1. row *************************** memc_list_distribution_types MEMACHED_DISTRIBUTION_MODULA MEMCACHED_DISTRIBUTION_CONSISTENT MEMCACHED_DISTRIBUTION_KETAMAmysql select memc_servers_behavior_get MEMCACHED_BEHAVIOR_DISTRIBUTION; -------------------------------------------------------------- | memc_servers_behavior_getMEMCACHED_BEHAVIOR_DISTRIBUTION | -------------------------------------------------------------- | MEMCACHED_DISTRIBUTION_MODULA | --------------------------------------------------------------Hashing typesmysql select memc_list_hash_typesG *************************** 1. row *************************** memc_list_hash_types MEMCACHED_HASH_DEFAULT MEMCACHED_HASH_MD5 MEMCACHED_HASH_CRC MEMCACHED_HASH_FNV1_64 MEMCACHED_HASH_FNV1A_64 MEMCACHED_HASH_FNV1_32 MEMCACHED_HASH_FNV1A_32 MEMCACHED_HASH_JENKINS MEMCACHED_HASH_HSIHashing typesmysql select memc_servers_behavior_getMEMCACHED_BEHAVIOR_HASH; ------------------------------------------------------ | memc_servers_behavior_getMEMCACHED_BEHAVIOR_HASH | ------------------------------------------------------ | MEMCACHED_HASH_DEFAULT | ------------------------------------------------------ 1 row in set 0.00 secmysql select memc_servers_behavior_setMEMCACHED_BEHAVIOR_HASH, MEMCACHED_HASH_DEFAULT;Statistical functionsselect memc_stats127.0.0.111214;Server 127.0.0.1 11214pid 15470uptime 1634time 1240205306version 1.2.6pointer_size 64rusage_user 0.15997rusage_system 0.23996curr_items 8total_items 8bytes 640curr_connections 4total_connections 27connection_structures 5...Statistical functionsmysql select memc_stat_get_keys;mysql select memc_stat_get_value127.0.0.111214, total_items; ------------------------------------------------------- | memc_stat_get_value127.0.0.111214, total_items | ------------------------------------------------------- | 8 | -------------------------------------------------------Version info mysql select memc_udf_version; -------------------- | memc_udf_version | -------------------- | 0.9 | --------------------mysql select memc_server_version127.0.0.111212mysql select memc_libmemcached_version; ----------------------------- | memc_libmemcached_version | ----------------------------- | 0.27 | -----------------------------by_key functionsmysql select memc_set_by_keyserverA, t1, this key; ---------------------------------------------- | memc_set_by_keyserverA, t1, this key | ---------------------------------------------- | 1 | ----------------------------------------------mysql select memc_get_by_keyserverA, t1; ---------------------------------- | memc_get_by_keyserverA, t1 | ---------------------------------- | this key | ----------------------------------Application * Triggers see sql/trigger_fun.sql * Stored procedures * You can use these with DBI programs, storing Perl objects using storable * Aggregation in select * Anything you can think ofSerialization setsub memc_set my this, key, value _;my sth this-dbh-prepare_cached SELECT memc_set, ;my storable_value ref value nfreezevalue value;sth-cutekey, storable_value;my stored sth-fetchrow_arrayref; returns 0 on success, greater than 0 errorreturn stored-0; Serialization getsub memc_get my this, key _;my de_serialized;my sth this-dbh-prepare_cachedSELECT memc_get ;sth-cutekey;my stored sth-fetchrow_arrayref; will be 1 or 0if defined stored-0 de_serialized thawstored-0;return stored-0 de_serialized;else return undef; Using with other UDFs GearmanDELIMITER | DROP TRIGGER IF EXISTS urls_stored_insert | CREATE TRIGGER urls_stored_insert BEFORE INSERT ON urls_stored FOR EACH ROW BEGINSET count memc_incrementurls_stored;SET index_count memc_incrementindex_counter;IF count 5 THENSET gd gman_servers_set127.0.0.14730;SET gd gman_do_backgroundurl_process, NEW.url;SET reset memc_seturls_stored, 0;END IF;IF index_count 20 THENSET gd gman_servers_set127.0.0.14730;SET gd gman_do_backgroundindr, NEW.url;SET reset memc_setindex_counter, 0;END IF; END |Gearman exampleShameless plugsDeveloping Web Applications with Apache, MySQL, memcached and Perl ISBN 978- 0470414644Expert PHP and MySQL ISBN 978-0470563120 memcached.org