performance problems

[rtrauer]

Dear Fabrik Support Team,

We are using Fabrik on our website for a couple years with no problems, and we are growing in user accounts and data that is stored.
One of our application build with fabrik is for home-automation, where we generate a customized template, for smartphone or tablet, that is generated based on the choice of the manufacturer and model of TV, BD, Receiver, Air Conditioning, and other typical home appliances controlled by IR codes.

Everything was doing fine, until the database is becoming larger, with more customers and appliances models being added to our database.

But the numbers of records in the DB are relatively small, and we are getting response time from the server over 15 seconds to render a list or to edit a stored configuration (one record in the fb_ir_template_config table).

To separate this application from the joomla website, all template-generator related tables are stored in a separate database, that contains the following tables:

fb_ir_template_config - stores the configuration options of the template - 389 records
fb_ir_model - stores the manufacturer, device model of the IR database for each appliance - 176 records
fb_ir_codes - stores the IR commands for each appliance model - 3642 records
cb_ir_manufacturers - stores the name of appliance manufacturers - 63 records
fb_ir_tv_chanels - stores cable TV channels and image file - 120 records
... others

The Search query that populates the form is huge, due to the database join elements that we use and is causing a long response time from the server, reaching 20 to 26 seconds to process this single query.

SELECT SQL_CALC_FOUND_ROWS DISTINCT `fb_ir_template_config`.`id` AS `fb_ir_template_config___id`, `fb_ir_template_config`.`id` AS `fb_ir_template_config___id_raw`, `fb_ir_template_config`.`ir_config_tv_manuf` AS `fb_ir_template_config___ir_config_tv_manuf_raw`, `fb_ir_model_4`.`manufacturer` AS `fb_ir_template_config___ir_config_tv_manuf`, `fb_ir_template_config`.`ir_config_tv_model` AS `fb_ir_template_config___ir_config_tv_model_raw`,
...
a long long list
...
`fb_ir_template_config`.`id` = '306'AND (( fb_ir_template_config.user_id = '1045' )) ORDER BY `fb_ir_template_config`.`id` ASC

This query takes 20.8 sec to process using PHPMyAdmin to access the MySQL server directly.
Yesterday, this query was generating alerts in our ISP with more than 1000 seconds processing time.
This was improved by optimizing all MySQL tables and by setting some elements to include in list query to yes.

• Include in list query​
  ​

  No​

  ​

  Yes​

I noticed that when this option is set to NO then the list is generated much faster, but the form editing gets slower. I don?t know how I can optimize this further.

I found out that a much better result could be obtained with a 2 modifications
eliminating the 'DISTICT' and adding 'LIMIT 1' at the end of the query.
"SELECT SQL_CALC_FOUND_ROWS `fb_ir_template_config`.`id` AS
....
`fb_ir_template_config`.`id` ASC LIMIT 1"

This would greatly improve the server response time, since the modified query takes only 0.5896 sec
to process, with the same huge amount of elements and joins. Thats 34 x faster !

So my questions are:
Can this be hacked into the fabrik code that we use in our server ? I have no idea if this would result in other problems along other fabrik lists and forms that we use in our website.
And have no idea how to make this modification either.

Is there any other idea how to improve performance in our fabrik applications ?

Currently, we are using Joomla 2.5.27
Fabrik 3.0.9???

Thanks in advance for your time.
Regards

 

[troester]

DISTINCT: edit your list, in "Advanced" set "add Distinct"=no
Not sure if you have to update from GitHub-joomla25-branch to see this option (backup before updating)

Performance in general: check if the table columns used in the joins have indexes (resp. create them manually).
In Fabrik3.3 (should be published tomorrow, but it's for Joomla3.4 only) there's an improved index handling.

 

[cheesegrits]

As per Troester's answer, it could well be you need to add some indexes. There were some issues with the way we automatically create indexe the foreign keys in joins, which we've fixed in 3.3.

Best way to tell id copy that big list query, and use phpMyAdmin to "explain" it. If you google "mysql explain" you'll find a number of useful guides for interpreting what it tells you.

But in general, make sure that for any JOIN in that query, both "ends" of the join have indexes.

Also, anything used in filtering (the WHERE clauses) needs to have an index.

-- hugh

 

[rtrauer]

I sorted the problem out by modifying the indexes of joined tables. A good improvement was obtained by changing the index from manufacturers to manufacturer_id and other chances similar to that, were the index is now a numeral, rather than a name. I?m posting this here to close this post.

Thanks for the help.
Regards,
Ricardo

p.s. Now we are in the way of moving from joomla 2.5 to 3.4 and this time we are rebuilding the website.