Forum: Building VoltDB Applications

Post: Complicate Stored Procedure

Complicate Stored Procedure
ppzhupapa
Oct 6, 2014
Hi all, I'm trying to create a fast concurrent transaction system by using VoltDB. But the performance of my stored procedure can only reach about 180 tps. I think something must be wrong, because I can get about 1200 tps by using MySQL running on SSD with same logic.
Here are definitions of my tables, the logic is a member places an order to book several products, and then we check his balance and the inventory of every product. If all things are ok, we update inventories of every product and also we update the member's balance. For some history reasons, we use a varchar column as an ID column and put a random string in it.
CREATE TABLE MEMBER (
  DB_ID                VARCHAR(60) NOT NULL,
  DB_DESCRIBE     VARCHAR(120) DEFAULT NULL,
  DB_NAME           VARCHAR(60) DEFAULT NULL,
  DB_BALANCE     FLOAT DEFAULT '0.000' NOT NULL,
  PRIMARY KEY (DB_ID)
);
PARTITION TABLE MEMBER ON COLUMN DB_ID;

CREATE TABLE PRODUCT (
  DB_ID                 VARCHAR(60)  NOT NULL,
  DB_DESCRIBE     VARCHAR(120) DEFAULT NULL,
  DB_NAME            VARCHAR(60)  DEFAULT NULL,
  DB_PRICE           FLOAT DEFAULT '0.00' NOT NULL,
  DB_INVENTORY   INT DEFAULT '0' NOT NULL,
  PRIMARY KEY (DB_ID)
);
PARTITION TABLE PRODUCT ON COLUMN DB_ID;

CREATE TABLE ORDER_TOTAL (
  DB_ID                    VARCHAR(60)  NOT NULL,
  DB_MEMBER_ID      VARCHAR(60) NOT NULL,
  DB_ORDER_DATE    TIMESTAMP NOT NULL,
  DB_STATUS            VARCHAR(20) DEFAULT 'WAIT_FOR_PAY' NOT NULL,
  PRIMARY KEY (DB_ID)
);
CREATE INDEX KEY_MEMBER_ID ON ORDER_TOTAL (DB_MEMBER_ID);
PARTITION TABLE ORDER_TOTAL ON COLUMN DB_ID;

CREATE TABLE ORDER_DETAIL (
  DB_ID                        VARCHAR(60)  NOT NULL,
  DB_ORDER_ID            VARCHAR(60) NOT NULL,
  DB_PRODUCT_ID         VARCHAR(60) NOT NULL,
  DB_PRODUCT_COUNT  INT NOT NULL,
  PRIMARY KEY (DB_ORDER_ID, DB_ID)
);
PARTITION TABLE ORDER_DETAIL ON COLUMN DB_ORDER_ID;

CREATE PROCEDURE FROM CLASS DoTransactionSP;

Here are my java stored procedure, I guess it can not be partitioned, am I right? But these logic have to be put in one stored procedure so that they can be done in one transaction.
public class DoTransactionSP extends VoltProcedure {
	
	public final SQLStmt updateMember = new SQLStmt("UPDATE MEMBER SET DB_BALANCE = ? WHERE DB_ID = ?");
	public final SQLStmt updateProduct = new SQLStmt("UPDATE PRODUCT SET DB_INVENTORY = ? WHERE DB_ID = ?");
	public final SQLStmt insertIntoOrderDetail = new SQLStmt("INSERT INTO ORDER_DETAIL (DB_ID, DB_ORDER_ID, DB_PRODUCT_ID, DB_PRODUCT_COUNT) VALUES (?, ?, ?, ?)");
	public final SQLStmt insertIntoOrder = new SQLStmt("INSERT INTO ORDER_TOTAL (DB_ID, DB_MEMBER_ID, DB_ORDER_DATE, DB_STATUS) VALUES (?, ?, NOW, 'WAIT_FOR_PAY')");
	public final SQLStmt productQuery = new SQLStmt("SELECT DB_PRICE, DB_INVENTORY FROM PRODUCT WHERE DB_ID = ? ORDER BY DB_ID LIMIT 1");
	public final SQLStmt memberQuery = new SQLStmt("SELECT DB_BALANCE FROM MEMBER WHERE DB_ID = ? ORDER BY DB_ID LIMIT 1");
			
