Re: [Phpgroupware-developers] Performance tests

[Markus Kaemmerer]

Alex Borges <address@hidden> wrote:,

meanwhile I did some more performance tests. Now with the profiler of
the Zend IDE. I could not evaluate the phpEd profiler, because this
thing shows only 20 lines in evaluation mode.

Profiling is really easy with Zend: just hit one button in Internet
Explorer and read the results :)

To test the initialisation overhead I used the a nearly empty start
page from our sync module. It has only two lines of echo. 

Overview: 35% common_functions, 17% class.db_mysql, 9% class_common,
7% class.db 33% others

More data: 28% for session_::read_repositories(),
  25% for session_::setup_cache()
  21% preferences::read_repository
 19% preferences::standard_substitutes (+ 1% db.query)
  17% lang() ->translation::translate


Without any change my test of 200 times main page of sync lasts 25
seconds.

With some little optimizations (see patch) translate take now 8.7%
instead of 17% from the whole time. To drop this down, the design has
to be changed. Either call the SQL vor every lang() call and get only
the needed value (and cache this) or cache the lang-table in a shared
memory complete.

Some more values: the initialization of the folling objects takes % of
whole time (after my optimization)

  18.81% session_::verify()
  7.8%  createobject of sessions::sessions()
   7% creatobject of accounts
  6% createobject of perferences


Much time is used in this function:

/**
                * Is this also useless?? (skwashd)
                */
                function
read_repositories($cached='',$write_cache=True)

which has an really interessting comment :)

Later: acl->read_repository does read of acl without index in
phpgw_acl:

select * from phpgw_acl where (acl_account in (3, 0,1)) 

Solution: add index to acl_account)

This tracks down acl.read_repositories from 4.5% to 0.5% from the
whole running time.

At the end of my mail is a list with all queries that where executet
everytime a phpGW page is requested. As we can see, there are some
queries executed twice or can be optimized. 

E.g. hooks->hooks (which does a complete table query on all
phpgw_hooks) is called twices. Once from from read_repositories in
common.functions and once from read_repositories in class.sessions
(see function above). 




---------------

[28-Feb-2004 15:22:53] Query: select count(config_name) from
phpgw_config

[28-Feb-2004 15:22:53] Query: select config_value from phpgw_config
WHERE config_app='phpgwapi' and config_name='cache_phpgw_info'

[28-Feb-2004 15:22:53] Query: select content from phpgw_app_sessions
where sessionid = '0' and loginid = '0' and app = 'phpgwapi' and
location = 'config'

[28-Feb-2004 15:22:53] Query: select * from phpgw_config where
config_app='phpgwapi'

[28-Feb-2004 15:22:53] Query: SELECT hook_appname, hook_location,
hook_filename FROM phpgw_hooks

[28-Feb-2004 15:22:53] Query: SELECT account_id FROM phpgw_accounts
WHERE account_lid='demo'

[28-Feb-2004 15:22:53] Query: SELECT * FROM phpgw_accounts WHERE
account_id=3

[28-Feb-2004 15:22:53] Query: SELECT acl_location, acl_rights FROM
phpgw_acl where acl_appname = 'phpgw_group' AND acl_account =3

[28-Feb-2004 15:22:53] Query: select * from phpgw_acl where
(acl_account in (3, 0,1))

[28-Feb-2004 15:22:53] Query: SELECT * FROM phpgw_preferences WHERE
preference_owner IN (-1,-2,3)

[28-Feb-2004 15:22:53] Query: select
account_lid,account_firstname,account_lastname from phpgw_accounts
where account_id=3

[28-Feb-2004 15:22:53] Query: SELECT account_lid FROM phpgw_accounts
WHERE account_id=3

[28-Feb-2004 15:22:53] Query: select message_id,content from
phpgw_lang where lang = 'en' and (app_name = 'sync' or app_name =
'common' or app_name = 'all') order by app_name asc

[28-Feb-2004 15:22:53] Query: select * from phpgw_applications where
app_enabled != 0 order by app_order asc

[28-Feb-2004 15:22:53] Query: SELECT acl_location, acl_rights FROM
phpgw_acl where acl_appname = 'phpgw_group' AND acl_account =3

[28-Feb-2004 15:22:53] Query: SELECT account_lid FROM phpgw_accounts
WHERE account_id=1

[28-Feb-2004 15:22:53] Query: select acl_appname, acl_rights from
phpgw_acl where acl_location = 'run' and acl_account in (3,1)

[28-Feb-2004 15:22:53] Query: SELECT hook_appname, hook_location,
hook_filename FROM phpgw_hooks

[28-Feb-2004 15:22:53] Query: select * from phpgw_applications where
app_enabled != 0 order by app_order asc

[28-Feb-2004 15:22:53] Query: SELECT * FROM phpgw_async WHERE
id='##last-check-run##'

[28-Feb-2004 15:22:53] Query: SELECT * FROM phpgw_async WHERE
id='##last-check-run##'

