Trimming Database Filesize

[esforim]

Just looking at my host provider and notice that I have a database filesize limit... looking at my database these two table is huge... not sure if it safe to delete or how I can trim down the size a little. Email is a must to keep there, but the other two I think we can safety delete?

Anyone can advise? I probably do a backup before deleting but if it kill EspoCRM, restoring backup is quite annoying.

Thank you.

[eymen-elkum]

Do you have "clean up" scheduled job working properly?

[esforim]

I remember seeing this 'logs' somewhere but can't find it within the GUI. Is it safe to delete the whole table?

Didn't expect logs which is supposed to be text only isn't it to be that big.

[macistda]

If you have MariaDB maybe I can help. I figured out that automatic "clean up" scheduled job is not working properly for all data stored in database.

1.) Espo Frontend Admin:
Delete Data from #Admin/actionHistory

2.) From SSH console:
mysql
use <database>

Useful select to determine which tables use the most of the data:
----
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "crm"
ORDER BY (data_length + index_length) DESC limit 10;
----
**adjust your table_schema Name and / or limit

-- Cleanup the missing parts in "clean up" scheduled job:

delete from bpmn_flow_node where created_at < '2022-12-01 0:0:00';
delete from bpmn_process where created_at < '2022-12-01 0:0:00';
delete from scheduled_job_log_record where created_at < '2022-12-01 0:0:00';

-- **adjust date. This tables are only log informations.
use \q (for quit)

3.) SSH console (This is an Espo script):
php /var/www/crm/rebuild.php
(to rebuild the index after deleting data)

4.) SSH console:
mysqlcheck -u root -p --auto-repair <dbname>
mysqlcheck -u root -p --optimize <dbname>


Sources:

• https://vitux.com/tune-and-optimize-mysql-mariadb/
• http://woshub.com/compress-defrag-op...iadb-mysql-db/
• https://docs.espocrm.com/development/db-indexes/
• own experience

[esforim]

Thank macistda, our server use MySQL from look of it. But your post will surely help other people.

[esforim]

Do you have "clean up" scheduled job working properly?

Manage to find it, seem like it been doing well...?


Detail view seem good too.

[eymen-elkum]

You can make cleaning more productive by changing some values on the config file

https://docs.espocrm.com/administration/config-params/#clean-up

[esforim]

I will reduce those and see if it help. I'm surprise the Job take so much space (filesize) considering it all "text" character isn't it?

Not like email where it probably storing HTML code, etc. I'm guessing here...

[eymen-elkum]

The storage is not all about the data itself, there are indexes too, also the data has some json fields that will sometimes be considered as not small data

[brotherlouie]

Hello,

Is it okay if I manually delete the lines in action_history_record / job / scheduled_job_log_record from phpmyadmin? Thanks already for your help.

[esforim]

Should be no use, it just mean you lose the log. I recommend still making a backup incase.

[brotherlouie]

Thank you for the answer.

[victor]

Firstly, any record should be deleted through the UI, and only then in phpMyAdmin you can delete records containing 1 in the deleted column.

[brotherlouie]

Thank you for the information. It worked for me.

[esforim]

It finally happen! And totally unexpectedly... suddenly EspoCRM not working anymore. Error 500. Review the log and it talk about database, had a look at my database and I see the issue. "Database oversize limit", our host only allow for 6GB per database!

So we either upgrade to VPS or delete stuff.

Currently we opted for deleting some of the big size in the database. The top two is: Email and Logs.

So I went into log with myPHPAdmin, view 500, select all, delete, Yes after filtering oldest date. I'm sure there is a better way but at least here is the quick solution. You might see this in your EspoCRM server log as the error:

ERROR: (42000) SQLSTATE[42000]: Syntax error or access violation: 1142 UPDATE command denied to user 'username'@'localhost' for table `databasefilename`.`auth_token`; GET /Notification/action/notReadCount; line: 76, file: /home/u874062233/domains/espocrmwebsite.com/public_html/app/application/Espo/ORM/Executor/DefaultSqlExecutor.php

[esforim]

Was deleting manual and it show very little progress at all! 500 per page but that is a drop in the bucket! I look at total record it was over 2.5millions! So I went to do some soul searching and was able to come up with this commands: delete April didn't do much with filesize, so the next one I did from May to July and the result on execution and deletion is below.

DELETE FROM job
WHERE execute_time >= '2023-04-01 00:00:00'
AND execute_time <= '2023-04-30 23:59:59';
(press ctrl+enter in myPHPAdmin console)

1303889 rows affected. (Query took 46.9820 seconds.)
DELETE FROM job WHERE execute_time >= '2023-04-01 00:00:00' AND execute_time <= '2023-07-30 23:59:59';

[esforim]

And the result from 1.0GB to 610mb, not enough in the long run!

So I did another one for scheduled job

DELETE FROM scheduled_job_log_record
WHERE execution_time >= '2020-04-01 00:00:00'
AND execution_time <= '2023-04-30 23:59:59';​