Comments (10)
akopytov
commented on June 30, 2024
Should be fixed in 0.4 and 0.5 now, please give them a try.
Re: OLTP tests being removed from the 0.5 branch, they are not, they have been converted to Lua scripts. The new way to run them is --test=tests/db/oltp.lua
from sysbench.
ckujau
commented on June 30, 2024
Unfortunately, it fails again with:
$ sysbench --test=oltp $opt_pgsql --num-threads=8 --max-requests=100000 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
[...]
Maximum number of requests for OLTP test is limited to 100000
Threads started!
FATAL: PQexecPrepared() failed: 7 ERROR: duplicate key value violates unique constraint "sbtest_pkey"
DETAIL: Key (id)=(5010) already exists.
FATAL: database error, exiting...
=== postgresql-9.4-main.log
2015-09-24 18:22:32 PDT [22095-1] osdb@osdb ERROR: duplicate key value violates unique constraint "sbtest_pkey"
2015-09-24 18:22:32 PDT [22095-2] osdb@osdb DETAIL: Key (id)=(5010) already exists.
2015-09-24 18:22:32 PDT [22095-3] osdb@osdb STATEMENT: INSERT INTO sbtest values($1,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')
And with the 0.5 branch:
$ opt_pgsql="--db-driver=pgsql --pgsql-user=osdb --pgsql-password=osdb --pgsql-db=osdb"
$ sysbench --test=tests/db/oltp.lua $opt_pgsql --num-threads=4 --max-requests=100 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored
Threads started!
ALERT: failed to execute query: INSERT INTO sbtest1 (id, k, c, pad) VALUES (4989, 5015, '35836993091-27342427326-12489013023-66691425911-42743808386-04128587050-01331464190-61614637517-49981549401-69519167015', '85508804729-64666211170-32327257822-17348569403-14238271740'):
ALERT: error: 7 ERROR: duplicate key value violates unique constraint "sbtest1_pkey"
DETAIL: Key (id)=(4989) already exists.
=== postgresql-9.4-main.log
2015-09-24 18:25:32 PDT [27567-1] osdb@osdb ERROR: duplicate key value violates unique constraint "sbtest1_pkey"
2015-09-24 18:25:32 PDT [27567-2] osdb@osdb DETAIL: Key (id)=(4989) already exists.
2015-09-24 18:25:32 PDT [27567-3] osdb@osdb STATEMENT: INSERT INTO sbtest1 (id, k, c, pad) VALUES (4989, 5015, '35836993091-27342427326-12489013023-66691425911-42743808386-04128587050-01331464190-61614637517-49981549401-69519167015', '85508804729-64666211170-32327257822-17348569403-14238271740')
from sysbench.
akopytov
commented on June 30, 2024
Apologies for the delayed response. It appears to be a result of the way PostgreSQL implements transaction isolation. The standard leaves a lot of room for implementations to differ, so this is one of those differences between PostgreSQL and MySQL/InnoDB.
More detailed description and explanation of the problem are here: https://dba.stackexchange.com/questions/27688/locking-issue-with-concurrent-delete-insert-in-postgresql
I will commit a workaround for it shortly.
from sysbench.
ziqiDev
commented on June 30, 2024
I use sysbench 0.5 , postgresql 9.4 I want to test the intser.lua ,then i meet this problem
cleanup -> prepare -> run
ALERT: failed to execute PgSQL query: INSERT INTO sbtest1 (id, k, c, pad) VALUES (1883, 4957, '42714243102-25777939982-84033466068-59421561488-34010177591-22961783484-72702449865-64036716253-91482126617-62212647210', '22134431310-80328592981-06536359546-12339975468-55884757695'):
ALERT: error: ERROR: duplicate key value violates unique constraint "sbtest1_pkey"
DETAIL: Key (id)=(1883) already exists.
from sysbench.
akopytov
commented on June 30, 2024
Is that the current sysbench HEAD?
from sysbench.
ziqiDev
commented on June 30, 2024
yes
from sysbench.
akopytov
commented on June 30, 2024
That should be fixed now. Thanks for reporting.
from sysbench.
ckujau
commented on June 30, 2024
With the latest HEAD, this works now:
$ opt_pgsql="--db-driver=pgsql --pgsql-user=osdb --pgsql-password=osdb --pgsql-db=osdb"
$ time sysbench --test=tests/db/oltp.lua $opt_pgsql --num-threads=64 --max-requests=10000 run
sysbench completes after 2 minutes w/o error. But there are still lots of errors in the PostgreSQL log (see below), I guess these are to be expected?
Also, I noticed that the same command on the same (virtual) machine completes in just 24 seconds against a MySQL 5.5.44 server instance. But maybe that's just what a benchmark program is all about - pretty numbers ;-)
Thanks for fixing this!
=== postgresql-9.4-main.log
2015-10-15 16:17:03 PDT [9706-90] osdb@osdb ERROR: deadlock detected
2015-10-15 16:17:03 PDT [9706-91] osdb@osdb DETAIL: Process 9706 waits for ShareLock on transaction 12781; blocked by process 9711.
Process 9711 waits for ShareLock on transaction 12748; blocked by process 9775.
Process 9775 waits for ShareLock on transaction 12822; blocked by process 9777.
Process 9777 waits for ShareLock on transaction 12900; blocked by process 9749.
Process 9749 waits for ShareLock on transaction 12772; blocked by process 9723.
Process 9723 waits for AccessExclusiveLock on tuple (594,58) of relation 16416 of database 16385; blocked by process 9706.
Process 9706: UPDATE sbtest1 SET k=k+1 WHERE id=4974
Process 9711: UPDATE sbtest1 SET c='76516046841-73646306033-89758115475-15772753850-39966731201-76410707069-42417451057-84916417234-77720865255-96925520927' WHERE id=4978
Process 9775: DELETE FROM sbtest1 WHERE id=5000
Process 9777: INSERT INTO sbtest1 (id, k, c, pad) VALUES (4980, 4960, '45587020228-59678652310-70229467830-48026054721-92549480043-48562994587-41084010829-40922007606-64866307575-35447110680', '74906338974-90081969011-15003265327-73488436565-85807788438')
from sysbench.
akopytov
commented on June 30, 2024
Deadlocks are inevitable when multiple connections modify the same data. It applies to both MySQL and PostgreSQL. In both cases sysbench restarts the deadlocked transaction. I'm not sure why PostgreSQL is much slower in this case, i.e. sysbench does nothing special in this regard.
On the other hand MySQL/InnoDB are not affected by the problem discussed at https://dba.stackexchange.com/questions/27688/locking-issue-with-concurrent-delete-insert-in-postgresql
So transaction restarts are more frequent with PostgreSQL. Which may be the reason for such a gap in performance. Another possibility is configuration differences.
from sysbench.
ckujau
commented on June 30, 2024
Configuration for PostgreSQL and MySQL on this system had not be altered, i.e. it's the "default Debian configuration". I've disabled query logging for PostgreSQL now, but execution time is still 2.5 minutes. Incidentally, I've noticed the following messages in the server log (lots of them!) with sysbench-0.5, I haven't seen these before:
2015-10-16 12:30:32 PDT [13498-4] osdb@osdb LOG: could not receive data from client: Connection reset by peer
2015-10-16 12:30:32 PDT [13498-5] osdb@osdb LOG: unexpected EOF on client connection with an open transaction
So yeah, maybe the transaction restarts are causing the performance hit.
from sysbench.
Related Issues (20)
- Memory performance is different with different Linux OS kernel
- Building from source on Amazon Linux for ARM does not work after rel 1.0.20 HOT 6
- Enable the new Discussions tab here on this site. It's like Issues 2.0
- Sysbench 1.0.20 + scripting disk benchmark HOT 1
- segementation faults when preparing data
- sysbench0.5 test oracle
- Create Sysbench 1.1.0 release tag HOT 1
- [BUG]Segmentation fault(core dumped) error when use --fored-shutdown
- [Question] Would running with `--threads=1` and `--rate=N` cause backpressure, if the database can't keep up with the `rate`?
- sysbench MySQL deadlock?
- `sysbench` command usage documentation not provided
- Only support prepare SelectStmt point query now
- Sysbench CPU is absolutley broken. HOT 1
- build error with mysql 8.3 HOT 1
- `--rand-seed` is non-deterministic
- Getting FATAL: `thread_init' function failed: /usr/share/sysbench/oltp_common.lua:275: SQL API error
- Fail to reconnect to mysql/mariadb when running with mariadb-libs
- How to intercept "Illegal instruction" error.
- Postgres Display actual query error
- Fail to reconnect to mysql when running with more than one thread
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sysbench.