Posted by meba on October 19, 2008 at 6:55pm
| Project: | Localization server |
| Version: | 6.x-2.x-dev |
| Component: | Performance |
| Category: | task |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Issue Summary
This issue is made to track performance tuning requirements for l10n server.
Overview: D.org has around 3500 contributed modules, counting 5.x and 6.x, it's around 5500 releases. They contain over 120 000 strings. We have 179 languages. If every language has one translation for every string, it makes around 22 millions of translations. Probably not every language will have all translations but many languages will have more suggestions and translations for one string.
More to come.
Comments
#1
I have made a special performance tuning database for everybody, who would like to help optimizing l10n server. This database is filled up with:
How to install
Because it's not easy to direct users how to install l10n server just to tune, I am providing a complete Drupal 5.11 tarball + database. Just untar, import your database and change settings.php to point to a new database. Administrator username is: admin, password is: admin
(Please note that due to my design error, all tables have 2_ prefix. But it's not a problem with Drupal API :)
What to expect
The User Interface for l10n_server is now fairly slow.
Download
Due to database dump size, I am providing download links here:
Enjoy!
#2
OK, first to go:
this is used on "Translate" screen:
SELECT COUNT(*) FROM 2_l10n_community_translation WHERE is_suggestion = 0 AND is_active = 1;
and takes around 5 seconds on my laptop.
After adding an index, it drops to 3 seconds.
Attaching an EXPLAIN. Small step, but that counts! (We will have to cache this anyway).
#3
This one would also use an index:
SELECT COUNT(DISTINCT uid_entered) FROM 2_l10n_community_translation GROUP BY uid_entered;
Attaching an output. From 7.52 secs to 2.96 secs.
#4
Great. There are certainly some obvious indexes not added. It is great to see that you are picking this up. Eagerly waiting for more :) This is critical for the successful drupal.org deployment of the module, so your name is praised :)
#5
Patch to go: #325721: Performance: introduce caching for Quick stats and Progress status
#6
And the winner is...
# Time: 081024 22:33:56
# User@Host: root[root] @ localhost []
# Query_time: 531 Lock_time: 0 Rows_sent: 2930 Rows_examined: 125442574
SELECT COUNT(DISTINCT t.sid) translation_count, r.pid, t.is_suggestion FROM 2_l10n_community_release r INNER JOIN 2_l10n_community_file f ON r.rid = f.rid INNER JOIN 2_l10n_community_line l ON f.fid = l.fid INNER JOIN 2_l10n_community_string s ON l.sid = s.sid LEFT JOIN 2_l10n_community_translation t ON s.sid = t.sid WHERE t.is_active = 1 AND t.translation != '' GROUP BY r.pid, t.is_suggestion;
:-)
#7
create index l10n_sid_language_is_suggestion ON 2_l10n_community_translation(sid,language,is_suggestion);Will 2x speed up Edit & View language screen. Still - too slow :-)
#8
Patch to go: #326423: Performance: add indexes to translations table
#9
Localization server D5 is not anymore available, so I close this here.
#10
Well, it applies to the 6.x version anyway. Would be great to see these formulated into a patch with an upgrade path.
#11
Would still be happy to see these in patch form :)
#12