database icon indicating copy to clipboard operation
database copied to clipboard

Published 20 hours ago • verified publisher icon nette

Nette 2.3 problem with Nette\Database\Structure for huge database

Open lukyrys opened this issue 9 years ago • 7 comments

After upgrade nette from 2.0.7 to 2.3.5 in my app i detected masive grow execution time and memory consuption at \Nette\Database\Structure after unserialize cached data. I have huge database with 6k tables and cached data in serialize structure string have 23MB. (by profiler - +180MB memory, extra 1000ms execution time) Before updating nette this not problem. Why?

lukyrys avatar Oct 04 '15 03:10 lukyrys

Can you send any other profiler info? Like comparison that two versions, most function calls, most time consuption functions...

adaamz avatar Mar 19 '16 20:03 adaamz

NDBT is not suitable for huge databases, sorry.

dg avatar Mar 20 '16 09:03 dg

The problem is generating the database structure and then serialize / unserialize from cache .. Big serialize string consumes a lot of time to unserialize. I solved it with custom static structure without cache because 95% my tables has same structure ..

lukyrys avatar Mar 20 '16 11:03 lukyrys

We use StaticConventions too. Because no FK, because we use structure which can be defined constantly easily.

adaamz avatar Mar 20 '16 12:03 adaamz

I was thinking, now structure provides lots of informations which are not needed for NDBT. NDBT needs only table names, lists of primary keys, referencing relations and sequences. I make small test on my database with updated Structure (store relevant informations instead of full informations there https://github.com/nette/database/blob/master/src/Database/Structure.php#L179).

Current version Load structure from cache: +- 60ms File size: 312kb

Store only relevant informations Load structure from cache: +- 8ms File size: 21kb

So, we can refactor Structure to provide only relevant informations for nette, but it will be in my opinion big BC break. Or we can implement some kind of smart cache, when cache will be separated into 2 parts. One parts with basic data needed for nette and second especially for getColumns() method.

Unlink avatar Jun 10 '16 19:06 Unlink

So, we can refactor Structure to provide only relevant informations for nette

:+1:

I did not know that it saves something what is not needed.

dg avatar Jun 11 '16 16:06 dg

Nette don't need informations about columns, so sth. like this will work, but it is BC break... https://github.com/Unlink/database/commit/699bf693976bf05ab25d7ae0b6d43d27babbd548

Unlink avatar Jun 11 '16 19:06 Unlink