Skip to content

Instantly share code, notes, and snippets.

@cruppstahl

cruppstahl/README.txt

Last active September 28, 2016 08:40
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save cruppstahl/7adae9c4dba2239641397dd50d21a3b7 to your computer and use it in GitHub Desktop.
Save cruppstahl/7adae9c4dba2239641397dd50d21a3b7 to your computer and use it in GitHub Desktop.
Download ZIP
upscaledb-mysql 0.0.1: initial benchmarks
Raw
README.txt
These files were used to create the benchmarks for the release of upscaledb-mysql 0.0.1.
=== sysbench
Use sysbench 1.0. The shell script loads and runs the tests.
I ran various tests - with and without transactions, with LOCK TABLE etc. Results are in sysbench-results.txt.
=== uqi1
This script performs a `SELECT COUNT(*) FROM table`. The table was from TPC-C order_line with 6 million rows.
The results for InnoDB and the upscaledb storage engine for MySQL were retrieved using the mysql console.
=== Wordpress
I used the siege tool for benchmarking wordpress. Results and benchmark commands are in siege-ups.txt (for upscaledb)
and siege-idb.txt (for InnoDB).
The wordpress database had five blog posts and a few comments.
Raw
siege-idb.txt
========== main page ====================================================
[chris@wintermute ~]$ siege -b -c 30 -t 30S http://localhost/wordpress
** SIEGE 3.0.8
** Preparing 30 concurrent users for battle.
The server is now under siege...
Lifting the server siege... done.
Transactions: 5648 hits
Availability: 100.00 %
Elapsed time: 29.89 secs
Data transferred: 12.01 MB
Response time: 0.16 secs
Transaction rate: 188.96 trans/sec
Throughput: 0.40 MB/sec
Concurrency: 29.84
Successful transactions: 5676
Failed transactions: 0
Longest transaction: 0.65
Shortest transaction: 0.00
========== page view ====================================================
[chris@wintermute ~]$ siege -b -c 30 -t 30S
http://localhost/wordpress/index.php/2016/09/19/post-4/
** SIEGE 3.0.8
** Preparing 30 concurrent users for battle.
The server is now under siege...
Lifting the server siege... done.
Transactions: 4002 hits
Availability: 100.00 %
Elapsed time: 29.58 secs
Data transferred: 16.19 MB
Response time: 0.22 secs
Transaction rate: 135.29 trans/sec
Throughput: 0.55 MB/sec
Concurrency: 29.86
Successful transactions: 4002
Failed transactions: 0
Longest transaction: 0.47
Shortest transaction: 0.05
Raw
siege-ups.txt
========== main page ====================================================
[chris@wintermute ~]$ siege -b -c 30 -t 30S http://localhost/wordpress
** SIEGE 3.0.8
** Preparing 30 concurrent users for battle.
The server is now under siege...
Lifting the server siege... done.
Transactions: 5604 hits
Availability: 100.00 %
Elapsed time: 29.38 secs
Data transferred: 11.82 MB
Response time: 0.16 secs
Transaction rate: 190.74 trans/sec
Throughput: 0.40 MB/sec
Concurrency: 29.84
Successful transactions: 5634
Failed transactions: 0
Longest transaction: 0.56
Shortest transaction: 0.00
========== page view ====================================================
[chris@wintermute ~]$ siege -b -c 30 -t 30S http://localhost/wordpress/index.php/2016/09/19/post-4/
** SIEGE 3.0.8
** Preparing 30 concurrent users for battle.
The server is now under siege...
Lifting the server siege... done.
Transactions: 4094 hits
Availability: 100.00 %
Elapsed time: 29.52 secs
Data transferred: 16.47 MB
Response time: 0.22 secs
Transaction rate: 138.69 trans/sec
Throughput: 0.56 MB/sec
Concurrency: 29.88
Successful transactions: 4094
Failed transactions: 0
Longest transaction: 0.36
Shortest transaction: 0.05
Raw
sysbench-results.txt
chris@home~$ uname -a
Linux home 4.2.0-38-generic #45-Ubuntu SMP Wed Jun 8 21:21:49 UTC 2016 x86_64
x86_64 x86_64 GNU/Linux
========================================================================
========================================================================
========================================================================
with transactions - unfair b/c upscaledb doesn't implement them
innodb ----------------------------------------------------
Database "testidb" dropped
sysbench 1.0: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
2.17user 0.04system 0:18.30elapsed 12%CPU (0avgtext+0avgdata 5328maxresident)k
776inputs+0outputs (2major+426minor)pagefaults 0swaps
sysbench 1.0: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
OLTP test statistics:
queries performed:
read: 80290
write: 22940
other: 11470
total: 114700
transactions: 5735 (95.57 per sec.)
read/write requests: 103230 (1720.34 per sec.)
other operations: 11470 (191.15 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0055s
total number of events: 5735
total time taken by event execution: 59.9957s
response time:
min: 3.70ms
avg: 10.46ms
max: 71.32ms
approx. 95 percentile: 16.73ms
Threads fairness:
events (avg/stddev): 5735.0000/0.00
execution time (avg/stddev): 59.9957/0.00
upscaledb -------------------------------------------------
mysqladmin: DROP DATABASE test failed;
error: 'Error dropping database (can't rmdir './test/', errno: 17)'
mysqladmin: CREATE DATABASE failed; error: 'Can't create database 'test';
database exists'
sysbench 1.0: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
2.02user 0.02system 0:50.82elapsed 4%CPU (0avgtext+0avgdata 5236maxresident)k
0inputs+0outputs (0major+422minor)pagefaults 0swaps
sysbench 1.0: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
OLTP test statistics:
queries performed:
read: 528374
write: 150964
other: 75482
total: 754820
transactions: 37741 (629.01 per sec.)
read/write requests: 679338 (11322.16 per sec.)
other operations: 75482 (1258.02 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0007s
total number of events: 37741
total time taken by event execution: 59.9660s
response time:
min: 1.05ms
avg: 1.59ms
max: 9.15ms
approx. 95 percentile: 3.75ms
Threads fairness:
events (avg/stddev): 37741.0000/0.00
execution time (avg/stddev): 59.9660/0.00
========================================================================
========================================================================
========================================================================
same tests w/o transactions (oltp_skip_trx = 'on') - only read/write
innodb ----------------------------------------------------
Database "testidb" dropped
sysbench 1.0: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
2.17user 0.04system 0:17.69elapsed 12%CPU (0avgtext+0avgdata 5236maxresident)k
0inputs+0outputs (0major+427minor)pagefaults 0swaps
sysbench 1.0: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
OLTP test statistics:
queries performed:
read: 22274
write: 6364
other: 0
total: 28638
transactions: 0 (0.00 per sec.)
read/write requests: 28638 (477.25 per sec.)
other operations: 0 (0.00 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0058s
total number of events: 1591
total time taken by event execution: 59.9990s
response time:
min: 22.75ms
avg: 37.71ms
max: 101.84ms
approx. 95 percentile: 54.81ms
Threads fairness:
events (avg/stddev): 1591.0000/0.00
execution time (avg/stddev): 59.9990/0.00
upscaledb -------------------------------------------------
mysqladmin: DROP DATABASE test failed;
error: 'Error dropping database (can't rmdir './test/', errno: 17)'
mysqladmin: CREATE DATABASE failed; error: 'Can't create database 'test';
database exists'
sysbench 1.0: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
2.07user 0.04system 0:53.79elapsed 3%CPU (0avgtext+0avgdata 5328maxresident)k
0inputs+0outputs (0major+420minor)pagefaults 0swaps
sysbench 1.0: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
OLTP test statistics:
queries performed:
read: 524748
write: 149928
other: 0
total: 674676
transactions: 0 (0.00 per sec.)
read/write requests: 674676 (11244.40 per sec.)
other operations: 0 (0.00 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0011s
total number of events: 37482
total time taken by event execution: 59.9594s
response time:
min: 1.03ms
avg: 1.60ms
max: 151.81ms
approx. 95 percentile: 3.63ms
Threads fairness:
events (avg/stddev): 37482.0000/0.00
execution time (avg/stddev): 59.9594/0.00
========================================================================
========================================================================
========================================================================
with LOCK TABLE
innodb ----------------------------------------------------
Database "testidb" dropped
sysbench 1.0: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
2.16user 0.03system 0:18.05elapsed 12%CPU (0avgtext+0avgdata 5368maxresident)k
0inputs+0outputs (0major+422minor)pagefaults 0swaps
sysbench 1.0: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
OLTP test statistics:
queries performed:
read: 21826
write: 6236
other: 3118
total: 31180
transactions: 1559 (25.97 per sec.)
read/write requests: 28062 (467.52 per sec.)
other operations: 3118 (51.95 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0230s
total number of events: 1559
total time taken by event execution: 60.0182s
response time:
min: 26.85ms
avg: 38.50ms
max: 99.85ms
approx. 95 percentile: 57.02ms
Threads fairness:
events (avg/stddev): 1559.0000/0.00
execution time (avg/stddev): 60.0182/0.00
upscaledb -------------------------------------------------
mysqladmin: DROP DATABASE test failed;
error: 'Error dropping database (can't rmdir './test/', errno: 17)'
mysqladmin: CREATE DATABASE failed; error: 'Can't create database 'test';
database exists'
sysbench 1.0: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
2.01user 0.03system 0:51.15elapsed 4%CPU (0avgtext+0avgdata 5368maxresident)k
0inputs+0outputs (0major+426minor)pagefaults 0swaps
sysbench 1.0: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
OLTP test statistics:
queries performed:
read: 536872
write: 153392
other: 76696
total: 766960
transactions: 38348 (639.12 per sec.)
read/write requests: 690264 (11504.23 per sec.)
other operations: 76696 (1278.25 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0009s
total number of events: 38348
total time taken by event execution: 59.9621s
response time:
min: 1.02ms
avg: 1.56ms
max: 6.91ms
approx. 95 percentile: 3.65ms
Threads fairness:
events (avg/stddev): 38348.0000/0.00
execution time (avg/stddev): 59.9621/0.00
Raw
sysbench.sh
OPTIONS="--max-time=60 --max-requests=1000000"
SIZE=1000000
echo "innodb ----------------------------------------------------"
/usr/local/mysql/bin/mysqladmin -f drop testidb --user=root
/usr/local/mysql/bin/mysqladmin -f create testidb --user=root
LD_LIBRARY_PATH=/usr/local/mysql/lib time ./sysbench --test=tests/db/oltp.lua --mysql-socket=/tmp/mysql.sock --oltp-table-size=$SIZE --mysql-db=testidb --mysql-user=root --mysql-password= --mysql-table-engine=innodb prepare
LD_LIBRARY_PATH=/usr/local/mysql/lib ./sysbench --test=tests/db/oltp.lua --mysql-socket=/tmp/mysql.sock --oltp-table-size=$SIZE --mysql-db=testidb --mysql-user=root --mysql-password= --mysql-table-engine=innodb $OPTIONS run
echo "upscaledb -------------------------------------------------"
/usr/local/mysql/bin/mysqladmin -f drop test --user=root
/usr/local/mysql/bin/mysqladmin -f create test --user=root
LD_LIBRARY_PATH=/usr/local/mysql/lib time ./sysbench --test=tests/db/oltp.lua --mysql-socket=/tmp/mysql.sock --oltp-table-size=$SIZE --mysql-db=test --mysql-user=root --mysql-password= --mysql-table-engine=upscaledb prepare
LD_LIBRARY_PATH=/usr/local/mysql/lib ./sysbench --test=tests/db/oltp.lua --mysql-socket=/tmp/mysql.sock --oltp-table-size=$SIZE --mysql-db=test --mysql-user=root --mysql-password= --mysql-table-engine=upscaledb $OPTIONS run
Raw
uqi1.c
/*
* Copyright (C) 2005-2016 Christoph Rupp (chris@crupp.de).
* All Rights Reserved.
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* See the file COPYING for License information.
*/
/**
* This example demonstrates a few simple UQI queries.
*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h> /* for exit() */
#include <sys/time.h>
#include <ups/upscaledb.h>
#include <ups/upscaledb_uqi.h>
#define DATABASE_NAME 1
void
error(const char *foo, ups_status_t st)
{
printf("%s() returned error %d: %s\n", foo, st, ups_strerror(st));
exit(-1);
}
int
main(int argc, char **argv)
{
ups_status_t st; /* status variable */
ups_env_t *env; /* upscaledb environment object */
/* First create a new upscaledb Environment */
st = ups_env_open(&env, "ups://localhost:54123/tpcc1000u/order_line", 0, 0);
if (st != UPS_SUCCESS)
error("ups_env_create", st);
uqi_result_t *result;
ups_key_t key = {0};
ups_record_t record = {0};
/* SELECT COUNT(*) FROM $table */
struct timeval t1, t2;
gettimeofday(&t1, 0);
st = uqi_select(env, "COUNT($key) FROM DATABASE 2", &result);
gettimeofday(&t2, 0);
printf("elapsed: %.3g seconds\n", t2.tv_sec - t1.tv_sec + 1e-6 * (t2.tv_usec - t1.tv_usec));
if (st != UPS_SUCCESS || !result)
error("uqi_select", st);
uqi_result_get_key(result, 0, &key);
uqi_result_get_record(result, 0, &record);
printf("COUNT($key): %u\n", *(uint32_t *)record.data);
uqi_result_close(result);
/* we're done! close the handles. UPS_AUTO_CLEANUP will also close the
* 'db' handle */
st = ups_env_close(env, UPS_AUTO_CLEANUP);
if (st != UPS_SUCCESS)
error("ups_env_close", st);
printf("success!\n");
return 0;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment