Project

General

Profile

Bug #743

Too slow

Added by Yannick Warnier over 9 years ago. Updated over 7 years ago.

Status:
New
Priority:
Urgent
Assignee:
-
Start date:
12/03/2010
Due date:
% Done:

0%

Estimated time:
Complexity:
Difficult

Description

The current translate.chamilo.org takes on average 20 seconds (not kidding) for us to load any page. It makes it impossible to use without going mad and fed up about translating. PLEASE do something about it in priority. I cannot imagine the quantity of lost efforts we are accumulating because people abandon translating...

I've been holding this criticism on, thinking this would improve when Stefaan said a "team of experts" was looking into this, but... come on... this is really unbearable. You've got to be kidding me if you think I think you can use that without it getting on your nerves :-)


Files

chamilo2_installer_error.png (50 KB) chamilo2_installer_error.png Ivan Tcholakov, 01/04/2010 22:30
queries_browse_language_packs.png (98.2 KB) queries_browse_language_packs.png Ivan Tcholakov, 12/04/2010 00:35
lang.zip (50.9 KB) lang.zip Ivan Tcholakov, 12/04/2010 01:01
a_missing_image.png (60.1 KB) a_missing_image.png Ivan Tcholakov, 01/07/2010 23:40

Related issues

Related to Common - Bug #893: Fattal error table_sortBug resolved02/04/2010

Actions

History

#1

Updated by Sven Vanpoucke over 9 years ago

I don't know exactly what is wrong here, but when I try to log in to the server and surf through some pages it only takes about 1 - 2 sec on average to load a page. We try to figure it out ASAP.

#2

Updated by Yannick Warnier over 9 years ago

Really? Are you surfing from the same network or something? Maybe we are put on a slow bandwidth rate for "racist" reasons or something :-)
In any case all of my employees are just passing through a very bad day when I ask them to add variables and translate them... and I am too as in the end I'm the one staying late to add these variables because they couldn't do it during the day. Maybe we should check the weight of each page or something...

