Ongoing Performance Work...

[Jacob]

It will be automatically detected and taken advantage of.

You have to install the module and restart PHP (typically Apache or IIS).

As of 4.5.0, the JSON module no longer has quite the performance boost. I implemented a local caching scheme that offloaded most of the work.

Jacob

[rbooz]

I just installed a new Sugar 4.5 instance for a local ministry we support - our business is still running 4.2.1 - been using Sugar for almost a year now.

Anyway, I was completely shocked when after importing around 2000 accounts/contacts that trying to get a listview on either tab (Contacts or Accounts) would time-out on the default PHP timeout of 30 seconds. So, I started doing reading through the forums thinking it was an INNODB issue (our work DB is MyISAM). Played with PHP, MySQL, IIS, installed a eAccellorator. No mater what, this DB consistently timed-out just trying to display the basic listview. Clicking on an Account/Contact was even worse - I never got the detail. I was baffled because our work DB (Sugar 4.2.1) has nearly 2000 accounts and many custom fields and we've never had a performance issue.

So, I couldn't come down to anything other than that it was a DB issue, but not sure what. Following this thread (or the other one by Jacob) - I turned off the counting for the listviews and the problem went away. Much better performance. Still, I was baffled - this just seems completely unacceptable for 2000 records. Finally turned on slow queries logging and did confirm that the bottleneck was with the "count(*)" statement when trying to display the listview.

So, this brings me to the question of the design for this query (and presumably the ones like it for each listview: Contacts, etc.).

SELECT count(*)  FROM accounts  left JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
where (accounts.assigned_user_id = '1') AND accounts.deleted=0

For me, at least, when I make this an "inner join", the query is executed very quickly (.003s). Understandably this would be missing any rows that didn't have a match in both tables, which is definitely possible. Doing a little more testing with it, I'm curious about these two questions:

1. Why is the join even happening if the purpose is to simply return the total number of accounts? Again, simply counting the accounts executes very quuickly.
2. Any thoughts on why MySQL is taking many times longer (I kill the query after 1 min and only a few hundred rows are pulled) when done as an "left join" rather than an "inner join"? I just can't make sense of it.

My overall point is that it's ashame to not be able to see the "total" of a list, especially when it appears (I realize I have a limited view) that it could be done in a much simpler way.

Thanks for any help/thoughts.

Ryan

[stevec]

have you tried rebuilding the indexes on the tables?

[rbooz]

Steve,

I have, to no avail. But, having some time to spare today, I started doing more searching on this and found the problem - I just have no idea how to fix it. I'm not an expert in MySQL, but I have been doing DBA stuff for a long time on MySQL/PostgreSQL/MSSQL - so I wouldn't think an index issue would be terribly hard for me to figure out.. but I sure am stumped here.

I have two different Sugar DBs (as mentioned before) on a new MySQL install (5.0.24 - Windows). One of the DBs comes from a Linux install of Sugar 4.2 (MySQL 4.1.14) which I moved to the Windows instance and then upgraded to Sugar 4.5. The other DB was a new creation with Sugar 4.5 (was that clear? Two DBs, both on Windows 5.0.24, one was new, one was an upgrade from MySQL 4.1.14 and Sugar 4.2)... anyway.

On the DB that we've been using for almost a year (the one moved form the linux box/MySQL 4.1.14), an explain on the statement I previously mentioned yeilds:

id  select_type  table          type   possible_keys           key                     key_len   ref                        rows   Extra 
1   SIMPLE       accounts       ref    idx_accnt_assigned_del  idx_accnt_assigned_del  112       const,const                1      Using where
1   SIMPLE       accounts_cstm  ref    PRIMARY                 PRIMARY                 110       gss_sugarcrm.accounts.id   1      Using index

HOWEVER, on the DB that was created from scratch on this Windows MySQL server, using SugarCRM setup, the same EXPLAIN statement produces unforunate results!

id  select_type  table          type   possible_keys           key   key_len   ref   rows   Extra 
1   SIMPLE       accounts       ALL    idx_accnt_assigned_del  NULL  NULL      NULL  4757   Using where 
1   SIMPLE       accounts_cstm  index  PRIMARY                 38    NULL      NULL  8689   Using index

