Dear All,
I am analysing the size of individual tables in the my ERPNext database.
Size of “'tabEmail Queue” table itself is around 40% of the overall database size.
Since all emails have been delivered can I go ahead and delete these records in email queue?
And in general, how do we handle old data that is not relevant anymore ( ex old material requests ) .
Should I go on upgrading my server resources as size of database increases?
Whats the best practice in this regard?
Thanks
The database size increased due to the attachments are stored in the Email Queue as base64. and @ManasSolanki and @netchampfaris has already fixed this issue.
So does this means, its safe to delete all sent items from email queue?
If the emails are sent and you no longer need the data, you can delete.
Just wanted to revive this topic since I think its a pertinent question and hasn’t been answered well in the last time it was asked.
Let me put things in perspective I have been using erpnext since 2010 and I have a DB file zipped (.gz) file at a size of about 3 GB and unzipped it becomes around 9GB, out of which the total size of the tables in my DB is as below just to give an idea:
+--------------------+----------------------------------------------------+------------+
| Database | Table | Size in MB |
+--------------------+----------------------------------------------------+------------+
| _040e7151bb844b3a | tabEmail Queue | 3178.11 |
| _040e7151bb844b3a | tabCommunication | 405.71 |
| _040e7151bb844b3a | __global_search | 388.40 |
| _040e7151bb844b3a | tabVersion | 361.02 |
| _040e7151bb844b3a | tabStock Ledger Entry | 178.69 |
| _040e7151bb844b3a | tabGL Entry | 148.82 |
| _040e7151bb844b3a | tabSales Taxes and Charges | 86.70 |
| _040e7151bb844b3a | tabFeed | 86.70 |
| _040e7151bb844b3a | tabComment | 74.10 |
| _040e7151bb844b3a | tabStock Entry Detail | 71.67 |
Now there are around 193301 records in my email queue table but I have hardly ever looked at them, what its basically showing whether an email is sent or not or there is error and out these 193301 records only 352 records which are not sent:
So does it make sense to have such a huge table sit on top of the DB which is basically of no use as of now.
The only use I could see in future for this table is whether an email is read or not and we might need to fetch the details from some service like Sparkpost about whether an email has been read or not, which I think still a dream.
Also would like to add here that having this table with such huge records is basically also delaying the process of migrations and patches since every time a table is renamed it checks the email queue and replaces the values in this table and almost takes forever to execute
I would myself want to know if its safe to remove the records atleast the ones which are sent and older than maybe like a week or we can give a field in settings page for users to define if they want to keep the email queue forever or the want to delete it by days and run a scheduler even every day for deleting the records.
Would love to hear from an expert on this topic?
Just wanted to add that email queue started to add up data somewhere in 2017-May and for a small users like me it has grown to 3GB or thereabouts in a matter of 1.25 years and what would be happening to DB load on erpnext cloud hosting servers god knows, no wonder the cost of hosting erpnext accounts is going to go up.
My solution would be add a field to state that whether users want email queue in settings page and run this command everyday:
DELETE FROM `tabEmail Queue` WHERE status = 'Sent' AND TIMESTAMPDIFF(DAY, creation, now()) > 90 LIMIT 10000;
WHERE 90 is the value which is defined by users in their settings page
Just a follow-up on the above said solution, I just ran it on my test environment and found out that after deleting entries older than 90 days the table size was reduced by 2.5GB which is clear from the below table:
+--------------------+----------------------------------------------------+------------+
| Database | Table | Size in MB |
+--------------------+----------------------------------------------------+------------+
| _040e7151bb844b3a | tabEmail Queue | 841.57 |
| _040e7151bb844b3a | tabCommunication | 405.71 |
| _040e7151bb844b3a | __global_search | 388.40 |
| _040e7151bb844b3a | tabVersion | 361.02 |
| _040e7151bb844b3a | tabStock Ledger Entry | 178.69 |
| _040e7151bb844b3a | tabGL Entry | 148.82 |
| _040e7151bb844b3a | tabSales Taxes and Charges | 86.70 |
| _040e7151bb844b3a | tabFeed | 86.70 |
| _040e7151bb844b3a | tabComment | 74.10 |
| _040e7151bb844b3a | tabStock Entry Detail | 71.67 |
| _040e7151bb844b3a | tabSales Invoice Item | 59.88 |
How to do this?
Truncate only helps to reduce the database, not delete, infact delete statement increases the size !
Useful links :
To know database and table size :