Hi all,

We have a large Sugar DB (over 2M e-mail addresses). it was running fine under Sugar 4.5.1. This weekend I did a 'silent upgrade' to Sugar 5.0; and while it worked fine, the performance has dropped through the floor!

Simply doing:

SELECT * FROM email_addresses WHERE email_address LIKE '%somedomain%';

takes 25 seconds or so. And when i try to join with the email_addr_beans_rel table, it's even worse:

SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE ea.email_address LIKE '%somedomain%';

This query runs for many minutes without giving anything. Mine's been running for nearly 10 minutes now, no result. My email_addresses table has over 2M records, email_addr_bean_rel about 1.3M records. My MySQL installation rung on an ubuntu 8.04 box with 2GB of RAM, and I gave plenty of memory to mysql:

Code:
[mysqld]
#
# * Fine Tuning
#
key_buffer              = 512M
max_allowed_packet      = 64M
thread_stack            = 512K
thread_cache_size       = 32
#
# * Query Cache Configuration
#
query_cache_limit       = 32M
query_cache_size        = 256M
query_cache_type        = 1
table_cache             = 512
sort_buffer_size        = 256M
The only thing which looks out of place is that when such a large query is running, udevd goes wild:

Code:
# top
 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
2504 root      16  -4 64040  61m  508 R 33.5  3.0   2250:22 udevd
11250 mysql     20   0 1133m 530m 5820 S  2.7 26.3  28:25.27 mysqld
I already did a full DB repair (took about 2 hours), but this hasn't helped. I am seriously considering downgrading back to 4.5.1f.

Does anyone have any idea about what could be going on here? The problem seems to be on the DB level, but it used to work fine under Sugar 4.5.1 (which admittedly had a much simpler DB structure).

Thanks in advance for any help.