search

nette / database

💾 A database layer with a familiar PDO-like API but much more powerful. Building queries, advanced joins, drivers for MySQL, PostgreSQL, SQLite, MS SQL Server and Oracle.
https://doc.nette.org/database
Other
512 stars 108 forks source link

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

Open lukyrys opened 9 years ago

lukyrys commented 9 years ago

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?

adaamz commented 8 years ago

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

dg commented 8 years ago

NDBT is not suitable for huge databases, sorry.

lukyrys commented 8 years ago

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 ..

adaamz commented 8 years ago

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

Unlink commented 8 years ago

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.

dg commented 8 years ago

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.

Unlink commented 8 years ago

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