	public long run(String memberID, String[] productIDs, long[] buyCounts) throws VoltAbortException {
		
		double totalPrice = 0.0;
		long[] newInventories = new long[productIDs.length];
		for (int i = 0; i < productIDs.length; ++i) {

			voltQueueSQL(productQuery, productIDs);
			VoltTable[] queryresults = voltExecuteSQL();
			if (queryresults.length < 1) {
				return 0;
			}
			VoltTable result = queryresults[0];
			if (result.getRowCount() < 1) {
				return 0;
			}
			double price = result.fetchRow(0).getDouble(0);
			long inventory = result.fetchRow(0).getLong(1);
			if (inventory < buyCounts) {
				return 0;
			}
			newInventories = inventory - buyCounts;
			totalPrice += (buyCounts*price);
		}
		voltQueueSQL(memberQuery, memberID);
		VoltTable[] memberQueryresults = voltExecuteSQL();
		if (memberQueryresults.length < 1) {
			return 0;
		}
		VoltTable memberQueryresult = memberQueryresults[0];
		if (memberQueryresult.getRowCount() < 1) {
			return 0;
		}
		double balance = memberQueryresult.fetchRow(0).getDouble(0);
		if (balance < totalPrice) {
			return 0;
		}
		
		double newBalance = balance - totalPrice;
		voltQueueSQL(updateMember, newBalance, memberID);
		String orderID = generateUUID();
		voltQueueSQL(insertIntoOrder, orderID, memberID);
		for (int i = 0; i < productIDs.length; ++i) {
			voltQueueSQL(updateProduct, newInventories, productIDs);
			String orderDetailID = generateUUID();
			voltQueueSQL(insertIntoOrderDetail, orderDetailID, orderID, productIDs, buyCounts);
		}

		return 1;
	}
	
	private static Random rnd = new Random();
	public static String generateUUID() {

		StringBuilder sbResult = new StringBuilder();
		
		for (int i = 0; i < 4; ++i) {
			int r = rnd.nextInt();
			if (r < 0) {
				r = -r;
			}
			sbResult.append(Integer.toString(r, 16).toLowerCase());
		}
		
		return sbResult.toString();
	}
}

I have read some old posts which told me that we can not call another stored procedure in a stored procedure. So how can I improved the performance? Thanks.
ppzhupapa
Oct 6, 2014
I forgot to say. There are 1 million members and also 1 million products in the table.
vtkstef
Oct 6, 2014
Hi,

A few things

1) ORDER_DETAIL is partitioned using a different key
2) one of the primitive parameters has to match the partition key (DB_ID). Sending an array of them won't help VoltDB discern which partition to dispatch the store proc to.
3) don't use random generated values because the proc executed on mirrored partitions will yield different values (use getUniqueID from VoltProcedure instead)
4) need to declare the store proc as a single partition one

PARTITION PROCEDURE DoTransactionSP ON TABLE MEMBER COLUMN DB_ID;

assuming that the first param in DoTransactionSP is the product id

Ciao
Stefano
ppzhupapa
Oct 7, 2014
Thanks for your reply. But for the business logic, I have to update the inventories of several products in just one transaction. And also I have to update the member's balance in the same transaction. So is it possible to put all of these updating in a single partitioned stored procedure? Or we have to implement these logic in a very different way under VoltDB?
vtkstef
Oct 7, 2014
... I have to update the inventories of several products in just one transaction. And also I have to update the member's balance in the same transaction.


your statement describes a multi partition operation. Partitions hold segmented portions of your data, and do not have access to the segments belonging to other partitions. If you want one encompassing transaction the only way I can see it, is that you do it with a multi partition store procedure. Now I believe that there is room for improving your implementation, but if you want fast you need to break it down further into multiple single partition store procedure invocation transactions. Is it possible for you to make your transaction scope more granular?
ppzhupapa
Oct 8, 2014
your statement describes a multi partition operation. Partitions hold segmented portions of your data, and do not have access to the segments belonging to other partitions. If you want one encompassing transaction the only way I can see it, is that you do it with a multi partition store procedure. Now I believe that there is room for improving your implementation, but if you want fast you need to break it down further into multiple single partition store procedure invocation transactions. Is it possible for you to make your transaction scope more granular?


If I split these logic into some simple stored procedure, maybe I only can implement the transaction at application level? Since it has to update the inventory and member's balance at same time. But I doubt that there is no way to improve the performance significantly for such simple logic. Because when I run same logic on MySQL which is running in a ram disk, I also can get about 1900 tps.
vtkstef
Oct 8, 2014
Try the following:


  • set up your deployment to start your database with only one site on one node
  • queue all selects in one batch (first the member one, then the product ones)
  • iterate through all the results tables returned from voltExecuteSQL()
  • batch all the inserts and updates (order, order details, product count updates, member balance)
  • end the procedure with voltExecuteSQL(true) telling volt that it is the last one in the store procedure invocation
ppzhupapa
Oct 9, 2014
Try the following:


  • set up your deployment to start your database with only one site on one node
  • queue all selects in one batch (first the member one, then the product ones)
  • iterate through all the results tables returned from voltExecuteSQL()
  • batch all the inserts and updates (order, order details, product count updates, member balance)
  • end the procedure with voltExecuteSQL(true) telling volt that it is the last one in the store procedure invocation

Thanks a lot for your help. I got about 3~4 times speedup after using your method. And i found that the poor performance is because that I'm running voltdb on a VM. Now I got about 4500 tps after moving to a physical PC. :-D