Re: [Info-Ingres] [openroad-users] Performance problems when table page size increased from 2k to 4k
Paul White
Hi Danny,
It sounds like you need to increase your 4KB Buffer Cache.
Get your DBA to run a tracepoint DM420 to see how each cache is performing.
I try to aim for >95% cache hit ratio but with very large table scans it is impossible.
Please heed the warnings about using tracepoints. (page 730 SQL reference guide Ingres 9.2)
You will get a better response to Ingres DBA type questions at the community forums or comp.databases.ingres
http://groups.google.com/group/comp.databases.ingres/topics
http://community.ingres.com/forum/dba-forum/
regards
Paul
From: Laverty, Danny [mailto:Danny dot Laverty
at falkirk dot gov dot uk]
Sent: Wednesday, 18 November 2009 10:19 PM
To: 'openroa...@googlegroups.com'
Subject: [openroad-users] Performance problems when table page size
increased from 2k to 4k
We hit fluctuating performance problems within our OpenROAD written application after increasing the page size of one of its frequently used Ingres tables from 2k to 4k. Before raising a Support call, I wondered if anyone else had hit this problem and what was their final resolution? We know we can fix symptoms of the problem within the application / database as and when they are reported, but that does not address the source of the problem.
The table had no secondary indexes. A simple select on a non-key column resulted in a read of the whole table (as would be expected). Prior to the change, this took between 2 and 4 seconds – which was acceptable to our users.
We recently started using VisualDBA to add / remove columns from Ingres tables. A prerequisite is that the table must have a page size > 2k (and all of our existing tables are 2k). We used VisualDBA to modify the structure to give the table a 4k page size. After the change (about a month later), we got reports that performance was volatile and could take anything between 2 and 100 seconds. The table is now; row width = 2347, storage structure = btree unique, columns = 73, pages/overflow = 62302/0, rows – 61843, journaling = enabled, keyed on a single char(6) column.
Observation showed that when a user was reading the whole table, both the disk I/O and CPU usage on the server increased significantly. Presumably, if several whole table reads are happening at the same time then we will hit a disk I/O and/or CPU bottleneck – giving us our volatile performance problems.
We were able to get around the initially reported performance problems by adding an index to the table and avoiding a full table read. However, we can see other instances in the application where a full table read will occur and there are bound to be other scenarios where a full table read will need to take place.
N..B. Upgrading the server to increase disk capacity / CPU has to be an option, but costs may prohibit this (and how much would we need to increase these by?).
Thanks in advance for any help.
Danny Laverty
Roy Hann
> It sounds like you need to increase your 4KB Buffer Cache.
Actually I don't think this is necessarily the solution to erratic
response time when repeatedly table-scanning.
There are only two ways to get consistent response time while
table-scanning: (1) have enough group buffers to guarantee neither you
nor anyone else will ever invalidate the one containing the first page
of the scan-- which is impossible for any but the smallest databases.
Or (2) use a very small number of group buffers to guarantee that you
*will* always invalidate the one with the first page, forcing a rescan
from disk.
If you choose the second option then you can consider making your
individual group buffers bigger, but if you make them too big the OS
will end up doing multiple disk reads to fill each one, so there is a
point at which making them bigger brings no benefit. Scattering of
pages within the table file will also limit the marginal benefit of
larger group buffers.
> Get your DBA to run a tracepoint DM420 to see how each cache is performing.
>
> I try to aim for >95% cache hit ratio but with very large table scans it is
> impossible.
When table-scanning with an 8-page read-ahead, with a very small number
of group buffers, you will get a cache hit-rate of ~88%. You get a
cache miss on the first page which forces the read-ahead, then you get
cache hits on the next 7 pages.
--
Roy
UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.
Paul White
> When table-scanning with an 8-page read-ahead, with a very small
> number of group buffers, you will get a cache hit-rate of ~88%.
> You get a cache miss on the first page which forces the read-ahead,
> then you get cache hits on the next 7 pages.
ahhh! 88% appears frequently in my stats.
Karl wrote
> large, but the 4K cache was too small. If you change the table page size
> to 8K, make sure the 8K cache is large enough.
If this is a time critical table, does it make sense to change the storage
structure to 16K and allocate 250MB of 16K buffers so it will always be
memory resident. Or is this a dumb approach?
Paul
-----Original Message-----
From: Karl Schendel
Sent: Wednesday, 18 November 2009 10:37 PM
To: openroa...@googlegroups.com
Subject: Re: [openroad-users] Performance problems when table page size
increased from 2k to 4k
On Nov 18, 2009, at 7:19 AM, Laverty, Danny wrote:
> ...
> We recently started using VisualDBA to add / remove columns from Ingres
tables. A prerequisite is that the table must have a page size > 2k (and
all of our existing tables are 2k). We used VisualDBA to modify the
structure to give the table a 4k page size. After the change (about a month
later), we got reports that performance was volatile and could take anything
between 2 and 100 seconds. The table is now; row width = 2347, storage
structure = btree unique, columns = 73, pages/overflow = 62302/0, rows -
61843, journaling = enabled, keyed on a single char(6) column.
>
> Observation showed that when a user was reading the whole table, both the
disk I/O and CPU usage on the server increased significantly.
Well, adding the column(s) probably multiplied the table size on disk by at
least 4x.
About half of the disk space is wasted, because only one 2347 byte row can
fit
on a 4k page.
I'd try several things:
- Compress the rows. I'm guessing that you added at least one large char
or varchar columns. Try "modify table to reconstruct with
compression=(data)"
(if you're running a 2.x server you'll have to modify to btree.)
- Use a larger page size. There will still be wasted space, but not as much
relative to the total table size. I'd suggest 8k.
- Check the size of the DMF cache. It sounds like your 2K page cache was
large, but the 4K cache was too small. If you change the table page size
to 8K, make sure the 8K cache is large enough.
Karl
--
You received this message because you are subscribed to the Google Groups
"OpenROAD Users Mailing List" group.
To post to this group, send email to openroa...@googlegroups.com.
To unsubscribe from this group, send email to
openroad-user...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/openroad-users?hl=.
Karl Schendel
On Nov 18, 2009, at 9:42 AM, Paul White wrote:
> Karl wrote
>> large, but the 4K cache was too small. If you change the table page size
>> to 8K, make sure the 8K cache is large enough.
>
> If this is a time critical table, does it make sense to change the storage
> structure to 16K and allocate 250MB of 16K buffers so it will always be
> memory resident. Or is this a dumb approach?
In the OP's situation, I am guessing that row compression will end up
being the big win, in which case it might be better to stick with 8K pages.
(That's assuming that the table is being accessed through
the btree index with some frequency.) Ignoring compression, there
ought to be relatively little savings in going from 8K to 16K,
compared to the 4K to 8K jump. I haven't bothered doing the math,
though.
In either case, as Roy pointed out, you either allocate enough group
buffers to make the whole thing resident, or you allocate a minimum
of group buffers and force re-reads from disk. (Which may in fact
end up coming from OS page cache.)
Karl