Category Archives: About MySql Proxy Cache

Features

  • MySqlProxyCache (MPC) is put in front of the MySql Server, between the Clients (like PHP websites) and the MySql Server
  • MPC analizes the Queries and transparently caches the queries SELECT
  • MPC detects INSERT/UPDATE/DELETE… and what are the affected objects and transparently removes Queries that were using those objects and so are no longer valid, from the cache
  • As a result, you can avoid using imprecise TTLs and no need to implement other caches in Code. The MPC always knows which data is valid
  • MPC uses a dynamic memory model. Unlike other Softwares that assign a fixed block size for the cached data (like 1 MB per query) and they waste a lot of memory, MPC dynamically stores the required memory for the results, no bytes are wasted, and no results are kept out because being too larger
  • When MPC analyzes the queries it detects the use of real time functions and Server vaiables in SELECTs like Date/Time functions (NOW(), DAY, YEAR(), SYSDATE(),TIMESTAMP()…), functions like UUID() and other uses like LAST_INSERT_ID, CONNECTION_ID and those Queries are never cached.
  • Queries using User-Defined variables and PREPARED STATEMENTS are detected and not cached.
  • All these analysis is also applied to JOINS
  • It allows to Debug and see all the conversation between the Client side and the MySql Server side.
  • Queries can be optionally written to a log file
  • MPC can be used as a Bridge between different networks
  • MPC is tested to work fast with popular CMS like WordPress, Drupal, etc…
  • MPC is compatible with MySql and derived forks
  • It detects queries that are the same with minor differences. Repeating queries is very usual and this feature saves you many hours of development.

Examples of queries that are detected as the same:

SELECT * FROM usersbase
SELECT * FROM usersbase;
SELECT  * FROM  `usersbase`;
select * from usersbase;
SELECT
      *
FROM
      usersbase
SELECT *
    FROM
USERBASE

MySqlProxyCache

MySql Proxy Cache (MPC) is a Software that runs between the Client (PHP, Java, crons, a MYSQL Client or any other Software using MySql) and the MySql Server and when there is a SELECT it transparently catches in memory the result, so posterior queries will be served from Memory without disturbing the MySql Server.

When a write query like INSERT, UPDATE, DELETE… appears if┬áthat affects the results cached the MPC transparently clears those cached queries affected.

This approach is a quick win for projects that are overloaded, or for projects where adding cache in the code is not possible or will costs many thousands of dollars in time of the programmers.

This approach is much better than the traditional putting cache in the Web Server’s code, because the cache on the Web side has a TTL (Time to Life) because is not possible to know when the data will be not valid because the database was updated.

As MySql Proxy Cache is the main listener for the connections, it always knows when an UPDATE to the data cached has been performed, and then it frees those results from the memory automatically.

Another good scenario is when your code has complex Queries with JOINS that take seconds or minutes to be responded from the MySql Server or Queries that are very inefficient or looking without indexes. MySql is efficient caching simple queries, but it is not caching JOINS. A JOIN that takes 30 seconds each time to be calculated from the MySql can be served in 0 seconds from the MySql Proxy Cache once has been cached. The MPC will automatically discover when data used by the cached JOIN has changed and remove from the Memory.

For CVS that do many queries for each request, like Drupal, WordPress, Joomla, etcetera this can turn sides, from having a very slow project that does not scale, to having a super-fast site, just in seconds without incurring in any additional Hardware or Coding investment.

For your Company this will save a lot of computing time, and a lot of money and headaches.