Databases Reference
In-Depth Information
Therefore, to boost performance under heavy loads, you need to cache as much data as possible.
Memcached is a typical in-memory caching solution that works well with MySQL. Rows are cached
and served to clients exclusively from memory. When large amounts of memory — say over 32 GB —
is available, MySQL with Memcached works well to serve more than 400,000 queries per second.
These queries of course are primary key lookups and not ad-hoc joins or such. The assumption also
is that the entire relevant data set can fi t in memory and does not need to be read from the disk. Disk
I/O is very expensive as compared to in-memory access and can impose a large overhead.
Memcached was presented earlier in this topic as a viable key/value-pair store. Alternatives like
Membase, Redis, Tokyo Cabinet, and Kyoto Cabinet could be used in conjunction with MySQL to
achieve similar performance characteristics. This is a case where an RDBMS — MySQL — is used
effectively in conjunction with a NoSQL solution, such as Memcached to serve get lookups by primary
key. Figure 15-2 depicts a typical MySQL with the Memcached data store being accessed by a client.
MySQL
Client
SQL
layer
Memcached
Storage Engine
FIGURE 15-2
Using Memcached with MySQL is benefi cial but the architecture has its drawbacks:
Data is in-memory in two places: the storage engine buffer and Memcached.
➤
Replication of data between the storage engine and Memcached can have inconsistent states
of data.
The data is fetched into Memcached via the SQL layer and so the SQL overhead is still pres-
ent, even if it's minimized.
➤
➤
Memcached performance is superior only until all relevant data fi ts in memory. Disk I/O
overheads can be high and can make the system slow.
An alternative to using MySQL with Memcached is to bypass the SQL layer and get directly to the
storage engine. This is exactly what the HandlerSocket plugin for MySQL does. The HandlerSocket
plugin for MySQL is an open-source plugin that allows the bypassing of the SQL layer to access
➤
