Slow performance, MySQL, MyISAM configuration

[liamk]

Hi --

I'm investigating performance problems with a client's SugarCRM installation.
When I look at the sugarcrm.log file, I see lots of slow query times.

Some of the tables have grown quite large. I'm seeing a lot of iowait when doing the slow queries, so I assume this is the culprit.

First, I want to make sure that MySQL config is optimal The Wiki article focuses mostly on InnoDB. This machine has 2G of RAM, RAID 1 (mirrored), 2 x 3.0GHz CPU). It's used pretty much exclusively for SugarCRM (including SMTP server).
OS: Mandriva 2006
Sugar version:4.5.0g
MySQL: 4.1.12
PHP: 5.0.4
Apache: 2.0

Here is the my.cnf file:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=500
set-variable=max_allowed_packet=16M
set-variable=key_buffer=384M
set-variable=sort_buffer=8M
set-variable=thread_concurrency=4
set-variable=record_buffer=16M
set-variable=query_cache_size=48M
set-variable=query_cache_type=2

# Default to using old password format for compatibility with old and
# shorter password hash.
# Reference: http://dev.mysql.com/doc/mysql/en/Password_hashing.html
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Second, I would like to archive the oversize tables and remove old entries. They are not really using them.
The emails table is 536,245 rows. Searching it takes about 1.5 minutes.
The contacts table is also large, because it contains many duplicates (caused by users re-uploading when the upload process failed).
Can I just directly delete old entries from the emails table? Are there any cross-references to them?
Can I just delete duplicate entries in the contacts table? How would I check to see if they are cross-referenced by other tables? (Also, SQL suggestion for de-duping in a table this size?)

Finally, again on performance, I'm puzzled by these entries.
Thu Aug 2 03:23:14 2007,984 [24757] FATAL SugarCRM - Slow Query (time:74.000688
SELECT st.id, st.execute_time FROM schedulers_times st
WHERE st.deleted=0
AND st.scheduler_id = '60423822-21a9-f5fc-d97b-45424d646
ORDER BY st.execute_time DESC
When I execute it by hand, it returns the Empty set in 0 seconds!

Can anyone point me in the right direction?

Thanks,
Liam

[ivolator]

Fine tunning MySQL is not set it and forget it task. You'll have to lookk up MySQL dedicated forums.
From my own experience I would say that the query caching is one of the more important things to look after.
But merely increasing the values you see in your config file will not improve performance. Contrary - it might worsen it.
Try different configs and read up on it. no other way. Every server for itself ......