Backup files have large size.

[ForrestGumpDDS]

Hi everyone,
First of all, I am not sure what the "normal" size would be for the backup file. I have an office with under 2,000 patients, maybe 1500 active ones. Backups are done on daily basis by the script, they are created and kept on the server, then rsynked to another machine.
I noticed that one day about a year and a half ago the size of the backup files increased dramatically. for example, it was around 20MB and every day it was incrementally adding a few extra kilobites. Logical. Then from the next day on the size jumped to 300MB and started adding up from there. Not only that. The larger backups did not contain all necessary tables, the list of tables would end at letter "E" on the table 'emails". The rest of the tables the script could not copy. The same fate affected the backups done by internal OD engine that I had from upgrading OD in the past. Identical useless backups.
The way I found everything it was pretty pathetic. Yesterday I attempted to start a backup server due to my main database server having issues with motherboard and not powering up. Well, not a problem, I got backups, dear frontdesk, don't cancel anyone, I got it! Right... no I don't! Then I went one by one to the past and something triggered my attention. File size. There was a day in 2013 when the size changed 15 times. I can't use that, too old.
I almost heard Jason's voice saying "I told ya, AL-WAYS, test, your, backup! Always! Like you don't know what they are meant for, come on!" I never met the guy in person, but that's what his voice was telling me...
Well... we are back online and running now, I changed a motherboard on the server and two PCI risers, it did the trick. Using the main database.
Adjusted settings for the MySQL and now my backups are useful again! But they are almost 800MB size!!!
Is that normal? I presume there was something in those emails tables that was conflicting with the size of the field upon which the backup script stumbled. I want my small backups back! What would be a solution? Should I exclude the emails from backupping?

[dgraffeo]

Wow, I thought you WERE Jason there for a little bit, that's uncanny!
Anyways, what are you backing up? Is it JUST the database files or are you also backing up the OpenDentImages folders? I can tell you the database itself shouldn't really be that big; typically databases even with a lot of information are only a few MB (though there is one I have that's 3.6 GB). However if you are backing up the ODI folder that could easily jump in size based on the kinds of things you may be doing.

[ForrestGumpDDS]

Wow, I thought you WERE Jason there for a little bit, that's uncanny!
Anyways, what are you backing up? Is it JUST the database files or are you also backing up the OpenDentImages folders? I can tell you the database itself shouldn't really be that big; typically databases even with a lot of information are only a few MB (though there is one I have that's 3.6 GB). However if you are backing up the ODI folder that could easily jump in size based on the kinds of things you may be doing.

I just back up the database. ODI folder is a network shared folder, so is ODExports. Those folders get mirrored so despite their size they don't eat up into the space much.
I strongly suspect that I somehow got a large attachment in the emails and it was getting backed up in each back up. Or not...

[jsalmon]

Let me start with this...


Moving onto the bloated db issue:

1. What version of Open Dental are you using? (most common bloat was from electronic claims and we now clear out old unnecessary bloat for old claim history)
2. What are the top 5 or 10 .MYD files in your database directory?

[ForrestGumpDDS]

OK, I'm on 15.3.32 currently.

Code: Select all

-rw-rw---- 1 mysql mysql 24836812 Jun 25 2015 rxnorm.MYD
-rw-rw---- 1 mysql mysql 24906636 Jan 13 19:29 securitylog.MYD
-rw-rw---- 1 mysql mysql 27411736 Jun 25 2015 snomed.MYD
-rw-rw---- 1 mysql mysql 38292016 Jan 7 15:29 sheetfield.MYD
-rw-rw---- 1 mysql mysql 1333541720 Jan 13 13:54 emailmessage.MYD

[jsalmon]

You'll never get back to the tiny 20MB backups, just the RXNORM and SNOMED tables are ~24MB each (the downfall of code systems). But goodness me, you must send a lot of emails because that table is monstrous in comparison to all your other tables!

Your email table pretty much where all your db size is coming from and we don't currently have any way of archiving or compressing email messages down to make it more suitable for you. Looks like ~800MB is pretty accurate unless you believe that you don't send that many emails.

How many rows return when you run the following query:

Code: Select all

SELECT COUNT(*) FROM emailmessage

[ForrestGumpDDS]

You'll never get back to the tiny 20MB backups, just the RXNORM and SNOMED tables are ~24MB each (the downfall of code systems). But goodness me, you must send a lot of emails because that table is monstrous in comparison to all your other tables!

Your email table pretty much where all your db size is coming from and we don't currently have any way of archiving or compressing email messages down to make it more suitable for you. Looks like ~800MB is pretty accurate unless you believe that you don't send that many emails.

How many rows return when you run the following query:

Code: Select all

SELECT COUNT(*) FROM emailmessage

