Sugar SQL performance problems

[vicdan]

Hi all,

I am hoping someone in the community would be able to help me with a pretty unpleasant Sugar performance problem. We have over a million leads in our DB, and some operations (such as updating a lead record for example, or even simply pulling it up) take minutes: I did a test just now, it was 4 minutes each, I tried again after restarting the MySQL server, 2 minutes each -- and that's with nobody else on the server! We are using Sugar 4.5.1e (yes, i am planning to upgrade, but critical performance fixes should already be in there).

The short of it is:

1) I already tweaked the MySQL configuration to increase key buffer and query cache about ten-fold. Anything else I can do to help Sugar along?

2) is there any reasonably easy way to profile Sugar operations, to determine exactly which actions take so much time?



Long version:

My current MySQL configuration is as follows (relevant section):

key_buffer = 256M
max_allowed_packet = 64M
thread_stack = 512K
thread_cache_size = 32
query_cache_limit = 32M
query_cache_size = 256M
query_cache_type = 1
table_cache = 512
sort_buffer_size = 32M

GUIDs are 36 bytes long, so for 1.3M records, 256M key buffer should be plenty enough, right?

it used to be that simply doing 'select count(id) from leads', for example, took a minute or two -- with the default MySQL memory configuration. I did some performance tuning to increase key buffer and query cache sizes, and such simple operations are now basically instant; but the system overall is still very slow. One problem could be that we have only 1GB of memory in this server. Could this really be the limiting factor though? For such a relatively simple use, 1GB should be enough, right?

if nobody knows a direct answer, can anyone give me a pointer on how to find out the performance bottleneck here? Thanks.