Backup files have large size.
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Backup files have large size.
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?
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.
Re: Backup files have large size.
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.
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
David Graffeo
Open Dental Software
http://www.opendental.com
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
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.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 strongly suspect that I somehow got a large attachment in the emails and it was getting backed up in each back up. Or not...
Re: Backup files have large size.
Let me start with this...
Moving onto the bloated db issue:
Moving onto the bloated db issue:
- 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)
- 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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
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
Re: Backup files have large size.
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:
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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
7552 is the returned number.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!
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
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.
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
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...
I checked the patients for that day (12/16/2014), none of them was even sent an email...
Re: Backup files have large size.
What are the size differences uncompressed?
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
I will have to check to speak certainly. Will be in that office only next week.
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
daily_opendental_2014-12-15_02h02m_Monday.sql.bz2 14,456 KB compressed 86,492 KB unzipped <- fully functional, all the tables are present.tgriswold wrote:What are the size differences uncompressed?
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!
Re: Backup files have large size.
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)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.
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
Jason Salmon
Open Dental Software
http://www.opendental.com
Re: Backup files have large size.
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
Jason Salmon
Open Dental Software
http://www.opendental.com
- Justin Shafer
- Posts: 596
- Joined: Sat Jul 28, 2007 7:34 pm
- Location: Fort Worth, TX.
Re: Backup files have large size.
cool meme!
Justin Shafer
Onsite Dental Systems
817-909-4222
justin@onsitedentalsystems.com
http://www.onsitedentalsystems.com
http://iocsnapshot.com
http://justinshafer.blogspot.com
Onsite Dental Systems
817-909-4222
justin@onsitedentalsystems.com
http://www.onsitedentalsystems.com
http://iocsnapshot.com
http://justinshafer.blogspot.com
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
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.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?
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?
Re: Backup files have large size.
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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
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?
I uploaded the backup file to Dropbox. I'll try to pull my.cnf from the server.
Where do I send you a link?
Re: Backup files have large size.
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
Jason Salmon
Open Dental Software
http://www.opendental.com
Re: Backup files have large size.
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.
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
Jason Salmon
Open Dental Software
http://www.opendental.com
Re: Backup files have large size.
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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
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!
And yes, the green hat looks cool!
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Backup files have large size.
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.
Re: Backup files have large size.
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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
Great, thanks Jason!
Good compelling reason to try the beta for the first time, have always been afraid to touch them.
Good compelling reason to try the beta for the first time, have always been afraid to touch them.
Re: Backup files have large size.
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).
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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Backup files have large size.
Any estimated date on 16.1 yet?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).
Re: Backup files have large size.
Nope.KevinRossen wrote:Any estimated date on 16.1 yet?
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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Backup files have large size.
I like it. Under-promise and over-deliver!jsalmon wrote:Nope.KevinRossen wrote:Any estimated date on 16.1 yet?
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
That is a one good button! Hurray to Jason and his "uncomplicate life" button!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).
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
Again, thank you Jason!
Re: Backup files have large size.
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).
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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 172
- Joined: Mon Aug 04, 2008 12:39 pm
Re: Backup files have large size.
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.
-
- Posts: 180
- Joined: Fri Oct 14, 2011 3:31 pm
Re: Backup files have large size.
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!
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!