Earlier Carlos wanted to approve a new translator and it took more than 180 seconds to just load the translators requests page... (that's what I mean by an average of 20 seconds).

#3

Updated by Sven Vanpoucke over 9 years ago

This is very very strange. The only problem that I'm currently having is that the pages where the progress bars are shown, are a bit slower then everything else. I really think this has more to do with a network/server setting then with the code.

#4

Updated by Hans De Bisschop over 9 years ago

Extremely weird. I would get frustrated too if the translator applications page took minutes to load. As Sven said we'll try to fix this as soon as possible.

#5

Updated by Yannick Warnier over 9 years ago

(16:35:21) Svennie:
hi ywarnier
(16:35:41) Svennie:
I would like to do some tests
(16:35:53) Svennie:
try to visit the translation page and tell me when it goes slow
(16:36:01) Svennie:
i'll check the database to see if it's a query / code that is getting stuck
(16:36:06) Svennie:
or if it's a connection problem
(16:37:18) yannoo:
ok
(16:37:55) yannoo:
just loading the homepage
(16:38:00) yannoo:
still waiting
(16:38:03) yannoo:
already 30 seconds
(16:38:08) Svennie:
no query that is hanging
(16:38:14) yannoo:
got the page
(16:38:25) yannoo:
note that since second 10, I was seeing the header
(16:38:27) Svennie:
i've loaded mine in 2sec
(16:38:32) Svennie:
ok
(16:38:38) yannoo:
then I waited for more than 20 seconds for the list of languages
(16:38:43) Svennie:
definatly a connection problem then
(16:39:00) Svennie:
checked the database every sec
(16:39:00) Svennie:
no query was hanging
(16:39:00) Svennie:
at all
(16:39:04) yannoo:
somewhere
(16:39:10) yannoo:
let me try another page
(16:39:51) yannoo:
got it
16:40
(16:40:04) Svennie:
no queries...
(16:40:08) yannoo:
loading the list of languages in the admin
(16:40:25) yannoo:
ok, that was a little faster (7.8 seconds following firebug)
(16:40:40) Svennie:
less then 2sec
(16:40:43) Svennie:
no queries
(16:40:43) Svennie:
;)
(16:40:57) yannoo:
took 6.5s to get to the variables list
(16:41:05) yannoo:
going back to homepage
(16:41:33) Svennie:
i'm doing the same actions as you do
(16:41:36) yannoo:
9s
(16:41:39) Svennie:
and i'm always finished before you say you are
(16:41:52) yannoo:
going to the translators queue > 17s
(16:41:54) Svennie:
seeying that we are on it at the same moment
(16:42:05) yannoo:
are we really? :-D
(16:42:07) Svennie:
i'm definatly sure that it's not a code / database problem
(16:42:09) yannoo:
I'm starting to doubt
(16:42:20) yannoo:
and it can't be a disk access problem either
(16:42:36) Svennie:
i don't think so
(16:42:41) Svennie:
since we don't have the problem
(16:42:42) yannoo:
www.chamilo.org is on the same server, right?
(16:42:45) Svennie:
correct
(16:42:55) yannoo:
let's try this one
(16:42:55) Svennie:
and so is demo2
(16:43:01) Svennie:
en only one person today complained about a slow
(16:43:16) yannoo:
loading the complete page in 5s (with account logged ing)
(16:43:18) yannoo:
in
(16:43:50) yannoo:
I'm clicking on IHM in the forum and it took me 3.2 seconds
(16:43:51) Svennie:
less then a second here
(16:44:02) Svennie:
much faster then the translation tool for me to
(16:44:02) yannoo:
I mean... much faster than translate
(16:44:05) yannoo:
yes
(16:44:17) Svennie:
i know the translation tool isn't 100% optimized
(16:44:26) Svennie:
but there is a difference between 2-3 sec and 20sec ofcourse :)
(16:44:41) yannoo:
yes, a big one
(16:44:53) yannoo:
and apparently the page is only 62KB
16:45
(16:45:11) yannoo:
but we have servers in Belgium (like support.chamilo.org) who are faster than that for us
(16:45:16) yannoo:
than translate
(16:45:22) yannoo:
not so much faster than www
(16:45:33) Svennie:
62kb is that for the translate
(16:45:35) Svennie:
or the www?
(16:45:41) yannoo:
translate
(16:45:51) Svennie:
ok
(16:46:02) yannoo:
is there any kind of back ping when connecting to translate?
(16:46:15) Svennie:
i have no idea, i'm not a server administrator i'm afraid :)
(16:46:22) yannoo:
something that would get a hard time getting news from Peru
(16:46:27) yannoo:
no, I mean in the PHP code
(16:46:36) Svennie:
then i don't think so
(16:46:42) yannoo:
ok
(16:47:03) yannoo:
but technically, it is the same server than www, so it should be at most 2 times slower
(16:47:10) yannoo:
not 10 times
(16:47:10) yannoo:
:-)
(16:47:18) Svennie:
indeed
(16:47:35) yannoo:
ok, let's leave it for now, I'll try to think about why that might be

#6

Updated by Ivan Tcholakov over 9 years ago

I think this slowliness hides within code, it does not look like a network problem. From my place some pages load quickly, but other don't. For example clicking on the "Translate" button leads to much waiting.

Unfortunately I have a presentation this Friday. After it I could be able to take this task and to do an attempt for performance improvement.

#7

Updated by Ivan Tcholakov over 9 years ago

I going to try.

#8

Updated by Hans De Bisschop over 9 years ago

Great! Do let us know if you have any questions ...

#9

Updated by Ivan Tcholakov over 9 years ago

1341:b1bd27142146 Task #743 - Trying to gain better performance - an improvement for the language translation class.
http://code.google.com/p/chamilo/source/detail?r=b1bd27142146f0b89f69d2de14d9314dd76b3102&repo=chamilo

#11

Updated by Ivan Tcholakov over 9 years ago

Attaching an image to the previous message.

1378:809e102ca443 Task #743 - Fixing an error in the installer script.
http://code.google.com/p/chamilo/source/detail?r=809e102ca443b361f2a1356d63f90f68a3813324&repo=chamilo

#12

Updated by Hans De Bisschop over 9 years ago

Sorry about that ... apparently forgot to rename the file, should be ok now. (it was a quick commit / push before catching the train home :p )

#13

Updated by Ivan Tcholakov over 9 years ago

No problem. :-)

