|
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.
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 The maximum allowable setting for 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
Maximum Number of Connections This may or may not be a problem for you, but it’s one of
the most important things for optimizing a mysql installation for high
usage. If you’re already limiting the number of apache processes, then
you’ll be fine. If you’re not, and you need to handle thousands of
users simultaneously, you need to increase this number. It’s the number
of connections MySQL allows at once. If it’s not set high enough,
you’ll get the dreaded, “too many connections” MySQL error, and your
users won’t be happy. You want to keep this number in sync with the max
number of users apache allows, and you’ll need to budget extra ram for
extra MySQL connections. See above for the rough formula used.
|
|
|
