SuiteCRM and Oracle databases

Hi All

Oracle is not in the list of supported databases for SuiteCRM, but SugarCRM CE has Oracle support listed on their compatibility matrix for Ultimate and Enterprise editions only. With checking the Code from SuiteCRM there are several code fragments for OCI8 support.

  • Is there any experience with running SuiteCRM on a Oracle DB?
  • Is it possible at all?
  • Any learnings from a migration from MariaDB to Oracle?

Reason behind this question: MariaDB causing more and more problems because the size is constantly growing (~30GB) and with this size we are running into problems with backups. Available Oracle instance promise more power regarding these problems. Also the DBAs are more experienced in Oracle environments.

Thanks for your thoughts.

I really don’t know about Oracle… maybe someone else can pick up on that part of the question.

But have you examined your overgrown database to see what’s wrong? I have seen people with huge databases that were able to clear all their problems with just some maintenance.

Can you try this command:


SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  15;

This will give you some hints about tables that look suspiciously large.

Hello,

I have a similar problem with my installation. The database has grown to a very large size. (Almost doubled in a week) It is now at 11 GB. Running the select statement you mentioned, shows the 2 largest tables are suitecrmdata.aow_processed (5.2 GB) and suitecrmdata.aow_processed_aow_actions (4.3 GB)

What could be causing these to grow so rapidly?

I do not have the prune database scheduler running. Would that reduce these table sizes, or is there any other maintenance that should be performed?

Thanks!

I wrote a blog post on this, not much more than what you find here, but still, might be useful to you.

https://pgorod.github.io/Database-tables-size/

How much room do you have for trying risky things? Do you have a clone, a test machine? Are you inside a VM?

Well that looks exactly like my issue. I suppose the first step is to run the prune DB script. However, from reading those posts, it doesn’t look that will help reduce the size of those 2 tables.

To answer your questions: It is a production system, so not a lot of room for error. However, it is a VM (VPS actually), and I have the opportunity to clone it and test if there is a procedure/script that can be run.

Yes, try the prunes first, they will help, although not for the aow_processed table.

In a VM you can also schedule some downtime, or simply work outside business hours, and make a snapshot, try the deletions, run some checks, and go back if necessary.

About the aow_processed: it really depends on what kinds of workflows you have, and how you configured them. Try looking around the table, see creation dates, which workflows have the most rows, and try to figure out patterns. If you don’t have any long-running workflows, it’s highly likely that you can delete most of the old stuff in there.

But you can only figure this out if you understand what the data means. I think it means basically this: workflow X has run for record Y at time Z. But the real question is, what does this mean in terms of your actual business?

And after you delete records, what is the best procedure to shrink the database size again?

Do you mean after deleting inside the app, how to delete from the database?

or

after deleting from the database, how to get MySQL / Oracle / whatever to reduce it’s file size?

If it is the first one, I think you already know the answer; if it is the second one, that’s a database admin question, better suited to any forums about your specific database, I guess. At least I don’t know enough to help you… :slight_smile: