CiviCRM Community Forums (archive)

Hi,

I recently created a Joomla website with CiviCRM. It was created on my development machine running WAMP, there the website ran just fine.
After moving the website to a test environment on a dedicated server every page that uses the civiCRM component (both back as front-end) has very slow loadtimes:

Non CiviCRM pages:
Application afterLoad: 0.002 seconds, 0.24 MB
Application afterInitialise: 0.052 seconds, 2.20 MB
Application afterRoute: 0.063 seconds, 2.66 MB
Application afterDispatch: 0.140 seconds, 4.87 MB
Application afterRender: 0.190 seconds, 5.21 MB

A Page showing a civiCRM Profile:
Application afterLoad: 0.002 seconds, 0.24 MB
Application afterInitialise: 0.049 seconds, 2.20 MB
Application afterRoute: 0.059 seconds, 2.66 MB
Application afterDispatch: 10.822 seconds, 21.79 MB
Application afterRender: 10.851 seconds, 22.13 MB

On the server I also see the httpd process spiking to 90% CPU usage while loading the page.

I followed this wiki while moving the website:
http://wiki.civicrm.org/confluence/display/CRMDOC/Moving+an+Existing+Installation+to+a+New+Server+or+Location

Configuration:
Host: Dedicated server with Plesk Panel
PHP: 5.2.9
Mysql 5.0.45
Joomla 1.5.14
CiviCRM 2.2.8

in php.ini i have set memory_limit to 64Mb

Does anybody have an idea where the problem might lie? Or how I can find out, which settings to double check?
i've been using both the Joomla and CiviCRM debuggers but neither show any errors or anything that might be the source of these load times.

Any help would be much appreciated

Thanks for your reply.

Did a scan for broken links, but found none, web developer plugin doesn't catch any errors in css or javascript.

mysql query log is as follows:

as you can see, the largest gap is after
(
  SELECT *
  FROM     civicrm_menu
  WHERE    path in ( 'civicrm/dashboard', 'civicrm' )
  ORDER BY length(path) DESC
  LIMIT    1
)

UNION (
  SELECT *
  FROM   civicrm_menu
  WHERE   path IN ( 'navigation' )
) 
/usr/libexec/mysqld, Version: 5.0.45-log (Source distribution). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
090907 11:04:37       4 Connect     vvcepc@localhost on
      4 Query       SET NAMES 'utf8'
      4 Init DB     lab_vvcepc
      4 Query       SET sql_mode = 'MYSQL40'
      4 Query       SELECT * FROM jos_session WHERE session_id = 'c3v66voq1uamkkm70oap4her83'
      4 Query       DELETE FROM jos_session WHERE ( time < '1252313377' )
      4 Query       SELECT * FROM jos_session WHERE session_id = 'c3v66voq1uamkkm70oap4her83'
      4 Query       UPDATE `jos_session` SET <sessiondata ommited> WHERE session_id='c3v66voq1uamkkm70oap4her83'
      4 Query       SELECT * FROM jos_components WHERE parent = 0
      4 Query       SELECT folder AS type, element AS name, params FROM jos_plugins WHERE published >= 1 AND access <= 2 ORDER BY ordering
      5 Connect     vvcepc@localhost on
      5 Init DB     lab_vvcepc
      5 Init DB     lab_vvcepc
      5 Query       /*!40101 SET NAMES utf8 */
      5 Init DB     lab_vvcepc
      5 Query       SELECT  config_backend, locales 
 FROM civicrm_domain
090907 11:04:38       5 Init DB     lab_vvcepc
      5 Query       (
  SELECT *
  FROM     civicrm_menu
  WHERE    path in ( 'civicrm/dashboard', 'civicrm' )
  ORDER BY length(path) DESC
  LIMIT    1
)

UNION (
  SELECT *
  FROM   civicrm_menu
  WHERE   path IN ( 'navigation' )
) 
090907 11:05:00       5 Init DB     lab_vvcepc
      5 Query       SELECT  id, value 
 FROM civicrm_option_value
 
 WHERE (  civicrm_option_value.name  = 'CRM_Contact_Form_Search_Custom_FullText' )
090907 11:05:01       5 Init DB     lab_vvcepc
      5 Query       SELECT *
 FROM civicrm_location_type
 
 WHERE (  civicrm_location_type.is_default = 1 )
090907 11:05:02      5 Init DB     lab_vvcepc
      5 Query       SELECT    civicrm_contact.display_name as display_name,
          civicrm_contact.contact_type as contact_type,
          civicrm_email.email          as email       
FROM      civicrm_contact
LEFT JOIN civicrm_email ON civicrm_email.contact_id = civicrm_contact.id
     AND  civicrm_email.is_primary = 1
