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.....ingres/topics



http://community.ingres.com/forum/dba-forum/



regards



Paul





From: Laverty, Danny [mailtoanny dot Laverty at falkirk dot gov dot uk]
Sent: Wednesday, 18 November 2009 10:19 PM
To: 'openroad-users (AT) googlegroups (DOT) 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

Paul White wrote:

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.

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.

Roy wrote
ahhh! 88% appears frequently in my stats.



Karl wrote
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: openroad-users (AT) googlegroups (DOT) 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:

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.

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 openroad-users (AT) googlegroups (DOT) com.
To unsubscribe from this group, send email to
openroad-users+unsubscribe (AT) googlegroups (DOT) com.
For more options, visit this group at
http://groups.google.com/group/openroad-users?hl=.

On Nov 18, 2009, at 9:42 AM, Paul White wrote:
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