Re: Get table fragmentation
Cannon:
You're welcome.
--
Douglas von Roeder
949-336-2902
On Tue, May 30, 2017 at 12:59 PM, Cannon Smith via 4D_Tech <
4d_tech@lists.4d.com> wrote:
> Hi Doug,
>
> Thanks for the information. I can see that there probably isn’t an easy
> way to algorithmically have a server tell me when it needs to be compacted.
> I guess I’ll just have it report information to me, similar to what you are
> doing, and use my brain.
>
> Thanks!
>
> --
> Cannon.Smith
> Synergy Farm Solutions Inc.
> Hill Spring, AB Canada
> 403-626-3236
>
>
>
>
> > On May 30, 2017, at 10:55 AM, Douglas von Roeder via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
> >
> > The table fragmentation level is a lot like a temperature - it's one
> number
> > that indicates something ("it's cold") but other factors that a lead us
> to
> > decide "it's too cold to go outside because it's too
> > humid/windy/dry/calm/etc."
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ: http://lists.4d.com/faqnug.html
> Archive: http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
>
**
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Get table fragmentation
Hi Doug,
Thanks for the information. I can see that there probably isn’t an easy way to
algorithmically have a server tell me when it needs to be compacted. I guess
I’ll just have it report information to me, similar to what you are doing, and
use my brain.
Thanks!
--
Cannon.Smith
Synergy Farm Solutions Inc.
Hill Spring, AB Canada
403-626-3236
> On May 30, 2017, at 10:55 AM, Douglas von Roeder via 4D_Tech
> <4d_tech@lists.4d.com> wrote:
>
> The table fragmentation level is a lot like a temperature - it's one number
> that indicates something ("it's cold") but other factors that a lead us to
> decide "it's too cold to go outside because it's too
> humid/windy/dry/calm/etc."
**
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Get table fragmentation
Cannon:
The table fragmentation level is a lot like a temperature - it's one number
that indicates something ("it's cold") but other factors that a lead us to
decide "it's too cold to go outside because it's too
humid/windy/dry/calm/etc."
In addition to fragmentation % look at the number of records in the table,
frequency of access, user audience for the data, RAM/cache, storage medium,
the downtime required to resolve the issue, and, last but not least, is to
determine if the fragmentation level is having a material impact on
performance.
If you're on SSD's, with a seek time of parts of a millisecond, the impact
of high fragmentation will be less compared to being on a spinning drive,
which is heavily impacted by fragmentation because of seek time being so
much greater than an SSD.
If you've got the datafile and indexes in RAM, does fragmentation matter?
I generate an Excel report that displays, by table, the records in table
and the fragmentation percentage. Tables that are 20%+ fragmented are
marked with 10 asterisks tables and that are 10-20% are marked with five
asterisks. In the one system that's using the code, the tables that have
the highest fragmentation levels are contain only dozens or perhaps
hundreds of records and there's no noticeable impact on performance so we
don't perform any extraordinary maintenance.
--
Douglas von Roeder
949-336-2902
On Tue, May 30, 2017 at 7:40 AM, Cannon Smith via 4D_Tech <
4d_tech@lists.4d.com> wrote:
> Hi Chuck,
>
> That’s a good question. I can remember hearing both ways on this at
> Summits. Definitely less of an issue with SSDs, but I think it still can be
> in some circumstances? Makes me wonder if there are any tech notes on this.
> I’ll have to take a look.
>
> Thanks.
>
> --
> Cannon.Smith
> Synergy Farm Solutions Inc.
> Hill Spring, AB Canada
> 403-626-3236
>
>
>
>
> > On May 30, 2017, at 8:05 AM, Chuck Miller via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
> >
> > One more thing if you are running using SSDs I do not think
> fragmentation is an issue?
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ: http://lists.4d.com/faqnug.html
> Archive: http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
>
**
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Get table fragmentation
Hi Chuck, That’s a good question. I can remember hearing both ways on this at Summits. Definitely less of an issue with SSDs, but I think it still can be in some circumstances? Makes me wonder if there are any tech notes on this. I’ll have to take a look. Thanks. -- Cannon.Smith Synergy Farm Solutions Inc. Hill Spring, AB Canada 403-626-3236 > On May 30, 2017, at 8:05 AM, Chuck Miller via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > One more thing if you are running using SSDs I do not think fragmentation is > an issue? ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Get table fragmentation
One more thing if you are running using SSDs I do not think fragmentation is an issue? Regards Chuck Chuck Miller Voice: (617) 739-0306 Informed Solutions, Inc. Fax: (617) 232-1064 mailto:cjmillerinformed-solutions.com Brookline, MA 02446 USA Registered 4D Developer Providers of 4D and Sybase connectivity http://www.informed-solutions.com This message and any attached documents contain information which may be confidential, subject to privilege or exempt from disclosure under applicable law. These materials are intended only for the use of the intended recipient. If you are not the intended recipient of this transmission, you are hereby notified that any distribution, disclosure, printing, copying, storage, modification or the taking of any action in reliance upon this transmission is strictly prohibited. Delivery of this message to any person other than the intended recipient shall not compromise or waive such confidentiality, privilege or exemption from disclosure as to this communication. > On May 29, 2017, at 5:40 PM, David Adams via 4D_Tech <4d_tech@lists.4d.com> > wrote: > >> I’m glad I’m not the only one who isn’t sure how to calculate a > meaningful fragmentation threshold value. :-) > > Yeah, you're not alone. I'll add that the automated health check business > is a *great* feature to have. So reassuring, so satisfying. If you have > your remote installs phone home, you can potentially contact clients about > problems before they're aware of them. You can even automate some of this: > > * Run the health checks (duplicates, orphans, MSC check, etc.) > > * Phone home the results so that you can track them over time. (Store them > with the customer's registration until you need them, whatever.) > > * For specific errors, send them an email describing the problem, outlining > the solution, and linking to resources. > > Sure, you can put up an alert, jump to a support page, etc. And maybe you > should...but the email (or communications platform your client prefers) is > also a pretty nice feature. > > Obviously I'm not talking here about either critical/emergency problems or > ones you might be able to heal immediately, like rebuilding an index. > ** > 4D Internet Users Group (4D iNUG) > FAQ: http://lists.4d.com/faqnug.html > Archive: http://lists.4d.com/archives.html > Options: http://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Get table fragmentation
Cannon writes and David responds: >I'm working on some server health reporting. I can get the percentage of table >fragmentation using "Get table fragmentation" for each table. >I'm not quite sure what to do with it, though. I'd like to get a report when >the overall fragmentation reaches some threshold where it might >be worth compacting the datafile. 20% is suggested by on tech note. >But I suspect the number of records matters. For example, if a table only has >100 records, I probably don't care if it is even 80% fragmented. >But if a table has 10 million records and is used a lot, I may not even want >20% fragmentation. And, if only one table is fragmented and the >rest are fine, maybe it is worth compacting? But probably not in all cases? >Has anyone worked out some logic that can be automated and which takes these >kinds of things into account to let you know when it makes sense to compact the whole datafile? >> If anyone knows the answer to Cannon's question, I'd really like to know >> too! I went down this road a few years ago in V13 and >>couldn't figure out any way to get worthwhile information out of the >>fragmentation percentage. >>Apart from getting the numbers for snapshots and automated health checks, I >>also had a little dialog you could look at in real-time. >>My observation was that tiny tables with deletes appeared to be massively >>fragmented. But there were only a few records in >>play, so it didn't really mean anything. I couldn't find a way to get >>anything like the results MSC seems to generate. I am way out of date with 4D, as we are stuck on v13.x until the end of time, but many years ago I had the same question about Get table fragmentation. I found that it returned a range of values before and after compacting/rebuilding/recovering the data file. Usually the numbers were smaller after MSC processing, but not always. I asked Josh Fletcher about it back in 2011 and he said: Hi Tom, So I have one possible explanation for this behavior. First it's important to understand how 4D allocates space in the data file. When needs to locate free space in the data file, for example, it doesn't "scan" the whole data file. Instead, for every 2 MB of data file content 4D creates a 2KB "bit table". Each bit in the bit table corresponds to a 128 KB block of the data file. If a bit in the bit table is 1, it means that block of the data file is occupied. If the bit is 0, that block is free. These bit tables are created "as needed", i.e. they are not located in one particular place. So, for example, say a table contains only 1 Mb records. When you compact it you'll get something like this: [bittable1][record1][record2][bittable2][record3][record4][bittable3][record5][record6]... Note: In fact, it's not exactly this simple because each bittable also needs to be allocated like any other data file object so its space must be tracked in another bittable. But because it is quite small (2 KB), we can ignore that for this example. Get table fragmentation returns the percentage of records that are not stored contiguously. If there are bit tables between the records, the level of fragmentation reported will increase. So in the example above Get table fragmentation will return near 50%. This is not a problem per se, it just reflects the reality of objects allocated in the data file. The fact is, because those objects are so large, they'll be inefficient to access no matter what (multiple blocks of data file will need to be loaded) so the fact that they table is considered fragmented is a non-issue in this case. I hope that makes sense. Let me know. -Josh I don't know what, if anything, has changed in 4D since v13.x. Maybe nothing. In any event, we just compact once a month, and have no idea whether it makes a difference. We're pretty certain it is better than compacting once a year, but one a month makes us feel good for some reason. Hope this helps. Tom Benedict Optum This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Get table fragmentation
> I’m glad I’m not the only one who isn’t sure how to calculate a meaningful fragmentation threshold value. :-) Yeah, you're not alone. I'll add that the automated health check business is a *great* feature to have. So reassuring, so satisfying. If you have your remote installs phone home, you can potentially contact clients about problems before they're aware of them. You can even automate some of this: * Run the health checks (duplicates, orphans, MSC check, etc.) * Phone home the results so that you can track them over time. (Store them with the customer's registration until you need them, whatever.) * For specific errors, send them an email describing the problem, outlining the solution, and linking to resources. Sure, you can put up an alert, jump to a support page, etc. And maybe you should...but the email (or communications platform your client prefers) is also a pretty nice feature. Obviously I'm not talking here about either critical/emergency problems or ones you might be able to heal immediately, like rebuilding an index. ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Get table fragmentation
Hi David, Thanks for chiming in on this. Your examples are exactly the kinds of things I’m checking for. I’m glad I’m not the only one who isn’t sure how to calculate a meaningful fragmentation threshold value. :-) -- Cannon.Smith Synergy Farm Solutions Inc. Hill Spring, AB Canada 403-626-3236 > On May 29, 2017, at 3:22 PM, David Adams via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > For automated health checks, one thing you can do is script a MSC run and > check what it says. It's not the same thing..but can be quite useful. While > not that much related, I also like to run some other integrity checks, like > for duplicate values (on one or more fields), orphan records (on a key), > parent records without child records (where that doesn't make sense) and > other data integrity checks. ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Get table fragmentation
Cannon, If anyone knows the answer to Cannon's question, I'd really like to know too! I went down this road a few years ago in V13 and couldn't figure out any way to get worthwhile information out of the fragmentation percentage. Apart from getting the numbers for snapshots and automated health checks, I also had a little dialog you could look at in real-time. My observation was that tiny tables with deletes appeared to be massively fragmented. But there were only a few records in play, so it didn't really mean anything. I couldn't find a way to get anything like the results MSC seems to generate. For automated health checks, one thing you can do is script a MSC run and check what it says. It's not the same thing..but can be quite useful. While not that much related, I also like to run some other integrity checks, like for duplicate values (on one or more fields), orphan records (on a key), parent records without child records (where that doesn't make sense) and other data integrity checks. ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Get table fragmentation
I’m working on some server health reporting. I can get the percentage of table fragmentation using "Get table fragmentation” for each table. I’m not quite sure what to do with it, though. I’d like to get a report when the overall fragmentation reaches some threshold where it might be worth compacting the datafile. 20% is suggested by on tech note. But I suspect the number of records matters. For example, if a table only has 100 records, I probably don’t care if it is even 80% fragmented. But if a table has 10 million records and is used a lot, I may not even want 20% fragmentation. And, if only one table is fragmented and the rest are fine, maybe it is worth compacting? But probably not in all cases? Has anyone worked out some logic that can be automated and which takes these kinds of things into account to let you know when it makes sense to compact the whole datafile? Thanks. -- Cannon.Smith Synergy Farm Solutions Inc. Hill Spring, AB Canada 403-626-3236 ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
