performance issues

[v.]

I am in the process of rewriting an old webapp, from pure PHP to F3.

The old app works well but is in dire need of an update for all sorts of reasons.

I am experiencing quite important performance issues in the f3 version, especially when accessing the DB.

The testing DB contains around 70K items and around 250 columns. It takes quite some time when I try to create a new one or modify an existing one.

The f3 version runs in a subdirectory of the PHP version, so the server can't be the issue.

I have tried on another server with a copy of the live database (200K rows). This returns 504 time outs.

I have included a simple execution time script like this: 

    function beforeroute() 

{

$this->f3->set('SESSION.timestart', microtime(true));

$this->f3->set('SESSION.timestart', microtime(true));
......

function afterroute() 

{

$exectime=(microtime(true) - $this->f3->get('SESSION.timestart'))/60;

$this->f3->logger->write($this->f3->get('PARAMS.0')." executed in ". $exectime, 'r');
......

which returns values well below 0.01.

I am puzzled what is causing this delay. 

Could this still be a f3 thing or should I look elsewhere? Where?

[Paul Herring]

> 70K items and around 250 columns

I presume you are being selective about what you're querying - restricting either the columns to those you actually need, and/or the rows that you'll actually be doing stuff with.

Otherwise, if you're simply doing the equivalent of `select * from table`, then the framework is going to grind through all of the results assigning them to variables.

--
-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en
---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/b9e81953-1562-4d38-8991-779145308fe1%40googlegroups.com.

--

PJH

[v.]

of course, I never load more than 1 item., although I may need all columns (which I don't think should be an issue).

Also: if this were the problem, it would show in the very sophisticated timing script, no?

[richgoldmd]

I'd be curious to see a sample of the db code or what's in the controller.

If is an issue occurring after afterroute() executes it could have something to do with the output  - is something very large being dumped out?

Also your timer functions should use a local var for the time, not SESSION because the timing data need not be saved across sessions and ai think could be subject to some issues and performance problems itself.

I would also consider if you have a redirect loop happening for unrelated reasons. And reroutes based on session data?

[richgoldmd]

Did you ever get to the bottom of this issue?

On Wednesday, April 29, 2020 at 10:57:04 AM UTC-4, v. wrote:

[v.]

Hi Doc,

Sorry for not getting back into this yet, I am still trying to figure out where the problem is and I did not want to flood this forum with my findings.

I have installed the exact same script on 3 different hosting accounts.

On the original account it seems, although it is noticably slower than the original native PHP script (I understand it would be a bit slower than native PHP, but not to this extent).

On a VPS it is just unusable with many 504 timeouts when accessing the DB. I have found that there were issues on the VPS though, had the server restarted and upped the HDD cache. It seems to be a bit more stable now, but I need to run some more tests on this.

On a third server with a copy of the complete production DB it runs very smoothly.

I am still at a loss and am wondering how to figure out what causes the slowness on the first and second server.

I will post more info here when I have some time to run more tests on the restarted vps

[Paul Herring]

> On a third server with a copy of the complete production DB it runs very smoothly.

Using that one, can you verify how many records your query is returning, and compare it with how many you think it should be (which is 1 from your earlier mail.)

If it is, can you turn on profiling on the DB servers, to see if that's the bottleneck?

Just throwing some suggestions in...

--

PJH

[ikkez]

Have a look at the sql log of F3 and see what really was queried.
What’s the loading time?

[v.]

Hi,

please check inline:

On Saturday, May 2, 2020 at 4:11:27 PM UTC+2, PJH wrote:

> On a third server with a copy of the complete production DB it runs very smoothly.

Using that one, can you verify how many records your query is returning, and compare it with how many you think it should be (which is 1 from your earlier mail.)

It is an insert query, i never noticed before, but the f3 mapper actually seems to perform a select query after the insert when you use the save command.

If it is, can you turn on profiling on the DB servers, to see if that's the bottleneck?

Unfortunately I do not have access to this functionality on my DB

[v.]

Hi ikkez, it seems you put me on the right track.

I actually performed a select query on a non-indexed field before the insert. This was the culprit that slowed everything down, adding an index to this column sped things up by a factor 1000.

Thanks a bunch for your help in figuring this out.

[PJH]

On Monday, May 4, 2020 at 8:41:10 AM UTC+1, v. wrote:

On Saturday, May 2, 2020 at 4:11:27 PM UTC+2, PJH wrote:

> On a third server with a copy of the complete production DB it runs very smoothly.

Using that one, can you verify how many records your query is returning, and compare it with how many you think it should be (which is 1 from your earlier mail.)

It is an insert query, i never noticed before, but the f3 mapper actually seems to perform a select query after the insert when you use the save command.

https://github.com/bcosca/fatfree/blob/master/lib/db/sql/mapper.php#L483

// Reload to obtain default and auto-increment field values

Glad you got it sorted though.

 

[ikkez]

Sounds like the issue describes here https://github.com/bcosca/fatfree/issues/1175

I guess we should implement the suggested fix from xfra35

[v.]

Hi Ikkez,

I'm not sure this is the same issue, the fault here was entirely mine: I should have set an index on the DB field I was querying before the actual insert. The problem was not the select query after the insert, rather a select query I programmed to run before the insert.