在mysql和cassandra中插入速度 [英] insert speed in mysql vs cassandra

问题描述

我有很多(每秒约一百万)结构数据必须插入数据库 我看到很多关于sql vs noSql和Nosql类型的基准，然后将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

但是我创建了一个基准来测试mysql vs cassandra的写入/更新/选择速度 mysql在我的基准测试中具有更好的性能，我想知道我的错误是什么?

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用作编程语言 YACassandraPDO 和

php use as programming language YACassandraPDO and cataloniaframework use as php driver and PDO use as mysql driver

我的服务器是具有2核心CPU和2GB RAM的centOS 6.5， mysql和cassandra具有默认配置

my server is centOS 6.5 with 2 core CPU and 2GB RAM, mysql and cassandra have default configuration

cassandra键空间和列族结构: 创建键盘空间test2 WITH REPLICATION = {'class':'SimpleStrategy'，'replication_factor':1} AND持久性写入=否；

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数据库和表结构: 创建数据库test;

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;

我的基准测试结果

在大约26秒内将每100000条记录插入cassandra中， 在大约11秒内将每条100000条记录插入mysql中

my result of benchmark:

Insert each 100000 record in cassandra in about 26s, Insert each 100000 record in mysql in about 11s

在大约cassandra的24秒内更新每个100000， 在大约mysql的12秒内更新每个100000

UPDATE each 100000 in 24s in about cassandra, UPDATE each 100000 in 12s in about mysql

在大约cassandra的741秒中选择每个10000， 在21s左右的mysql中选择每个10000

SELECT each 10000 in 741s in about cassandra, SELECT each 10000 in 21s in about mysql

cassandra代码:

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代码:

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

推荐答案

如果要测试cassandra，则可以简单地使用随datastax一起安装的cassandra-stress工具. 你可以在里面找到它 C:\ Program Files \ DataStax-DDC \ apache-cassandra \ tools \ bin 这是一个蝙蝠文件. 无需编写任何代码，只需使用所需的参数执行它并对cassandra进行基准测试即可.

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.

这篇关于在mysql和cassandra中插入速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！