Performance question - large number of customers

[CRMrulez]

Hi,

Doees anyone have experience about a large number of customers (say 200 000 - 400 000) in Sugar? How's the performance? What kind of HW you suggest for 20 or 100 users?

Thanks in advance!

[mycrmspacegunnar]

Hi,

Doees anyone have experience about a large number of customers (say 200 000 - 400 000) in Sugar? How's the performance? What kind of HW you suggest for 20 or 100 users?

Thanks in advance!

Hi
The answer to your question includes many "depends"...

Sugars performance in relation to the number of records
basicly translates to the raw database aka MYSQL speed.
Working with millions of records is in general not a problem for a database at all.
A database is designed to be fast even with high numbers of records.
When you work with indexed data you can say that working with
one million records usually takes twice as long as working with thousand records.
So the number of records is not an issue at all.

But depending on your working pattern Sugar will not always
work with indexes but depend on tables scans.
The time needed for table scan does increase lineary with the number
of records. So scanning a million records will take thousand times longer
than scanning thousand records.

So if you work with 400,000 records then Sugar
*can* get a bit slow in certain areas.
In addition to the table scan issue the configuration of your server will play a big part.
Running a server with default (Sugar bitrock) configuration or
tuning the server easely makes a speed difference of 2-4 times.


Now for working with 20 users you can usually use every entry server.
But 100 busy users will be able to generate quite a workload.

Typically an average single webserver is able to generate 4-30 Sugar webpages per second.

If your users generate more than 20-30 clicks per seconds you will need a professional tuned setup.

For 100 or more users that really use the system you will run into problems with even a beefe single server.
Setting Sugar up to use multible server in a reliable and effective way will require profesional experince in webserver/database administration.
For bigger installs with 200+ users, I would recommand to use bladecenter.
That uses a fast webserver together with a loadbalanced fastcgi PHP cluster behind it.

Please mind that the configuration in general plays a more important part than the actual hardware.
Often people run their servers using only a fraction of the possible performance because of not tuning the system correctly.

If you are considering to set up a system for 100 users get someone with experience
in setting up such systems to help you with that and to teach you how to tune such systems.


Cheers
Gunnar

[CRMrulez]

Gunnar,

Many thanks to you for the comprehensive answer! One specifying question:

Quote---
So if you work with 400,000 records then Sugar *can* get a bit slow in certain areas.
Quote---

Can you elaborate, which are the areas you're referring to here?

Thanks once again!

[mycrmspaceclemens]

Hi,

Quote---
So if you work with 400,000 records then Sugar *can* get a bit slow in certain areas.
Quote---

Can you elaborate, which are the areas you're referring to here?

Sugar's pages can be categorized in two groups.

a) Those that directly display one record.
E.G. Detailview of one account and the directly connected child records.

b) Those that display lists of records.
E.g The Accounts and Contact entry pages which
display the first xxx records (usually 10 or 20) of all Accounts or Contacts.


The first group of pages use nromal indexed database methods to access the pages.
These pages will always have the same speed, independent of the number of records in the database.

The second group of pages, like the Account tab or Contacts tab
don't use indexes and use brute force power to sort and display the records.
These pages will get slower with every extra record.



Okay here come some real benchmarks for you:

I have benchmarked for you the time that Sugar needs for typical
operations that fall into the brute force category, like loading the Account tab,
sorting or searching the results for 10 Accounts
or just browsing to the next 10 records.

The time was measured on a tuned webserver (new Athlon 64, plenty of ram)

1000 account records - the time to do this is about 0.10 seconds

100,000 account - time : 2.0 - 3.0 seconds

200,000 account - time : about 5.0 seconds

400,000 account - time : about 10.0 seconds


Because of obvious data security and reliabilty reasons we always recommend to run Sugar on innodb tables only
and never to rely MyISAM for a mission critical applications. MyISAM is not designed to be used as backend for an app like Sugar. Unfortunatey the Sugar db-design will create an extra performance penalty on innodb.
With 500,000 records you can expect a time of average 10 to 20 seconds per page.


We need to mind that Sugar is a sales supporting tool
its designed to work with typically less than 10,000 records
and to be used by only a few users that are generating relatively few clicks only.

If you run Sugar with a CRM typical amount of records e.g 10,000 or less records
then all pages in Sugar will always be reasonably fast.


Sugar is NOT an e-commerce tool like a webshop and Sugar is NOT a callcenter software.

Please don't try to use Sugar in a Call-center environment with 100 busy bees using it!
Sugar will not be able to handle to workload as it.
You will need a lot of work tuning and adapting it for it.
And you need serious investmenst in hardware.



If you estimate how much users you can serve with Sugar then good guess number
is that one Sugar server can typically only create about 5-10 pages per second.
Look at how many users you want to use the system and estimate how often they will click per minute.


Maybe you can give use some examples for you want to use Sugar and what you think how your users will
work with the system.

Cheeers

[malcolmh]

Hi,

Has anyone had any expereience with the actual 4.5.1x Sugar versions, whether the problems still exist with these no. of records with MySQL or ´MS SQL Server?

[egork]

We will try to do a stress test on our machine with a million or so customers and a few hundred simulated users.
Malcolm, would you like to propose any test strategies?

[salesagility]

You should also look at the following SugarCRM wiki - it made a considerable difference to the perfomance for one of our clients:

Sugar performance Wiki

[mipapage]

Wow, that link was super useful. Thanks salesagility, hadn't seen it before...

[interbayuk]

the wiki doesn't mention implementing mysql performance tweaks, such as setting the mysql key buffer size, implementing mysql cache queries, increasing the mysql sort buffer and many more.


just google for "mysql performance tweaks"