Is there any way to speed up Fabrik?

[Bren]

It seems like Fabrik is only taking longer to load pages; as more data is added to my site. I saw on the roadmap for Fabrik version 3.5 that you guys are planning migrating the data in the meta tables into JSON files to speed things up. If that is what it takes then I can't wait for version 3.5. I just hope my users don't lose there patience in the mean time. Is there anything I can do to speed up Fabrik for now?
Thanks in advance.

 

[troester]

Depends pn your setup. If you have joins check directly in your database if the elements/columns used in the joins have indexes set. Especially if you have e.g dbjoin elements set to not using the recommended id as value.
I think in the recent GitHub there's also some more indexing added.

gesendet mit Tapatalk

 

[rob]

As Troester say's its 99% likely that you have a missing index in your database tables.

To see if that is the case, turn on fabrik's global configuration debug setting.
Then reload the list page that is taking a long time
You should see an accordian with lots of debug out put
One of those will contain the main query that your list uses
Copy that and then paste
"Explain {paste the query here}"
into phpmyadmin's query field

A tutorial on using Explain can be found here http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

Hopefully the output should give you a lead on where to put the index

 

[Bren]

Thanks guys. This is great information. I'll try it out. Thanks.

 

[Bren]

Hi Rob.
I read the article per the link you shared. It was good. I didn't have a chance to try the Fabrik debug until now. With fabrik debug on and then rerunning my list I do not see the accordian as you describe. Is that a good thing? Even if I did see the accordian; I'm a little confused about indexes relating to Fabrik. I'm used to following Fabrik's suggestions on database joins of using a primary key with database joins. I'm not really sure how else I deal with Indexes in Fabrik. Could you help me understand (possibly with some examples) on how to deal with indexes in Fabrik?
Thanks in advance.

 

[Bren]

Recently, in the past couple of days, one of my Fabrik lists started taking forever to load (like 30 to 45 seconds) and it's only a list pre-filtered to display on average about 30 records. What's strange is the other pre-filtered lists that are identical (except for 1 field with a different selected databasejoin item) are displaying as quickly as normal. So, this prompted me to dive back into your above suggestions and in search for any databasejoin elements that are not linked via the id (recommended) value. I found 4 of these in this list. One of the 4 is in the pre-filter menu item. What is strange is the value doesn't change between this slow list and the faster lists. So, I went ahead and tested changing the other 3 (in an Akeeba backup / restore copy in my test localhost environment) to be linked via the id (recommended) value. Unfortunately, this didn't help at all. So, I tried, again, to generate the Fabrik debug mode accordion and still could not. I also tried it with Joomla error reporting on and still nothing. Any suggestions...
Thanks in advance.

 

[cheesegrits]

I'd have to take a look at your site to make any suggestions. Fill out your My Sites. I'll probably need phpMyAdmin access as well.

-- hugh

 

[Bren]

Hi Hugh.

I would be glad to let you look at it; but unfortunately it's a closed internal network site with no outside access (except via VPN). So, the best I could do is a JoinMe session some time.

 

[thomasmes]

If possible, you can try to activiate the mysql slow queries logon your server. This might give you more information about the actual queries that are causing the delay. You need ssh access to your server to do this and can not be done within Joomla or Fabrik as far as I know.

 

[Bren]

Thanks for the tip, Thomasmes.
So, I turned on mysql slow queries log and then opened my fabrik list that took a long time to load, previously; but now loads fast. Not sure why; but below is what was also written to the mysql slow queries log:

