Slow Responce Times

**Barakanooz** · 2006-09-22, 04:38 PM

I have SugarCRM version 4.2.1b ISO install and I imported all my ACT! data. I have about 13,000 contacts in the sugar system. When users log into the system and access data (drill down through accounts, etc) everything seems fine, but if they click on the Contacts tab it take the system about 2 minutes to return the list of contacts.

I think it is the hardware (specs below) because when they click the Contacts tab the mysqld jumps to 99.x% of CPU util.
But is there anything in mySQL I can do to the tables to try to speed it up ?

The hardware it is running on is:
Intel(R) Pentium(R) 4 CPU 2.40GHz 512MB cache
1 GB RAM
2GB swap space
2 - Western Digital 200GB 7200 RPM drives

thx

**stevec** · 2006-09-22, 04:49 PM

Ive' got a similar number of contacts and mine come back in fractions of a second. I would try rebuilding indexes. Also, try an monitor memory to try an make sure it's not running swap - though 2 minutes is still long for a swapping issue.

**Barakanooz** · 2006-09-22, 09:06 PM

I used 'mySQL Admin' and ran an Optimaization (repairs, sorts index pages, and update statistics) on all the tables
Also I monitor the machine state by running 'top' when people are using the system. It reports no swap memory is used.
Although I noticed when the CPU is slammed at 99.x% i get syslog messages:
kernel: CPU1: Running in modulated clock mode
and
kernel: CPU0: Temperature above threshold

from what I can find on the web these erros seem to be from a low threshold setting rather than a actual temp problem.

however these messages stop as soon as the cpu load drops back down...
this is a 1U unit but I have never had overheat problem with it.

I'll be looking into 'modulated clock mode' as that seems like a like cause to way the system slows.
(What is "modulated clock mode" anyone)

thx

**Proware** · 2006-09-22, 11:09 PM

I had a similar problem - almost had a riot in the office.

Turned out that my contacts table was a different character set to the rest and hence, was acting as if unindexed.

Fixing up the character set fixed the problem.

Hope this helps.

**stevec** · 2006-09-25, 11:58 AM

Originally Posted by Barakanooz

I'll be looking into 'modulated clock mode' as that seems like a like cause to way the system slows.
(What is "modulated clock mode" anyone)

thx

I assume it's where the system responds to overheating by effectively reducing the clock-speed of the processor. I wouldn't think it'll slow down enough to exhibit that behavior though. Proware's thoughts on character sets seems a good one to investigate though.

**Barakanooz** · 2006-09-25, 09:09 PM

Checked the character set by running SHOW VARIABLES LIKE 'char%'; on the sugarcrm table and I get:
+--------------------------+----------------------------+
| Variable_name | Value
+--------------------------+----------------------------+
| character_set_client | utf8
| character_set_connection | utf8
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | utf8
| character_set_server | utf8
| character_set_system | utf8
| character_sets_dir | /usr/share/mysql/charsets/
+--------------------------+----------------------------+

The only other charset described in the database is the Collation value set in the tables which is utf8_general_ci. This Collation value is the standard value throughout the Sugar system so I dont see a problem here.

I have a test box with 3.4Ghz P4, 1GB RAM , single HD 7200RPM, and I setup the exact sugar system and DB on it and the system also takes extremely long to return the Contacts. I definately think it is a DB prob now.

There are only two other things I can think of that could be slowing it. I create a custom module Licenses which shares a relation to the Contacts, maybe the slow down is when mySQL reads the license_contacts table and/or the licenses table. But they both have indexes assigned and I have run the mySQL Admin optimizer on them.

The other thing is that when I imported the data to Sugar I used the following function to create the ID values for each record. I see that the now Sugar uses ID values 4 digits longer. I can not see how this would cause such a problem, but I figure I would put it out there.

hhhmmm..

ID Function:
CREATE FUNCTION `dbname`.`NEWID`() RETURNS char(32)
BEGIN

DECLARE var_char CHAR(1);
DECLARE var_time CHAR(4) DEFAULT '0000';
DECLARE var_count INT DEFAULT 28;
DECLARE var_guid CHAR(32) DEFAULT ''; test with 36 no cat below

SET var_time = RIGHT(CONCAT("0000", HEX(UNIX_TIMESTAMP())), 4);
SET var_guid = LOWER(var_time);

WHILE var_count > 0 DO
SET var_char = HEX(FLOOR(RAND()*16));
IF (var_count = 24 OR var_count = 20 OR var_count = 16 OR var_count = 12) THEN
SET var_guid = CONCAT(var_guid, "-");
END IF;
SET var_guid = CONCAT(var_guid, LOWER(var_char));
SET var_count = var_count - 1;
END WHILE;

RETURN var_guid;

END

Trigger:
CREATE TRIGGER
newid
BEFORE INSERT ON
acl_actions
FOR EACH ROW
SET NEW.id = NEWID()

**kpit** · 2006-09-25, 09:55 PM

You will have to increase your keybuffer in your my.cnf file. this will increas the amount of memory that mysql will use by default is set for low memory machines. The following are the settings impact performance that I use for my.cnf:

Code:

key_buffer  = 16M
 max_allowed_packet  = 16M
 table_cache  = 256
 sort_buffer_size  = 32M
 read_buffer_size  = 256K
 read_rnd_buffer_size  = 512K
 myisam_sort_buffer_size  = 16M
 # query cache 2M
 query_cache_size  = 2M
 # query type OFF=0, ON=1, DEMAND=2
 query_cache_type  = 1
 #
 tmp_table_size  = 16M
 max_heap_table_size = 32M

We are running about 10,000 contacts and my response times hover arround 1 second. The big factor is the key_buffer as this is used by indexes to hold keys in memory. The second most important is table_cache. I also have has some problems with the max_packet_size with some queries involving the email part of SugarCRM. For more information on tuning MySQL check out http://dev.mysql.com/doc/refman/4.1/...arameters.html

**Barakanooz** · 2006-09-26, 03:49 PM

Thanks for the tip, but it doesnt seem to help that much.. Since I am now testing with just my one connection I inflated the values as specified:

key_buffer = 128M
max_allowed_packet = 32M
table_cache = 2048
sort_buffer_size = 64M
read_buffer_size = 4M
read_rnd_buffer_size = 2048K
myisam_sort_buffer_size = 32M
# query cache 2M
query_cache_size = 16M
# query type OFF=0, ON=1, DEMAND=2
query_cache_type = 1
#
tmp_table_size = 32M
max_heap_table_size = 64M

The rest of the mysqld variables are:
Variable_name | Value
+---------------------------------+-------------------------------+
| auto_increment_increment | 1
| auto_increment_offset | 1
| automatic_sp_privileges | ON
| back_log | 50
| basedir | /usr/
| bdb_cache_size | 8388600
| bdb_home | /srv/mysql/
| bdb_log_buffer_size | 1048576
| bdb_logdir
| bdb_max_lock | 10000
| bdb_shared_data | OFF
| bdb_tmpdir | /tmp/
| binlog_cache_size | 32768
| bulk_insert_buffer_size | 8388608
| character_set_client | utf8
| character_set_connection | utf8
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | utf8
| character_set_server | utf8
| character_set_system | utf8
| character_sets_dir | /usr/share/mysql/charsets/
| collation_connection | utf8_general_ci
| collation_database | utf8_general_ci
| collation_server | utf8_general_ci
| completion_type | 0
| concurrent_insert | 1
| connect_timeout | 5
| datadir | /srv/mysql/
| date_format | %Y-%m-%d
| datetime_format | %Y-%m-%d %H:%i:%s
| default_week_format | 0
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| div_precision_increment | 4
| engine_condition_pushdown | OFF
| expire_logs_days | 0
| flush | OFF
| flush_time | 0
| ft_boolean_syntax | + -><()~*:""&|
| ft_max_word_len | 84
| ft_min_word_len | 4
| ft_query_expansion_limit | 20
| ft_stopword_file | (built-in)
| group_concat_max_len | 1024
| have_archive | YES
| have_bdb | YES
| have_blackhole_engine | NO
| have_compress | YES
| have_crypt | YES
| have_csv | NO
| have_example_engine | NO
| have_federated_engine | YES
| have_geometry | YES
| have_innodb | YES
| have_isam | NO
| have_ndbcluster | NO
| have_openssl | DISABLED
| have_query_cache | YES
| have_raid | NO
| have_rtree_keys | YES
| have_symlink | YES
| init_connect
| init_file
| init_slave
| innodb_additional_mem_pool_size | 1048576
| innodb_autoextend_increment | 8
| innodb_buffer_pool_awe_mem_mb | 0
| innodb_buffer_pool_size | 8388608
| innodb_checksums | ON
| innodb_commit_concurrency | 0
| innodb_concurrency_tickets | 500
| innodb_data_file_path | ibdata1:10M:autoextend
| innodb_data_home_dir
| innodb_doublewrite | ON
| innodb_fast_shutdown | 1
| innodb_file_io_threads | 4
| innodb_file_per_table | OFF
| innodb_flush_log_at_trx_commit | 1
| innodb_flush_method
| innodb_force_recovery | 0
| innodb_lock_wait_timeout | 50
| innodb_locks_unsafe_for_binlog | OFF
| innodb_log_arch_dir
| innodb_log_archive | OFF
| innodb_log_buffer_size | 1048576
| innodb_log_file_size | 5242880
| innodb_log_files_in_group | 2
| innodb_log_group_home_dir | ./
| innodb_max_dirty_pages_pct | 90
| innodb_max_purge_lag | 0
| innodb_mirrored_log_groups | 1
| innodb_open_files | 300
| innodb_support_xa | ON
| innodb_sync_spin_loops | 20
| innodb_table_locks | ON
| innodb_thread_concurrency | 8
| innodb_thread_sleep_delay | 10000
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 134217728
| key_cache_age_threshold | 300
| key_cache_block_size | 1024
| key_cache_division_limit | 100
| language | /usr/share/mysql/english/
| large_files_support | ON
| large_page_size | 0
| large_pages | OFF
| license | GPL
| local_infile | ON
| locked_in_memory | OFF
| log | OFF
| log_bin | OFF
| log_bin_trust_function_creators | OFF
| log_error
| log_slave_updates | OFF
| log_slow_queries | OFF
| log_warnings | 1
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_file_system | OFF
| lower_case_table_names | 0
| max_allowed_packet | 33553408
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connect_errors | 10
| max_connections | 100
| max_delayed_threads | 20
| max_error_count | 64
| max_heap_table_size | 67107840
| max_insert_delayed_threads | 20
| max_join_size | 18446744073709551615
| max_length_for_sort_data | 1024
| max_prepared_stmt_count | 16382
| max_relay_log_size | 0
| max_seeks_for_key | 4294967295
| max_sort_length | 1024
| max_sp_recursion_depth | 0
| max_tmp_tables | 32
| max_user_connections | 0
| max_write_lock_count | 4294967295
| multi_range_count | 256
| myisam_data_pointer_size | 6
| myisam_max_sort_file_size | 2147483647
| myisam_recover_options | OFF
| myisam_repair_threads | 1
| myisam_sort_buffer_size | 33554432
| myisam_stats_method | nulls_unequal
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| new | OFF
| old_passwords | OFF
| open_files_limit | 4206
| optimizer_prune_level | 1
| optimizer_search_depth | 62
| pid_file | /var/run/mysqld/mysqld.pid
| prepared_stmt_count | 0
| port | 3306
| preload_buffer_size | 32768
| protocol_version | 10
| query_alloc_block_size | 8192
| query_cache_limit | 1048576
| query_cache_min_res_unit | 4096
| query_cache_size | 16777216
| query_cache_type | ON
| query_cache_wlock_invalidate | OFF
| query_prealloc_size | 8192
| range_alloc_block_size | 2048
| read_buffer_size | 4190208
| read_only | OFF
| read_rnd_buffer_size | 2093056
| relay_log_purge | ON
| relay_log_space_limit | 0
| rpl_recovery_rank | 0
| secure_auth | OFF
| server_id | 0
| skip_external_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slave_compressed_protocol | OFF
| slave_load_tmpdir | /tmp/
| slave_net_timeout | 3600
| slave_skip_errors | OFF
| slave_transaction_retries | 10
| slow_launch_time | 2
| socket | /srv/mysql/mysql.sock
| sort_buffer_size | 67108856
| sql_mode
| sql_notes | ON
| sql_warnings | ON
| storage_engine | MyISAM
| sync_binlog | 0
| sync_frm | ON
| system_time_zone | EDT
| table_cache | 2048
| table_lock_wait_timeout | 50
| table_type | MyISAM
| thread_cache_size | 0
| thread_stack | 196608
| time_format | %H:%i:%s
| time_zone | SYSTEM
| timed_mutexes | OFF
| tmp_table_size | 33554432
| tmpdir
| transaction_alloc_block_size | 8192
| transaction_prealloc_size | 4096
| tx_isolation | REPEATABLE-READ
| updatable_views_with_limit | YES
| version | 5.0.22
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 25, 2006) |
| version_comment | Source distribution
| version_compile_machine | i686
| version_compile_os | pc-linux-gnu
| wait_timeout | 28800
+---------------------------------+------------------+