search

Percona-Lab / sysbench-tpcc

Sysbench scripts to generate a tpcc-like workload for MySQL and PostgreSQL
Apache License 2.0
290 stars 104 forks source link

Execution crashes for PostgreSQL with SQL syntax error #25

Open jobinau opened 4 years ago

jobinau commented 4 years ago

Syntactically wrong SQL is fired against PostgreSQL after few hours of execution. here is the extract from the log

... [ 20060s ] thds: 4 tps: 11.00 qps: 360.60 (r/w/o: 165.80/163.90/30.90) lat (ms,99%): 90.78 err/s 0.00 reconn/s: 0.00 [ 20070s ] thds: 4 tps: 8.50 qps: 282.90 (r/w/o: 128.80/128.10/26.00) lat (ms,99%): 48.34 err/s 0.00 reconn/s: 0.00 [ 20080s ] thds: 4 tps: 10.80 qps: 316.10 (r/w/o: 146.20/135.70/34.20) lat (ms,99%): 42.61 err/s 0.00 reconn/s: 0.00 [ 20090s ] thds: 4 tps: 11.60 qps: 342.50 (r/w/o: 157.20/149.50/35.80) lat (ms,99%): 87.56 err/s 0.00 reconn/s: 0.00 [ 20100s ] thds: 4 tps: 9.10 qps: 220.40 (r/w/o: 100.80/96.00/23.60) lat (ms,99%): 34.95 err/s 0.00 reconn/s: 0.00 [ 20110s ] thds: 4 tps: 9.30 qps: 260.40 (r/w/o: 118.90/117.50/24.00) lat (ms,99%): 39.65 err/s 0.10 reconn/s: 0.00 FATAL: PQexec() failed: 7 syntax error at or near "LIMIT" FATAL: failed query was: DELETE FROM history1 where h_w_id=2 AND h_d_id=2 LIMIT 10 FATAL: `thread_run' function failed: /usr/share/sysbench/tpcc_run.lua:785: SQL error, errno = 0, state = '42601': syntax error at or near "LIMIT" ...

A DELETE statement should no have LIMIT clause.

vadimtk commented 4 years ago

@jobinau that DELETE is used only if you use enable_purge , which should not be used in the normal benchmark.

jobinau commented 4 years ago

Thank you @vadimtk for clarifying. We have been using the sysbench for creating a sustained load.

vadimtk commented 4 years ago

@jobinau Anyway, I am surprised you see it only after a few hours and not immediately. Can you suggest a way how to rewrite the DELETE for PostgreSQL ?

jobinau commented 4 years ago

@vadimtk , Currently what we have is a syntax error because of "LIMIT" Is it meaningful to delete all the rows which are satisfying the WHERE condition? Exact same behavior of limited row delete can be achieved by DELETE with sub select : DELETE FROM history1 WHERE ctid IN (SELECT ctid FROM history1 where h_w_id=2 AND h_d_id=2 LIMIT 10);