How can I speed up a site that is slow because a huge locales_source table?

Question

I'm working on a site that slows down to an unbearable crawl when you view it in a different language (specifically "zh-hans"). When looking at the Devel query log, this appears to be because this query takes about 5 minutes.

SELECT s.source, t.translation, t.language 
FROM locales_source s 
LEFT JOIN locales_target t 
  ON s.lid = t.lid 
  AND t.language = 'zh-hans' 
WHERE s.textgroup = 'default' 
  AND s.version = '6.25' 
  AND LENGTH(s.source) < 75

The reason it takes so long is that the locales_source table has almost 1 million rows, even though there are only 1 thousand nodes on the site. This seems like an extremely large amount to me. So my questions are:

1. Is this normal?
2. If not, how can I go about identifying which of the data in locales_source is not applicable or bad? I can't find the way to map it back to the original source (node body, title, menu item, etc.) so I'm not sure how to determine if the original source exists and is valid.

The locales_source table looks like this, if it helps.

Field	Type	Null	Key	Default	Extra
lid	int(11)	NO	PRI	NULL	auto_increment
location	varchar(255)	NO
textgroup	varchar(255)	NO	MUL	default
source	blob	NO	MUL	NULL
version	varchar(20)	NO		none

Answer 1

Yes, 1 million is an extremely high amount. That's not normal.

The amount of nodes should have nothing to do with that table, because content translations happens by creating separate nodes for each language that are linked together.

What is added to that table are all strings which are passed through t(), and everything that is registered through the various i18n modules (menus, terms, ...).

t() must not be used for user generated content but this is often done and I suspect that might be responsible for your amount of rows in that table. The problem with something like t($term->name) is that the identifier is the term name. If you change your original term name, then your translations are lost and the source entry will stay there forever. See below on how to identify problems here.

The interesting column to check where something is coming from is the location, there are 3 different possible types of values in there:

1. When imported from a .po file (e.g. official translations from localize.drupal.org), the location is filename:line. These are most likely ok and should be much much less than 1 million.
2. Translations managed by i18n_string have some sort of context/identifier in there, that looks like this: taxonomy:term:1:name. That allows to look up a translation based on a unique identifier of something that won't change. There could be many of these, if you have many taxonomy terms/huge menus but again, not 1 million (except you have 1 million or close terms). You can also identify them by the text group, case 1 and 3 is both textgroup default, while this has a textgroup for each source type (taxonomy, menus, ...).
3. Every string passed to t() that is not yet in that table is automatically added. In that case, the location is the URL of the page where this string first occured. Those will probably be the most interesting ones for you.

What you need to find out is which pages are adding these dynamic translations and then track down the exact module/call and fix it. There two ways to start. First, look at the most recent additions to that table to see what URL's you have in there (last page on the translate overview, or you can look directly in the database). The second option is group by location and then sort by count(), which might forever on a table of that size. But it's likely that a large amount of sources have been added on the same page. Once you know the page, it's time to do some profiling there and track down who exactly is calling t() often.

Two contrib module examples that I recently identified while doing a performance review on a site:

1. The site had two hierarchy/relation select boxes using hierarchical select, which currently falls back to t($term->name) if i18n_taxonomy is not installed. That was a free tagging vocabulary with 10k terms and this resulted in 40k calls to t(), which obviously also makes it extremely slow. This issue is still there, I need to find the time to provide a patch.
2. The other case was the print module, which "translated" the node title on the print page. I think this was recently fixed, but the already added sources will stay in that table.

Once you identified the problem, you need to fix it and then remove the existing strings from that table, e.g with a like 'taxonomy/term/edit/%' condition for the term edit pages. Just removing the strings can be useful to quickly check how it affects the site load time but they will get re-added as soon as the problematic code is executed again.

For the record, the site that I looked into had "only" 40k entries in that table but even that resulted in cache entry of the short translations of 10MB (in PHP, not serialized).