java - 如何加速 Hibernate 批处理并避免 OutOfMemoryException

Question

描述

我有一个使用 Hibernate ORM 与数据库通信的 Spring 应用程序。我有这个函数，它填充名为 orders、order_line 和 cc_xacts 的数据库表。表之间的关系如下所示:

--------            --------------
|orders|--- 1:m --->| order_line |
--------            --------------
   |                ------------
   -------- 1:m --->| cc_xacts |
                    ------------

所以 orders 与 cc_xacts 和 order_line 实体具有一对多关系。

public void populateOrdersAndCC_XACTSTable()
{
    GregorianCalendar cal;
    String[] credit_cards = {"VISA", "MASTERCARD", "DISCOVER", "AMEX",
            "DINERS" };
    int num_card_types = 5;
    String[] ship_types = {"AIR", "UPS", "FEDEX", "SHIP", "COURIER", "MAIL" };
    int num_ship_types = 6;

    String[] status_types = {"PROCESSING", "SHIPPED", "PENDING", "DENIED" };
    int num_status_types = 4;

    // Order variables
    int O_C_ID;
    java.sql.Timestamp O_DATE;
    double O_SUB_TOTAL;
    double O_TAX;
    double O_TOTAL;
    String O_SHIP_TYPE;
    java.sql.Timestamp O_SHIP_DATE;
    int O_BILL_ADDR_ID, O_SHIP_ADDR_ID;
    String O_STATUS;

    String CX_TYPE;
    int CX_NUM;
    String CX_NAME;
    java.sql.Date CX_EXPIRY;
    String CX_AUTH_ID;
    int CX_CO_ID;

    System.out.println( "Populating ORDERS, ORDER_LINES, CC_XACTS with "
            + NUM_ORDERS + " orders" );

    System.out.print( "Complete (in 10,000's): " );


    for ( int i = 1; i <= NUM_ORDERS; i++ )
    {
        if ( i % 10000 == 0 )
            System.out.print( i / 10000 + " " );

        int num_items = getRandomInt( 1, 5 );
        O_C_ID = getRandomInt( 1, NUM_CUSTOMERS );
        cal = new GregorianCalendar();
        cal.add( Calendar.DAY_OF_YEAR, -1 * getRandomInt( 1, 60 ) );
        O_DATE = new java.sql.Timestamp( cal.getTime().getTime() );
        O_SUB_TOTAL = (double) getRandomInt( 1000, 999999 ) / 100;
        O_TAX = O_SUB_TOTAL * 0.0825;
        O_TOTAL = O_SUB_TOTAL + O_TAX + 3.00 + num_items;
        O_SHIP_TYPE = ship_types[getRandomInt( 0, num_ship_types - 1 )];
        cal.add( Calendar.DAY_OF_YEAR, getRandomInt( 0, 7 ) );
        O_SHIP_DATE = new java.sql.Timestamp( cal.getTime().getTime() );

        O_BILL_ADDR_ID = getRandomInt( 1, 2 * NUM_CUSTOMERS );
        O_SHIP_ADDR_ID = getRandomInt( 1, 2 * NUM_CUSTOMERS );
        O_STATUS = status_types[getRandomInt( 0, num_status_types - 1 )];

        Orders order = new Orders();

        // Set parameter
        order.setCustomer( customerDao.findById( O_C_ID ) );
        order.setODate( new Date( O_DATE.getTime() ) );
        order.setOSubTotal( O_SUB_TOTAL );
        order.setOTax( O_TAX );
        order.setOTotal( O_TOTAL );
        order.setOShipType( O_SHIP_TYPE );
        order.setOShipDate( O_SHIP_DATE );
        order.setAddressByOBillAddrId( addressDao.findById( O_BILL_ADDR_ID ) );
        order.setAddressByOShipAddrId( addressDao.findById( O_SHIP_ADDR_ID ) );
        order.setOStatus( O_STATUS );
        order.setCcXactses( new HashSet<ICcXacts>() );
        order.setOrderLines( new HashSet<IOrderLine>() );
        ordersDao.shrani( order );

        for ( int j = 1; j <= num_items; j++ )
        {
            int OL_ID = j;
            int OL_O_ID = i;
            int OL_I_ID = getRandomInt( 1, NUM_ITEMS );
            int OL_QTY = getRandomInt( 1, 300 );
            double OL_DISCOUNT = (double) getRandomInt( 0, 30 ) / 100;
            String OL_COMMENTS = getRandomAString( 20, 100 );

            OrderLine orderLine = new OrderLine();
            orderLine.setItem( itemDao.findById( OL_I_ID ) );
            orderLine.setOlQty( OL_QTY );
            orderLine.setOlDiscount( OL_DISCOUNT );
            orderLine.setOlComment( OL_COMMENTS );
            orderLine.setOrders( order );

            orderLineDao.shrani( orderLine );
            order.getOrderLines().add( orderLine );

        }

        CX_TYPE = credit_cards[getRandomInt( 0, num_card_types - 1 )];
        CX_NUM = getRandomNString( 16 );
        CX_NAME = getRandomAString( 14, 30 );
        cal = new GregorianCalendar();
        cal.add( Calendar.DAY_OF_YEAR, getRandomInt( 10, 730 ) );
        CX_EXPIRY = new java.sql.Date( cal.getTime().getTime() );
        CX_AUTH_ID = getRandomAString( 15 );
        CX_CO_ID = getRandomInt( 1, 92 );

        CcXacts ccXacts = new CcXacts();

        ccXacts.setCountry( countryDao.findById( CX_CO_ID ) );
        ccXacts.setOrders( order );
        ccXacts.setCxType( CX_TYPE );
        ccXacts.setCxNum( CX_NUM );
        ccXacts.setCxName( CX_NAME );
        ccXacts.setCxExpiry( CX_EXPIRY );
        ccXacts.setCxAuthId( CX_AUTH_ID );
        ccXacts.setCxXactAmt( O_TOTAL );
        ccXacts.setCxXactDate( O_SHIP_DATE );

        ccXacts.setCountry( countryDao.findById( CX_CO_ID ) );

        order.getCcXactses().add( ccXacts );

        ccXactsDao.shrani( ccXacts );

    }

    System.out.println( "" );
}

问题

当我想在 orders 实体中填充大约 250000 个订单时，问题就出现了，因为像我现在那样做它非常慢。用如此多的订单填充数据库需要花费很多时间。有时我也会用完 Java 堆大小并得到 OutOfMemoryException。

您有什么建议可以加快速度并且不至于耗尽 Java 堆大小吗？

Answer 1

1. 您需要activate Hibernate batch support ，因此您需要设置以下 Hibernate 属性:

   properties.put("hibernate.jdbc.batch_size", "50");
   properties.put("hibernate.order_inserts", "true");
   properties.put("hibernate.order_updates", "true");
   properties.put("hibernate.jdbc.batch_versioned_data", "true");
   

2. 为避免 OutOfMemoryError，您需要在批处理准备好刷新时清除当前 Session:

   doInTransaction(session -> {
       int batchSize = batchSize();
       for(int i = 0; i < itemsCount(); i++) {
   
           //batch insert logic
   
           if(i % batchSize == 0 && i > 0) {
               session.flush();
               session.clear();
           }
       }
   });
   

3. 尝试维护一个实体映射，这样您就不必在每个批处理步骤中获取所有这些实体:

   customerDao.findById( O_C_ID );
   countryDao.findById( CX_CO_ID );
   

   您可以在 batch process 的开头获取它们或者至少将结果缓存在本地 map 中。每个选择triggers the current Session to flush (如果您使用自动刷新)，因此会降低批处理性能。