insert speed in mysql vs cassandra

问题:

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);

回答1:

Disable possible MySQL caching by saying

SELEC SQL_NO_CACHE ...

MySQL checks the PRIMARY KEY integrity with each INSERT/UPDATE. MariaDB at least can disable this for updates, might work in MySQL, too.

回答2:

if you wanna test cassandra, you can simply use cassandra-stress tools, installed with datastax. you can find it in C:\Program Files\DataStax-DDC\apache-cassandra\tools\bin it's a bat file. no need to write even a line of code, simply execute it with desired parameter and benchmark the cassandra.