hello team,
I am experiencing very slow response times today on backendless, making the platform almost unusable both from the client and on the online console (we are getting emails now that users are even unable to register an account, probably due to the very slow response)
it can take a few minutes to do operations that normally I would do in seconds, can anyone please check what is going on?
I apologize for the delay, but I had to collect the necessary data and check everything in detail.
We added resources twice for db servers as there should be. But this will not solve the problem. Now there are many slow queries against a large table in you db. These requests greatly slow down the overall operation.
The main problem is that one of the tables in your database is very large and it is not optimized; it may contain a lot of deleted and fragmented data.
We can run the table optimization but it may block access to it for some time, given the size of the table it may take 3-5 hours.
This operation will lock only the DailySummaryNoCoreData table.
But all requests in which this table appears will also be blocked and based on the fact that this table is architecturally important for your software product, I predict that the service will be completely blocked or will respond slowly.
The optimization operation as a whole will be difficult because a large volume of data will be manipulated. There is also no guarantee that the optimization will be successful and there will be enough memory.
I will start the optimization process and let you know how the process is progressing.
as an alternative, would it be possible for you to delete entries collected prior to 2019 (so we get the table smaller). We do not want to lose anything collected in the past 3 years, but we could drop what was collected earlier if you had a way to do that (using the “created” field in that table)
@Alessandra_Saviotti You should have cleared that table if you didn’t need that data.
Now it will be more difficult to do.
I will estimate how much data will need to be deleted and let you know.
the table is needed, but I am thinking about ways to solve the problem here, since right now you are telling me that we are screwed and nothing will work
older data could therefore be deleted, if that’s something you can do more easily than the “optimization”
can you do that? it would be fine if you could delete data collected prior to 2020. I cannot do that myself as the operations are really slow or not working (and I could only delete very few entries at the time)
also: how is it even possible that yesterday everything was perfect and now nothing works? very little has changed in these 24 hours, this makes no sense
So, at this point, I understand that we can delete data older than 2019 and after that try to optimize the table? I think such actions should lead us to success.
Started making changes in table.
thank you @Marian_Koreniuk, let’s go with that, delete everything before 2019, and if it is not enough we will think about it, then also the optimizations
I made some optimization of the table and I see that the work has improved somewhat, and backups of the data that will be deleted are being made. 5.2 million records will be deleted. I will inform you further about the removal. At the moment, is there any improvement in your requests?
I think so, it makes sense, for 2019 there are approximately 3 million records. This way we can delete more than 7 million records.
So it will speed it up, but I would recommend to look at the architecture of your software product and to separate the generation into a separate table, it would give better results.
@Marian_Koreniuk can you double-check this as we seem to be experiencing some delays again today, while yesterday after resolving the issues it seemed all good. Thank you
edit: we are down now again, please help
edit2: now it is working again, please if you have time check that there isn’t anything off as we cannot have these issues on a daily basis, thank you
Hello, @Alessandra_Saviotti.
We solved only part of the problem yesterday. Data is deleted in portions, we cannot delete a large volume at once, this greatly blocks the work of all databases in general. Removal will continue today. In a few hours, will give an estimate of how long it will take for complete removal. We see that there are delays in the operation of the service and are doing everything possible.
thank you for the explanation, the Users table seems unresponsive now, just flagging it in case something happened, as this prevents people from registering accounts after downloading the (paid) app.