[28-Feb-2004 15:22:53] Query: UPDATE phpgw_async SET
next=0,times='a:0:{}',method='none',data='a:3:{s:6:\"run_by\";s:8:\"fallback\";s:5:\"start\";i:1077978173;s:3:\"end\";i:0;}',account_id=0
WHERE id='##last-check-run##'

[28-Feb-2004 15:22:53] Query: SELECT * FROM phpgw_async WHERE
next<=1077978173 AND id!='##last-check-run##'

[28-Feb-2004 15:22:53] Query: SELECT * FROM phpgw_async WHERE
id='##last-check-run##'

[28-Feb-2004 15:22:53] Query: SELECT * FROM phpgw_async WHERE
id='##last-check-run##'

[28-Feb-2004 15:22:53] Query: UPDATE phpgw_async SET
next=0,times='a:0:{}',method='none',data='a:3:{s:6:\"run_by\";s:8:\"fallback\";s:5:\"start\";i:1077978173;s:3:\"end\";i:1077978173;}',account_id=0
WHERE id='##last-check-run##' 


---------------

--- class.translation_sql.inc.old       Tue Feb 10 14:51:19 2004
+++ class.translation_sql.inc.new       Sat Feb 28 13:37:47 2004
@@ -38,7 +38,7 @@
                        if (!isset($GLOBALS['lang']) ||
!$GLOBALS['lang'] || !is_array($GLOBALS['lang']))
                        {
                                $GLOBALS['lang'] = array();
-                               if
(isset($GLOBALS['phpgw_info']['user']['preferences']['common']['lang'])
&&
+        if
(isset($GLOBALS['phpgw_info']['user']['preferences']['common']['lang'])
&&

$GLOBALS['phpgw_info']['user']['preferences']['common']['lang'])
                                {
                                        $userlang =
$GLOBALS['phpgw_info']['user']['preferences']['common']['lang'];
@@ -47,8 +47,8 @@
                                {
                                        $userlang = 'en';
                                }
-                               $sql = "select message_id,content from
phpgw_lang where lang like '".$userlang."' ".
-                                       "and (app_name like
'".$GLOBALS['phpgw_info']['flags']['currentapp']."' or app_name like
'common' or app_name like 'all')";
+                               $sql = "select message_id,content from
phpgw_lang where lang = '".$userlang."' ".
+                                       "and (app_name =
'".$GLOBALS['phpgw_info']['flags']['currentapp']."' or app_name =
'common' or app_name = 'all')";
 
                                if (strcasecmp
($GLOBALS['phpgw_info']['flags']['currentapp'], 'common')>0)
                                {
@@ -58,14 +58,15 @@
                                {
                                        $sql .= ' order by app_name
desc';
                                }
-
-
$GLOBALS['phpgw']->db->query($sql,__LINE__,__FILE__);
-                               $GLOBALS['phpgw']->db->next_record();
-                               $count =
$GLOBALS['phpgw']->db->num_rows();
-                               for ($idx = 0; $idx < $count; ++$idx)
-                               {
-                                       $GLOBALS['lang'][strtolower
($GLOBALS['phpgw']->db->f('message_id'))] =
$GLOBALS['phpgw']->db->f('content');
-
$GLOBALS['phpgw']->db->next_record();
+        
+        $db = $GLOBALS['phpgw']->db;                           
+        $db->query($sql,__LINE__,__FILE__);
+        while ($db->next_record())
+        {
+          // directly access of the Record is not the best design,
but improves
+          // speed of the whole function by 25% 
+          $GLOBALS['lang'][strtolower($db->Record['message_id'])] =
$db->Record['content'];
+          // $GLOBALS['lang'][strtolower($db->f('message_id'))] =
$db->f('content');        
                                }
                        }
                        $ret = $key.'*';        // save key if we dont
find a translation


------- 

--- tables_current.inc.old      Sat Feb 28 16:04:09 2004
+++ tables_current.inc.php      Sat Feb 28 16:12:56 2004
@@ -19,7 +19,7 @@
                                'config_name' => array('type' =>
'varchar','precision' => '255','nullable' => False),
                                'config_value' => array('type' =>
'text')
                        ),
-                       'pk' => array(),
+                       'pk' => array('config_app', 'config_name'),
                        'fk' => array(),
                        'ix' => array(),
                        'uc' => array('config_name')
@@ -46,7 +46,7 @@
                                'acl_rights' => array('type' =>
'int','precision' => '4')
                        ),
                        'pk' => array(),
-                       'ix' => array(''),
+                       'ix' => array('acl_account', 'acl_location'),
                        'fk' => array(),
                        'uc' => array()
                ),


--
Markus Kämmerer         Team Software Solutions
pro|business AG, EXPO Plaza 1, 30539 Hannover
E-Mail: address@hidden,  Phone.: 0511/60066-0
WWW: http://www.probusiness.de/,    Mobile: 0177/5990932

**********************************************
           address@hidden 2004

*       Halle 1, Stand 3k4 (Magirus Deutschland GmbH)
*       Halle 1, Stand 7f2 (EMC Deutschland GmbH)
*       Halle 6, Stand D46 (Land Niedersachsen)
**********************************************