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 .
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'
(http://www.actian.com/kb/article/413374)
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.
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.
-----Original Message-----
From: info-ingres-boun...@kettleriverconsulting.com [mailto:info-ingres-boun...@kettleriverconsulting.com] On Behalf Of Ingres Forums
Sent: February-03-12 11:03 AM
To: info-ing...@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 .
> 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.
> [...]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'
(http://www.rationalcommerce.com/papers/tuner.htm). The benchmark is
woeful but it -is- testing concurrency and the performance boost is
real.
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
> 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.
