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 fairlystraightforward. 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:
Roughly, the amount of memory mysql uses is defined by a fairlysimple formula: query_cache + key_buffer + max_connections * (otherbuffers). For a low volume site, query cache and key buffer are goingto be the most important things, but for a larger site, you’re going toneed to look at other things. Additionally, using the key buffer andthe query cache are AMAZING performance increasers. I’m only showingyou how to lower the amount of ram MySQL uses for if you’re trying torun a few smaller sites that don’t store hundreds of megs of data.
Things We Can Disable
First off, InnoDBrequires about 10 megs of memory to run, so disable it. You shouldn’tneed it if you’re going small. For those unfamilar, innodb is adifferent storage engine within mysql that you can use. It supportstransactions and most importantly (to me, at least), row level locking.It’s a little bit slower than MyISAM, but it can greatly improveperformance later. Basic example: changing a table in a MyISAM tablelocks the entire table. You can’t do any selects while you’reinserting. If you’re inserting a lot, this can be a problem. InnoDBlets you insert or update a row while still performing selects. Itlocks 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 alternativeto InnoDB) and NDB, MySQL’s clustering database. Do this with“skip-bdb” and “skip-ndbcluster” I haven’t noticed skipping BDB and NDBto 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” Ihaven’t noticed this lower my RAM utilization, but if you’re notaccessing mysql from a remote server, you should use the local unixsocket to get better performance as well as better security. If youdon’t have mysql listening on a TCP port, then you’re a lot less likelyto get hacked. Also, for those of you who might be worried about havingto configure PHP to connect to MySQL on the local socket, if youspecify localhost as your hostname in mysql_connect() in php, itautomatically 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_sizeis 4GB. The effective maximum size might be less, depending on youravailable physical RAM and per-process RAM limits imposed by youroperating system or hardware platform.
Increase the value to get better indexhandling (for all reads and multiple writes) to as much as you canafford. Using a value that is 25% of total memory on a machine thatmainly runs MySQL is quite common. However, if you make the value toolarge (for example, more than 50% of your total memory) your systemmight start to page and become extremely slow. MySQL relies on theoperating system to perform filesystem caching for data reads, so youmust leave some room for the filesystem cache. Consider also the memoryrequirements of other storage engines.
In other words, MySQL tries to put everything that’s indexed intothe key buffer. This is a huge performance speedup. If you can getevery table column in a specific selectstatement to be indexed, and your entire index fits into the keybuffer, the SQL statement in question will be served directly from RAM.It’s possible to take that kind of optimization overboard, but if youare 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 onlyyou know how much ram you have free. However, you can probably get bywith 2-3 megs here, bigger if you need it. If you want to play MySQLMemory Limbo (how low can you go!), you can look and see how much yourkey buffer is being used. Essentially, you’ll need to write a querythat 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. Afterrestarting mysql, let your site run a while and have time to fill upthe key buffer (assuming it’s live. if not, simulate some use, first).Then, check the usage using the aforementioned equation. If you’rerunning below, say 0.8 or so, you can probably safely lower your keybuffer 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 reallyimportant for performance, marginally so for memory usage. In anutshell, every time you access a table, MySQL loads a reference to atable as one entry in the table cache. This is done for everyconcurrent access of a table. So, if you have 10 people accessing yourwebsite simultaneously, and each of them is accessing a page that doesa join across 3 tables, you’ll need to set your table cache to at least30. If you don’t, MySQL will refuse to perform queries.
You can keep upping the table cache, but you’ll eventually hit alimit on the number of files your operating system can have open, sokeep 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 timesmysqld has had to open a table. If this is low, you’re never having anycache misses. If your table_cache is set too low, you’ll havecache 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, hittingthe disk occasionally is probably better than paging a lot, so find abalance, lowering table_cache to the point where you’re not hitting thedisk 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 youdo the same query two times in a row, and the result fits in the querycache, mysql doesn’t have to do the query again. If you’re going forperformance, 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 helpyou much. Chances are, it will help, and there’s probably some benefitfor 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.
Maximum Number of Connections
This may or may not be a problem for you, but it’s one ofthe most important things for optimizing a mysql installation for highusage. If you’re already limiting the number of apache processes, thenyou’ll be fine. If you’re not, and you need to handle thousands ofusers simultaneously, you need to increase this number. It’s the numberof connections MySQL allows at once. If it’s not set high enough,you’ll get the dreaded, “too many connections” MySQL error, and yourusers won’t be happy. You want to keep this number in sync with the maxnumber of users apache allows, and you’ll need to budget extra ram forextra MySQL connections. See above for the rough formula used.
I’ll discuss a few more minor tweaks to MySQL in the next article, where I’ll discuss, among other things:
联系客服
