Notes on <High Performance MySQL> -- Ch10: Application-Level Optimization

Chapter 10: Application-Level Optimization

Application Performance Overview

Find the Source of the Problem

Add profiling capabilities to your application.

Look for Common Problems

-          What’s using the CPU, disk, network, and memory resources on each of the machine involved?

-          Is the application really using all the data it’s getting?

-          Is the application doing processing that ought to be done in the database, or vice versa?

-          Is the application doing too many queries?

-          Is the application doing too few queries?

-          Is the application connecting to MySQL unnecessary?

-          Is the application connecting too many times to the same MySQL instance, perhaps because different parts of the application open their own connections?

-          Is the application doing a lot of “garbage” queries?

-          Does the application use a connection pool?

-          Does the application use persistent connections?

-          Is the application holding connections open even when it’s not using them?

-           

Web Server Issues

Finding the Optimal Concurrency

For a CPU-bound workload, the optimal concurrency is equal to the number of CPUs (or CPU cores). However, processes are not always runnable, because they make blocking calls such as I/O, database queries, and network requests. Therefore, the optimal concurrency is usually higher than the number of CPUs.

Caching

You can think about caches in two broad categories: passive caches and active caches. Passive caches do nothing but store and return data. When you request something from a passive cache, either you get the result or the cache tells you “that doesn’t exist.” An example of a passive cache is memcached.

In contrast, an active cache does something when there’s a miss. It usually passes your request on to some other part of the application, which generates the requested result.  The active cache then stores the result and returns it. The Squid caching proxy server is an active cache.

Caching Below the Application

 

Application-Level Caching

An application-level cache typically stores data in memory on the same machine, or across the network in another machine’s memory.

-          Local caches

-          Local shared-memory caches

-          Distributed memory caches

The best-known example of a distributed memory cache is memcached. Distributed caches are much larger than local shared-memory caches and are easy to grow. Only one copy of each bit of cached data is created.

-          On-disk caches

One very useful trick with on-disk caches and web servers is to use 404 error handlers to catch cache misses.

Cache Control Policies

-          TTL (time to live)

The cached object is stored with an expiration date. This invalidation policy is best for data that changes rarely or doesn’t have to be fresh.

-          Explicit invalidation

There are two variations of this policy: write-invalidate and write-update.

-          Invalidation on read

Instead of invalidating stale data when you change the source data from which it’s derived, you can store some information that lets you determine whether the data has expired when you read it from the cache.

Cache Object Hierarchies

 

Pre-generating Context

 Extending MySQL

Alternatives to MySQL