Backup files have large size.

For users or potential users.
Post Reply
ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Backup files have large size.

Post by ForrestGumpDDS » Wed Jan 13, 2016 12:13 pm

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?
Last edited by ForrestGumpDDS on Wed Jan 13, 2016 1:54 pm, edited 1 time in total.

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: Backup files have large size.

Post by dgraffeo » Wed Jan 13, 2016 1:21 pm

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.
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Wed Jan 13, 2016 2:07 pm

dgraffeo wrote: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...

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Wed Jan 13, 2016 2:57 pm

Let me start with this...
Image

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?
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Thu Jan 14, 2016 7:02 am

:lol: :lol: :lol:
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

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Thu Jan 14, 2016 10:09 am

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! :shock:

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
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Thu Jan 14, 2016 2:54 pm

jsalmon wrote: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! :shock:

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
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Thu Jan 14, 2016 3:20 pm

I still don't get the reason for that instant jump
Image
I checked the patients for that day (12/16/2014), none of them was even sent an email...

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Backup files have large size.

Post by tgriswold » Thu Jan 14, 2016 4:58 pm

What are the size differences uncompressed?
Travis Griswold
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Fri Jan 15, 2016 11:34 am

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

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Sat Jan 16, 2016 10:54 am

tgriswold wrote: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! :shock:

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Mon Jan 18, 2016 10:51 am

ForrestGumpDDS wrote: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)
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Mon Jan 18, 2016 10:54 am

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?
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
Justin Shafer
Posts: 596
Joined: Sat Jul 28, 2007 7:34 pm
Location: Fort Worth, TX.

Re: Backup files have large size.

Post by Justin Shafer » Mon Jan 18, 2016 4:10 pm

:lol: cool meme!

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Tue Jan 19, 2016 6:26 am

jsalmon wrote: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?

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Tue Jan 19, 2016 11:57 am

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.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Tue Jan 19, 2016 12:07 pm

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?

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Tue Jan 19, 2016 12:21 pm

I'll send you a PM.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Wed Jan 20, 2016 11:25 am

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.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Wed Jan 20, 2016 11:47 am

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...
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Wed Jan 20, 2016 5:14 pm

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
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Backup files have large size.

Post by KevinRossen » Wed Jan 20, 2016 5:37 pm

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.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Thu Jan 21, 2016 4:38 pm

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.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Fri Jan 22, 2016 7:03 am

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

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Wed Feb 03, 2016 4:38 pm

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).
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Backup files have large size.

Post by KevinRossen » Thu Feb 04, 2016 12:48 pm

jsalmon wrote: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?
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Thu Feb 04, 2016 1:40 pm

KevinRossen wrote:Any estimated date on 16.1 yet?
Nope.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Backup files have large size.

Post by KevinRossen » Thu Feb 04, 2016 1:41 pm

jsalmon wrote:
KevinRossen wrote:Any estimated date on 16.1 yet?
Nope.
I like it. Under-promise and over-deliver!
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Wed May 18, 2016 5:02 pm

jsalmon wrote: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.
Image
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!

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Backup files have large size.

Post by jsalmon » Wed May 18, 2016 5:32 pm

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 :cry:

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).
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

nathansparks
Posts: 172
Joined: Mon Aug 04, 2008 12:39 pm

Re: Backup files have large size.

Post by nathansparks » Thu May 19, 2016 9:25 am

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
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: Backup files have large size.

Post by ForrestGumpDDS » Thu May 19, 2016 10:52 am

Hmm... the automated backup size is even better :)
Image
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!

Post Reply