After tinkering a bit earlier came to conclusion that ERPNext is pretty much a MySQL based ERP. It would take like an open heart surgery to plugin a different database. I’m also not sure whether performance problems in EPRNext at scale is due to the database and would be solved by moving to Postgresql.
How do you mitigate against performance issues? Is it a matter of throwing more hardware at the problem?
Hard to say if you have a decent hardware then throwing more would improve the situation… As far as I know ERPNext is a document based system and uses MySQL only as dumb storage of metadata of the documents, ( this is a nice design and provides enormous benefits ) So a relationship between documents is at the metadata level and not at a database level. ERPNext loads the metadata of the document and then again loads all related documents from database this might be the cause of performance issue at scale, where you have millions of documents and each one is related to many other documents. The whole tree of document being loaded up takes considerable time. How can it be improved needs a larger community discussion I suppose.
1 Like
I have had much better luck restoring a large database from mysql directly rather than using bench restore. The biggest advantage was that it didn’t have a timeout error. This was an import of about 3.5GB.
Though I’m sure you’ve already figured this part out.
mysql -u root -p --verbose database_name < 20180509_12345678-site1_local-database.sql
I’m very interested to hear @felix 's thoughts on the Frappe ORM.
@pigeonflight I dont have any progress on this, due we solved major of all performace issues with some steps:
- Indexing better the database, using the slow query
- Frappe now, send data assyncronows to the database, what improves the performance.
- MySQL has support to JSON, so in specific situations, store the blob, and query over this directly from the database are more performant.
- Memory, Memory, Memory, we upgrade the mysql server to one server with 64Gb of memory, it speed up the performance of the mysql.
- Caching is the key, certaing queries, inserts or views, will require a lot of caching, so InnoDb needs that amount of memory.
- SSD, counts in terms of storage, for the writing process be faster, what affects also the mysql performance
1 Like
@max_morais_dmm has laid out the points very well. I’ve seen implementations with 1,000+ users with 500+ concurrent perform excellently on very reasonable hardware. I’ve also seen some smaller implementations struggle. PostgreSQL is a very good database, and I have no doubt that if ERPNext were built today, it would be built on Postgres rather than MariaDB, but I would hesitate to say we need to shift to it for performance reasons. The people who would have performance issues which are solved by moving to Postgres would be in the extreme outlier/minority of the community.
I hope my comment isn’t misconstrued as painting the Frappe ORM in a bad way. At a high level, I believe Frappe’s ORM has pretty much the same performance pros and cons as other ORMs. Its good for certain things and not good for certain things, like all ORMs are. Ultimately, if developing, we have to use the right tool in the right situation - sometimes that is the ORM, sometimes that isn’t.
Thanks @felix. I’m not hating on the Frappe ORM either. The idea that you should rewrite a performance-sensitive query in SQL is a good best-practices suggestion. That’s what I meant by the sentence you quoted, not “tell me how bad the Frappe ORM is so I can agree with you.” Your statement:
is very balanced and I agree with it. You’ve made me think about refactoring frappe.db.get... calls in a loop in to frappe.db.sql(... so that you don’t have to pass the same query through the ORM each time.
@felix, Is it possible to provide some references to clients who have 1000+ users and 500+ concurrent users? It will be a great piece of marketing for ERPNext. Best
2 Likes
I agree with the thought of progressing towards PostgreSQL as the DB fo ERPNextr
i like to see PostgreSQL support in the future.