Ongoing Performance Work...

[stevec]

I installed 4.5.1 on our remote LINUX server and tried to use it. The message at the bottom of each page shows response time avg 1 sec. but a real time check, from mouse click to page new page filled is never under 10 seconds, and often much longer.

The time given is the server response time generating the page - the other 10+ seconds is the delivery over the network and rendering in the browser. The usual culprit is non-caching of static items (images, javascript, css etc). Have a look at the wiki and earlier on in this thread on ways to configure the web server and browser for performance.

[CMTG]

PHP 5.2.2 and above include a working version of the encoder/decoder that handle ascii, high ascii, and multi byte characters well.

Jacob

This implementation was the basis for the JSON built into PHP 5.2. Since the 5.2.1 version is working, I am hoping that version 1.2.1 of their DLL (available on their site) will work.

Jacob

So is this the case? Have you done some testing since making those posts?

[Jacob]

So is this the case? Have you done some testing since making those posts?

I just tried the latest release posted against PHP 4.4.3. My simple JSON test failed.

I am still looking for the JSON DLL that we recommend. My current recommendation is to upgrade to PHP 5.2.2+ or use a cache (APC and Zend Platform are supported) to cache the JSON output.

Jacob

[sacramentojoe]

I'm not sure if this is in this thread because I didn't read every post but what about speeding up the import part of the migrate custom fields?
We have our time out to 2 minutes and it fails when we have about 200 custom fields.

[DJuser]

Have other folks made the same SQL changes we have?

Our slow_query_log was showing nearly *every* page was having the exact same 1/4 second query.

We fixed it, by creating a query in table Calls for reminder_time
create index idx_reminder_time on calls (reminder_time)

And pages are noticeably faster (still bot fast enough).

We run 4.5.1.e, nothing custom, just changes made by Studio.
Our Calls table is 22,000 records - imported from an Access application: running on a LAMP box with 2GB RAM, nothing but Sugar running
Max of 3 users on the system.

It makes us wonder why this index was not already in place?

Sugar just 'felt slow' from the start for us - which drove us to look at slow_queries.

It's still to slow, so we've made further looks at the slow_query log show , which suggest that some other queries are not optimal too: eg the one below, taking 0.7 second - which seems way too slow, given the small size of the tables.

The OR is the root cause for this query being slow (we tested); but isn't that poor SQL design to have the OR running - isn't calls_Users the 'correct' place for user_ids - so why does calls also have a user_id column (assigned_user_id) ??

Could we find the PHP and remove the OR part?

Sounds like lack of normalisation - but I guess we must be missing somethng, or else folks would have spotted and foxed these long ago?

Thoughts?

DJUser

Wed Jul 11 22:41:44 2007,311 [12221] FATAL SugarCRM - Slow Query (time:0.719901
SELECT count(*) c FROM calls INNER JOIN calls_users ON calls.id = calls_users.call_id where (calls.status IN ('Planned')
) AND calls.deleted=0 AND calls_users.deleted = 0 AND (calls.assigned_user_id = '6a97b4a9-b88d-4a04-471b-46556f0cd8ce' OR calls_users.user_id = '6a97b4a9-b88d-4a04-471b-46556f0cd8ce')

[DJuser]

I guess this is not the right place for such detail MySQL questions?

Can anyone tell me which forum the in-depth SQL /performance guys can be found?

My environment is standard - and table size is small, so there must be folk out there that have already solved the issues I'm seeing?

Thanks for any advice

DJUser

Have other folks made the same SQL changes we have?

Our slow_query_log was showing nearly *every* page was having the exact same 1/4 second query.

We fixed it, by creating a query in table Calls for reminder_time
create index idx_reminder_time on calls (reminder_time)

And pages are noticeably faster (still bot fast enough).

We run 4.5.1.e, nothing custom, just changes made by Studio.
Our Calls table is 22,000 records - imported from an Access application: running on a LAMP box with 2GB RAM, nothing but Sugar running
Max of 3 users on the system.

It makes us wonder why this index was not already in place?

Sugar just 'felt slow' from the start for us - which drove us to look at slow_queries.

It's still to slow, so we've made further looks at the slow_query log show , which suggest that some other queries are not optimal too: eg the one below, taking 0.7 second - which seems way too slow, given the small size of the tables.

The OR is the root cause for this query being slow (we tested); but isn't that poor SQL design to have the OR running - isn't calls_Users the 'correct' place for user_ids - so why does calls also have a user_id column (assigned_user_id) ??

Could we find the PHP and remove the OR part?

Sounds like lack of normalisation - but I guess we must be missing somethng, or else folks would have spotted and foxed these long ago?

Thoughts?

DJUser

Wed Jul 11 22:41:44 2007,311 [12221] FATAL SugarCRM - Slow Query (time:0.719901
SELECT count(*) c FROM calls INNER JOIN calls_users ON calls.id = calls_users.call_id where (calls.status IN ('Planned')
) AND calls.deleted=0 AND calls_users.deleted = 0 AND (calls.assigned_user_id = '6a97b4a9-b88d-4a04-471b-46556f0cd8ce' OR calls_users.user_id = '6a97b4a9-b88d-4a04-471b-46556f0cd8ce')

[Jacob]

I am going to start a new thread on this soon. This thread is getting a little unwieldy.

Jacob