How could this be? I've racket my brain over and over, played with the indexes and all to no avail. This definitely explains why the a simple "count" statement is taking so long to execute, it's building millions of rows for the query. Does anyone have any ideas what could be going on here? How can the same two tables in two different DBs with the same indexes give completely different results.

I'd love to hear from anyone that might have suggestions.

Thanks!
Ryan

[stevec]

Odd.

A few suggestions:

On each machine, dump the mysql settings and compare the differences:

mysql> show variables;

maybe

mysql> show status;

will show something but I'm not so sure about that. As for the variables, it might be that index space is limited and so full indexes cannot be used?

For database specifics, it might be worth dumping the databases and 'diff'ing the two files to see if the structures are in any way different.

[Jacob]

You should not be seeing multiple second delays for a count query with 2,000 records. That setting was created for systems that had potentially hundreds of thousands to millions of records.

The custom table is joined into the query in case you are filtering by a column in that talbe. Ideally the custom table would not even be joined in the query, since it is not used to filter in this instance.

Go to the MySQL command line and run:

 show create table accounts;
show create table accounts_cstm; 
  

This will dump out the SQL used to create the table. This shows all indexes and columns. Is there a difference?

Jacob

[rbooz]

Steve and Jacob,

Thanks for the suggestions, but I'm still now where. It's (presumably) a database issue and not a server issue. I say this only because I can run the Sugar 4.2 DB (one we've been using for almost a year) on either MySQL server (Linux or Windows) and the indexes work - the queryies are fast. However, the Sugar 4.5 DB that was initually created on the Windows box is "broken" (indexes) on either machine - both MySQL 5.0.24 and 4.1.2.

1. Variables are not the same but show nothing out of the ordinary. The Windows box actually has more memory allocated for the MySQL stuff.

2. doing the "show create" produces the same SQL, with the exception of an index I added in trying to diagnos the problem yesterday. Otherwise the same.

The DB in question was initially started as an INNODB database, which I subsequently dumped and changed to MyISAM by altering the script. (the other DB has always been MyISAM). I changed it because of this issue, thinking that for some reason INNODB was slow AND because I noticed that the on "cstm" tables, the indexes had a size of "0Kb", even though there were items in the table. Making them MyISAM made the index appear to have stuff in it (383Kb), but it is obviously still functioning as if it thinks that index is empty.

Does that provide any hints?
Ryan

[kpit]

MySQL when doing a count does a table scan when you use *. Select an indexed field to speed this up like count (id); This will perfom the scan faster as it will use the index instead of the whole table.

[Jacob]

I have seen 0 byte indexes in the DB folder when using InnoDB. InnoDB in the out of box configuration actually puts all data in one file, and all indexes in another file. The database specific folders don't end up holding much in that case.

One instance where MySQL will ignore indexes is if it thinks it will be reading a large percentage of the table anyways.

I am actually getting an even better result from my instance. MySQL 5.0.21.

accounts - ref
accounts_cstm - eq_ref

Both using keys.

Jacob

[rbooz]

Thank you both for your replies It's interesting Jacob that I never get notified via email that you posted a reply, but I get notified of other replies for this thread. But, that's not the issue at hand...

Max, I understand the premise of the table scan, but my point is that this is an included Sugar query, not something I'm doing on my own. The crazy fact is that one Sugar DB has indexes that work with this query, one Sugar DB does not. I think that's what is getting me so frustrated about this. And I realize that it's not specifically a Sugar issue most likely, but I am having a hard time figuring out what the difference is between the two that's causing the problem.

Jacob that's good info about InnoDB indexes, I'll have to shuffle that away in my memory bank. But, at present I'm working with the DB in MyISAM since that's what the other working DB is in, just to compare apples to apples. Is it possible that somehow the query optimizer isn't working with DB somehow? Even though I've dropped and recreated this DB that somehow it's not actually doing it's job? Any way to really force it to rebuild everything? I've tried repair with no success. Anything else I'm missing?

Again, I appreciate the help tremendously. I'm hoping to come out the other side of this with a new level of understanding for the future!

Ryan