inserts slowing down after 2.5m rows
I have a big table that gets a lot of inserts. Rows are inserted 10k at a time with a table function. At around 2.5 million rows, inserts slow down from 2-7s to around 15-20s. The table's dat file is around 800-900M. I have durability set to test, table-level locks, a primary key index and another 2-column index on the table. Page size is at the max and page cache set to 4500 pages. The table gets compressed (inplace) every 500,000 rows. I'm using Derby 10.4 with JDK 1.6.0_07, running on Windows XP. I've ruled out anything from the rest of the application, including GC (memory usage follows a consistent pattern during the whole load). It is a local file system. The database has a fixed number of tables (so there's a fixed number of dat files in the database directory the whole time). The logs are getting cleaned up, so there's only a few dat files in the log directory as well. Any ideas what might be causing the big slowdown after so many loads? Brian
Re: inserts slowing down after 2.5m rows
Could be checkpoint.. BTW to speed up bulk load you may want to use large log files located separately from data disks. 2009/2/27, Brian Peterson dianeay...@verizon.net: I have a big table that gets a lot of inserts. Rows are inserted 10k at a time with a table function. At around 2.5 million rows, inserts slow down from 2-7s to around 15-20s. The table's dat file is around 800-900M. I have durability set to test, table-level locks, a primary key index and another 2-column index on the table. Page size is at the max and page cache set to 4500 pages. The table gets compressed (inplace) every 500,000 rows. I'm using Derby 10.4 with JDK 1.6.0_07, running on Windows XP. I've ruled out anything from the rest of the application, including GC (memory usage follows a consistent pattern during the whole load). It is a local file system. The database has a fixed number of tables (so there's a fixed number of dat files in the database directory the whole time). The logs are getting cleaned up, so there's only a few dat files in the log directory as well. Any ideas what might be causing the big slowdown after so many loads? Brian
Re: Installation problem with Apache Derby
Hi Amee, Don't be discouraged by the EOL status of JDK1.4. You can still download it. You just have to fill in a short form. Then you will receive an email telling you where you can get the JDK. Here's the form you need to fill out: https://www2.sun.de/dct/forms/reg_us_2402_929_0.jsp You can reach this form from the top-level JDK 1.4 page: http://java.sun.com/j2se/1.4.2/download.html The next version of Derby (10.5, due out this spring) will not have this dependency on an EOL'd JDK. Hope this helps, -Rick amee.de...@wipro.com wrote: Hi, I am trying to install apache derby version 10.4.2.0 using source distribution. This distribution equires jdk 1.4.x. I am not being able to get the source of jdk-1.4.x as sun is not supported this version. Can you please tell me that is it possible to install derby without jdk 1.4.x or how can I get the source of jdk 1.4.x? Thanks and Regards, Amee Wipro Technologies * Please do not print this email unless it is absolutely necessary. * The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Re: inserts slowing down after 2.5m rows
The application is running on a client machine. I'm not sure how to tell if there's a different disk available that I could log to. If checkpoint is causing this delay, how to a manage that? Can I turn checkpointing off? I already have durability set to test; I'm not concerned about recovering from a crashed db. Brian On Fri, Feb 27, 2009 at 9:34 AM, Peter Ondruška wrote: Could be checkpoint.. BTW to speed up bulk load you may want to use large log files located separately from data disks. 2009/2/27, Brian Peterson dianeay...@verizon.net mailto:dianeay...@verizon.net mailto:dianeay...@verizon.net : I have a big table that gets a lot of inserts. Rows are inserted 10k at a time with a table function. At around 2.5 million rows, inserts slow down from 2-7s to around 15-20s. The table's dat file is around 800-900M. I have durability set to test, table-level locks, a primary key index and another 2-column index on the table. Page size is at the max and page cache set to 4500 pages. The table gets compressed (inplace) every 500,000 rows. I'm using Derby 10.4 with JDK 1.6.0_07, running on Windows XP. I've ruled out anything from the rest of the application, including GC (memory usage follows a consistent pattern during the whole load). It is a local file system. The database has a fixed number of tables (so there's a fixed number of dat files in the database directory the whole time). The logs are getting cleaned up, so there's only a few dat files in the log directory as well. Any ideas what might be causing the big slowdown after so many loads? Brian
Re: inserts slowing down after 2.5m rows
I've increased the log size and the checkpoint interval, but it doesn't seem to help. It looks like the inserts begin to dramatically slow down once the table reaches the initial allocation of pages. Things just fly along until it gets to about 1100 pages (I've allocated an initial 1000 pages, pages are 32k). Any suggestions on how to keep the inserts moving quickly at this point? Brian On Fri, Feb 27, 2009 at 3:41 PM, publicay...@verizon.net wrote: The application is running on a client machine. I'm not sure how to tell if there's a different disk available that I could log to. If checkpoint is causing this delay, how to a manage that? Can I turn checkpointing off? I already have durability set to test; I'm not concerned about recovering from a crashed db. Brian On Fri, Feb 27, 2009 at 9:34 AM, Peter Ondruška wrote: Could be checkpoint.. BTW to speed up bulk load you may want to use large log files located separately from data disks. 2009/2/27, Brian Peterson dianeay...@verizon.net mailto:dianeay...@verizon.net : I have a big table that gets a lot of inserts. Rows are inserted 10k at a time with a table function. At around 2.5 million rows, inserts slow down from 2-7s to around 15-20s. The table's dat file is around 800-900M. I have durability set to test, table-level locks, a primary key index and another 2-column index on the table. Page size is at the max and page cache set to 4500 pages. The table gets compressed (inplace) every 500,000 rows. I'm using Derby 10.4 with JDK 1.6.0_07, running on Windows XP. I've ruled out anything from the rest of the application, including GC (memory usage follows a consistent pattern during the whole load). It is a local file system. The database has a fixed number of tables (so there's a fixed number of dat files in the database directory the whole time). The logs are getting cleaned up, so there's only a few dat files in the log directory as well. Any ideas what might be causing the big slowdown after so many loads? Brian
RE: inserts slowing down after 2.5m rows
Ok, For testing, if you allocate 2000 pages, then if my thinking is ok, then you'll fly along until you get until 2100 pages. It sounds like you're hitting a bit of a snag where after your initial allocation of pages, Derby is only allocating a smaller number of pages at a time. I would hope that you could configure the number of pages to be allocated in blocks as the table grows. _ From: publicay...@verizon.net [mailto:publicay...@verizon.net] Sent: Friday, February 27, 2009 8:48 PM To: Derby Discussion Subject: Re: inserts slowing down after 2.5m rows I've increased the log size and the checkpoint interval, but it doesn't seem to help. It looks like the inserts begin to dramatically slow down once the table reaches the initial allocation of pages. Things just fly along until it gets to about 1100 pages (I've allocated an initial 1000 pages, pages are 32k). Any suggestions on how to keep the inserts moving quickly at this point? Brian On Fri, Feb 27, 2009 at 3:41 PM, publicay...@verizon.net wrote: The application is running on a client machine. I'm not sure how to tell if there's a different disk available that I could log to. If checkpoint is causing this delay, how to a manage that? Can I turn checkpointing off? I already have durability set to test; I'm not concerned about recovering from a crashed db. Brian On Fri, Feb 27, 2009 at 9:34 AM, Peter Ondruška wrote: Could be checkpoint.. BTW to speed up bulk load you may want to use large log files located separately from data disks. 2009/2/27, Brian Peterson dianeay...@verizon.net : I have a big table that gets a lot of inserts. Rows are inserted 10k at a time with a table function. At around 2.5 million rows, inserts slow down from 2-7s to around 15-20s. The table's dat file is around 800-900M. I have durability set to test, table-level locks, a primary key index and another 2-column index on the table. Page size is at the max and page cache set to 4500 pages. The table gets compressed (inplace) every 500,000 rows. I'm using Derby 10.4 with JDK 1.6.0_07, running on Windows XP. I've ruled out anything from the rest of the application, including GC (memory usage follows a consistent pattern during the whole load). It is a local file system. The database has a fixed number of tables (so there's a fixed number of dat files in the database directory the whole time). The logs are getting cleaned up, so there's only a few dat files in the log directory as well. Any ideas what might be causing the big slowdown after so many loads? Brian
RE: inserts slowing down after 2.5m rows
I thought I read in the documentation that 1000 was the max initial pages you could allocate, and after that, Derby allocates a page at a time. Is there some other setting for getting it to allocate more at a time? Brian From: Michael Segel [mailto:mse...@segel.com] On Behalf Of de...@segel.com Sent: Friday, February 27, 2009 9:59 PM To: 'Derby Discussion' Subject: RE: inserts slowing down after 2.5m rows Ok, For testing, if you allocate 2000 pages, then if my thinking is ok, then you'll fly along until you get until 2100 pages. It sounds like you're hitting a bit of a snag where after your initial allocation of pages, Derby is only allocating a smaller number of pages at a time. I would hope that you could configure the number of pages to be allocated in blocks as the table grows. _ From: publicay...@verizon.net [mailto:publicay...@verizon.net] Sent: Friday, February 27, 2009 8:48 PM To: Derby Discussion Subject: Re: inserts slowing down after 2.5m rows I've increased the log size and the checkpoint interval, but it doesn't seem to help. It looks like the inserts begin to dramatically slow down once the table reaches the initial allocation of pages. Things just fly along until it gets to about 1100 pages (I've allocated an initial 1000 pages, pages are 32k). Any suggestions on how to keep the inserts moving quickly at this point? Brian On Fri, Feb 27, 2009 at 3:41 PM, publicay...@verizon.net wrote: The application is running on a client machine. I'm not sure how to tell if there's a different disk available that I could log to. If checkpoint is causing this delay, how to a manage that? Can I turn checkpointing off? I already have durability set to test; I'm not concerned about recovering from a crashed db. Brian On Fri, Feb 27, 2009 at 9:34 AM, Peter Ondruška wrote: Could be checkpoint.. BTW to speed up bulk load you may want to use large log files located separately from data disks. 2009/2/27, Brian Peterson dianeay...@verizon.net : I have a big table that gets a lot of inserts. Rows are inserted 10k at a time with a table function. At around 2.5 million rows, inserts slow down from 2-7s to around 15-20s. The table's dat file is around 800-900M. I have durability set to test, table-level locks, a primary key index and another 2-column index on the table. Page size is at the max and page cache set to 4500 pages. The table gets compressed (inplace) every 500,000 rows. I'm using Derby 10.4 with JDK 1.6.0_07, running on Windows XP. I've ruled out anything from the rest of the application, including GC (memory usage follows a consistent pattern during the whole load). It is a local file system. The database has a fixed number of tables (so there's a fixed number of dat files in the database directory the whole time). The logs are getting cleaned up, so there's only a few dat files in the log directory as well. Any ideas what might be causing the big slowdown after so many loads? Brian
