作者：Arlene Lee

如果您已经在网站上使用链接单元，也许会注意到一个小小的变化，当访问者点击链接单元标题之后进入广告页面，在页面的底部我们新增了一个图片广告。我们的测试显示，许多发布商的收入在此变更之后均有所提高。

这些图片广告会根据访客所点击的链接单元标题进行文字的精确匹配。如果所点选的标题暂时没有可匹配的图片广告，我们会仅显示原初的文字广告——我们不会显示公益广告或者是在广告位插入空白，请放心。广告主可以CPC或CPM的方式对图片广告进行出价，因此您的收入将会根据有效点击数或展示量进行结算。您可以从高级报告页面——链接单元报告来评估图片广告效果。

如果您尚未使用链接单元，不妨到此处一试，它不会占用太多空间却能提升广告收入。了解更多，请访问支援中心链接单元主题。

I vaguely recall a couple of blog posts recently asking something like "what's the formula to compute mysqld's worst-case maximum memory usage?" Various formulas are in wide use, but none of them is fully correct. Here's why: you can't write an equation for it.

The most popular equation, of course, is the one in the error log after a crash: "It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections". This was never true -- not even when ISAM and friends were the only storage engines, which was before most of us remember. And at a minimum, most servers these days need to consider the added memory for InnoDB: specifically, the buffer pool. (There's also an innodb_additional_mem_pool_size but it's typically set pretty small, like 20M).

But even that's not enough. Staying with InnoDB, we also need to count the data dictionary. This can take many gigabytes on servers with a lot of tables. We're trying to fix that, but it's unbounded in a non-Percona server. And InnoDB has other needs that it doesn't actually promise to fit within innodb_additional_mem_pool_size, by the way. If it needs more it will allocate it. innodb_additional_mem_pool_size is just a handy way to avoid repeated malloc() calls by doing it all up front in one call.

And what about the query cache and the table cache and the size needed for each thread (thread stack, etc)? Those take memory too. The query cache can use quite a bit if it's configured to. There are a bunch more global buffers to think about; that's just a partial list. And there are some "global buffers" that aren't really global. Suppose you set sort_buffer_size to 128K -- I can quite well connect and say "set @@sort_buffer_size := 1 * 1024 * 1024 * 1024;" and my sort_buffer_size is now 1GB. And a single query may cause multiple sort buffers to be allocated. You can't control that or calculate it at a global level.

But let's keep going! Any stored code (triggers, stored routines, etc) uses memory to execute; so do temporary tables. The following query creates at least 2 temporary tables:

You can have many of those per query. Sorting and grouping and DISTINCT and UNION may/will also create temp tables; the same applies to views and probably some other things I'm forgetting.

Let's not forget about the memory needed to parse and optimize and execute queries; mysqld creates internal structures for the query plan, for example, and keeps it around until the query is done. And there are prepared statements, which you can create and never deallocate.

Any more? Of course... I can also set a bunch of user variables -- they use memory too. And they can be really big (but no bigger than max_allowed_packet). And keys for user locks! I set my max_allowed_packet bigger and ran the following on my laptop:

Now I've got a 100M user variable and a 100M lock key. Note how long it took to run these statements! And now mysqld is suddenly using 324M of memory, up from 124M.

I'm sure you can think of other things I've forgotten here (the manual page has a bunch). My point is there simply is no formula to compute the maximum possible memory usage, and there cannot be because there are some things that have no defined limits.

It's not something that's worth worrying about much, in my opinion. I think it's far better to use a monitoring tool to watch the actual memory usage over time and see how it varies. When I'm initially setting up a my.cnf for a server, I usually calculate the Big Ones like the buffer pool, query cache etc; leave some memory for other needs; and see what happens. The results are workload-specific.

---

Entry posted by Baron Schwartz | 4 comments

Add to: | | | |

Update:Presentation: Behind the Scenes at MySpace.com. Dan Farino, Chief Systems Architect at MySpace shares details of some of MySpace's cool internal operations tools.

MySpace.com is one of the fastest growing site on the Internet with 65 million subscribers and 260,000 new users registering each day. Often criticized for poor performance, MySpace has had to tackle scalability issues few other sites have faced. How did they do it?

read more