1383:9115ffc85ee3 Task #743 - Optimizations for TableSort class.
http://code.google.com/p/chamilo/source/detail?r=9115ffc85ee334777128f0a6b5d81a1df2dbf8d7&repo=chamilo

#15

Updated by Ivan Tcholakov over 9 years ago

1390:92f32a1bfe3e Task #743 - A correction for PlatformSetting class: Avoiding useless clonning of a large array when get() method is called.
http://code.google.com/p/chamilo/source/detail?r=92f32a1bfe3e31b119ac8620cbf2cb917e384d13&repo=chamilo

#16

Updated by Ivan Tcholakov over 9 years ago

1394:8a42eb4be0fd Task #743 - A correction for LocalSetting class: Avoiding clonning of a large array when get() method is called.
http://code.google.com/p/chamilo/source/detail?r=8a42eb4be0fd032999554a8d4fc362f01b3ce625&repo=chamilo

#18

Updated by Ivan Tcholakov over 9 years ago

1418:2dbb6c5ae9c0 Task #743 - Various code optimizations for performance about Chamilo Translation Application.
http://code.google.com/p/chamilo/source/detail?r=2dbb6c5ae9c05e5e24bc1568562efe5ac1e23e38&repo=chamilo

#19

Updated by Ivan Tcholakov over 9 years ago

1419:52e4b71aa332 Task #743 - An optimization in Database class for avoiding multiple calculation of same boolean result based on $_SERVER['PHP_SELF'].
http://code.google.com/p/chamilo/source/detail?r=52e4b71aa332482173bc7388210752a703ff13da&repo=chamilo

#21

Updated by Ivan Tcholakov over 9 years ago

1427:1eb46b794d3c Task #743 - Some comments are added to remind that debugging features are to be disabled before software production release.
http://code.google.com/p/chamilo/source/detail?r=1eb46b794d3c7d88cdd5661d385e0b40dfa7420c&repo=chamilo

#22

Updated by Ivan Tcholakov over 9 years ago

1428:2d409ad20b98 Task #743 - Chamilo Translation Application, optimizations for showing the action icons and other things.
http://code.google.com/p/chamilo/source/detail?r=2d409ad20b98b9f39f0ec5ee55707e9127ac3c32&repo=chamilo

#24

Updated by Ivan Tcholakov over 9 years ago

1431:f4e20ba8749c Task #743 - Renaming constant occurences: Mail::FROM_NAME --> Mail::NAME ; Mail::FROM_EMAIL --> Mail::EMAIL .
http://code.google.com/p/chamilo/source/detail?r=f4e20ba8749c17b156cb38aac6501f48b1fa0240&repo=chamilo

#25

Updated by Ivan Tcholakov over 9 years ago

1432:ee62e39ca918 Task #743 - Optimization of the methods Display::get_progress_bar() and Display::get_rating_bar().
http://code.google.com/p/chamilo/source/detail?r=ee62e39ca9187ff4ae72f6970306f05eed58a7fb&repo=chamilo

Ok, I think this is for now what I am able to do about speeding. I am stopping making transactions here. For the next working day I have a request the code of the Chamilo Translation Application in the official server to be updated, so we to be able to test its speed.

A message to Hans De Bisschop: I received a letter from you, I am going write an answer.I can say here briefly: Don't worry, here I am applying some old tricks that exist in Chamilo 1.8.6.x or higher for long time.

#26

Updated by Hans De Bisschop over 9 years ago

Ivan,

Trust me, there's plenty of other things that worry / frustrate me a lot more right now, so you're in the green. That being said, things which may have been common practice or even downright necessary within the confines of the Chamilo 1.8.x architecture might not be desirable / necessary within Chamilo 2.x.

And please do take your time for the answer, after all I do think it's saturday (= weekend) in Bulgaria also ;-)

#27

Updated by Ivan Tcholakov over 9 years ago

I have just sent the answer.

You are right, there are some practices in Chamilo 1.8.x that does not fit for Chamilo 2.0, I know this.

Have a nice weekend, I am stopping working here.

:-)

#28

Updated by Ivan Tcholakov over 9 years ago

From the core libraries I squeezed what I can, but speed improvement in my installation is not good enough. I continued to digg in the application's code.

I found for sure the major reasons that lead to slow performance:

