st_reclaim_longcol


1   /*
2   
3      Derby - Class org.apache.derbyTesting.functionTests.harness.procedure
4   
5      Licensed to the Apache Software Foundation (ASF) under one or more
6      contributor license agreements.  See the NOTICE file distributed with
7      this work for additional information regarding copyright ownership.
8      The ASF licenses this file to You under the Apache License, Version 2.0
9      (the "License"); you may not use this file except in compliance with
10     the License.  You may obtain a copy of the License at
11  
12        http://www.apache.org/licenses/LICENSE-2.0
13  
14     Unless required by applicable law or agreed to in writing, software
15     distributed under the License is distributed on an "AS IS" BASIS,
16     WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17     See the License for the specific language governing permissions and
18     limitations under the License.
19  
20   */
21  
22  package org.apache.derbyTesting.functionTests.tests.storetests;
23  
24  
25  import org.apache.derby.iapi.services.sanity.SanityManager;
26  
27  import org.apache.derbyTesting.functionTests.tests.store.BaseTest;
28  
29  import java.sql.CallableStatement  ;
30  import java.sql.Connection  ;
31  import java.sql.PreparedStatement  ;
32  import java.sql.ResultSet  ;
33  import java.sql.SQLException  ;
34  import java.sql.Statement  ;
35  
36  import java.util.Arrays  ;
37  
38  import org.apache.derby.tools.ij;
39  
40  
41  /**
42  
43  The purpose of this test space reclamation of long rows and long columns.
44  This addresses DERBY-670.
45  
46  The main issue is that previous to fixes for DERBY-670, space reclamation
47  was only automatically queued when the last row on a page was deleted.  In
48  the case of long columns, the actual row on the main page can be quite small
49  as the long data is streamed onto other pages.  So the table can grow 
50  unexpectedly quite large before the default space reclamation kicks in.  The
51  change queues space reclamation in the case of long columns (blob/clob),
52  imediately post commit of the single delete.
53  
54  The testing strategy is to loop doing insert, delete, commit of a blob for
55  a number of iterations and check that the actual size of the table is 
56  reasonable.  A sleep will be added to allow time for post commit to catch up
57  as the test may be run in a number of environments with varying performance
58  of background activities.
59  
60  **/
61  
62  public class st_reclaim_longcol extends BaseTest
63  {
64      static boolean verbose = false;
65  
66      public st_reclaim_longcol()
67      {
68      }
69  
70  
71      /**
72       * Create the base table.
73       **/
74      private static void setup()
75          throws Exception  
76      {
77      }
78  
79      /**
80       * Test reclaim of a single deleted blob on a page with non-deleted rows.
81       * <p>
82       * loops through inserting alternating long and short column rows resulting
83       * in pages with 1 short and one long.  Deletes the long column row and
84       * tests that space from the long column row is reclaimed even though
85       * there are non-deleted rows on the page.
86       **/
87      private static final int SHORT_BLOB_SIZE = 10;
88      public void test1(Connection   conn, int blob_size, int num_rows)
89          throws SQLException  
90      {
91          byte[]  long_byteVal    = new byte[blob_size];
92          byte[]  short_byteVal   = new byte[10];
93  
94          beginTest(
95              conn, 
96              "test1:insert/delete of " + num_rows + 
97                  " rows with blob(" + blob_size + ")"); 
98  
99          Arrays.fill(long_byteVal,  (byte)'L');
100         Arrays.fill(short_byteVal, (byte)'S');
101 
102         createTable(
103             conn, 
104             "longcol", 
105             "create table longcol (id int primary key not null, val blob(" + 
106             blob_size + "))");
107 
108         conn.commit();
109 
110         PreparedStatement   ins_stmt = 
111             conn.prepareStatement("insert into longcol values (?, ?)");
112         PreparedStatement   del_stmt = 
113             conn.prepareStatement("delete from longcol where id = ?");
114 
115         // worst case is a mixture of rows with long columns and those without.
116         // Insert of row with a long column always first goes onto a new 
117         // page by itself, but subsequent non-long column rows can be inserted
118         // on that page.  Then when the long column row is deleted - before the
119         // change - it and all it's chain won't get reclaimed until all rows
120         // on the page get deleted.
121 
122         // now do insert/delete/commit for subsequent rows.  Before fix the
123         // space used in the table will grow until the deleted rows do not
124         // fit on the first page.  And even then before the fix the rows
125         // on the first page are never reclaimed as the 1st one is never
126         // deleted.
127         for (int iter = 1; iter < num_rows; iter++)
128         {
129             // insert the long blob
130             ins_stmt.setInt(  1, iter);
131             ins_stmt.setBytes(2, long_byteVal);
132             ins_stmt.executeUpdate();
133 
134             // insert the short blob
135             ins_stmt.setInt(  1, -(iter));
136             ins_stmt.setBytes(2, short_byteVal);
137             ins_stmt.executeUpdate();
138 
139             // delete the long blob
140             del_stmt.setInt(1, iter);
141             del_stmt.executeUpdate();
142 
143             // commit the xact, post commit should kick in to reclaim the
144             // blob space sometime after the commit.
145             conn.commit();
146 
147             // sleep, just in case on this machine background
148             // post commit is slow.
149             try
150             {
151                 Thread.sleep(20);
152             }
153             catch (Exception   ex)
154             {
155                 // just ignore interupts of sleep.
156             }
157         }
158 
159         int[] sp_info = getSpaceInfo(conn, "APP", "LONGCOL", true);
160 
161         int total_pages = 
162             sp_info[SPACE_INFO_NUM_ALLOC] + sp_info[SPACE_INFO_NUM_FREE];
163 
164         int total_expected_page_max = 12 + num_rows;
165 
166         if (total_pages > total_expected_page_max)
167         {
168             // for the above test case we expect the following space:
169             //     page 0
170             //     page 1 
171             //     free space from 1 blob - 9 pages per blob
172             //     allocated page per long/short blob insert.  Each long
173             //         inserts onto a new page to try and fit it entirely
174             //         on a page.  Then the short blob goes to last inserted
175             //         page.  This process repeats.  The previous pages are
176             //         marked "half-filled" and can be used in future for
177             //         short rows that don't fit on the last page inserted.
178             
179 
180 
181             System.out.println(
182                 "Test failed, expected less than " + 
183                 total_expected_page_max + " pages - count is:\n" +
184                 "free pages     : "   + sp_info[SPACE_INFO_NUM_FREE] +
185                 "\nallocated pages: " + sp_info[SPACE_INFO_NUM_ALLOC]);
186         }
187 
188         if (verbose)
189         {
190             System.out.println(
191                 "Space information after " + num_rows + 
192                 "insert/delete pairs of rows in longcol table containing " + 
193                 blob_size + "blobs:");
194 
195             System.out.println("isindex = "   + sp_info[SPACE_INFO_IS_INDEX]);
196             System.out.println("num_alloc = " + sp_info[SPACE_INFO_NUM_ALLOC]);
197             System.out.println("num_free = "  + sp_info[SPACE_INFO_NUM_FREE]);
198             System.out.println("page_size = " + sp_info[SPACE_INFO_PAGE_SIZE]);
199             System.out.println(
200                 "estimspacesaving = " + sp_info[SPACE_INFO_ESTIMSPACESAVING]);
201         }
202 
203         endTest(
204             conn, 
205             "test1:insert/delete of " + num_rows + 
206                 " rows with blob(" + blob_size + ")"); 
207     }
208 
209     /**
210      * Test reclaim of sequence of deleted blobs.
211      * <p>
212      * Simulates a "queue" of work of input "work_size".  Inserts "work_size"
213      * elements, and then subsequently in each transaction inserts a new 
214      * work item and deletes the oldest work item.  Checks that the used
215      * space reaches a steady state, rather than constantly growing.
216      *
217      **/
218     public void test2(
219     Connection    conn, 
220     int         blob_size, 
221     int         work_size, 
222     int         total_work)
223         throws SQLException  
224     {
225         byte[]  long_byteVal    = new byte[blob_size];
226         byte[]  short_byteVal   = new byte[10];
227 
228         beginTest(
229             conn, 
230             "test2:queue of " + work_size + 
231                 " rows with blob(" + blob_size + "), total_work = " + 
232                 total_work); 
233 
234         Arrays.fill(long_byteVal,  (byte)'L');
235         Arrays.fill(short_byteVal, (byte)'S');
236 
237         createTable(
238             conn, 
239             "longcol", 
240             "create table longcol (id int primary key not null, val blob(" + 
241             blob_size + "))");
242 
243         conn.commit();
244 
245         PreparedStatement   ins_stmt = 
246             conn.prepareStatement("insert into longcol values (?, ?)");
247         PreparedStatement   del_stmt = 
248             conn.prepareStatement("delete from longcol where id = ?");
249 
250         // insert the "work_size" number of elements into the table
251         for (int iter = 0; iter < work_size; iter++)
252         {
253             // insert the long blob
254             ins_stmt.setInt(  1, iter);
255             ins_stmt.setBytes(2, long_byteVal);
256             ins_stmt.executeUpdate();
257 
258             // commit the xact, post commit should kick in to reclaim the
259             // blob space sometime after the commit.
260         }
261         conn.commit();
262 
263 
264         // for each subsequent work item, queue it to the end and delete
265         // the oldes existing work item.
266         for (int iter = work_size; iter < total_work; iter++)
267         {
268             // insert the long blob
269             ins_stmt.setInt(  1, iter);
270             ins_stmt.setBytes(2, long_byteVal);
271             ins_stmt.executeUpdate();
272 
273 
274             // delete the long blob
275             del_stmt.setInt(1, iter - work_size - 1);
276             del_stmt.executeUpdate();
277 
278             // commit the xact, post commit should kick in to reclaim the
279             // blob space sometime after the commit.
280             conn.commit();
281 
282             try
283             {
284                 Thread.sleep(20);
285             }
286             catch (Exception   ex)
287             {
288                 // just ignore interupts of sleep.
289             }
290         }
291 
292 
293         int[] sp_info = getSpaceInfo(conn, "APP", "LONGCOL", true);
294 
295         int total_pages = 
296             sp_info[SPACE_INFO_NUM_ALLOC] + sp_info[SPACE_INFO_NUM_FREE];
297 
298         // Expect at least allocated pages * 10 for each item in work_size, 
299         // plus some overhead for 1st page and such.
300         // Free page count depends on how quick post commit can free before
301         // subsequent insert, and very likely is machine/jvm/os dependent. In
302         // my testing adding a sleep of 100 ms. to the above insert/delete
303         // loop changed free from 60 to 30.  Minimum is 10 for the one row 
304         // that is deleted in the same xact as the first inserted row in the 
305         // insert/delete loop.  The 30 below is expected allocate of 10
306         // per work size, and then a guess at how fast post commit can keep
307         // up with free pages.  Run the test with total_work reasonably 
308         // bigger than worksize, something like work_size=5 and total_work >100
309         int total_expected_page_max = 30 * work_size; 
310 
311         if (total_pages > total_expected_page_max)
312         {
313             System.out.println(
314                 "Test failed, expected less than " + 
315                 total_expected_page_max + " pages - count is:\n" +
316                 "free pages     : "   + sp_info[SPACE_INFO_NUM_FREE] +
317                 "\nallocated pages: " + sp_info[SPACE_INFO_NUM_ALLOC]);
318         }
319 
320         if (verbose)
321         {
322             System.out.println("Space information:");
323 
324             System.out.println("isindex = "   + sp_info[SPACE_INFO_IS_INDEX]);
325             System.out.println("num_alloc = " + sp_info[SPACE_INFO_NUM_ALLOC]);
326             System.out.println("num_free = "  + sp_info[SPACE_INFO_NUM_FREE]);
327             System.out.println("page_size = " + sp_info[SPACE_INFO_PAGE_SIZE]);
328             System.out.println(
329                 "estimspacesaving = " + sp_info[SPACE_INFO_ESTIMSPACESAVING]);
330         }
331 
332         // Run another iteration of the work loop, by now memory should 
333         // have gotten to constant.
334         for (int iter = work_size + total_work; iter < (total_work * 2); iter++)
335         {
336             // insert the long blob
337             ins_stmt.setInt(  1, iter);
338             ins_stmt.setBytes(2, long_byteVal);
339             ins_stmt.executeUpdate();
340 
341 
342             // delete the long blob
343             del_stmt.setInt(1, iter - work_size - 1);
344             del_stmt.executeUpdate();
345 
346             // commit the xact, post commit should kick in to reclaim the
347             // blob space sometime after the commit.
348             conn.commit();
349 
350             try
351             {
352                 Thread.sleep(100);
353             }
354             catch (Exception   ex)
355             {
356                 // just ignore interupts of sleep.
357             }
358         }
359 
360 
361         int[] second_sp_info = getSpaceInfo(conn, "APP", "LONGCOL", true);
362 
363         int second_total_pages = 
364             sp_info[SPACE_INFO_NUM_ALLOC] + sp_info[SPACE_INFO_NUM_FREE];
365 
366         if (total_pages != second_total_pages)
367         {
368             System.out.println(
369                 "Test failed, expected constant memory after second run." +
370                 "initial total = " + total_pages +
371                 "second total = " + second_total_pages);
372         }
373 
374         if (verbose)
375         {
376             System.out.println("Space information:");
377 
378             System.out.println("isindex = "   + sp_info[SPACE_INFO_IS_INDEX]);
379             System.out.println("num_alloc = " + sp_info[SPACE_INFO_NUM_ALLOC]);
380             System.out.println("num_free = "  + sp_info[SPACE_INFO_NUM_FREE]);
381             System.out.println("page_size = " + sp_info[SPACE_INFO_PAGE_SIZE]);
382             System.out.println(
383                 "estimspacesaving = " + sp_info[SPACE_INFO_ESTIMSPACESAVING]);
384         }
385 
386         endTest(
387             conn, 
388             "test2:queue of " + work_size + 
389                 " rows with blob(" + blob_size + "), total_work = " + 
390                 total_work); 
391     }
392 
393 
394     public void testList(Connection   conn)
395         throws SQLException  
396     {
397         test1(conn, 250000, 20);
398         test2(conn, 250000, 5, 500);
399     }
400 
401     public static void main(String  [] argv) 
402         throws Throwable  
403     {
404         st_reclaim_longcol test = new st_reclaim_longcol();
405 
406         ij.getPropertyArg(argv); 
407         Connection   conn = ij.startJBMS();
408 
409         try
410         {
411             test.testList(conn);
412         }
413         catch (SQLException   sqle)
414         {
415             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
416                 System.out, sqle);
417             sqle.printStackTrace(System.out);
418         }
419     }
420 }
421
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags