I have a lot of (about 1 million in second)structural data that must be insert to database I see a lot of benchmark about sql vs noSql and type of Nosql then collect cassandra as database
but I create a benchmark to test mysql vs cassandra in write/update/select speed mysql have better performance in my benchmark, I want to know what is my mistake??
php use as programming language YACassandraPDO and cataloniaframework use as php driver and PDO use as mysql driver
my server is centOS 6.5 with 2 core CPU and 2GB RAM, mysql and cassandra have default configuration
detail of benchmark:
cassandra keyspace and column family structure: CREATE KEYSPACE test2 WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 } AND durable_writes = false;
CREATE TABLE test (
uuid int PRIMARY KEY,
symbol_id int,
bid int,
ask int,
time timestamp,
);
mysql database and table structure:
CREATE DataBase test;
CREATE TABLE `test` (
`id` INT NOT NULL ,
`symbol_id` int,
`bid` int,
`ask` int,
time timestamp,
PRIMARY KEY (id)
)ENGINE=MyISAM;
my result of benchmark:
Insert each 100000 record in cassandra in about 26s, Insert each 100000 record in mysql in about 11s
UPDATE each 100000 in 24s in about cassandra, UPDATE each 100000 in 12s in about mysql
SELECT each 10000 in 741s in about cassandra, SELECT each 10000 in 21s in about mysql
my php code use for benchmark:
cassandra code:
$db_handle = new PDO("$dbtype:host=$dbhost;port=$dbport;cqlversion=3.0.0;dbname=$dbname", $dbuser, $dbpass);
while ($count < $rowNum){
$stmt = $db_handle->prepare("INSERT INTO test (uuid, symbol_id, bid, ask, time) values ($count, " . rand(1, 100) . ", " . rand(1, 10000) . ", ". rand(1, 10000).", dateof(now())); ");
$exec = $stmt->execute();
}
unset($db_handle);
mysql code:
$db_handle = new PDO("$dbtype:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
while ($count < $rowNum){
$stmt = $db_handle->prepare("INSERT INTO test (id, symbol_id, bid, ask, time) values ($count, " . rand(1, 100) . ", " . rand(1, 10000) . ", ". rand(1, 10000).", now()); ");
$exec = $stmt->execute();
}
unset($db_handle);
