Re: [Treebase-devel] TreeBASE disk space

[Hilmar L. <hl...@ne...>]

It is not unusual at all for the combined indices on a table to take  
up as much space as the table itself. Especially so for highly  
connected tables, i.e., tables where almost all columns are either  
primary key or foreign key columns, as is by design the atomic element  
table in vertical storage, such as the matrixelement table.

In fact, it is common practice to estimate total space needed by a  
database by doubling the amount of space needed by the data, unless a  
substantial fraction of the data is in LOBs (which can be big but are  
never indexed). That is because in typical databases the one or two  
tables driving the overall space requirements are also the most highly  
indexed ones.

So I see nothing out of the ordinary or unusual or unexpected here - I  
don't think it's worth spending time on right now. Let's just add more  
space.

	-hilmar

On Apr 23, 2010, at 11:16 AM, Jon Auman wrote:

> It's specifically an issue with the matrixelement index. The table  
> is 44 GB and the index is 45GB.
>
> You could run two different queries against the database using  
> "EXPLAIN ANALYZE SELECT ..."
> The first query would run against the index, the second would run  
> after we drop the index, and then compare results
>
> Of course, we should do this on dev or staging. I'd be happy to  
> coordinate.
>
> -Jon
>
>
> On Apr 23, 2010, at 10:45 AM, William Piel wrote:
>
>>
>> On Apr 23, 2010, at 9:58 AM, Jon Auman wrote:
>>
>>> If we fine tune the query, we see that the indices are taking over
>>
>> Hmm.. at some point we added a bunch of indices in a effort to  
>> improve performance. I wonder if the alarming growth from 50GB to  
>> 90GB occurred as a results of adding indices? In which case this  
>> increase would be a "one-off" big jump, and therefore future growth  
>> would, hopefully, not be so alarming.
>>
>> Is there a way to know which indices have a valuable improvement in  
>> performance and are just wasting disk space?
>>
>> bp
>>
>>
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> Treebase-devel mailing list
>> Tre...@li...
>> https://lists.sourceforge.net/lists/listinfo/treebase-devel
>
> -------------------------------------------------------
> Jon Auman
> Systems Administrator
> National Evolutionary Synthesis Center
> Duke University
> http:www.nescent.org
> jon...@ne...
> ------------------------------------------------------
>
>
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Treebase-devel mailing list
> Tre...@li...
> https://lists.sourceforge.net/lists/listinfo/treebase-devel

-- 
===========================================================
: Hilmar Lapp  -:- Durham, NC -:- informatics.nescent.org :
===========================================================