Support for single database per application deployment
Closed, InvalidPublic
Actions

Asked by brainchild on Jul 26 2023, 22:35.

Details

MySQL is a database management system that follows an extremely common design pattern, that within each database cluster, an administrator may provision one or more databases, each corresponding to a deployed application.

An applications instance opens connections to an appropriate database, through which it may read, write, create, and drop tables or other schema types.

Provisioning of a database and its use by an application are considered as separate administrative activities occurring within separate spheres.

A database administrator, a person or tool responsible for administrating a database cluster, should provision each database with a unique name within the cluster, and with appropriate permissions. Name selection occurs within the context of an existing database cluster, which may include other databases, and entails the responsible use of the namespace, for preventing collisions of database names, including for separate deployment instances of the same application package.

In turn, an application administrator is responsible for provisioning an application to connect to the appropriate database, by providing the cluster, name, and credentials. Applications are capable of connecting to any database through configuration of appropriate application parameters.

The pattern, and the constraints understood within it, are quite widespread, and indeed nearly universal.

Applications assume no control over a database cluster outside of the designated operations, within a designated database, relevant to managing schema and data. Clusters are subject to no constraints except the availability of a database for each application deployment with suitable permissions.

Presently, Phorge is not following such design principles.

One consequence was discussed in Q67, but the ubiquity of the general principles provides that the present design choices would have far more widespread ramifications.

Suggested is adapting Phorge to follow the common design pattern. When provisioned, the application would be associated with a single existing database, the cluster, name, and credentials given as configuration parameters.

Event Timeline

Note that Q67 tries to make similarities between LAMP apps like nextCloud and Phorge, but nextCloud has not 400+ database tables. This is just a minimal indication of the complexity, that may indicate that this design principle may be not totally suitable for this.

This is not to indicate that it's impossible to do, it's just to indicate that your direction is not so obvious. But, anybody is encouraged to fork and work on this. Forking is good in Phorge.

Your objection concerning the reference to Nextcloud is based on quote mining. It is neither germane nor accurate.

The documentation gives no indication that 400 tables in a single database is inherently problematic.

I also doubt the underlying storage of tables in a cluster, or operation for query processing, is particularly different based on whether the tables are spread in many databases within the cluster versus consolidated within one.

I have given technical arguments that I consider sound. It is entirely your choice whether to engage them, but I feel it is unhelpful to circumvent them through constructing a straw man.

Database management systems are designed to scale. Phorge is not the first application to manage a database with scalability expectations, and circumvention of the design principles for the database system itself is not included within the accepted solutions for achieving a scalable database.

The current approach is in fact not only arduous and error-prone from an administrative standpoint, but also a source of operational fragility and an obstacle to data protection.

I understand your interest in this simplification. I think your opinion is helpful, useful, and I hope you will somehow solve your problem.

But, please also try to understand that you cannot just convince all Phorge system administrators that they are alien if they like to "just move" a single database to another host, instead of having to design table partitions, etc. :D

Therefore, this is not easy, and a compromise is necessary.

I am not intent on maintaining my own fork for private use, but the processing pathways for the segmented versus consolidated cases are only minimally different, and may easily be switched dynamically, within some hypothetical future version of the application, depending on the deployment.

If separate database connections are created and maintained, it is no matter if they happen to target the same database.

Again, you have not convinced me to work on that, for you, for free :)

If you or a friend of yours are able to have a fork that somehow does that (tomorrow, or in months or years), feel free to come back and propose a patch. We are here to review your code. The possibilities to have that merged are high, as long as the code is testable and it does not destroy other-people workflows.

If it is likely in principle that you would accept a patch, then it may be most sensible to leave the discussion open. Perhaps it would be found by someone wishing to contribute.

I also like to discuss. Please read this:

https://we.phorge.it/book/contrib/article/feature_requests/

In my opinion PROBABLY investing an unknown amount of hours in this will probably help you in simplifying the installation from Softauculus. Or, investing 10 minutes in contacting Softauculus to ask them "is it a problem if the app installs some phabricator_% databases?" would also contribute a lot in your goal.

I think you are misunderstanding the technical considerations.

The issue is not a flaw or limitation in Softaculous.

