Testing ERPnext 700,000 rows of data

[Coleteral]

Hi Everyone

Im new to ERPNext

Im currently testing ERPNext using a virtual box image with 2GB RAM, I successfully uploaded 700,000 rows of records on the tabItem i used Navicat to import the data ERPNext works fine with 60,000 rows of data but now ERPNext is now running slow with 700,000 rows specialy when saving an Item and doesn’t respond when i choose an item in making a quotation. Im planning to upload 3 Million+ rows of item data.
Do i need to adjust my MySQL my.cnf
or i can use frappe / bench to adjust some settings for it to run faster?

What i tried

1. item_code,item_name i used numbers from 1-700000 instead of using original item_name and itemcode because i noticed it was using item_name for indexing [still running slow].
2. I increased MySQL settings to 150% [still running slow].

[bobzz_zone]

Hi, im really curiues with that you do…but i think rather testing with items master , why dont you try test with the transaction data , why dont you try to t millions data in transaction table like Journal Entry or Invoice?
i think items is not to be supposed to be that much… so testing with transaction will be better i guess

anyway where you tested them ?
Processor ?
RAM ?
OS ?
Offline or Online?

[Coleteral]

 We are in the trading business we are going to use Sales and Stock frequently im testing ERPNext using live Data.
     Processor I3 2.4ghz                            | 2 Cores allotted to VM
     RAM 6GB                                          | 2 GB allotted to VM
     OS windows 7 64 bit pro                     
     Local

[bobzz_zone]

Hi, why dont you tried to allocate more RAM and cores ?
i think the database runs speed is affected by that spec…
please inform me if its runs well or not

Thanks

[Coleteral]

Will do
i will inform you

Thanks for the Reply

[Coleteral]

Still no Improvement. i will now try to adjust MySQL my.cnf with the following specs

Tested on:
Processor I7 3.0Ghz | 2 cores allotted to VM
RAM 10GB | 6GB RAM alloted to VM
OS Windows 7 64bit pro
Local

[bobzz_zone]

please try to adjust the core…try to set to 4 core … i wonder if there is stil no improvement

[Coleteral]

There are now some Improvement used the specs.

Processor I7 3.0Ghz           |   2 cores allotted to VM
RAM 10GB                        |    6GB RAM alloted to VM
OS Windows 7 64bit pro
Local

I modified MySQL my.cnf increased it to 300% now it runs good
when i open.

Opening Item master: 1 min 30 secs —> 40 secs
Saving an item: 1 min --> 20 secs
Selecting an Item in Quotation: Crashing --> 50 secs

For now concluded that the performance will now depend on the MySQL configuration and also make the item_name column short as possible.

[rmehta]

@Coleteral interesting problem.

ERPNext / Frappe has a “key” problem. The primary key should ideally be an integer but we have kept it as a VARCHAR becuase its makes things so much easier.

If you are planning to use ERPNext for such a large scale, you should sponsor a re-design to change this architecture.

[bobzz_zone]

@rmetha i belive this will be very good improvement…
i have a suggestion for this… why dont we create a poll for this improvement , so every one can donate for this request and please inform the total cost you guys will need to do the redesign things…

i think it will be fair enough for the community…
anyway please inform in the poll what will be affected when this updates done , so normal user can understand why they need it…

Thanks

[Jonathan_Fanny_Lie]

@Coleteral, where we can find my.cnf file? What did you modify? Can you share?

Thanks

[rmehta]

@bobzz_zone do we have enough people who can fund? Maybe we can go to kickstarter / indigogo ?

[bobzz_zone]

@rmetha my idea is just because i really want to contribute to erpnext… but i dont have that much money to donate right away (i had to cover my cost first anyway) since i tried to be erpnext implementator here (for first timer i think i got wrong pricing for my client), but im still no good or still not finish even 1 client…
but i think after i can done 1 or 2 i can sell more…

for now my client still waiting for multi currency features…

mean while i tried to get another client with different bussiness , since i want to try to build software implementator with erpnext in my town…

THanks

[cgpurbaugh]

How many hours of development are we talking about?

[Coleteral]

you can find my.cnf in /etc/mysql/

[Coleteral]

• Fine Tuning

max_connections = 10
max_allowed_packet = 128M
sort_buffer_size = 64M
bulk_insert_buffer_size = 128M
tmp_table_size = 256M
max_heap_table_size = 256M

• MyISAM

key_buffer_size = 512M
myisam_sort_buffer_size = 1G
read_buffer_size = 64M
read_rnd_buffer_size = 32M

query_cache_limit = 2M
query_cache_size = 256M

*Inno DB
innodb_buffer_pool_size = 1G

[inventobd]

Hello

@rmehta We are here implementing ERPNext to medium sized organizations in Bangladesh. We are not completely aware of the limitations . Exactly how large data scale can ERPNext handle . If it can be improved to handle large data I can contribute too . But within my limitation . Is there any instruction about ERPNext’s capability ?

Thanks

[Coleteral]

well only way to find out is to test.
im going to try 3 million rows in the tabItem on my development laptop while calibrating MySQL and see what happens if it crashes or runs slow i will try it in a Xeon Server with 16GB. maybe if ERPnext runs smoothly with 10 million rows of data it is safe enough for a large scale implementation

[rmehta]

@cgpurbaugh I am not sure. We will have to add 2 fields for all Link Fields and add joins for default list views. Also Link controls will be redesigned to have a label / value instead of just value. Plus a patch to migrate all to the new system.

Seems like at least 2 weeks, but we can sponsor (50%) so maybe a week’s funding will help us get started.

@Coleteral do share your results!

[pdvyas]

innodb_buffer_pool_size = 1G

For such a large db, 1G seems too less. Can you make it at least as large as your dataset? (don’t make it more than 70% of your RAM)