memcache

memcache :: memcached is a simple, highly scalable key-based cache that stores data and objects wherever dedicated or spare RAM is available for quick access by applications, without going through layers of parsing or disk I/O. To use, you run the memcached command on one or more hosts and then use the shared cache to store objects.

2 types:
  - InnoDB memcached  
  - Traditional memcached  
 
Default Port Used: 11211 

InnoDB Memcached plugin :: The InnoDB memcached plugin (daemon_memcached) provides an integrated memcached daemon that automatically stores and retrieves data from InnoDB tables, turning the MySQL server into a fast “key-value store”. Instead of formulating queries in SQL, you can use simple get, set, and incr operations that avoid the performance overhead associated with SQL parsing and constructing a query optimization plan. You can also access the same InnoDB tables through SQL for convenience, complex queries, bulk operations, and other strengths of traditional database software.

This “NoSQL-style” interface uses the memcached API to speed up database operations, letting InnoDB handle memory caching using its buffer pool mechanism. Data modified through memcached operations such as add, set, and incr are stored to disk, in InnoDB tables. The combination of memcached simplicity and InnoDB reliability and consistency provides users with the best of both worlds.

Advantages:
 - Direct access to the InnoDB storage engine avoids the parsing and planning overhead of SQL.
 - Running memcached in the same process space as the MySQL server avoids the network overhead of passing requests back and forth.
 - Data requested through the memcached protocol is transparently queried from an InnoDB table, without going through the MySQL SQL layer.
 - Data is stored in a MySQL database to protect against crashes, outages, and corruption.
 - You can ensure high availability by using the daemon_memcached plugin on a master server in combination with MySQL replication.

Note: The InnoDB memcached plugin implements memcached as a MySQL plugin daemon that accesses the InnoDB storage engine directly, bypassing the MySQL SQL layer.

Features:
 - memcached as a daemon plugin of mysqld. Both mysqld and memcached run in the same process space, with very low latency access to data.
 - Control over how often data is passed back and forth between InnoDB and memcached operations through the innodb_api_bk_commit_interval, daemon_memcached_r_batch_size, and daemon_memcached_w_batch_size configuration options.
 - Direct access to InnoDB tables, bypassing the SQL parser, the optimizer, and even the Handler API layer.

Parameters:
 daemon_memcached_w_batch_size=25     //Specifies how many memcached write operations, such as add, set, or incr, to perform before doing a COMMIT to start a new transaction.

 daemon_memcached_option='-v -m 1024'   //Used to pass space-separated memcached options to the underlying memcached memory object caching daemon on startup.

 innodb_api_enable_binlog=1    //To use the daemon_memcached plugin with the MySQL binary log

Steps:
  1> Configure the daemon_memcached plugin so it can interact with InnoDB tables by running the innodb_memcached_config.sql configuration script, which is located in MYSQL_HOME/share. This script installs the innodb_memcache database with three required tables (cache_policies, config_options, and containers). It also installs the demo_test sample table in the test database. It will create:
    - Database : innodb_memcache
    - Tables : cache_policies, config_options, containers
   
  2> Alter table - Add columns:
    - c3 : int(11) NULL comment 'Must exists col for flag in memcahe req'
    - c4 : bigint(20) unsigned  NULL comment 'Must exists col for compare and swap in memcahe req'
    - c5 : int(11) NULL comment 'Must exists col for expire timestamp in memcahe req'
   
  3> Add index/FK to the key_value column.
 
  4> Add entry of the table into innodb_memcache.containers
    - insert ignore into innodb_memcache.containers(name,db_schema,db_table,key_columns,value_columns,flags,cas_column,expire_time_column,unique_idx_name_on_key) values ('env_pd','database_name','table_name','key_column','value_columns', c3,c4,c5,'PRIMARY');commit;"
    Note: value_columns -> deviceId|statusTS|deviceTS
   
  5> Activate the daemon_memcached plugin by running the INSTALL PLUGIN statement: 
     mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
     Note: mysql -u$DB_USER -pDB_PASSWD -e"install plugin daemon_memcached soname \"libmemcached.so\";"
   
  6> Restart DB and check for:
     memcachedRunning=`netstat -tap | grep -i memcache | wc -l`
   
Note:
   1> To uninstall
      mysql> uninstall plugin daemon_memcached;   
   2> To check if memcache installed or not 
      memcachedInstalled=`mysql -u$DB_USER -e "show plugins;" | grep -i  libmemcached.so | wc -l`  
 
References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-intro.html
http://dev.mysql.com/doc/refman/5.7/en/ha-memcached-using-deployment.html