wampmysqld, Version: 5.6.17-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: /tmp/mysql.sock
Time                Id Command    Argument
# Time: 150725 11:01:12
# User@Host: root[root] @ localhost [::1]  Id:    1
# Query_time: 0.066536  Lock_time: 0.066536 Rows_sent: 0  Rows_examined: 0
use gilesoffice;
SET timestamp=1437836472;
SELECT `query`
FROM `ido1i_admintools_wafexceptions`
WHERE (`option` IS NULL OR `option` = '') AND (`view` IS NULL OR `view` = '')
GROUP BY `query`
ORDER BY `query` ASC;
# Time: 150725 11:01:13
# User@Host: root[root] @ localhost [::1]  Id:    2
# Query_time: 0.001033  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1437836473;
SELECT `query`
FROM `ido1i_admintools_wafexceptions`
WHERE (`option` IS NULL OR `option` = '') AND (`view` IS NULL OR `view` = '')
GROUP BY `query`
ORDER BY `query` ASC;

How does this look and why is it opening fast now? I'm happy; but confused.
Thanks in advance.

 

[Bren]

Actually, I take it back. I just cleaned my cache and tried again and this time it took a long time to load the list again. Attached is what the mysql slow queries log wrote this time (it was too long to paste here).
Still not sure what to do with all this info. Any suggestions...
Thanks in advance.

 

[thomasmes]

The Query times and Lock times in this list look fine. There all way below 1 second. Look in the total log for the query (or queries) with higher Query times and Lock times.

 

[Bren]

So, I looked at the longer Query times; which was Query_time: 0.042031. All of the rest were micro seconds. I ran this query in phpadmin and it returned the 30+ rows, very quickly,which eventually display on my website with a lot more patience. So, I decided to restore an Akeeba backup from the day before I noticed this recent issue and the list loads as quick as the others. I compared the list records and they are identical. I'm lost as to what the issue is here. The only thing I added to the site the night before that the issue started was the "editinlist" Fabrik list plug-in. I also added it to my other lists that night. Since the other lists don't have any issues then I thought it was unrelated. Now I'm not sure. I would hate to deactivate the plug-in because all of my users love it. Are there any known performance issues with using the "editinlist" Fabrik list plug-in?
Thanks in advance.

 

[cheesegrits]

Not that I know of.

I'd really have to get my hands on the site, as I mentioned before.

I'd suggest trying to add the inline edit (I assume that's what you mean?) again, and see if it happens again (obviously take a backup first!).

-- hugh

 

[Bren]

Hi Hugh.
I tried adding the inline edit list plugin to my backup (before the list got slow) and it didn't slow down the list. Then I made a current backup, restored it to my localhost, removed the inline edit list plugin from the slow list and it didn't speed up the list at all. So, I'm now thinking it has nothing to do with the issue.
OK, I'll post a current restored backup to a temp sub-domain & add it to the sites section of my profile to let you take a look; but how do you do that? When I visit my profile and the sites tab it says: "No sites set up yet, or you do not have permission to view this content."
Thanks in advance.

 

[cheesegrits]

http://fabrikar.com/you/my-sites

-- hugh

 

[Bren]

Thank you, Hugh. I just added it to mysites. Please fee free to do what ever you need to the site; as it's just a backup test. The slow page load is per menu item Contracts > Approved Jobs from the left slide out menu. You'll notice it is much slower than all the other pages.
Thanks in advance.

 

[cheesegrits]

It's your 'duration' calc. I don't know why it's so slow but it is. You may have to find a different way of calculating the duration.

Also you had a syntax error in 'remainingbudgetcost' (missing closing semicolon).

And your 'tracklaborhoursremaining' seems to be empty, so that's tossing a PHP warning.

Anyway ... test it on your live site ... unpublish 'duration', and you'll see it speed up.

-- hugh

 

[Bren]

Awesome. Will do. Thanks you so much, Hugh.

 

[Bren]

How did you manage to turn on the Fabrik Debug mode. I couldn't make it work; but maybe I'm enabling it wrong. I tried Fabrik Options > Debugging Tab > Allow fabrikDebug = No to turn it off on the test site; but it's still displaying the Fabrik Debug on accordian on the front & back ends (even after cleaning the cache). Am I missing a step?
Thanks in advance.