-
Language:
English
-
Language:
English
4.5.3. Batch Inserts
INSERT INTO EJB3.OrderLine (OrderId, LineNumber, ProductId, Quantity, Price, ExtendedPrice) VALUES("67ef590kalk4568901thbn7190akioe1", 1, 25, 10, 1.00, 10.00); INSERT INTO EJB3.OrderLine (OrderId, LineNumber, ProductId, Quantity, Price, ExtendedPrice) VALUES("67ef590kalk4568901thbn7190akioe1", 2, 16, 1, 1.59, 1.59); INSERT INTO EJB3.OrderLine (OrderId, LineNumber, ProductId, Quantity, Price, ExtendedPrice) VALUES("67ef590kalk4568901thbn7190akioe1", 3, 55, 5, 25.00, 125.00); INSERT INTO EJB3.OrderLine (OrderId, LineNumber, ProductId, Quantity, Price, ExtendedPrice) VALUES("67ef590kalk4568901thbn7190akioe1", 4, 109, 1, 29.98, 29.98);
INSERT INTO EJB3.OrderLine (OrderId, LineNumber, ProductId, Quantity, Price, ExtendedPrice) VALUES("67ef590kalk4568901thbn7190akioe1", 1, 25, 10, 1.00, 10.00) , ("67ef590kalk4568901thbn7190akioe1", 2, 16, 1, 1.59, 1.59) , ("67ef590kalk4568901thbn7190akioe1", 3, 55, 5, 25.00, 125.00) , ("67ef590kalk4568901thbn7190akioe1", 4, 109, 1, 29.98, 29.98);
Note
@TransactionTimeout(4800) public void createInventories(int batchSize) throws CreateDataException { if(numberOfInventoryRecords() > 0) { throw new CreateDataException("Inventory already exists!"); } int rowsToFlush = 0, totalRows = 0; Random quantity = new Random(System.currentTimeMillis()); List<Product> products = productManager.findAllProducts(); List<DistributionCenter> distributionCenters = distributionCenterManager.findAllDistributionCenters(); InventoryPK inventoryPk = null; Inventory inventory = null; for(Product product: products) { for(DistributionCenter distributionCenter: distributionCenters) { inventoryPk = new InventoryPK(); inventory = new Inventory(); inventoryPk.setProductId(product.getProductId()); inventoryPk.setDistributionCenterId(distributionCenter.getDistributionCenterId()); inventory.setPrimaryKey(inventoryPk); inventory.setQuantityOnHand(quantity.nextInt(25000)); inventory.setBackorderQuantity(0); inventory.setVersion(1); batchEntityManager.persist(inventory); rowsToFlush++; totalRows++; if(rowsToFlush == batchSize) { batchEntityManager.flush(); rowsToFlush = 0; batchEntityManager.clear(); if(log.isTraceEnabled()) { log.trace("Just flushed " + batchSize + " rows to the database."); log.trace("Total rows flushed is " + totalRows); } } } } return; }
persistence.xml
for the persistence unit of the application entities.
<persistence-unit name="batch-services" transaction-type="JTA"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <jta-data-source>java:/MySqlDS</jta-data-source> <properties> <property name="hibernate.hbm2ddl.auto" value="none"/> <property name="hibernate.default_catalog" value="EJB3"/> <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/> <property name="hibernate.order_updates" value="true"/> <property name="hibernate.order_inserts" value="true"/> <property name="hibernate.jdbc.batch_versioned_data" value="true"/> <property name="hibernate.jdbc.fetch_size" value="50000"/> <property name="hibernate.jdbc.batch_size" value="50000"/> <property name="hibernate.default_batch_fetch_size" value="50000"/> <property name="hibernate.connection.release_mode" value="auto"/> </properties> </persistence-unit>
public Order createOrder(Customer customer , List<OrderLine> orderLines , BigDecimal totalOrderAmount) { String addressLine2 = customer.getAddressLine2(); if (addressLine2 == null) { addressLine2 = ""; } Address shippingAddress = new Address(); shippingAddress.setAddressLine1(customer.getAddressLine1()); shippingAddress.setAddressLine2(addressLine2); shippingAddress.setCity(customer.getCity()); shippingAddress.setState(customer.getState()); shippingAddress.setZipCode(customer.getZipCode()); shippingAddress.setZipCodePlusFour(customer.getZipCodePlusFour()); Order newOrder = new Order(); newOrder.setCustomerId(customer.getCustomerId()); newOrder.setDistributionCenterId(customer.getDistributionCenterId()); newOrder.setShippingAddressLine1(shippingAddress.getAddressLine1()); newOrder.setShippingAddressLine2(shippingAddress.getAddressLine2()); newOrder.setShippingCity(shippingAddress.getCity()); newOrder.setShippingState(shippingAddress.getState()); newOrder.setShippingZipCode(shippingAddress.getZipCode()); newOrder.setShippingZipCodePlusFour(shippingAddress.getZipCodePlusFour()); newOrder.setTotalOrderAmount(totalOrderAmount); newOrder.setOrderDate(new Date()); newOrder.setCustomer(customer); entityManager.persist(newOrder); String orderId = newOrder.getOrderId(); for (OrderLine orderLine: orderLines) { orderLine.getOrderLinePK().setOrderId(orderId); } newOrder.setOrderLines(orderLines); entityManager.persist(newOrder); return newOrder; }
<persistence-unit name="services" transaction-type="JTA"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <jta-data-source>java:/MySqlDS</jta-data-source> <properties> <property name="hibernate.hbm2ddl.auto" value="none"/> <property name="hibernate.default_catalog" value="EJB3"/> <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/> <property name="hibernate.order_updates" value="true"/> <property name="hibernate.order_inserts" value="true"/> <property name="hibernate.jdbc.batch_versioned_data" value="true"/> <property name="hibernate.jdbc.fetch_size" value="500"/> <property name="hibernate.jdbc.batch_size" value="500"/> <property name="hibernate.default_batch_fetch_size" value="16"/> <property name="hibernate.connection.release_mode" value="auto"/> <property name="hibernate.cache.region.factory_class" value="org.hibernate.cache.jbc2.JndiMultiplexedJBossCacheRegionFactory"/> <property name="hibernate.cache.region.jbc2.cachefactory" value="java:CacheManager"/> <property name="hibernate.cache.use_second_level_cache" value="true"/> <property name="hibernate.cache.use_query_cache" value="false"/> <property name="hibernate.cache.use_minimal_puts" value="true"/> <property name="hibernate.cache.region.jbc2.cfg.entity" value="mvcc-entity"/> <property name="hibernate.cache.region_prefix" value="services"/> </properties> </persistence-unit>
true
, and you can do this through setting the connection property in the data source XML file for MySQL as such:
<datasources> <local-tx-datasource> <jndi-name>MySqlDS</jndi-name> <connection-url>jdbc:mysql://localhost:3306/EJB3</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>username</user-name> <password>password</password> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name> <min-pool-size>400</min-pool-size> <max-pool-size>450</max-pool-size> <!-- The transaction isolation level must be read committed for optimitic locking to work properly --> <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation> <connection-property name="rewriteBatchedStatements">true</connection-property> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) --> <metadata> <type-mapping>mySQL</type-mapping> </metadata> </local-tx-datasource> </datasources>
Warning
4.5.3.1. Batch Processing; Tests And Results
Example 4.1. Batch Processing Test
Test Parameters
- Environment:
- Quad-core Intel i7
- 8 GB of RAM
- MySQL database
- JBoss Enterprise Application Platform
production
profile.
- Table Contents:
- 41,106 rows
- 250,000 products
- 6.25 million inventory records
- 2 thousand suppliers
- 1.25 million supplier inventory records
- 2 million rows for customers and pricing methods.
- Batching enabled in Hibernate.
- Batch insert size of 10,000 rows.
- MySQL Batching refers to the
rewriteBatchedStatements
parameter (which is set inmysql-ds.xml
).
Table 4.1. Batch Processing Test Results
MySQL Batching | Throughput (rows per second) |
---|---|
Result | 487.7% increase in throughput. |
Off | 6,734.01 |
On | 39,575.75 |
Example 4.2. OLTP Baseline Testing
Test Parameters
- Environment:
- Quad-core Intel i7
- 8 GB of RAM
- MySQL database
- JBoss Enterprise Application Platform
production
profile.
- The composite benchmark is run with randomly created orders with 1 to 9 order line items. The average is around 4.5 to 5 order line items per order. This mimics a real world scenario.
- Batch processing off.
rewriteBatchedStatements
parameter unset inmysql-ds.xml
.
Table 4.2. OLTP Baseline Results
Order Inserts | Throughput (orders per second). |
---|---|
Result | Single item orders have the highest throughput, with five and nine following respectively. |
10,000 order inserts with a single order line item | 45.80 |
10,000 order inserts with five order line items | 43.96 |
10,000 order inserts with nine order line items | 43.14 |
Example 4.3. OLTP Hibernate Batch Processing Test
Test Parameters
- Environment:
- Quad-core Intel i7
- 8 GB of RAM
- MySQL database
- JBoss Enterprise Application Platform
production
profile.
- The composite benchmark is run with randomly created orders with 1 to 9 order line items. The average is around 4.5 to 5 order line items per order. This mimics a real world scenario.
- Hibernate batch processing on.
- MySQL batching (
rewriteBatchedStatements
) unset inmysql-ds.xml
.
Table 4.3. OLTP Hibernate Batch Processing Results
Order Inserts | Throughput (orders per second). |
---|---|
Result | Ordering the inserts lowered throughput slightly; differences range from .24 to .36 orders per second. |
10,000 order inserts with a single order line item | 45.64 |
10,000 order inserts with five order line items | 43.62 |
10,000 order inserts with nine order line items | 42.78 |
Example 4.4. OLTP Hibernate and MySQL Batch Processing Test
Test Parameters
- Environment:
- Quad-core Intel i7
- 8 GB of RAM
- MySQL database
- JBoss Enterprise Application Platform
production
profile.
- The composite benchmark is run with randomly created orders with 1 to 9 order line items. The average is around 4.5 to 5 order line items per order. This mimics a real world scenario.
- Hibernate batch processing on.
- MySQL batching (
rewriteBatchedStatements
) on inmysql-ds.xml
.
Table 4.4. OLTP Hibernate and MySQL Batch Processing Results
Order Inserts | Throughput (orders per second). |
---|---|
Result |
An increase in throughput ranging from .21 to .63 orders per second, with nine line item orders showing the highest improvement.
Five line item orders show a .66% improvement over the baseline and nine line item orders show a 1.46% improvement.
|
10,000 order inserts with a single order line item | 46.11 |
10,000 order inserts with five order line items | 44.25 |
10,000 order inserts with nine order line items | 43.77 |
Example 4.5. Higher Line Items Baseline Test
Test Parameters
- Environment:
- Quad-core Intel i7
- 8 GB of RAM
- MySQL database
- JBoss Enterprise Application Platform
production
profile.
- The composite benchmark is run with randomly created orders with 1 to 9 order line items. The average is around 4.5 to 5 order line items per order. This mimics a real world scenario.
- Hibernate batch processing off.
- MySQL batching (
rewriteBatchedStatements
) unset inmysql-ds.xml
.
Table 4.5. Higher Line Items Baseline Results
Order Inserts | Throughput (orders per second). |
---|---|
Result | Because 15 and 20 line items orders take much longer, the server can process even more single line item orders. |
10,000 order inserts with a single order line item | 53.34 |
10,000 order inserts with 15 order line items | 31.25 |
10,000 order inserts with 20 order line items | 30.09 |
Example 4.6. Higher Line Items Hibernate Batch Processing Test
Test Parameters
- Environment:
- Quad-core Intel i7
- 8 GB of RAM
- MySQL database
- JBoss Enterprise Application Platform
production
profile.
- The composite benchmark is run with randomly created orders with 1 to 9 order line items. The average is around 4.5 to 5 order line items per order. This mimics a real world scenario.
- Hibernate batch processing on.
- MySQL batching (
rewriteBatchedStatements
) unset inmysql-ds.xml
.
Table 4.6. Higher Line Items Hibernate Batch Processing Results
Order Inserts | Throughput (orders per second). |
---|---|
Result |
Single line item orders showed a 2.79% increase (an extra 1.49 orders per second).
15 line item orders increased by .06 orders per second and the twenty line item orders decreased by .03 orders per second. These results are within the expected margin of test variations.
|
10,000 order inserts with a single order line item | 54.83 |
10,000 order inserts with fifteen order line items | 31.31 |
10,000 order inserts with twenty order line items | 30.06 |
Example 4.7. Higher Line Items Hibernate And MySQL Batch Processing Test
Test Parameters
- Environment:
- Quad-core Intel i7
- 8 GB of RAM
- MySQL database
- JBoss Enterprise Application Platform
production
profile.
- The composite benchmark is run with randomly created orders with 1 to 9 order line items. The average is around 4.5 to 5 order line items per order. This mimics a real world scenario.
- Hibernate batch processing on.
- MySQL batching (
rewriteBatchedStatements
) on inmysql-ds.xml
.
Table 4.7. Higher Line Items Hibernate And MySQL Batch Processing Results
Order Inserts | Throughput (orders per second). |
---|---|
Result |
Single line orders showed a 13.97% increase, 15 line item orders showed an increase of 19.65% and 20 line item orders increased by 18.05%.
|
10,000 order inserts with a single order line item | 60.79 |
10,000 order inserts with fifteen order line items | 37.39 |
10,000 order inserts with twenty order line items | 35.52 |