(05-29-2021, 05:17 AM)InsiteFX Wrote: CodeIgniter's caching may help in this matter.
Thanks for the suggestion InsiteFX. So far I've mostly ignored CI caching, having left it on a list of things to do once I had something up and running... I'll have another look over the caching section of the user manual to see if there's anything that I can currently push into my code.
(05-29-2021, 09:01 AM)includebeer Wrote: How many rows do you have? I have made a test app to try the paginate function and with over 500 rows it is super quick. Did you forgot to add a primary key to your table? Maybe MySQL always do a full table scan instead of using the index on the id column.
Thanks includebeer, your test and observation made me put the whole offending function (query set-up, and execution, pagination and prep for the View) in a for() loop... perhaps something I should have tried from the offset (clearly programming under the influence isn't always helpful)... and found that it had to loop many thousands of times before the delay became observable on the client (and in spite of this, the reported processor loading didn't seem to change significantly, even thought the process was taking fractionally longer)... which seems points toward something pushing the processor into a wait-state (which typically counts at 100% usage during the wait portions of the time 'slices') during (or overlapping with) the pagination function, such that the > 5000x more processing still doesn't make an appreciable difference to the overall work-load (I have double checked MariaDB has the query cache disabled (which it's supposed to be by default from 10.1.7)).
My test data-set currently only has about 30 records... but I had made certain to ensure that (when it grows) any query (this particular one included) that may have even the slightest chance of being time-consuming and impact on performance had a covering index to minimise the damage... which was a large part of my disbelief and dismay at the notable poor performance and heavy processor load during the function call. In my initial investigation, I had commented out anything that might have even slightly impeded the displaying (in the View), and done some eliminations in the function invoking the pagination (which appeared to make the problem go away, and lead me to mistakenly finger pagination as the guilty party).
The weird thing is the View and response prep routines are universal throughout the whole site, as is the pagination functionality) and I'm not seeing any of this processor loading where there's no significant pagination effort required.
The other observation is that with the server taking longer to respond, the client (currently run on the same development machine) is behaving in a way that I can only describe as more smoothly with it's page update (the client side shows an indication that the server is busy)... so perhaps there's some interaction or wait-state bug with my JavaScript code that's now showing up on the Linux machine for the first time. Or perhaps I'm just grasping at straws.
There is a fair amount of JavaScript flapping around on the client side... though, here again, this is the same routines that service the rest of the site... but as I can't think of anything else that's simple to try, I'll move the client off the development machine and check if there's any difference in the processor loading...
