Evaluating distributed database

Contents

Installing cluster on 3 machines

Let's start by installing manager GUI application and .NET provider. Go, to download page, download x64 MSI package and install. During installation, uncheck the "Server" feature. After installation, verify in task manager that scimoredb.exe is not running.

Next, install cluster of 6 nodes on 3 machines. Follow installation instructions here....And, verify the cluster is operational. Open manager, connect to any installed node and execute: 

SELECT * FROM SYSTEM.SYSINSTANCES

The output must be 6 rows, listing all nodes (scimoredb.exe's) in the cluster.

Create database

Now, create the first ScimoreDB distributed database. In the managers' query window , paste the database creation SQL script: 

CREATE DATABASE demo;
GO;
USE demo;
GO;
CREATE TABLE customer
(
c_id BIGINT NOT NULL partition PRIMARY KEY,
c_name CHAR,
c_zip CHAR
)
GO;
CREATE TABLE products
(
p_id BIGINT NOT NULL PRIMARY KEY,
p_name CHAR,
p_price money NOT NULL
)
GO;
CREATE TABLE orders
(
o_id autobigint NOT NULL PRIMARY KEY,
o_c_id BIGINT NOT NULL partition,
o_p_id BIGINT NOT NULL,
o_amount INT,
o_date datetime NOT NULL DEFAULT(getdate()),
o_price money  NOT NULL,
CONSTRAINT fk_customer FOREIGN KEY(o_c_id) REFERENCES customer(c_id),
CONSTRAINT fk_products FOREIGN KEY(o_p_id) REFERENCES products(p_id)
)
GO;
CREATE PROCEDURE create_order (
@c_id BIGINT,
@p_id BIGINT,
@amount INT)
AS 
BEGIN
DECLARE @price money
SET @price = (SELECT p_price*@amount FROM products WHERE p_id = @p_id)
INSERT INTO orders(o_c_id,o_p_id,o_amount,o_price) VALUES (@c_id,@p_id,@amount,@price)
END
GO;
CREATE PROCEDURE cust_orders(
@c_id BIGINT)
AS 
BEGIN
SELECT  COUNT() 'orders', SUM(o_price) 'price' FROM customer JOIN orders ON (c_id = o_c_id)
WHERE c_id = @c_id
GROUP BY c_id
END
GO;
CREATE PROCEDURE popular_products
AS 
BEGIN
SELECT p1.p_id,p1.amount,p2.p_name FROM (
SELECT top 10 p_id, SUM(o_amount) amount  FROM products JOIN orders ON (p_id = o_p_id)
GROUP BY p_id
ORDER BY SUM(o_amount) DESC) p1 JOIN products p2 ON (p1.p_id = p2.p_id)
END

Next, populate the demo database by executing SQL script: 

USE demo;
GO;
BEGIN tran
DECLARE @i INT = 0
while @i < 100000
BEGIN
INSERT INTO customer VALUES(@i,concat('customer-',@i),CAST((rand()*9999) AS INT) + 1)
INSERT INTO products VALUES(@i,concat('product-',@i),CAST((rand()*1000) AS money))
SET @i = @i+1
END
commit

Building stress application

And now is the time to get hands dirty. Create VS2010, .NET40, c# project. In the project, add reference to scimore.data.scimoreclient.dll (the .NET provider usage is identical to SQL Server), start 30 threads (or more threads; hammer db as you can, no mercy, you are evaluating) where each executes

ScimoreCommand.ExecuteReader() using either SQL: 

DECLARE @c_id BIGINT = CAST((rand()*100000) AS INT)
DECLARE @p_id BIGINT = CAST((rand()*100000) AS INT)
DECLARE @amount INT = CAST((rand()*100) AS INT)
EXECUTE demo.create_order @c_id,@p_id,@amount


DECLARE @c_id BIGINT = CAST((rand()*100000) AS INT)
EXEC demo.cust_orders @c_id

We suggest one thread running the reporting SQL. 

EXEC demo.popular_products


Surround ExecuteReader() by try/catch, since, if cluster configuration changes or nodes killed, some temporary errors will appear. Might be good idea to print them to console window.

Finally, run the stress application and gather the stats. The stress application could record queries/sec, or, execute monitor queries in the manager: 

SELECT SUM(tran_sec) FROM system.mtransactions


SELECT SUM(inserts_sec) inserts_sec, SUM(selects_sec) selects_sec, FROM system.mtables

For more options read Monitoring page.

Expanding the cluster

Next, let's expand the cluster. First, create additional 6 stand-by nodes on another 3 machines. Follow the previous example, except don't execute CREATE CLUSTER. Instead, connect with manager to any node in the cluster and execute: 

ALTER CLUSTER
GO
ADD PARTITION GROUP REPARTITION WITH 0 
(      
   ADD endpoint('machine4:16250'),
   ADD endpoint('machine5:16251')
);
ADD PARTITION GROUP REPARTITION WITH 1 
(      
   ADD endpoint('machine5:16250'),
   ADD endpoint('machine6:16251')
);
ADD PARTITION GROUP REPARTITION WITH 2 
(      
   ADD endpoint('machine6:16250'),
   ADD endpoint('machine4:16251')
);
GO;
COMMIT CLUSTER

Of course, before executing cluster alter, start stress application to watch how the cluster change, affects the performance.

Testing failover

While running stress application, shutdown any machine. The stress application will receive a few errors, but after 20 sec, the cluster should heal itself.

Retrieved from "http://bug.scimore.com/wiki/Evaluating_distributed_database"