Handling large dataset on Datatables

SalKhattak

All, I have about 12,000 stores. Now when I login to the admin dashboard & click on stores, I am getting the error below. Please advise,

DataTables warning: table id=storesDatatable - Ajax error. For more information about this error, please see http://datatables.net/tn/7

Saurabh

SalKhattak

Set php version to 7.3
Check /storage/logs folder find the logs file and try to see if there is any generic issue like memory limit exceeded or similar (as you have a ton of stores)

Django85

Bro, SalKhattak Do u facing issue with slow store listing loading/appearing on homepage?

Attention

Django85

yes we face this issue what solution?

[deleted]

Attention How many stores do you have on homepage at a time?

Django85

Attention no, haven't found solution yet, maybe if 3.5 stable, sir @Saurabh can consider lazy loading at the homepage to solve that.

SalKhattak

[deleted] @Saurabh This is what dev tools is telling me, I am not a developer and surely can't make sense out of this :-)

DevTools failed to load source map: Could not load content for https://mydomainname.com/assets/backend/global_assets/js/plugins/notifications/jquery.jgrowl.map: HTTP error: status code 404, net::ERR_HTTP_RESPONSE_CODE_FAILURE
jquery.min.js:2

GET https://mydomainname.com/public/admin/storesDatatable?pending=false&draw=1&columns%5B0%5D%5Bdata%5D=id&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=false&columns%5B0%5D%5Borderable%5D=false&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=image&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=false&columns%5B1%5D%5Borderable%5D=false&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=name&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=areas&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=false&columns%5B3%5D%5Borderable%5D=false&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=owner&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=false&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=created_at&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=action&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=false&columns%5B6%5D%5Borderable%5D=false&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=desc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1645630249948 500 (Internal Server Error)

SalKhattak

Django85 I have not been able to find a solution & can no longer manage stores as an admin. I am working on moving to amazon from the current hosting company, will provide update if the issue goes away after moving to a more powerful infrastructure.

Saurabh

SalKhattak any rows deleted from order, users or any table in the database?

babyseya

Saurabh I still can't do it now, and still have the same error, because now I have a lot of data, but support said I have to install from scratch 🙁 is there any way to help me?

SalKhattak

@Saurabh I am only running into this issue when I load the entire list of stores "12,000". Everything works just fine when the total store count is around 8,000. Once I cross the 8,000 limit, I start getting this error. I have only imported the stores + logo so far and started experiencing this problem. I have not uploaded the items + images that totals in hundards of thousands.

I am also noticing that the delivery guy is also showing offline on/off.

I believe performance related issues maybe the root cause. If you would like to take a look, I will gladly give you root before I move everything over to AWS. Below are the virtual private server specifications from namecheap.com

CentOS
4 CPU Cores
6 GB RAM
120 GB SSD RAID 10

[deleted]

SalKhattak I want to give you a suggestion

Check mysql max connections. Set it to at least 2000-2500 if you are using a VPS.
Check php max_input_time and max_execution_time set both to 300
Max memory limit to your server ram
max_vars to 20000.

Then check.

Saurabh

SalKhattak then the issue is with the server not able to handle such large data.

SalKhattak

[deleted] I will suely give it a shot before vacating the VPS sir & provide feedback. Thank you sir.

SalKhattak

[deleted] Saurabh the solution you suggested worked. Foodomaa is working as a champ now. I can clearly see the performance issues with my VPS now, I just need a better underlying infrastructure to run the system.

Check mysql max connections. Set it to at least 2000-2500 if you are using a VPS.
Check php max_input_time and max_execution_time set both to 300
Max memory limit to your server ram

max_vars to 20000. (I set this to 25000)

SalKhattak

babyseya follow the solution suggested by Saurabh [deleted]

SalKhattak

thank you sir, I should be able to set things up in AWS within the next 24 to 48. It will be setup to take whatever load possible & prvide you with some cloudwatch insight that maybe helpful to my fellows :-)

babyseya

HI!, I also have the same situation as you currently, I have sent it to the support team but that party said because I deleted the data in the database, (I'm sure I never went to the database to delete anything) and now at the current state still unresolved!
If you have solved the above situation, please guide me

Saurabh

babyseya
Support team will never tell you that you have deleted data unless you have actually done that.

Tell me your ticket ID and I will post the screenshot here 🙂

[deleted]

SalKhattak Happy to help as always 😃