Optimizing Apache and MySQL for Low Memory Usage, Part 2

In Optimizing Apache and MySQL for Low Memory Usage, Part 1, I discussed some system tools and Apache optimization. I’ve also discussed mod_deflate, thttpd, and lighttpd in Serving Javascript and Images — Fast. Now, i’ll talk about MySQL.

Tweaking MySQL to use small amounts of memory is fairly straightforward. You just have to know what to tweak to get the most “bank for your buck,” so to speak. I’m going to try to show you the why instead of the what, so you can hopefully tweak things for your specific server.
We’ll look at the following MySQL types of mysql settings:

• Things We Can Disable
• The Key Buffer
• The Table Cache
• The Query Cache
• Max Connections

Roughly, the amount of memory mysql uses is defined by a fairly simple formula: query_cache + key_buffer + max_connections * (other buffers). For a low volume site, query cache and key buffer are going to be the most important things, but for a larger site, you’re going to need to look at other things. Additionally, using the key buffer and the query cache are AMAZING performance increasers. I’m only showing you how to lower the amount of ram MySQL uses for if you’re trying to run a few smaller sites that don’t store hundreds of megs of data.

Things We Can Disable

First off, InnoDB requires about 10 megs of memory to run, so disable it. You shouldn’t need it if you’re going small. For those unfamilar, innodb is a different storage engine within mysql that you can use. It supports transactions and most importantly (to me, at least), row level locking. It’s a little bit slower than MyISAM, but it can greatly improve performance later. Basic example: changing a table in a MyISAM table locks the entire table. You can’t do any selects while you’re inserting. If you’re inserting a lot, this can be a problem. InnoDB lets you insert or update a row while still performing selects. It locks just the rows you’re working with, rather than the whole table.

You can disable InnoDB with “skip-innodb”

You can also disable BDB (berkely database, a deprecated alternative to InnoDB) and NDB, MySQL’s clustering database. Do this with “skip-bdb” and “skip-ndbcluster” I haven’t noticed skipping BDB and NDB to reduce memory much, but if you’re not using them, it can’t hurt.

The last thing you can skip is networking, with “skip-networking” I haven’t noticed this lower my RAM utilization, but if you’re not accessing mysql from a remote server, you should use the local unix socket to get better performance as well as better security. If you don’t have mysql listening on a TCP port, then you’re a lot less likely to get hacked. Also, for those of you who might be worried about having to configure PHP to connect to MySQL on the local socket, if you specify localhost as your hostname in mysql_connect() in php, it automatically uses the local unix socket, so there’s no need to worry.

The Key Buffer

This is probably the single most important thing you can tweak to influence MySQL memory usage and performance. The MySQL Reference Manual says about the key buffer:

Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

The maximum allowable setting for key_buffer_size is 4GB. The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform.

Increase the value to get better index handling (for all reads and multiple writes) to as much as you can afford. Using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common. However, if you make the value too large (for example, more than 50% of your total memory) your system might start to page and become extremely slow. MySQL relies on the operating system to perform filesystem caching for data reads, so you must leave some room for the filesystem cache. Consider also the memory requirements of other storage engines.

In other words, MySQL tries to put everything that’s indexed into the key buffer. This is a huge performance speedup. If you can get every table column in a specific select statement to be indexed, and your entire index fits into the key buffer, the SQL statement in question will be served directly from RAM. It’s possible to take that kind of optimization overboard, but if you are going for speed (not memory), that’s one way to do it.

I can’t say what size you should make your key buffer, because only you know how much ram you have free. However, you can probably get by with 2-3 megs here, bigger if you need it. If you want to play MySQL Memory Limbo (how low can you go!), you can look and see how much your key buffer is being used. Essentially, you’ll need to write a query that uses the SHOW syntax and uses the following equation:

1 – ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)

This yields the percentage of the key buffer in use. After restarting mysql, let your site run a while and have time to fill up the key buffer (assuming it’s live. if not, simulate some use, first). Then, check the usage using the aforementioned equation. If you’re running below, say 0.8 or so, you can probably safely lower your key buffer size.

The Table Cache

MySQL seems to think that this one is the second most important thing to tweak, and it is. However, it’s really important for performance, marginally so for memory usage. In a nutshell, every time you access a table, MySQL loads a reference to a table as one entry in the table cache. This is done for every concurrent access of a table. So, if you have 10 people accessing your website simultaneously, and each of them is accessing a page that does a join across 3 tables, you’ll need to set your table cache to at least 30. If you don’t, MySQL will refuse to perform queries.

You can keep upping the table cache, but you’ll eventually hit a limit on the number of files your operating system can have open, so keep that in mind.

If you have table_cache set a little bit low, you’ll see the “opened_tables” server variable be high. It’s the number of times mysqld has had to open a table. If this is low, you’re never having any cache misses. If your table_cache is set too low, you’ll have cache misses, and you’ll hit the disk. If table cache is set TOO low, mysql will barf on you, and you don’t want that. In summary, hitting the disk occasionally is probably better than paging a lot, so find a balance, lowering table_cache to the point where you’re not hitting the disk on every query and also not using up memory unnecessarily.

The Query Cache
The Query Cache is essentially a mapping of queries to results. If you do the same query two times in a row, and the result fits in the query cache, mysql doesn’t have to do the query again. If you’re going for performance, this can be a huge benefit, but it can also eat up memory. If you’re not doing a lot of the same query, this probably won’t help you much. Chances are, it will help, and there’s probably some benefit for having a 500-1000 kb of query cache, even on a tight memory budget. There are three variables that influence how the query cache works.

• query_cache_size – This is the total size of the query cache. This much memory will be used for storing the results of queries. You must allocate at least 40k to this before you get any benefit. There’s a 40k data structure overhead, so if you allocate 41k, it “works,” but you don’t have much space to actually get anything done.
• query_cache_limit – This is the maximum size of an individual query that is cachable. If you have a 10 megabyte query cache, and a 1 megabyte query cache limit, you can have at least 10 one-megabyte queries cached. This is extremely useful to prevent big queries from busting your cache. Precise benchmarking probably will help you decide what’s best. Use your judgement here.
• query_cache_type – Here, you can turn the query cache totally on or off. Also, if you want to get really sophisticated, you can turn it on or off — but enable or disable it for specific queries. If you want it to default on, leave it on, and disable the query cache for specific queries with something like, “select sql_no_cache * from table” — Alternatively, if you want it to default OFF, set query_cache_type to “2” or “DEMAND” and write queries that look like “select sql_cache * from table”

Maximum Number of Connections