MySQL 5.6 memcached Support

[Marcus]

Does anyone use the new memcache support for existing innodb tables?

I guess it's more straight forward to setup memcache within config.php, but maybe there are other core xenforo tables that would benefit from memcache?

This is from the documentation https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html

14.18 InnoDB Integration with memcached
14.18.1 Benefits of the InnoDB / memcached Combination
14.18.2 Architecture of InnoDB and memcached Integration
14.18.3 Getting Started with InnoDB Memcached Plugin
14.18.4 Security Considerations for the InnoDB memcached Plugin
14.18.5 Writing Applications for the InnoDB memcached Interface
14.18.6 Using the InnoDB memcached Plugin with Replication
14.18.7 Internals of the InnoDB memcached Plugin
14.18.8 Troubleshooting the InnoDB memcached Plugin
buffer pool mechanism. Data modified through memcached operations such as ADD,SET, INCR are stored to disk, using InnoDB mechanisms such as change buffering, the doublewrite buffer, andcrash recovery. The combination of memcached simplicity and InnoDB reliability and consistency provides users with the best of both worlds, as explained in Section 14.18.1, “Benefits of the InnoDB / memcached Combination”. For architectural details about how the components fit together, see Section 14.18.2, “Architecture of InnoDB and memcached Integration”.

 

[Marcus]

I found out that the innodb integration with memcached is a way to communicate with the innodb database 1. without the sql layer and 2. in the memcache language. It is not a connection between mysql and a real memcache server. Instead it is the innodb database that "speaks" memcache language for a specific table mentioned in the mysql configuration (like "xf_session") and therefore is a lot faster as the memcache language is far less complex than SQL. This approach is a bit slower than directly connecting to a real memcache server. The xf_session table would have to be added along with the key and value to the innodb-memcache configuration table.

These are the benefits https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-benefits.html

14.18.1 Benefits of the InnoDB / memcached Combination
This section outlines advantages of the memcached interface to InnoDB tables introduced in Section 14.18, “InnoDB Integration with memcached”. The combination of InnoDB tables and memcached offers advantages over using either by themselves:

• 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 that is written using the memcached protocol is transparently written to an InnoDB table, without going through the MySQL SQL layer. You can control the frequency of writes to achieve higher raw performance when updating non-critical data.
  
  
• Data that is requested through the memcached protocol is transparently queried from an InnoDB table, without going through the MySQL SQL layer.
  
  
• Subsequent requests for the same data is served from the InnoDB buffer pool. The buffer pool handles the in-memory caching. You can tune the performance of data-intensive operations using the familiar InnoDBconfiguration options.
  
  
• Data can be unstructured or structured, depending on the type of application. You can make an all-new table for the data, or map the NoSQL-style processing to one or more existing tables.
  
  
• InnoDB can handle composing and decomposing multiple column values into a single memcached item value, reducing the amount of string parsing and concatenation required in your application. For example, you might store a string value 2|4|6|8 in the memcached cache, and InnoDB splits that value based on a separator character, then stores the result into four numeric columns.
  
  
• The transfer between memory and disk is handled automatically, simplifying application logic.
  
  
• Data is stored in a MySQL database to protect against crashes, outages, and corruption.
  
  
• You can still access the underlying table through SQL, for reporting, analysis, ad hoc queries, bulk loading, multi-step transactional computations, set operations such as union and intersection, and other operations well suited to the expressiveness and flexibility of SQL.
  
  
• You can ensure high availability of the NoSQL data by using this feature on a master server in combination with MySQL replication.

• The integration of memcached with MySQL provides a painless way to make the in-memory data persistent, so you can use it for more significant kinds of data. You can put more add, incr, and similar write operations into your application, without worrying that the data could disappear at any moment. You can stop and start thememcached server without losing updates made to the cached data. To guard against unexpected outages, you can take advantage of InnoDB crash recovery, replication, and backup procedures.
  
  
• The way InnoDB does fast primary key lookups is a natural fit for memcached single-item queries. The direct, low-level database access path used by the memcached plugin is much more efficient for key-value lookups than equivalent SQL queries.
  
  
• The serialization features of memcached, which can turn complex data structures, binary files, or even code blocks into storeable strings, offer a simple way to get such objects into a database.
  
  
• Because you can access the underlying data through SQL, you can produce reports, search or update across multiple keys, and call functions such as AVG() and MAX() on the memcached data. All of these operations are expensive or complicated with the standalone memcached.
  
  
• You do not need to manually load data into memcached at startup. As particular keys are requested by an application, the values are retrieved from the database automatically, and cached in memory using the InnoDBbuffer pool.
  
  
• Because memcached consumes relatively little CPU, and its memory footprint is easy to control, it can run comfortably alongside a MySQL instance on the same system.
  
  
• Because data consistency is enforced by the mechanisms used for regular InnoDB tables, you do not have to worry about stale memcached data or fallback logic to query the database in the case of a missing key.