(This follows 15341 comment 5) From https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#STARTUP-SPEED There are some tips to follow to reduce the DBIx::Class startup delays. === 1 - Statically Define Your Schema If you are using DBIx::Class::Schema::Loader to build the classes dynamically based on the database schema then there will be a significant startup delay. For production use a statically defined schema (which can be generated using DBIx::Class::Schema::Loader to dump the database schema once - see make_schema_at and dump_directory for more details on creating static schemas from a database). 2 - Move Common Startup into a Base Class Typically DBIx::Class result classes start off with use base qw/DBIx::Class::Core/; __PACKAGE__->load_components(qw/InflateColumn::DateTime/); If this preamble is moved into a common base class:- package MyDBICbase; use base qw/DBIx::Class::Core/; __PACKAGE__->load_components(qw/InflateColumn::DateTime/); 1; and each result class then uses this as a base:- use base qw/MyDBICbase/; then the load_components is only performed once, which can result in a considerable startup speedup for schemas with many classes. 3 - Explicitly List Schema Result Classes The schema class will normally contain __PACKAGE__->load_classes(); to load the result classes. This will use Module::Find to find and load the appropriate modules. Explicitly defining the classes you wish to load will remove the overhead of Module::Find and the related directory operations: __PACKAGE__->load_classes(qw/ CD Artist Track /); If you are instead using the load_namespaces syntax to load the appropriate classes there is not a direct alternative avoiding Module::Find. 4 - MEMORY USAGE Cached statements DBIx::Class normally caches all statements with prepare_cached(). This is normally a good idea, but if too many statements are cached, the database may use too much memory and may eventually run out and fail entirely.
1 - Already done 2 - We don't load any components at the moment 3 - In Koha::Schema, I have tried to replace __PACKAGE__->load_namespaces; with my $r = [ qw( Accountline Accountoffset ... Zebraqueue ) ]; __PACKAGE__->load_classes({ 'Koha::Schema::Result' => $r}); I have not noticed any gain using the following script use Koha::Database; Koha::Database->new()->schema()->resultset('Discharge'); Always between 0.85 and 0.90 second 4 - Something to try?
Created attachment 45606 [details] DBIX call stacks This graph show the time spent in DBIx::Class::Schema::load_namespace by a script which does nothing but creating a connection to Koha DB. It take half a second on recent CPU. The question for me is whether the schema creation, which implies creating thousand of classes, is redone each time a Koha page is loaded, or if it's cached under Plack.
Created attachment 48519 [details] [review] Bug 15350: Cache loaded DBIx::Class schema classes This separates the Koha::Schema creation into two stages: 1) The loading of the schemas themselves, which is done upon import of Koha::Database. This requires moving the import of Koha::Schema from an on-demand `require` to just a `use`; most everything in Koha uses C4::Context, which will indirectly end up calling Koha::Database->schema->new anyway, so this was no longer saving anything. 2) The actual database connection, which is done by cloning the schema created above and adding a database connection. Note that this saves time only when a Plack-based Koha needs to create a database connection, which is usually only the case for a new worker. Unscientific timings before patch: * First load of koha2marclinks.pl on a new worker: 0.70 seconds * Each load after: 0.17 seconds After patch: * First load: 0.31 seconds * Each load after: 0.17 seconds
(In reply to Jesse Weaver from comment #3) > Created attachment 48519 [details] [review] [review] > Bug 15350: Cache loaded DBIx::Class schema classes > > This separates the Koha::Schema creation into two stages: > > 1) The loading of the schemas themselves, which is done upon import of > Koha::Database. This requires moving the import of Koha::Schema from > an on-demand `require` to just a `use`; most everything in Koha uses > C4::Context, which will indirectly end up calling > Koha::Database->schema->new anyway, so this was no longer saving > anything. Yes, I totally agree. This was previously done when our Plack implementation was not considered stable and we did not want to load the schema everywhere. Now we want. > 2) The actual database connection, which is done by cloning the schema > created above and adding a database connection. I am not sure about this change, the Koha::Schema->connect will load all the classes (see Koha::Schema: 12 __PACKAGE__->load_namespaces;). > Note that this saves time only when a Plack-based Koha needs to create > a database connection, which is usually only the case for a new worker. > > Unscientific timings before patch: > * First load of koha2marclinks.pl on a new worker: 0.70 seconds > * Each load after: 0.17 seconds > > After patch: > * First load: 0.31 seconds > * Each load after: 0.17 seconds Are you sure it's brought by the ->clone call or just the move from require to use? :)
Created attachment 48653 [details] [review] Bug 15350: Move the import of Koha::Schema to a basic `use` This moves the import of Koha::Schema from an on-demand `require` to just a `use`; most everything in Koha uses C4::Context, which will indirectly end up calling Koha::Database->schema->new anyway, so this was no longer saving anything. Note that this saves time only when a Plack-based Koha needs to create a database connection, which is usually only the case for a new worker. Unscientific timings before patch: * First load of koha2marclinks.pl on a new worker: 0.70 seconds * Each load after: 0.17 seconds After patch: * First load: 0.31 seconds * Each load after: 0.17 seconds Jonathan, you seem to be right. NYTProf was making me think we needed to do both, but timings suggest otherwise.
Jesse, I finally don't think this patch is worth the gain: for the scripts not using Plack (misc/*), they will load of the schema even if it's not needed.
(In reply to Jonathan Druart from comment #6) > Jesse, > I finally don't think this patch is worth the gain: for the scripts not > using Plack (misc/*), they will load of the schema even if it's not needed. Maybe it is time to think Plack-wise.
Nothing more from here, closing.