1. 

   eugfri, Member

   hi,

   I am running latest version of mydbr (5.5.0) on XAMPP. It is very important for my customer for one of the reports/screens in my application to be as fast as possible. I have timed the stored proc producing this report and its execution takes about 1 second. When I look at report stats in MyDBR it actually shows execution time being between 2 to 4 seconds. However, when I time the report as user would see it in browser, it actually seems much longer - could be 5-7 seconds from the click on submit button till the page is fully rendered.
   I must say the resultset I have is very small - probably no more than 10 rows. There are no charts or graphs, just some basic columns with text. There is styling and some conditional display of the columns (logic is in the proc). Plus, most columns are clickable as sub-reports or external links etc.

   I am wondering what is the expected ratio between stored proc run time and page rendering time should be in case like I have described?
   What can be done to improve performance (besides stored proc optimization of course)?

   Thank you

   Eugene
2. 

   myDBR Team, Key Master

   In order for myDBR to construct the sub-report links, it requires more than just the execution of the main report (checking permissions, getting expected parameters, etc), so you cannot directly compare the main report's execution time for the full process.

   Having the execution time of 5-7 seconds sounds a bit long even with sub-reports attached. This may come from the server configuration. We've had incidents where some MySQL servers are slow with their INFORMATION_SCHEMA, which myDBR uses to query report parameters (including the sub-report's). You can see if this is causing the slowdown for you by adding following line to user/defaults.php

   $mydbr_defaults['mysql_no_information_schema_parameter'] = true;

   This will tell myDBR not to use INFORMATION_SCHEMA.PARAMETERS table, but to use an alternative method of getting the report parameter (this will also be the default in the next versions). To have this setting to be effective, make sure you are running the latest version (run the automatic updater).

   If this does not help, please contact support with more details on the report so we can have a closer look.

   --
   myDBR Team
3. 

   eugfri, Member

   Thank you!
   This config change does make the screen to render faster indeed. I see execution time reduction of 1 sec on average - based on mydbr report stats.
4. 

   myDBR Team, Key Master

   So the INFORMATION_SCHEMA.PARAMETERS-system table is the culprit. It scales up quite poorly. Can you tell what is your database version and the output of following query (if you do not want to share it in public, you can use the support email):

   select
   
     count(distinct SPECIFIC_SCHEMA) as  'dbs',
   
     count(distinct concat(SPECIFIC_SCHEMA, SPECIFIC_NAME)) as 'routines'
   
   from INFORMATION_SCHEMA.PARAMETERS;

   --
   myDBR Team
5. 

   eugfri, Member

   i have 8 dbs and 860 procs.
   I am on old version - 5.6.21

   If i understand you correctly, the fewer dbs and procs i have on my mysql instance the faster information_schema can be queried and thus rendering will be faster?

   So my options are -
   1) move anything but absolute essentials to another mysql instance
   2) upgrade mysql to 8.0+ which hopefully does not have scaling issue for information_schema.parameters and other system tables
   3) possibly do both #1 and #2

   But you said if I use "$mydbr_defaults['mysql_no_information_schema_parameter'] = true;" (which I do now) - then mydbr won't be using information_schema.parameters...
6. 

   myDBR Team, Key Master

   The good news is that you do not need to do anything. Everything is fine.

   myDBR uses for now, by default, INFORMATION_SCHEMA.PARAMETERS which is the standard way of getting parameter info for stored procedures. Unfortunately, the implementation for this system view is far from perfect. If you have a system with large number of procedures (and use server with hard disks), a simple query against the view can take 0.5 sec. If you also happen to have more complex report with 10 linked reports, the slowdown can be up to 5 seconds.

   The next version of myDBR (will be out soon) no longer uses INFORMATION_SCHEMA.PARAMETERS, but rather parses the data from elsewhere. Using the mysql_no_information_schema_parameter will do the same in the current version. So, no need to update to 8.0 for this nor to do anything else.

   --
   myDBR Team
7. 

   eugfri, Member

   yes, I do have reports with a lot of linked reports (actually more than 10), large number of stored procs in the system and use server with HDDs.

   I have used parameter you've suggested and it has improved the performance indeed.
   Thank you!
8. 

   myDBR Team, Key Master

   The newly released 5.6 relase takes care of this issue.

   --
   myDBR Team

Reply

You must log in to post.