1. Data-aggregation from 2 (or more) tables is done not by the database server by using JOIN clause, but it is done on php-level. For every row of the showed table 1 (or more) SQL query is generated and run. This problem is not so visible in Chamilo 1.8.x, however in Chamilo there is database abstraction layer that makes the problem painful.

2. A special case of the above is to query for every shown row the same right for the same location for the same user.

3. Not optimized SQL queries like the following:

SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1202')
SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1203')
SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1204')
SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1205')
SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1206')
SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1207')
SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1208')
SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1209')
SELECT * FROM `cda_variable_translation` AS vaon WHERE (`language_id` IN ( SELECT cdge.`id` FROM cda_cda_language AS cdge WHERE cdge.`english_name` = 'english') AND vaon.`variable_id` = '1210')

Note: .... vaon.`variable_id` = '1210' The table contains 1213 rows for the 1.8.x package "admin" for every language.

I am preparing for tomorrow some quick corrections for the most heavy places.

#29

Updated by Ivan Tcholakov over 9 years ago

1435:4c71776418e9 Task #743 - SQL: Changing order of conditions (yet another trick in our typology). On my testing system, in this particular case the resulting SQL query runs ~5 times faster.
http://code.google.com/p/chamilo/source/detail?r=4c71776418e91ad1c3f20da1ba8964af60d1bfc5&repo=chamilo

1434:2611732df339 Task #743 - Removing conditions based on the obsolete "language" "english_org".
http://code.google.com/p/chamilo/source/detail?r=2611732df33909494287d126dc6596b4d5d35c8e&repo=chamilo

1433:2378d97b8a7f Task #743 - Caching the results of CdaRights::is_allowed_in_languages_subtree() for avoiding multiple database queries with same results.
http://code.google.com/p/chamilo/source/detail?r=2378d97b8a7fe50a8b09e36eec3513793886d8ca&repo=chamilo

One of my patches had a unwanted side-effect, so I did not commit it. Only these three transactions are added for now, the last one (1435) gives a significant "profit".

