Table Size > 16 GB

[krkumar]

We’re working to an understanding that there is an absolute table limitation of 8.3m pages (approximately 16Gb) .

we have a customer who is now very close to hitting the above limits on a 2k page size .
although increasing page size is an option we would not want to do that due to concurrency issues that might crop up .

Would it be possible to split the table over 2 locations to overcome this problem
i.e. location = (ii_database,test_loc)

will there be a performance overhead in terms of database I/O due to split locations?

I do not want to do partitioning as we have had problems with performance in the past .

[Divya]

Hi,

Depending on what version of Ingres this is, you might want to review the knowledge base document :
Knowlege Base | Maximum Size of Table - 413374 - Actian

Adding multiple locations to table does not slow down the performance. It just splits the table to all the locations into smaller files so you do not have one large table file in one location.

Hope this helps
Divya

Hi,

We have tables around 400GB (using 64K pages) spread over 16 database locations, we are also very aware of Ingres' page limitations.
I'm not sure what your concerns are about increasing the page size. Non-2K page tables enables row level locking making them more 'concurrency' friendly than 2K page tables.

As far as multiple locations, if you storage is set up right, it can increase I/O performance, but the table page size remains the same.

Andre


-----Original Message-----
From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of Ingres Forums
Sent: February-03-12 11:03 AM
To: info-ingres@kettleriverconsulting.com
Subject: [Info-Ingres] Table Size > 16 GB


We’re working to an understanding that there is an absolute table limitation of 8.3m pages (approximately 16Gb) .

we have a customer who is now very close to hitting the above limits on a 2k page size .
although increasing page size is an option we would not want to do that due to concurrency issues that might crop up .

Would it be possible to split the table over 2 locations to overcome this problem i.e. location = (ii_database,test_loc)

will there be a performance overhead in terms of database I/O due to split locations?

I do not want to do partitioning as we have had problems with performance in the past .


--
krkumar
------------------------------------------------------------------------
krkumar's Profile: http://community.actian.com/forum/me...p?userid=11853
View this thread: Table Size > 16 GB

On Feb 3, 2012, at 11:03 AM, Ingres Forums wrote:

>
> We’re working to an understanding that there is an absolute table
> limitation of 8.3m pages (approximately 16Gb) .
>
> we have a customer who is now very close to hitting the above limits on
> a 2k page size .
> although increasing page size is an option we would not want to do that
> due to concurrency issues that might crop up .

If the applications are carefully tuned to simulate row locking in a 2K
page, you could simply turn on row locking in the larger pages.

> Would it be possible to split the table over 2 locations to overcome
> this problem
> i.e. location = (ii_database,test_loc)

No, that doesn't help. You still have the 8m page limit, it's not per
location.

> I do not want to do partitioning as we have had problems with
> performance in the past .

Such as?

WIthout knowing more specifics such as storage structure and table
usage patterns, I'd raise the page size to 8K first, and then start
thinking about partitioning.

Karl

[rhann]

[...]although increasing page size is an option we would not want to do that due to concurrency issues that might crop up.

I understand your concern and in fact I made exactly the same assumption until a couple of years ago.

While tuning a database to improve its performance running the (admittedly flawed) BenchmarkSQL test for concurrency, I actually achieved a 30% boost in performance by switching to 8k pages from 2k. Take a look at slide 29 in my presentation. The benchmark is woeful but it is testing concurrency and the performance boost is real.

[krkumar]

Thanks All,

we have now decided to go 4k page on this particular table , this is more of a transactional table which only ever has new rows created for each financial transaction in the system , the rows never get updated or deleted from this table .

I have run a few tests with 'set lock_trace' with row level locking on this table and my results show that the it only ever takes a PAGE level lock (Mode:IX) during insert ( creation of a new row )
extract from the lock trace is detailed below .

However it does take a row level locks during an udpate/insert if done on the table

NOTE: - it does not seem to hold any locks on a secondary index that I have on this table for a new insert

Is this behaviour expected ? just need a second opinion on this one



-------------------------------------------------------------------
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (testdb,k_testtable,0.0)
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (testdb,k_testtable,0.354604)
UNLOCK: PAGE Key: (testdb,ktesttable,0.0)
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (testdb,k_testtable,0.504325)
UNLOCK: PAGE Key: (testdb,ktesttable,0.354604)
UNLOCK: PAGE Key: (testdb,ktesttable,0.504325)
-------------------------------------------------------------------
-------------------------------------------------------------------
LOCK: TABLE NOWT Mode: IX Timeout: -4 Key: (testdb,k_testtable)
LOCK: VALUE PHYS Mode: X Timeout: 0 Key: (testdb,k_testtable,3309846,262144,0)
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (testdb,k_testtable,0.0)
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (testdb,k_testtable,0.354604)
UNLOCK: PAGE Key: (testdb,ktesttable,0.0)
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (testdb,k_testtable,0.504325)
UNLOCK: PAGE Key: (testdb,ktesttable,0.354604)
UNLOCK: PAGE Key: (testdb,ktesttable,0.504325)
LOCK: PAGE LOCL Mode: IX Timeout: 0 Key: (testdb,k_testtable,0.504569)
-------------------------------------------------------------------

On Feb 8, 2012, at 5:41 AM, Ingres Forums wrote:

> I have run a few tests with 'set lock_trace' with row level locking on
> this table and my results show that the it only ever takes a PAGE level
> lock (Mode:IX) during insert ( creation of a new row )
> extract from the lock trace is detailed below .

The IX page lock is an intent lock, used for the same reason that
page level locking takes an IX table lock. That is normal.

The PHYS page locks are probably space management pages, and
page locks on them are normal also.

Karl