-
New Commercial Services Section
We have now opened a commercial services section here on the forum for registered users. If you have a Fabrik project that you wish to have someone work on for you, post it under Help Wanted. If you are an application developer and wish to earn some money helping others, post your details under Fabrik Application Developers.
Both of these are unmoderated. It will be up to both parties to work out the details and come to an agreement.
-
Joomla 5.1
For running J!5.1 you must https://fabrikar.com/forums/index.php?wiki/update-from-github/ or include the new file manually https://fabrikar.com/forums/index.php?threads/joomla-5-1-and-fabrik-cannot-find-files-error.54473/post-285151 See also Announcements
performance problem
- Thread starter toschi
- Start date
I tried the mysqltuner script with the following output:
[--] Up for: 1d 0h 52m 5s (68K q [0.761 qps], 2K conn, TX: 185M, RX: 29M)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 3.7G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 4.1G (72% of installed RAM)
[OK] Slow queries: 0% (0/68K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 3.4G/307.0K
[OK] Key buffer hit rate: 99.9% (461K cached / 591 reads)
[OK] Query cache efficiency: 72.4% (33K cached / 46K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Joins performed without indexes: 2333
[!!] Temporary tables created on disk: 36% (7K on disk / 21K total)
[OK] Thread cache hit rate: 99% (5 created / 2K connections)
[!!] Table cache hit rate: 9% (292 open / 3K opened)
[OK] Open file limit used: 0% (20/2K)
[OK] Table locks acquired immediately: 100% (35K immediate / 35K locks)
[OK] InnoDB data size / buffer pool: 12.7M/128.0M
any Ideas?
[--] Up for: 1d 0h 52m 5s (68K q [0.761 qps], 2K conn, TX: 185M, RX: 29M)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 3.7G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 4.1G (72% of installed RAM)
[OK] Slow queries: 0% (0/68K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 3.4G/307.0K
[OK] Key buffer hit rate: 99.9% (461K cached / 591 reads)
[OK] Query cache efficiency: 72.4% (33K cached / 46K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Joins performed without indexes: 2333
[!!] Temporary tables created on disk: 36% (7K on disk / 21K total)
[OK] Thread cache hit rate: 99% (5 created / 2K connections)
[!!] Table cache hit rate: 9% (292 open / 3K opened)
[OK] Open file limit used: 0% (20/2K)
[OK] Table locks acquired immediately: 100% (35K immediate / 35K locks)
[OK] InnoDB data size / buffer pool: 12.7M/128.0M
any Ideas?
cheesegrits
Support Gopher
We have sites with tables with millions of rows, so it's definitely something to do with your setup.
Do you have any cascading dropdown elements?
Do you have any join or CDD elements to other tables which you are using as filters on the list?
When you say "performance", do you mean just on list display? Or when submitting a form?
Try enabling J! debug. That will give you a profile section at the bottom of every page, which should include a bunch of information from Fabrik, with memory usage and timing info at various stages during page rendering. Copy and paste the profile data here, I'll take a quick loo see if there's anything obvious.
If it's slow list loading, the other thing you can do is enable Fabrik debugging (in the global Fabrik options), append &fabrikdebug=1 to the page URL. That shuld output a bunch of debugging in at the top of the page. if it's a slow ist load, find the "getData" section, which should show you a large query which is the one fetching the main table data. You can then copy that query, and get phpMyAdmin to "explain" it (google to find out how to "explain" a query), which should show you any obvious problems, like a missing index, etc.
-- hugh
Do you have any cascading dropdown elements?
Do you have any join or CDD elements to other tables which you are using as filters on the list?
When you say "performance", do you mean just on list display? Or when submitting a form?
Try enabling J! debug. That will give you a profile section at the bottom of every page, which should include a bunch of information from Fabrik, with memory usage and timing info at various stages during page rendering. Copy and paste the profile data here, I'll take a quick loo see if there's anything obvious.
If it's slow list loading, the other thing you can do is enable Fabrik debugging (in the global Fabrik options), append &fabrikdebug=1 to the page URL. That shuld output a bunch of debugging in at the top of the page. if it's a slow ist load, find the "getData" section, which should show you a large query which is the one fetching the main table data. You can then copy that query, and get phpMyAdmin to "explain" it (google to find out how to "explain" a query), which should show you any obvious problems, like a missing index, etc.
-- hugh
Hi thankes for your answer, here is the output of the "list GetData:Artikeldatenbank":
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fab_artikel`.`id` AS `fab_artikel___id`, `fab_artikel`.`id` AS `fab_artikel___id_raw`, `fab_artikel`.`date_time` AS `fab_artikel___date_time`, `fab_artikel`.`date_time` AS `fab_artikel___date_time_raw`, `fab_artikel`.`fab_artikelname` AS `fab_artikel___fab_artikelname`, `fab_artikel`.`fab_artikelname` AS `fab_artikel___fab_artikelname_raw`, `fab_artikel`.`fab_beschreibung_kurz` AS `fab_artikel___fab_beschreibung_kurz`, `fab_artikel`.`fab_beschreibung_kurz` AS `fab_artikel___fab_beschreibung_kurz_raw`, `fab_artikel`.`fab_vorgangsnummer` AS `fab_artikel___fab_vorgangsnummer`, `fab_artikel`.`fab_vorgangsnummer` AS `fab_artikel___fab_vorgangsnummer_raw`, `fab_artikel`.`fab_beschreibung` AS `fab_artikel___fab_beschreibung`, `fab_artikel`.`fab_beschreibung` AS `fab_artikel___fab_beschreibung_raw`, (SELECT GROUP_CONCAT(fab_bilder SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_bilder WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_bilder`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_bilder WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_bilder_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_bilder WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_bilder___params`, (SELECT GROUP_CONCAT(fab_files SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_files WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_files`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_files WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_files_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_files WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_files___params`, (SELECT GROUP_CONCAT(fab_aenderungsmeldungen SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_aenderungsmeldungen WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_aenderungsmeldungen`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_aenderungsmeldungen WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_aenderungsmeldungen_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_aenderungsmeldungen WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_aenderungsmeldungen___params`, (SELECT GROUP_CONCAT(fab_filesextra SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_filesextra WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_filesextra`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_filesextra WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_filesextra_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_filesextra WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_filesextra___params`, `fab_artikel`.`fab_artikeluser` AS `fab_artikel___fab_artikeluser_raw`, `wfkhb_users`.`name` AS `fab_artikel___fab_artikeluser`, `fab_artikel`.`fab_artiklanderunguser` AS `fab_artikel___fab_artiklanderunguser_raw`, `wfkhb_users_0`.`name` AS `fab_artikel___fab_artiklanderunguser`, `fab_artikel`.`id` AS slug , `fab_artikel`.`id` AS `__pk_val` FROM `fab_artikel` LEFT JOIN `#__users` AS `wfkhb_users` ON `wfkhb_users`.`id` = `fab_artikel`.`fab_artikeluser` LEFT JOIN `#__users` AS `wfkhb_users_0` ON `wfkhb_users_0`.`id` = `fab_artikel`.`fab_artiklanderunguser` ORDER BY `fab_artikel`.`id` DESC
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fab_artikel`.`id` AS `fab_artikel___id`, `fab_artikel`.`id` AS `fab_artikel___id_raw`, `fab_artikel`.`date_time` AS `fab_artikel___date_time`, `fab_artikel`.`date_time` AS `fab_artikel___date_time_raw`, `fab_artikel`.`fab_artikelname` AS `fab_artikel___fab_artikelname`, `fab_artikel`.`fab_artikelname` AS `fab_artikel___fab_artikelname_raw`, `fab_artikel`.`fab_beschreibung_kurz` AS `fab_artikel___fab_beschreibung_kurz`, `fab_artikel`.`fab_beschreibung_kurz` AS `fab_artikel___fab_beschreibung_kurz_raw`, `fab_artikel`.`fab_vorgangsnummer` AS `fab_artikel___fab_vorgangsnummer`, `fab_artikel`.`fab_vorgangsnummer` AS `fab_artikel___fab_vorgangsnummer_raw`, `fab_artikel`.`fab_beschreibung` AS `fab_artikel___fab_beschreibung`, `fab_artikel`.`fab_beschreibung` AS `fab_artikel___fab_beschreibung_raw`, (SELECT GROUP_CONCAT(fab_bilder SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_bilder WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_bilder`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_bilder WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_bilder_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_bilder WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_bilder___params`, (SELECT GROUP_CONCAT(fab_files SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_files WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_files`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_files WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_files_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_files WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_files___params`, (SELECT GROUP_CONCAT(fab_aenderungsmeldungen SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_aenderungsmeldungen WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_aenderungsmeldungen`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_aenderungsmeldungen WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_aenderungsmeldungen_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_aenderungsmeldungen WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_aenderungsmeldungen___params`, (SELECT GROUP_CONCAT(fab_filesextra SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_filesextra WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_filesextra`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_filesextra WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_filesextra_raw`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM fab_artikel_repeat_fab_filesextra WHERE parent_id = `fab_artikel`.`id`) AS `fab_artikel___fab_filesextra___params`, `fab_artikel`.`fab_artikeluser` AS `fab_artikel___fab_artikeluser_raw`, `wfkhb_users`.`name` AS `fab_artikel___fab_artikeluser`, `fab_artikel`.`fab_artiklanderunguser` AS `fab_artikel___fab_artiklanderunguser_raw`, `wfkhb_users_0`.`name` AS `fab_artikel___fab_artiklanderunguser`, `fab_artikel`.`id` AS slug , `fab_artikel`.`id` AS `__pk_val` FROM `fab_artikel` LEFT JOIN `#__users` AS `wfkhb_users` ON `wfkhb_users`.`id` = `fab_artikel`.`fab_artikeluser` LEFT JOIN `#__users` AS `wfkhb_users_0` ON `wfkhb_users_0`.`id` = `fab_artikel`.`fab_artiklanderunguser` ORDER BY `fab_artikel`.`id` DESC
cheesegrits
Support Gopher
Yup, that looks like a standard getData query - they are long!
I think the problem may be in those repeated join tables.
After your initial post, I had a look through some of my tables, and noticed that there may be an issue with us not creating indexes on the parent_id FK's when we create the tables for repeated groups. I'm working on tracking that down and fixing it.
Meanwhile, I suggest you use something like phpMyAdmin, and check that the parent_id in each of those joined tables has an index on it. If not, create one. Call the index something like idx_parent_id, index type BTREE.
That may well speed up the query.
-- hugh
I think the problem may be in those repeated join tables.
After your initial post, I had a look through some of my tables, and noticed that there may be an issue with us not creating indexes on the parent_id FK's when we create the tables for repeated groups. I'm working on tracking that down and fixing it.
Meanwhile, I suggest you use something like phpMyAdmin, and check that the parent_id in each of those joined tables has an index on it. If not, create one. Call the index something like idx_parent_id, index type BTREE.
That may well speed up the query.
-- hugh
cheesegrits
Support Gopher
Just FYI, I added the code to create indexes on the parent_id FK's when we create those map tables for the multi select join elements. if you update to the latest github, and then edit and save the any joins that are set to 'checkbox' or 'multi select dropdown', we should create the indexes for you.
-- hugh
-- hugh