You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by David Parker <da...@tractionsoftware.com> on 2010/02/01 23:09:34 UTC
pagesize for a given table
I am trying to address some insert performance issues we are having for a wide table by increasing the pagesize for that table. As far as I can tell, this means issuing a call to syscs_set_database_property to set the desired pagesize, then creating the table. I'd kind of like to be able to verify that the table created has the proper pagesize.
Is there a way to determine the pagesize being used by a given table?
TIA
=============================================
David Parker
dap@tractionsoftware.com
Re: pagesize for a given table
Posted by David Parker <da...@tractionsoftware.com>.
My thought was that the large page size might be contributing to lock escalation so that the whole table was getting locked per insert or something, but I confess I don't know how derby handles that.
I will dig into some of the locking diagnostics. Thanks!
- DAP
On Feb 2, 2010, at 10:53 AM, Bryan Pendleton wrote:
> David Parker wrote:
>> slowdowns that we have traced to this insert - a single insert taking several seconds
>
> That feels more like a locking issue than an I/O issue, to me.
>
> Perhaps you have some sort of rarely-run query which, when it runs,
> scans and processes the entire table, thus locking the table and
> preventing any insert into the table.
>
> Just a guess.
>
> thanks,
>
> bryan
>
>
>
=============================================
David Parker
dap@tractionsoftware.com
Re: pagesize for a given table
Posted by Bryan Pendleton <bp...@amberpoint.com>.
David Parker wrote:
> slowdowns that we have traced to this insert - a single insert taking several seconds
That feels more like a locking issue than an I/O issue, to me.
Perhaps you have some sort of rarely-run query which, when it runs,
scans and processes the entire table, thus locking the table and
preventing any insert into the table.
Just a guess.
thanks,
bryan
Re: pagesize for a given table
Posted by David Parker <da...@tractionsoftware.com>.
Thanks, that's good to know.
We have a high rate of inserts into this wide table - page access statistics. In the field we have customers who have experienced
slowdowns that we have traced to this insert - a single insert taking several seconds, long enough to make it unusable as part
of the site.
Unfortunately we are not able to repro the issue in-house, so I'm just trying to put together as many diagnostics as I can, and I'm new to derby. So I appreciate everybody's assistance!
- DAP
On Feb 2, 2010, at 9:49 AM, Bryan Pendleton wrote:
> Hi David,
>
>> At what point does derby decide on the pagesize when it is creating a table?
>
> During the bind processing (compilation) of the CREATE TABLE statement itself.
>
>> I have a table with several varchar(4000) fields in it. Though they are
> > varchar, does derby calculate row size based on the maximum possible column
> > size for variable sized columns in deciding whether to bump up the pagesize to 32K?
>
> Yes, Derby bases its calculation on the max column size for VARCHAR, so 4000
> in your case.
>
> I'm a little surprised that the too-large pagesize would adversely affect
> your performance, but you should be able to test it yourself by setting
> the derby.storage.pageSize property, as Derby will obey that property even
> if you have multiple long VARCHAR columns in your table.
>
> thanks,
>
> bryan
>
>
=============================================
David Parker
dap@tractionsoftware.com
Re: pagesize for a given table
Posted by Bryan Pendleton <bp...@amberpoint.com>.
Hi David,
> At what point does derby decide on the pagesize when it is creating a table?
During the bind processing (compilation) of the CREATE TABLE statement itself.
> I have a table with several varchar(4000) fields in it. Though they are
> varchar, does derby calculate row size based on the maximum possible column
> size for variable sized columns in deciding whether to bump up the pagesize to 32K?
Yes, Derby bases its calculation on the max column size for VARCHAR, so 4000
in your case.
I'm a little surprised that the too-large pagesize would adversely affect
your performance, but you should be able to test it yourself by setting
the derby.storage.pageSize property, as Derby will obey that property even
if you have multiple long VARCHAR columns in your table.
thanks,
bryan
Re: pagesize for a given table
Posted by David Parker <da...@tractionsoftware.com>.
Following up on my pagesize question....
At what point does derby decide on the pagesize when it is creating a table? I have a table with several varchar(4000) fields in it. Though they are varchar, does derby calculate row size based on the maximum possible column size for variable sized columns in deciding whether to bump up the pagesize to 32K?
I am trying to track down an insert performance problem, and I'm wondering if the extra large pagesize is causing a problem. The maximum row size for this table is around 5K, but in practice I'm pretty sure it is a small fraction of that.
Thanks.
- DAP
On Feb 1, 2010, at 5:26 PM, Knut Anders Hatlen wrote:
> David Parker <da...@tractionsoftware.com> writes:
>
>> I am trying to address some insert performance issues we are having
>> for a wide table by increasing the pagesize for that table. As far as
>> I can tell, this means issuing a call to syscs_set_database_property
>> to set the desired pagesize, then creating the table. I'd kind of like
>> to be able to verify that the table created has the proper pagesize.
>>
>> Is there a way to determine the pagesize being used by a given table?
>
> You can use the SYSCS_DIAG.SPACE_TABLE table function:
> http://db.apache.org/derby/docs/10.5/ref/rrefsyscsdiagtables.html
>
> For example, if you want to verify the page size of the table MYTABLE in
> schema MYSCHEMA, execute the following statement:
>
> ij> select pagesize from table (syscs_diag.space_table('MYSCHEMA', 'MYTABLE')) s;
> PAGESIZE
> -----------
> 4096
>
> 1 row selected
>
> Hope this helps,
>
> --
> Knut Anders
=============================================
David Parker
dap@tractionsoftware.com
Re: pagesize for a given table
Posted by David Parker <da...@tractionsoftware.com>.
perfect! Thanks!
On Feb 1, 2010, at 5:26 PM, Knut Anders Hatlen wrote:
> David Parker <da...@tractionsoftware.com> writes:
>
>> I am trying to address some insert performance issues we are having
>> for a wide table by increasing the pagesize for that table. As far as
>> I can tell, this means issuing a call to syscs_set_database_property
>> to set the desired pagesize, then creating the table. I'd kind of like
>> to be able to verify that the table created has the proper pagesize.
>>
>> Is there a way to determine the pagesize being used by a given table?
>
> You can use the SYSCS_DIAG.SPACE_TABLE table function:
> http://db.apache.org/derby/docs/10.5/ref/rrefsyscsdiagtables.html
>
> For example, if you want to verify the page size of the table MYTABLE in
> schema MYSCHEMA, execute the following statement:
>
> ij> select pagesize from table (syscs_diag.space_table('MYSCHEMA', 'MYTABLE')) s;
> PAGESIZE
> -----------
> 4096
>
> 1 row selected
>
> Hope this helps,
>
> --
> Knut Anders
=============================================
David Parker
dap@tractionsoftware.com
Re: pagesize for a given table
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
David Parker <da...@tractionsoftware.com> writes:
> I am trying to address some insert performance issues we are having
> for a wide table by increasing the pagesize for that table. As far as
> I can tell, this means issuing a call to syscs_set_database_property
> to set the desired pagesize, then creating the table. I'd kind of like
> to be able to verify that the table created has the proper pagesize.
>
> Is there a way to determine the pagesize being used by a given table?
You can use the SYSCS_DIAG.SPACE_TABLE table function:
http://db.apache.org/derby/docs/10.5/ref/rrefsyscsdiagtables.html
For example, if you want to verify the page size of the table MYTABLE in
schema MYSCHEMA, execute the following statement:
ij> select pagesize from table (syscs_diag.space_table('MYSCHEMA', 'MYTABLE')) s;
PAGESIZE
-----------
4096
1 row selected
Hope this helps,
--
Knut Anders