在mysql vs cassandra中插入速度

2022-01-13 00:00:00 sql benchmarking nosql mysql cassandra-2.0

我有很多(大约一百万秒)必须插入数据库的结构数据我看到很多关于 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 与 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 和 cataloniaframework 用作 php 驱动程序和 PDO 用作 mysql 驱动程序

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

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

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

cassandra 键空间和列族结构:创建密钥空间测试2WITH 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;

我的基准测试结果:

在 cassandra 中插入每 100000 条记录大约需要 26 秒，在mysql中插入每100000条记录大约需要11s

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 个，21秒内SELECT每10000个约mysql

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，你可以简单地使用 cassandra-stress 工具，随 datastax 一起安装.你可以在C:Program FilesDataStax-DDCapache-cassandra oolsin这是一个bat文件.甚至不需要编写一行代码，只需使用所需的参数执行它并对 cassandra 进行基准测试.

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

相关文章