vtiger

[martinb]

We have a critical problem here with performance of 4.2.2 . In the database are circa 30000 account datasets (middle sized company) witch leads to a mysql-response-time of nearly 2 seconds - much too long for a running system with at least 40 users at the same time. We guess that the huge number of joins is responsible for this matter. Has anyone such problems, too?
Is there an idea how to solve it?
Has 5.0 a better performance? I have doubts in this matter cause there are more tables... (caching???)
vTiger is a great product but with this problems it's not possible to use it in business use.

[Bushwack]

Hi marinb,

Have you narrowed down the query (or queries) which is causing the slow down?

If not uncomment line 660 of include/database/PearDatabase.php:
Code: $this->database->debug = true;
and reload the page in question to see all the queries performed.

Run them individually trough your database and determine which ones are the slow ones. Then perpend "describe" to the queries and post the output here and we'll see if there's anything that can be done to speed up your queries.

[steven_doempke]

Hi Bushwack,

one query that causes us trouble is the list-query of the accounts.
With 30000+ accounts the mysql db needs 2 secounds to finish the query (on a 3Ghz Xeon processor with 2GB ram).

The main reason why this query is so slow is that there are to many joins (crmentity, accountbillads, accountshipadds ...). With only crmentity and account it only takes 0.3-0.4 s for the db to perform this query.

I see 2 ways to solve this problem.
1. Using LIMIT (like vtiger 5 does) and removing the total number of the found results.
2. Merging all account tables into one table

Using Limit would be a quick solution, while the table merge would increase to overall performance (especially for some of the bigger Jasper Reports we created).

[Bushwack]

Thanks for your analysis steven.

I'd love to see the output of describe on that select statement in your environment. That the only way to figure out what's really going on.

I agree with your first point limiting the results of that query is definitely needed. I'm uncertain how feasible that will be to do on the 4.2.x line though (ie: without raising the minimum version mysql requirements).

It may just be because I usually use postgres databases where as I understand joins are much cheaper then in mysql but your second point is something I'd like to avoid at all cost.

I'm thinking that we are probably just missing some index required for that query with large datasets. I've had vtiger set up on similar hardware (albeit using a postgres database) with ~30000 actives and I didn't experience any issues listing the activities.

[steven_doempke]

The query is the standard one, except for the accountextra1 table that i have added for the addional fields we needed.
All the joins use the primary key of the table and all primary keys have the same datatype int(19).
The tables use mysqls Innodb.


Code:
$query = "select crmentity.crmid, account.accountname,
               accountbillads.city, accountbillads.code,
               accountbillads.country, account.website,
               account.phone, crmentity.smownerid,
               accountextra1.customernumber, accountscf.* 
from account
inner join crmentity on crmentity.crmid=account.accountid
inner join accountbillads on acount.accountid=accountbillads.accountaddressid
inner join accountshipads on account.accountid=accountshipads.accountaddressid
inner join accountextra1 on account.accountid=accountextra1.accountextra1id
inner join accountscf on account.accountid = accountscf.accountid
where crmentity.deleted=0";

[Bushwack]

Yes, I understand what query you are talking about.

What I'd like to see is what the out put of running that query with the word describe in front of it is.

You'll also want to test the final query being executed on the list page as the conditional statements which get added to it probably effect the query plan. See my first post in this thread for how to find this query.

[steven_doempke]

Ok, here is the output of the list query with describe prepended to it.

Code:
+----+-------------+----------------+--------+-----------------+---------+---------+-----------------------+-------+--------------------------+
| id | select_type | table          | type   | possible_keys   | key     | key_len | ref                   | rows  | Extra                    |
+----+-------------+----------------+--------+-----------------+---------+---------+-----------------------+-------+--------------------------+
|  1 | SIMPLE      | crmentity      | ref    | PRIMARY,deleted | deleted |       4 | const                 | 24440 | Using where; Using index |
|  1 | SIMPLE      | account        | eq_ref | PRIMARY         | PRIMARY |       4 | crm.crmentity.crmid   |     1 |                          |
|  1 | SIMPLE      | accountbillads | eq_ref | PRIMARY         | PRIMARY |       4 | crm.account.accountid |     1 |                          |
|  1 | SIMPLE      | accountshipads | eq_ref | PRIMARY         | PRIMARY |       4 | crm.account.accountid |     1 | Using index              |
|  1 | SIMPLE      | accountextra1  | eq_ref | PRIMARY         | PRIMARY |       4 | crm.account.accountid |     1 |                          |
|  1 | SIMPLE      | accountscf     | eq_ref | PRIMARY         | PRIMARY |       4 | crm.account.accountid |     1 | Using index              |
+----+-------------+----------------+--------+-----------------+---------+---------+-----------------------+-------+--------------------------+

[Bushwack]

Well it looks like the problem is simply the number of rows being pulled.
Code: SELECT * FROM crmentity WHERE deleted=0; would probably take just about as long.

Giving the query a limit clause or a hardware upgrade are probably your only solutions at this point.

What hard drive setup is that database running off of? Clustering on the crmentity deleted index may help, but I don't know if mysql can do something like that.

[steven_doempke]

I fixed the problem for now by implementing LIMIT for the most important Listviews.
And i've added a session cache for (almost) static database data like the layout and userid -> username.