7552 is the returned number.
I am thinking of PDF's with post-op instructions and all other possible attachments in the emails. Are they getting backed up? Those can be large... Especially tx plans with graphics.

[ForrestGumpDDS]

I still don't get the reason for that instant jump

I checked the patients for that day (12/16/2014), none of them was even sent an email...

[tgriswold]

What are the size differences uncompressed?

[ForrestGumpDDS]

I will have to check to speak certainly. Will be in that office only next week.

[ForrestGumpDDS]

What are the size differences uncompressed?

daily_opendental_2014-12-15_02h02m_Monday.sql.bz2 14,456 KB compressed 86,492 KB unzipped <- fully functional, all the tables are present.
daily_opendental_2014-12-16_02h02m_Tuesday.sql.bz2 288,657 KBcompressed 478,143 KB unzipped <- 20 times the size of previous, just overnight; keep in mind that the backup file is not even full, stops at letter E tables.
daily_opendental_2016-01-13_07h26m_Wednesday.sql.bz2 735,834 KB, uncompressed 1,579,530 KB <- this one is latest, fully functional, all the tables are present. 1.5Gb uncompressed, ouch!

[jsalmon]

7552 is the returned number.
I am thinking of PDF's with post-op instructions and all other possible attachments in the emails. Are they getting backed up? Those can be large... Especially tx plans with graphics.