The issue is that administrators of database clusters create databases per request of application administrators, and grant the application administrators permissions to populate the databases assigned to them, but not to create new databases.

Database administrators are responsible for managing names of databases within each cluster, and for protecting each database from applications not assigned to it.

Such constraints were already explained in the original text.

Granting an application permissions to create (or to drop) arbitrary databases is not a responsible or agreeable policy for administrating a cluster, and placing constraints on a database name is not an agreeable design for an application.

Answers

avivey
Updated 284 Days Ago
Actions

This is not a reasonable "suggestion".

See https://we.phorge.it/book/flavor/article/so_many_databases/ for details about the current DB architecture, and https://we.phorge.it/book/contrib/article/feature_requests/ on how suggest features.

Event Timeline

Where do you receive feature requests?

I feel it is not necessarily productive to parse my choice of words, respecting whether to characterize the remark as a "suggestion", request, or by using another term.

The arguments in relation to established design patterns and the harm of breaking them, I believe, are relevant and valid.

I reviewed the architecture notes. While I understand the objectives concerning scalability, I continue to feel doubtful that the design that has been adopted, of a very high number of separate databases, is the most suitable for the needs of a user base.

While such an architecture may appear as offering support for operation at large scales, it certainly impedes operation at small scales. Thus, only one side of scalability considerations is being supported. Meanwhile, upward scalability generally has been addressed through completely different methods. To begin, modern hardware supports single nodes of immense capacity, in both storage and processing. An organization that has such great requirements might consider whether a large node is a better solution compared to a distributed deployment. Even so, in case a distributed deployment should be indeed necessary, solutions already are available for clustering single databases across multiple nodes.

An organization with such intensive requirements surely has the resources for acquiring and operating the hardware and software assets appropriate for maintaining a database that is extremely large, while also following the standard design patterns supported by the database management system.

A proper clustering solution accommodates database contents to the hardware and network layout, without requiring commitment to the restrictive scheme of assigning each single database among many, as prescribed by the application design, to a particular node. The hard coding of many distinct schema, such that each one is provisioned independently, constrains more than advances flexibility for deployment targets, whether the targets are extremely large or quite minimal.

The current application design is nonstandard. The stated rationale is of seeking to solve a particular problem that is important, but also extremely common, and supported by various standard solutions, different from the one currently in use.

The broader ramifications of the nonstandard design are of crippling support for the widest range of possible deployment scenarios.

The referenced document provides the claim that "In almost all cases, creating more databases has zero cost". I believe the statement is misleading.

Even in the best case, the various databases spread across separate nodes is likely to be more arduous to provision, and less efficiently to utilize resources, compared to employment of tools specifically intended to support large and possibly distributed databases.

@brainchild We receive feature requests here, and we have the choice to decline them here. The admin team has discussed it, we have decided that the current database setup works best for Phorge as is discussed in the documents avivey has linked. As such, changes will not be made to Phorge's database architecture at this time. If you are looking for something to provision with Softaculous, please consider a different solution.

You absolutely have the choice to decline requests, but I think it is not quite fair to consider the current one as though connected strongly to Softaculous.

The earlier discussion simply was my entry point into the current, over an issue that appears to me as quite general and significant, relating to the ramifications of breaking adherence to established patterns.

(comment migrated as answer)

valerio.bozzolan
Updated 284 Days Ago
Actions

We are sorry if we are not able to find a specific volunteer that will work on this (and for free).

Just to clarify that of course you are encouraged to try to hack Phorge and fork it to fit for your needs, to try to put all the 400+ tables in a single database.

Probably here you can find some useful files, and then dig more:

https://we.phorge.it/source/phorge/browse/master/src/infrastructure/storage/connection/

New Answer

Answer

This question has been marked as closed, but you can still leave a new answer.

Support for single database per application deploymentClosed, InvalidPublicActions

Details

Event Timeline

Answers

aviveyUpdated 284 Days AgoActions

Event Timeline

valerio.bozzolanUpdated 284 Days AgoActions

Event Timeline

New Answer

Answer

Support for single database per application deployment
Closed, InvalidPublic
Actions

avivey
Updated 284 Days Ago
Actions

valerio.bozzolan
Updated 284 Days Ago
Actions