This is it, I am Ok for testing speed on the real server (http://translate.chamilo.org/).

#30

Updated by Ivan Tcholakov over 9 years ago

Sven, If you are not satisfied enough, maybe another way worths to be tried. Some MySQL ini settings on the server might be increased, for example this is what I did in my XAMPP my.ini file:

#sort_buffer_size = 512K
sort_buffer_size = 2M

#read_buffer_size = 256K
read_buffer_size = 512K

#read_rnd_buffer_size = 512K
read_rnd_buffer_size = 2M

#myisam_sort_buffer_size = 8M
myisam_sort_buffer_size = 16M

You may try other values.

#31

Updated by Stefaan Vanbillemont over 9 years ago

Ivan,

Those settings where already modified!

Kind regards,

Stefaan.

#32

Updated by Sven Vanpoucke over 9 years ago

I've done my best to improve the code, more indexes, more checks to my best. I saw some improvements in the overal speed. However the page where the language packs are loaded (the page after languages) is still very slow. I don't know how to fix this. Tried everything (indexes, joins, subselects, php loading) it is still as slow as it was first. I do know that it is a query problem because when you load the same page twice, the second time it goes very fast due to results beeing in cache already.

I installed this on demo2.chamilo.org with admin - admin as login data. I did this because of many many kernel changes in the chamilo 2 which results in a few problems when updating the actual translation tool (all the translators there statusses will be lost). The actual translations can be saved from the backup though. When we now for sure it is a good improvement over the old tool we can change to this version, but as you may understand because cda was built on top of chamilo 2 it isn't so easy to update the code after a month or so due to many many changes.

I would like you, who state it is very slow (like 10 - 15+ seconds) to test this the following days to see if ivan and my improvements were of any good.

Thanks in advance!

#33

Updated by Yannick Warnier over 9 years ago

Hello guys,

One common method used to improve relational database efficiency is to "de-normalize", that is to introduce duplication of data in some specific cases.

I haven't looked at the code nor the database structure recently, so my explanation might sound a bit abstract, sorry about that.

For example, the languages page would actually be deadly fast if it only had to show the list of languages (i.e. query only one table). The problem is it goes to the language table, then for each one it checks how many terms are currently translated to show the percentage.
This percentage is calculated every time (I guess), which generates a whole bunch of (unnecessary) queries. Instead, the language table could contain a total number of translated variables, making sure that every time a translation is added, the total number is updated.

I know, this breaks normalization, but don't go think all websites have normalized databases :-)
I believe this will greatly improve the efficiency of the languages list.

#34

Updated by Sven Vanpoucke over 9 years ago

Hello yannick i know exactly what you mean, and the fact is that the language table has been optimized this well that the language page in itself isn't a problem anymore. The problem lies within the combination of language with a language pack (which is visually only linked) so there is no way to store the translation progress of a certain language pack in a certain language. Please let me know how the system works for you guys now? Here in belgium we have most pages within 1-2 seconds except for the language-language pack page which takes about 4seconds.

#35

Updated by Yannick Warnier over 9 years ago

I'm currently translating a lot (must be at 150 terms already) to French and the loading time is between 3 and 6 seconds between each page, which is acceptable. It would be nice if it was faster, but I don't think that might improve much further from now.

This being said, having to go through all terms of the 2.0 is still a nightmare. Most of them are not even translated correctly to English, and they seem to never end. All I want is to translate the terms of 1.8 to French but playing with the distinct packages one by one is just a nightmare.
We've already asked to be allowed to use one specific package but I guess that's up to me to develop it and I have no time to do that now.

#36

Updated by Ivan Tcholakov over 9 years ago

Results from my place:

Site: http://dalton....... (you know)

Page: "Browse languages" (shows list of languages)
20 records:       ~1s
all (54) records: ~2s

Page: "Български" (selected language, shows a list of packages)
20 records:       ~2s
all (82) records: ~6s

Page: "Classic (1.8.x) - admin" (selected language pack, shows a list of translations)
20 records        ~1s
50 records        ~1.5s
#37

Updated by Ivan Tcholakov over 9 years ago

About the problematic page I did the following test:

1. I installed locally Chamilo Translation Application, the newest at the moment code 1645:779d82063f4f
2. I imported admin language pack only in Bulgarian and English.
3. In chamilo2/plugin/pear/MDB2.php, and at the beginning of the method query() I added the line

echo $query.'<br />';

so I can see the passed queries.

See the picture which shows the problematic page. Very heavy queries are constructed and passed, this makes this page to load slowly. I suspect the construct IN (... a long list of identificators ...).

#38

Updated by Ivan Tcholakov over 9 years ago

An additional observation: See the last condition in the WHERE clauses from the picture:

... WHERE ( ... AND vaon.`variable_id` IN ('1','2','3', ... ,'1229'));

I think, this last condition says "select them all", so it can be omited. It seems to me that there is php-code that constructs a filter and the case where actually there is no filter is not processed in the optimal way.

#39

Updated by Ivan Tcholakov over 9 years ago

For reproduction of this tes I am uploading here the zip-file with the language package "admin".

#40

Updated by Ivan Tcholakov over 9 years ago

These long queries are constructed together with the table as follows:
column "STATUS" - 1 query;
column "PROGRESS" - 2 queries;
the column with modification icons - 3 queries.
For every displayed row there are 6 very heavy queries to run.

#41

Updated by Ivan Tcholakov over 9 years ago

In some queries the tables "cda_variable_translation" and "cda_variable" should be joined so the field "language_pack_id" to be accessed. Making "joins" on the php-level, i.e. using methods like this one (see DatabaseCdaDataManager class)

private function retrieve_variables_from_language_pack($language_pack_id)

seems not to be a good solution.

This is from me.

#42

Updated by Sven Vanpoucke over 9 years ago

We changed it from joins / subselects to general IN queries, which made it a lot faster though. The real problem is that we need at least 6 queries for each row, to retrieve all the data. We could easily fix this by removing the progress bar and the quick translate button from that page to get it working again.

#43

Updated by Ivan Tcholakov about 9 years ago

After the recent code update of http://translate.chamilo.org/ I tried the site briefly. Forms are fast enough for me, I am satisfied. The only thing I saw is a missing image (see the attached file).

#44

Updated by Stefaan Vanbillemont over 8 years ago

  • Target version set to 2.1.0
#45

Updated by Stefaan Vanbillemont over 7 years ago

  • Target version changed from 2.1.0 to Backlog (default)

Also available in: Atom PDF