7552 entries should not take up that much room IMHO. The only thing I could imagine is if your emails have small novels stored within them. Attachments are stored in a different table and have pointers to files stored within your AtoZ share (unless of course you are storing images within the db which is very, very rare but still a possibility. see A to Z Images Folder for storing images and documents: http://www.opendental.com/manual/paths.html). I would recommend calling in and giving us a copy of your database so that we can inspect it and give you an exact answer as to why the email table is so large. Maybe there is an email template that you use that has lots of invisible characters? (I've actually seen that before)

[jsalmon]

My database has ~82,000 emailmessage entries and the table is only ~60MB if that tells you anything... is your mysql folder encrypted or anything that would cause this additional bloat?

[Justin Shafer]

cool meme!

[ForrestGumpDDS]

My database has ~82,000 emailmessage entries and the table is only ~60MB if that tells you anything... is your mysql folder encrypted or anything that would cause this additional bloat?

60MB will make me a whole lot happier. I don't think I am encrypting anything. But even in that case the increase in size would be progressive.
Let's try you guys checking into my system. I will restore the latest backup to my backup server and open the connection through Workbench and you can see yourself. Will that work?

[jsalmon]

That could work but something tells me it is going to take much more time to diagnose and fix. If I were the one to look into it I would want a copy of your db here so that I could prove without a doubt that my "fix" corrects the root of the issue. I would want a backup of your db without the images folder (http://www.opendental.com/manual/backuptool.html) and then a copy of your my.ini (or my.cnf) that way I could look at it at my leisure and not tie up any of your resources.

[ForrestGumpDDS]

Jason,
I uploaded the backup file to Dropbox. I'll try to pull my.cnf from the server.
Where do I send you a link?

[jsalmon]

I'll send you a PM.

[jsalmon]

It's definitely our email inbox feature that is bloating your database. You have several emails in your database that have a MASSIVE "RawEmailIn" column. We're talking over 20,000,000 characters. It's so large that MySQL will not physically let me query those columns because it exceeds the maximum max_allowed_packet size (roughly 1 GB). Very interesting. I'll dig deeper to see if I can create a safe DBM cleanup tool for the emailmessage table.

At the very least I should be able to give you a query that will significantly reduce the size of your db because the message text (roughly 12KB) for these emails came through just fine so it is unnecessary to keep the "RawEmailIn" column around (mainly for debugging if there are any issues parsing the content of an email) which is the root of your problem.

[jsalmon]

You were right, it's attachments (pdfs to be exact) that are causing your bloat. My post above is still accurate in relation to how Open Dental treats attachments but what I didn't consider was that raw emails from your email server might have attachments that were SENT TO YOU which is where the unexpected bloat is coming from. This is fascinating to me because I thought emails wouldn't let you send such large files! E.g. GMail limits users to 20MB I believe. Which is probably why we haven't run into this issue and other customers haven't complained about it? Crazy...

[ForrestGumpDDS]

Jason, I really appreciate your work, and desire to help us all. I'll wait for the fix for everyone, it's not a big deal for me to wait whatever is necessary.
And yes, the green hat looks cool!

[KevinRossen]

I've had a similar bloat for that table. I have 2,283 messages in the emailmessage table, but the table is taking up 125MB of data. I think it's related to PDFs, too.

[jsalmon]

I talked this over with another engineer and we've come up with a plan. This plan is pretty in depth so it will only be added in the next beta version (v16.1) but should alleviate a lot of stress from the emailmessage table.

[ForrestGumpDDS]

Great, thanks Jason!
Good compelling reason to try the beta for the first time, have always been afraid to touch them.

[jsalmon]

Phew, that was mighty complicated and took a long time to fix. I can say for certain now that the enhancement to not "double store" attachments will be included with v16.1 which will stop future emails from downloading and storing the extra bloat of Base64 string representations of every attachment. For old emails (what you will need to do) users will have to go to Database Maintenance and run the new DBM tool I created (a button that will say something like Raw Email) which will go through every old email currently residing in your inbox and clean up the RawEmailIn column as to not store attachments (because they are already extracted and sitting in your AtoZ share!).
This change will take your emailmessage.MYD table from 1,300,261KB down to 30,133KB (something we can all be happy about).

[KevinRossen]

Phew, that was mighty complicated and took a long time to fix. I can say for certain now that the enhancement to not "double store" attachments will be included with v16.1 which will stop future emails from downloading and storing the extra bloat of Base64 string representations of every attachment. For old emails (what you will need to do) users will have to go to Database Maintenance and run the new DBM tool I created (a button that will say something like Raw Email) which will go through every old email currently residing in your inbox and clean up the RawEmailIn column as to not store attachments (because they are already extracted and sitting in your AtoZ share!).
This change will take your emailmessage.MYD table from 1,300,261KB down to 30,133KB (something we can all be happy about).

Any estimated date on 16.1 yet?

[jsalmon]

Any estimated date on 16.1 yet?

Nope.

[KevinRossen]

Any estimated date on 16.1 yet?

Nope.

I like it. Under-promise and over-deliver!

[ForrestGumpDDS]

Phew, that was mighty complicated and took a long time to fix. I can say for certain now that the enhancement to not "double store" attachments will be included with v16.1 which will stop future emails from downloading and storing the extra bloat of Base64 string representations of every attachment. For old emails (what you will need to do) users will have to go to Database Maintenance and run the new DBM tool I created (a button that will say something like Raw Email) which will go through every old email currently residing in your inbox and clean up the RawEmailIn column as to not store attachments (because they are already extracted and sitting in your AtoZ share!).
This change will take your emailmessage.MYD table from 1,300,261KB down to 30,133KB (something we can all be happy about).

That is a one good button! Hurray to Jason and his "uncomplicate life" button!
Stable 16.1 hit the world yesterday.

Tomorrow I will check my backup size. Manual dump using Workbench gives me half the size of the old back up. Good reason to pop a beer can. I however had to exclude documentmisc table, the export could not handle something big in there.

Code: Select all

20:07:08 Dumping opendental (all tables)
Running: mysqldump.exe --defaults-file="c:\users\user\appdata\local\temp\tmph9_mpt.cnf"  --set-gtid-purged=OFF --user=opendental --host=192.168.41.3 --protocol=tcp --port=3306 --default-character-set=utf8 --routines --events --skip-triggers "opendental"
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `documentmisc` at row: 0

Operation failed with exitcode 3
20:07:10 Export of C:\Users\User\Documents\dumps\Dump20160518.sql has finished with 1 errors

Is that normal?
Again, thank you Jason!

[jsalmon]

Yes that is normal (sadly). For several versions (15.4.50 - 16.1.20) we tried to store all the files necessary for updates within the database. This was great especially for users with the middle tier! However, we did it all in one row and as we added more and more to the program, the files necessary for updating increased and soon grew over the max_allowed_packet size for many users. We now store the update files in 1MB chunks that span however many rows necessary. The reason your database will have that large row stick around is because it needs to stay there for workstations that are still at the versions between the span I mentioned above. Backwards compatibility is always nasty

Once all your workstations are updated past v16.1.20 you can manually delete the large singular row (if you feel comfortable enough). Note that any time you go to Help | Update | Setup | Recopy that big row will get recreated again (once again for backwards compatibility purposes we needed a way to get it back into a database that might not already have it).

[nathansparks]

We should begin working on an archive protocol for tables, and add functionality within the program for each one as we add it. Unfortunately, while there are some groups that could be considered, many tables will have distinct archive functionality. Clinical items (think procedures , prescriptions etc) still need to be available from UI for all time, even if in an archive table, while s three year old security log entry could perhaps be archived to a file in the AZ folder and it would be OK if access was more time consuming as it would be very rare.

[ForrestGumpDDS]

Hmm... the automated backup size is even better

I guess this is because of the compression. Uncompressed is slightly over 500Mb
That is awesome. I just checked on a test server and everything seems to be working fine!
I did notice some code is still left after the update in selected emails, but this is nothing compared to scrolling through a miles of a text to finds the "confirmed, I'm coming".
Looks neat and clean!
Thank you guys! I hope you realize how we appreciate you doing what you are doing!