WHERE     civicrm_contact.id = 1
      5 Init DB     lab_vvcepc
      5 Query       select count(civicrm_activity.id) from civicrm_activity
                  left join civicrm_activity_target on
                            civicrm_activity.id = civicrm_activity_target.activity_id
                  left join civicrm_activity_assignment on
                            civicrm_activity.id = civicrm_activity_assignment.activity_id
                 
                  where  ( source_contact_id = 1 or target_contact_id = 1 or assignee_contact_id = 1 )
                  and is_test = 0 and 1 and 1 and 1
090907 11:05:03       5 Init DB     lab_vvcepc
      5 Query       SELECT *
 FROM civicrm_preferences
 
 WHERE (  civicrm_preferences.is_domain = 1 )
      5 Init DB     lab_vvcepc
      5 Query       SELECT  v.name as name ,v.value as value, v.grouping as grouping
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'contact_view_options'
  AND  v.is_active       = 1
  AND  g.is_active       = 1   ORDER BY v.weight
      5 Init DB     lab_vvcepc
      5 Query       select count(civicrm_activity.id) from civicrm_activity
                  left join civicrm_activity_target on
                            civicrm_activity.id = civicrm_activity_target.activity_id
                  left join civicrm_activity_assignment on
                            civicrm_activity.id = civicrm_activity_assignment.activity_id
                 
                  where 1
                  and is_test = 0 and 1 and  civicrm_activity.status_id = 1  and 1
      5 Init DB     lab_vvcepc
      5 Query       SELECT *
 FROM civicrm_component
      5 Init DB     lab_vvcepc
      5 Query       select DISTINCT(civicrm_activity.id), civicrm_activity.activity_date_time,
                         civicrm_activity.status_id, civicrm_activity.subject,
                         civicrm_activity.source_contact_id,civicrm_activity.source_record_id,
                         sourceContact.sort_name as source_contact_name,
                         civicrm_activity_target.target_contact_id,
             targetContact.sort_name as target_contact_name,
                         civicrm_activity_assignment.assignee_contact_id,
             assigneeContact.sort_name as assignee_contact_name,
                         civicrm_option_value.value as activity_type_id,
                         civicrm_option_value.label as activity_type,
                         civicrm_case_activity.case_id as case_id,
                         civicrm_case.subject as case_subject
                  from civicrm_activity
                  left join civicrm_activity_target on
                            civicrm_activity.id = civicrm_activity_target.activity_id
                  left join civicrm_activity_assignment on
                            civicrm_activity.id = civicrm_activity_assignment.activity_id
                  left join civicrm_contact sourceContact on
                            source_contact_id = sourceContact.id
          left join civicrm_contact targetContact on
                            target_contact_id = targetContact.id
                  left join civicrm_contact assigneeContact on
                            assignee_contact_id = assigneeContact.id
                  left join civicrm_option_value on
                            ( civicrm_activity.activity_type_id = civicrm_option_value.value )
                  left join civicrm_option_group on 
                            civicrm_option_group.id = civicrm_option_value.option_group_id
                  left join civicrm_case_activity on
                            civicrm_case_activity.activity_id = civicrm_activity.id
                  left join civicrm_case on
                            civicrm_case_activity.case_id = civicrm_case.id
                  left join civicrm_case_contact on
                            civicrm_case_contact.case_id = civicrm_case.id
                  where 1
                        and civicrm_option_group.name = 'activity_type'
                        and (civicrm_option_value.component_id IS NULL OR civicrm_option_value.component_id IN (1, 2, 3, 4, 6, 8))
                        and is_test = 0  and 1 and  ((civicrm_case_activity.case_id Is Null) OR (civicrm_option_value.name <> 'Inbound Email' AND civicrm_option_value.name <> 'Email' AND civicrm_case_activity.case_id Is Not Null))  and  civicrm_activity.status_id = 1 
                        GROUP BY id ORDER BY status_id asc, activity_date_time asc  LIMIT 0, 50
      5 Init DB     lab_vvcepc
      5 Query       select DISTINCT(civicrm_activity.id), civicrm_activity.activity_date_time,
                         civicrm_activity.status_id, civicrm_activity.subject,
                         civicrm_activity.source_contact_id,civicrm_activity.source_record_id,
                         sourceContact.sort_name as source_contact_name,
                         civicrm_activity_target.target_contact_id,
             targetContact.sort_name as target_contact_name,
                         civicrm_activity_assignment.assignee_contact_id,
             assigneeContact.sort_name as assignee_contact_name,
                         civicrm_option_value.value as activity_type_id,
                         civicrm_option_value.label as activity_type,
                         civicrm_case_activity.case_id as case_id,
                         civicrm_case.subject as case_subject
                  from civicrm_activity
                  left join civicrm_activity_target on
                            civicrm_activity.id = civicrm_activity_target.activity_id
                  left join civicrm_activity_assignment on
                            civicrm_activity.id = civicrm_activity_assignment.activity_id
                  left join civicrm_contact sourceContact on
                            source_contact_id = sourceContact.id
          left join civicrm_contact targetContact on
                            target_contact_id = targetContact.id
                  left join civicrm_contact assigneeContact on
                            assignee_contact_id = assigneeContact.id
                  left join civicrm_option_value on
                            ( civicrm_activity.activity_type_id = civicrm_option_value.value )
                  left join civicrm_option_group on 
                            civicrm_option_group.id = civicrm_option_value.option_group_id
                  left join civicrm_case_activity on
                            civicrm_case_activity.activity_id = civicrm_activity.id
                  left join civicrm_case on
                            civicrm_case_activity.case_id = civicrm_case.id
                  left join civicrm_case_contact on
                            civicrm_case_contact.case_id = civicrm_case.id
                  where 1
                        and civicrm_option_group.name = 'activity_type'
                        and (civicrm_option_value.component_id IS NULL OR civicrm_option_value.component_id IN (1, 2, 3, 4, 6, 8))
                        and is_test = 0  and 1 and  ((civicrm_case_activity.case_id Is Null) OR (civicrm_option_value.name <> 'Inbound Email' AND civicrm_option_value.name <> 'Email' AND civicrm_case_activity.case_id Is Not Null))  and  civicrm_activity.status_id = 1 
                        GROUP BY id ORDER BY status_id asc, activity_date_time asc  LIMIT 0, 50
      5 Init DB     lab_vvcepc
      5 Query       SELECT  id, display_name 
 FROM civicrm_contact
 
 WHERE (  civicrm_contact.id = 1 )
      5 Init DB     lab_vvcepc
      5 Query       SELECT  v.name as name ,v.value as value, v.grouping as grouping
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'contact_view_options'
  AND  v.is_active       = 1
  AND  g.is_active       = 1   ORDER BY v.weight
090907 11:05:04       4 Query       SELECT template FROM jos_templates_menu WHERE client_id = 1 AND menuid = 0
      4 Query       SELECT id, title, module, position, content, showtitle, control, params FROM jos_modules AS m LEFT JOIN jos_modules_menu AS mm ON mm.moduleid = m.id WHERE m.published = 1 AND m.access <= 2 AND m.client_id = 1 ORDER BY position, ordering
      4 Query       SELECT a.name, a.admin_menu_link, a.admin_menu_img FROM jos_components AS a INNER JOIN jos_components AS b ON b.id = a.parent WHERE b.option = 'com_civicrm' AND b.parent = 0 ORDER BY a.ordering ASC
090907 11:05:06       4 Query       SELECT a.*, SUM(b.home) AS home FROM jos_menu_types AS a LEFT JOIN jos_menu AS b ON b.menutype = a.menutype GROUP BY a.id
      4 Query       SELECT * FROM jos_components WHERE `option` <> "com_frontpage" AND `option` <> "com_media" AND enabled = 1 ORDER BY ordering, name
      4 Query       SELECT COUNT(*) FROM jos_messages WHERE state = 0 AND user_id_to = 62
      4 Query       SELECT COUNT( session_id ) FROM jos_session WHERE guest <> 1
090907 11:05:09      4 Query       SELECT * FROM jos_session WHERE session_id = 'c3v66voq1uamkkm70oap4her83'
      4 Query       UPDATE `jos_session` SET <sessiondata ommited> WHERE session_id='c3v66voq1uamkkm70oap4her83'
      4 Quit       
      5 Quit       

The query is executed under 1/10 of a second, so i think the problem lies somewhere else.
Any idea what CiviCRM might be doing after that query?

Hey,

It's quite surprising that it was this part of the code causing such major performance problems - is there anything specific about your hosting setup that makes file reads so expensive? Any specific settings in Windows server? I'm scratching my head, since this particular piece of the code didn't change for quite a long time and never caused such problems to others.

However, thanks a lot for sharing the solution and the patch - I'll definitely work on them and try to integrate them to nearest suitable release (3.1 most probably). Storing strings in memory should definitely be faster, but I need to check if it doesn't influence memory usage too much. I've created an issue for this: http://issues.civicrm.org/jira/browse/CRM-5210

Thanks,
m

Sorry for resurrecting an old thread, but I feel like I am running in exactly the same issue, with a CiviCRM 3.3.2 (I noticed the issue in older versions, but blamed it on the hosting).

Here is a screenshot of kcachegrind:
http://i.imgur.com/Ffg1C.jpg

gettext_reader->load_tables is using about 47% of the page load time (of which 23% is FileReader->read, 11% is FileReader->seekto).

APC is running on this server, but it is a rather loaded server, so I am not sure to which extent it is caching those files.